How to filter choices in a dropdown from another table

[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 😀

[v-bofeng-msft · Accepted solution]

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

[MichaelF1]

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! 🙂

[Juanes9812]

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

[Valerio1980]

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

[Rahul320]

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?

[andreas_TR]

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

thanks 

[MichaelF1]

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 🙂

[Rahul320]

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.

[andreas_TR]

thank you @Rahul320