How to filter choices in a dropdown from another table

(0) Share

Report

Posted on by MichaelF1

Hi All

I'm new to Power Apps and I need a bit of help with what I think should be fairly simple to achieve! 🤔

My App is built on 2 SharePoint lists.

The purpose is to record orders when a customer places one.

So I have a SP list for 'Orders' with Customer as a lookup column from another list: 'Customers'. In the Customers list there is a column to specify whether they are Active or not.

When my users record a sale, they select the customer from a dropdown (Combobox). The Items property for the Combobox is Choices([@'Orders'].CustomerName)

My problem is that I need to filter this list to show only the customers who are 'Active' - I can't see how to achieve a filter that uses information from the Customers list to then save to the Orders list.

I also work with MS Access so I understand about joining tables, primary and foreign keys and so on. I just can't seem to achieve this in Power Apps!

Many thanks for any help or suggestions

Michael 😀

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

Verified answer

v-bofeng-msft Microsoft Employee on at

Like (5)

Report
Copy link

Link copied!
Hi @MichaelF1 :

You could try this formula(Some content may need to be adjusted according to the actual situation):

Filter( Choices([@'Orders'].CustomerName), Value in Filter(Customers,Status="Active").Title )

If this formula does not work, then please consider providing the following information:

What is the status field's data type?

What is the CustomerName field's setting of 'Select a column from the list above'

Best Regards,

Bof

Was this reply helpful? Yes No
MichaelF1 82 on at

Like (0)

Report
Copy link

Link copied!

Hello Bof, yes your suggestion works perfectly! My status field was a Boolean value but I managed to work that one out.

Thanks so much for your help! 🙂

Was this reply helpful? Yes No
Juanes9812 5 on at

Like (1)

Report
Copy link

Link copied!

Your suggestion is simple and pretty useful. Thanks for sharing your knowledge

Was this reply helpful? Yes No
VG-30071056-0 13 on at

Like (0)

Report
Copy link

Link copied!

Hello.
Thank you for your suggestion. i am trying your formula on powerapps canvas, i have the same condition as Michael:
status field is boolean and two sp list. Combo box have search bar, but when I write the formula the dropdown works, but I no longer have the possibility to do the search.
it's correct?
Regards

Was this reply helpful? Yes No
Rahul320 47 on at

Like (0)

Report
Copy link

Link copied!

I have a very similar situation:
2 Sharepoint lists, "Call Log" and "Clients"
Call Log has a lookup column to Clients list with a column name as "Client Office" that displays "Client" from the other list.
The Clients list has a column of "Active" that's a single line of text with a value of either "false" or "true"

Setting my combobox Items value to Choices([@'Call Log'].'Client Office') works fine but if I try to filter out Clients that have "Active" is "true" the browser hangs for many minutes. Eventually the form does display the filtered combobox but the delay makes it unusable. This is the filtered code on the Items property:

Filter(
Choices([@'Call Log'].'Client Office'),
Value in Filter('Clients', Active = "true").Client)

Any ideas?

Was this reply helpful? Yes No
AndreasTR 90 on at

Like (0)

Report
Copy link

Link copied!

@Rahul320 same issue here(browser hangs)..did you manage to solve it ?
thanks

Was this reply helpful? Yes No
MichaelF1 82 on at

Like (0)

Report
Copy link

Link copied!

Hi @Rahul320 , the solution above from @v-bofeng-msft worked for me, with minor adjustment for the boolean data type I was using. Hope this helps 🙂

Was this reply helpful? Yes No
Rahul320 47 on at

Like (1)

Report
Copy link

Link copied!

I solved it by not using Choices for the combobox and instead simply setting the items property to Filter('Clients', Active = "true") then changing the update property of the data card to
{
Id: cb_ClientOffice.Selected.ID,
Value: cb_ClientOffice.Selected.Client
}
Where cb_ClientOffice is the combobox control.

Was this reply helpful? Yes No
AndreasTR 90 on at

Like (0)

Report
Copy link

Link copied!

thank you @Rahul320

Was this reply helpful? Yes No
dulaa 74 on at

Like (0)

Report
Copy link

Link copied!

Hello there,

I got I think a similar problem. I got 2 lists and in gallery im trying to set filter from dropdown that is connected to other List . I was trying to add line
Drp_nr_wag.Selected.Value = "Wszystko"|| Lista_wagon_zlecenia.Nr_Wagonu = Value(Drp_nr_wag.Selected.Value);

where Drp_nr_wag - is a dropdown list connected to second list name Lista_wagon_zlecenia
but it does not work .. any clue and help please 🙂 ?

Was this reply helpful? Yes No