How do you get distinct records when populating a drop down?

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

Admin

Tech

Customer Service

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)?

Hi Anthony,

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.

I would suggest creating two more lists:

Divisions with the values:

Global Client Group

Investments

Operations

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

Facilities, Operations

Finance, Operations

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.

Regards

Bhuti

Hi ,

You can refer below link. I have used in nintex form to remove duplicate from dropdown which is lookup.

Did Aarti's or Anthony's solutions work for you ‌ - which one should be marked as correct to help others out?