cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
medBrahmi
Level: Powered On

Filter a lookup field based on another list columns

hi guys,

 

my 2 lists used for my app are :

Reservation { Name, Registration Number(LookUp Field), Destination }

Vehicules { Registration Number, Brand, Status(availability) }

 

i want to filter the displayed choices of the registration number lookUp field based on the availability of the vehicule (the status field),

is it possible to do this ?

and if so what should i do ?

 

thank you in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter a lookup field based on another list columns

Hi medBrami,

 

Can you take some screenshots of the controls and their formulas?

You can also break the formula up to see which parts work and which don't separately, before stringing it all together - so;

 

use this on the Items: property of a dropdown;

Filter(Vehicules, Status="Available").RegistrationNumber

or just 

Filter(Vehicules, Status="Available")

and set the Field: property of the dropdown control to RegistrationNumber

 

If this returns the expected result, then move onto the next piece and so forth.  Test each piece separately and perhaps we can find where it's breaking down.

You can also post some screenshots that show the controls and formula's and perhaps any errors and we can try resolve from there.

 

 

Kind regards,


RT

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Filter a lookup field based on another list columns

Hi medBrahmi,

 

By the looks of it, the common key between the two lists is the registration number.

You could do this by filtering the Choices/DropDown control in the Reservation form.

 

So on the Reservation form datacard for RegistrationNumber, set the Items: property of the dropdown/choice control to;

 

Filter(Choices(Reservation.RegistrationNumber), Value in Filter(Vehicules, Status="Available").RegistrationNumber)

This will filter the available options down to those that have an "Available" status in the Vehicules list.  Just be aware that if you have more than 500 reservations you may run into delegation issues.

 

Hope this helps,

 

RT

medBrahmi
Level: Powered On

Re: Filter a lookup field based on another list columns

Thank you for the quick response good sir,

 

i tried your formula, wich is totally correct, but sadly no luck the dropdown shows no results and i don't know why.

Super User
Super User

Re: Filter a lookup field based on another list columns

Hi medBrami,

 

Can you take some screenshots of the controls and their formulas?

You can also break the formula up to see which parts work and which don't separately, before stringing it all together - so;

 

use this on the Items: property of a dropdown;

Filter(Vehicules, Status="Available").RegistrationNumber

or just 

Filter(Vehicules, Status="Available")

and set the Field: property of the dropdown control to RegistrationNumber

 

If this returns the expected result, then move onto the next piece and so forth.  Test each piece separately and perhaps we can find where it's breaking down.

You can also post some screenshots that show the controls and formula's and perhaps any errors and we can try resolve from there.

 

 

Kind regards,


RT

View solution in original post

medBrahmi
Level: Powered On

Re: Filter a lookup field based on another list columns

UPDATE

the first formula worked like a charm it was just the registration number field type, i forgot and set it to a lign of text

thank you very much you helped me a lot,

Best regards 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 254 members 6,562 guests
Please welcome our newest community members: