When you using another for a list lookup at times there is duplicate data. How do you get distinct records when populating a drop down in Nintex form?
Solved! Go to Solution.
Ideally your reference list would only have one item per distinct value. If there's more than that, there's a good chance that you want a separate list to act as the reference list.
Is it possible that you could create a list for the column that you're trying to lookup? You could then use that list as a lookup column in both the list and the form.
For example: if you've got an Employee Information list with the following columns
Employee, Branch, Favourite Colour
Then say that list has the following items
Jeff, Admin, Green
Julie, Admin, Yellow
Andrew, Tech, Blue
Adam, Customer Service, Yellow
and you want to create a lookup dropdown on the Branch column. You might want to create a separate list "Branches" with a column named Branch, with the following items
Then in Employee Information you would set the Branch column as a lookup column to your Branches List
On your form, you would then reference the Branches List as well.
The issue with trying to get the dropdown values directly from the Employee Information list to begin with is this: if you are only displaying one 'Admin' which one are you displaying? Jeff's Item, or Julie's Item? Which ID does SharePoint use? If I change Jeff's Branch, should I be changing the dropdown's selected value (if Admin is selected)?
I think it is better to visualize what I am talking about. This data could in BCS or List etc, but it is a matrix and depending on what the user selects, determines the next values in the drop down. Cascading drop down but based on a matrix.
I hope this is not too much but basically in a Division drop down I only want to show distinct division which my drop down will have Global Client Group, Investments and Operations.
Then in the sub division drop down it will get data based on Division, again it must be unique. I hope this is clear.
|Global Client Group||Asia Institutional||Hong Kong|
|Central Client Group||Central Client Group|
|UK Client Group||UK Client Group|
|Investments||Emerging Market Fixed|
|SA & African|
|Investments||Global Multi Asset||Global Multi Asset|
|Investments||SA Multi-Asset||SA Multi-Asset|
|Operations||Fund Operations||Fund Operations|
|Operations||Investec Investment Institute||Investec Investment|
I would suggest creating two more lists:
Divisions with the values:
Global Client Group
Sub-Divisions with two columns, Sub-division and Parent Division(lookup column) with the values:
Asia Institutional, Global Client Group
Central Client Group, Global Client Group
UK Client Group, Global Client Group
Emerging Market Fixed Income, Investments
Global Multi Asset, Investments
SA Multi Asset, Investments
Fund Operations, Operations
Information Technology, Operations
Investec Investment Institute, Operations
Legal & Compliance, Operations
Then, in the form that you want to have your drop-downs,
Add one list lookup named lookup 1, with Divisions as the Source List and the list column name as Division
Add one list lookup named lookup 2, with Sub-Divisions as the Source List and the list column name as Sub-Division, filter by a controls value where Field Division is equal to lookup 1
Add the third list lookup named lookup 3, with your matrix as the Source List and the list column name as Team, filter by a controls value where Sub-Division equals lookup 2
This might sound quite involved, but it's the best way to be able to treat these as proper cascading values.
There is that way but I can't really do that because those lists are dynamic, I don't want to create a new list because when the division's changes then I must maintain the list and when new sub divisions are added I would also need to maintain it.
I have to find a different way to handle that but not creating extra list. There is many of my list that has a matrix like that with different types of data, maybe for those I will have to leave them on InfoPath for now. Surely Nintex has thought of these functionalities that infoPath provide and hopefully they will come on Nintex forms as well.
Thank you for your reply.