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".
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?
Solved! Go to Solution.
Just for the sake of this example I have a List on SP which has three columns, First Name, Last Name and Rank. I added the same data as you have provided in the example.
I created a form in Nintex Workflow Cloud, which looks like this:
I create a Data Source Variable which connects to the SharePoint List. In the condition I added two conditions as below, note that in the value fields I am referencing the Form controls used above:
I have set it such that "If all of the conditions are true"
I then created a Rule as below:
where the value for the Rank is set to the Data Source Variable created previously.
So, when the user adds the First name and Last name, the Rank field automatically gets filled in with the Rank that was specified in the SP list
Hope that helps.