If you have a unique employee id that is imported with the data, there's an update matching records option for import records that you may wish to investigate. This might make it possible to import your data nightly into a unified contacts table. Just make a back up before each nightly import.
To prune your pop up list length down to size, consider using a conditional value list that restricts the number of listed employees listed from the entire table. A conditional value list, could list only those employees that are labeled as "gatekeepers" for example.
Having never used conditional value lists I did some research and it seems that the "condition" is based on a related table (at least from all the examples I have found). How do I set the condition on the value of say a gatekeeper field being "Yes"?
That's one of two methods, the most flexible and the most steps to set up.
Here's a simpler but less flexible method.
In Contacts, Define a calculation field, cGateKeeperIDs as
If (ContactType = "GateKeeper" ; ContactID ; "" )
Note: ; "" may be left out and you'll get the same result
Now simply refer to this field in your value list definition.
Using a relationship, you'd define a field in the table where you'll have your conditional value list. We'll call it cGateKeeperLabel and set it to return the text: GateKeeper
Now link your parent table to a separate table occurrence of contacts like this:
ParentTable::cGateKeeper = GateKeeperContacts::ContactType
Now you can specify ParentTable as the "starting from" table occurrence and specify GageKeeperContacts::ContactID as the field entered in column 1 of your value list.
OK, well I'm just not getting it...
A contact can have many roles (types). They can be a gatekeeper, site contact, and Security Coordinator, so I have to allow for multiple choices. I setup a multiple choice checkbox field using a list from another table, but I was unable to figure out a way to test that the field contained "Gatekeeper" when other types were checked. It seems I need FM Advanced to load a function to do parse the field for a specific string.
So, I setup a field just for each role. Let's just focus on the Gatekeepers. I have a field setup name "Gatekeeper" that is a checkbox. I also have a calculation field called "cGatekeeperTab" that is based on the formula
If (Gatekeeper = "Gatekeeper"; EmployeeID)
When I check the Gatekeeper checkbox cGatekeeperTab fills in with the Employee ID. From here on out for the life of me I cannot seem to use this field to limit the list of Gatekeepers listed. I put a file with sample data out at
for anyone who would look and tell me what to do. I'm sure it is something simple, but...
Also, if anyone has a thought on how to use a single field to cover all role types (issue in paragraph 2) and still get the same results, I would like to hear that as well. Overall, that seems to be a better design.
Thank you for all the help!
Didn't know your field stored a list of multiple values. Here's a modified calculation that will work with your original field and no custom function is needed:
If ( Not IsEmpty ( FilterValues ( ContactType ; "GateKeeper" ) ) ; EmployeeID ; "" )
You seem to have stopped short of the second half of this process: If the above field is labeled cGateKeeperTag, you should be able to set up your value list to refer to EmployeeID's from this field instead of ContactID's.
Define a new value list, Specify column 1 values as coming from cGateKeeperTag, and column 2 values as coming from cFullName and you should have your value list.
I was able to get the first half working successfully, but after fighting with the second half (and uttering a few ugly words to myself) I backed out the second half and took a break. I will attempt again and let you know. Thanks!!
OK, finally got my brain around it and it works great! Thanks!