I am trying to do a lookup to an employee information list using first and last name to get their rank in a repeating section of a NWC form.
The original source data has separate columns for first and last name, but only one column can be shown on the data source look up. I can't use only one column because there are multiple people with the same last name.
I created a calculated column that has [LastName]&", "&[FirstName], and it works as a lookup for the employee name, but I can't use it as a condition when I create an external data variable to look up their rank.
To illustrate what I mean, I have the three employees below, and the lookup should pull them by name, then the condition would be get their rank, so when the user selects "Brown, Tony" the Rank field will show "Lieutenant".
| Rank | FN | LN |
| Lieutenant | Tony | Brown |
| CO | Mike | Brown |
| Sergeant | Jeff | Brown |
There doesn't appear to be any way to use the concatenated fields in the external data variable, only list columns, and calculated columns are not listed to select. I can't use a People picker because it has the same issue of not being available for lookups.
Any ideas on how I can do this?
Best answer by sid
View original
