How can I use a lookup field if a client is on a team, but not otherwise?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

My team just got started using Quickbase to track coaching support.  Our Quickbase Users are the Coaches within our organization.  The way coaching is structured is as follows: some clients receive coaching as part of a Team of Clients, and other Clients receive individual coaching.

So far, we have successfully set up tables of Teams and Clients.  (The relationship is that one Team may have many Clients.)  When a Coach is assigned to a Team, then that Coach is also assigned to all Clients on that Team via a lookup field.  So far so good.

Now, I would also like to be able to create Client records for everyone who is not on a Team.  I would like to indicate the Coach for each of these Clients, if possible, in a way that is consistent with how the Coach name appears in the lookup field for Clients on Teams.  As things currently stand, I can't enter a Coach name into the Lookup field that draws from Teams -- which I know is a good thing, but that I'd also like to work around it not, for my non-team-affiliated Clients.  

So, what's my best work around?  And, thanks!

Photo of James

James

  • 0 Points

Posted 4 years ago

  • 0
  • 1
I think that there is an easy solution.

You have a relationship where 1 Coach has many Teams. Then you have another Relationships where 1 Team has many Clients and a lookup field there from the Team down to the clients for Coach.  Rename that Lookup field to be called Team Coach.

Then make a new direct Relationship where 1 Coach has many Clients. Do a lookup of the Coach and call it Individual Coach

Then what you might want to do on the Client record is to make a combined field like this:



List("\n",

IF([Team Coach]<>"","Team Coach: " & [Team Coach]),

IF([Individual Coach]<>"","Individual Coach: " & [Individual Coach]))

I am assuming in the above example that the [Team Coach] and [Individual Coach] fields are text field types, not user field types.



The "\n" as a delimiter for the List function will make them list vertically if there are clients who have both a Team Coach and an individual Coach.