Forum Discussion

DonSloane's avatar
DonSloane
Qrew Trainee
7 years ago

Lookup list that does not include the current record

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.
  • 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.
  • 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!


  • 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.