Lookup list that does not include the current record

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • In Progress
I have a table named 'Contacts'. I have another table named 'Services' with a one to many relationship between the two (one contact to many services). In the 'Services' table I have a field 'Referred by' which I want to be a lookup of the Names in the 'Contacts' table excluding the Name of Contact that is receiving the Service. Being a newbie I am hoping I am missing something simple.
Photo of Don Sloane

Don Sloane

  • 150 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,904 Points 50k badge 2x thumb
I’m not understanding. One Contact has Many Services.

What field type is Names in Contacts? Is it one field or multiple fields. Maybe give an example so I can understand.
Photo of Don Sloane

Don Sloane

  • 150 Points 100 badge 2x thumb
Thanks for your question, sorry I wasn't more clear.

So I have the table Contacts with the field 'Name' which is a Formula - Text (Formula is simply First and Last name fields concatenated [First Name]& " " & [Last Name]). Each name can have multiple services associated with it. For example...

Names

Fred Smith
Mary Jones
John Brooks
Betty Boop
Sally Sue

Services

Service 1
Service 2
Service 3
Service 4

Fred Smith can have a separate record for having received Service 1 and Service 2 (This is the one to many).

With each service I need to track who the referral came from and once done with my service who I am referring them on to - Referred From and Referred To respectively. The source of the two referred fields are the list of names in my contact list. I ma trying to accomplish two things; have the list not include the name of the record that is receiving the service - so when creating a a new Servie record for Fred Smith I don't want his name in the list of options and secondly, to have a filter for the list that is base on another field in Contacts, 'Category'. In other words I want the list to show all names, other than Fred's that have a field 'Category' value of "Referent".

Hope this clarifies and again, thanks!


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,904 Points 50k badge 2x thumb
I believe that you are saying that you are trying to limit the choices available in the drop down list. 

To answer the second part 

secondly, to have a filter for the list that is base on another field in Contacts, 'Category'. In other words I want the list to show all names, other than Fred's that have a field 'Category' value of "Referent".

On the form where you have the drop down list, it may say that the list is coming from either a report or more likely the default record picker.  make a report of Contacts which is filtered based on Type = Referent.

Then set the form properties to use that report for the drop down list. 

There is not an easy way to not show "Fred" in your example.
You'd probably be best served with another approach, for example you could use form rules to display a warning message to stop the user saving the Add Service form if Related Contact and Referred From are the same.