Answered

Filtering a DataVerse Lookup Column to NOT include values that are already in the Lookup column.

(0) Share

Report

Posted on by CU14100144-0

I have a Data Verse LookUp Column which looks up Users Table in Data Verse, and is a Data Card on the form whose Data Source is the 'Employee Details' table.

The above combo box shows all the lists of users in my tenant, that I can choose from.

The 'Employee Full Name' column in Data Verse is the shown in the following:

How would we Filter the Data Card control to show all the users except the users that have already been picked. i.e. all users except 'Jane Smith'; 'Sarah Jones'; 'Jone Doe'.

Thank you for your guidance.

Categories:

Using Apps Using Formulas

All responses (2)

Answers (1)

PrachiM 23 on at

Like (0)

Report
Re: Filtering a DataVerse Lookup Column to NOT include values that are already in the Lookup column.

You can add a Dropdown control to the form and set its Items property to a formula that returns a filtered list of users. Here's an example of how you can do this:
Add a Dropdown control to the form and set its Items property to the following formula:

Filter(Users, !(User in Employee_Details))

In the above formula, Users is the name of the Users table in Data Verse, and Employee_Details is the name of the Employee Details table. The Filter function returns all the records from the Users table where the User is not in the Employee_Details table.
In the Data Card for the 'Employee Full Name' column, set the control's Visible property to false. This will hide the original Lookup control from the form.
Set the Dropdown control's DefaultSelectedItem property to the following formula:

First(Filter(Users, !(User in Employee_Details)))

This formula returns the first record from the filtered list of users, which will be the default selected item in the dropdown control.
With these steps, you should be able to filter the Data Card control to show all the users except the ones that have already been picked in the 'Employee Details' table.
Verified answer

v-qiaqi@microsoft.com on at

Like (1)

Report
Re: Filtering a DataVerse Lookup Column to NOT include values that are already in the Lookup column.

Hi @qxaziz123,

Here is a demo for your reference.

Note that the 'Lulai Trackers' is the many table including the LookUp column ‘The Food’. In your scenario, you should replace it with 'Employee Details' table that has a lookup column named 'Employee Full Name' column. And the 'Food Name' is the column in my another table where the lookup column named 'Employee Full Name' retrieves values from.

Create a collection to save the LookUp column values as:

ClearCollect(colLook,ForAll('Lulai Trackers'.'The Food','The Food'.'Food Name'))

Set the Items property of the ComboBox as:

Filter(Choices('Lulai Trackers'.'The Food'),!('Food Name' in colLook))