Answered

Trying to filter data in a combobox based on a selected value in another combobox

(0) Share

Report

Posted on by MM-25111418-0

Hi.

Im struggling to filter data i've connected to my PowerApp and im hoping someone here can help me see a solution.

In this app im using a built in form that are connected to two MS lists. One list is called registrated errors while the other list is called contracts and suppliers. In the list registrated errors i have two columns that is connected through lookup in the list contracts and suppliers.

These columns are called supplier and contractname. When i connected the list registrated errors to the built in form in PowerApps it searches for the suppliers name and contract from list contracts and suppliers as it should. So the data is showing correctly but i want to filter this data so the user wont get all the contractnames in powerapps. They can search for a contract but i want to narrow it down for the user because it can often be 10-20 contracts on one supplier. Additionally the names on the contracts often are similar to the different suppliers as well.

Above is a small screenshot from the app where one supplier is selected and the data in contract is where i now just want to show all the rows from the list contracts and suppliers that has the name of the selected supplier.

I have though tried this formula in the combobox named contract:
Filter(Choices([@'Kontrakter/Leverandører'].Konktrakt);Concat('Leverandører med oversikt kontrakter i konsernet';Leverandør;",") = DataCardValue30.Selected.Value) - Datacardvalue30 is the combobox named supplier

It seems to work, it does not show anny error messages and i get the data i need according to Powerapps when i hover the formula but the concat-data dont seem to be validated against the selected value. The contracts will then not show in the combobox named contracts because i think that the formulas condition is not met.

Hopefully someone in here can see what im missing or doing wrong here.

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Devel,

Do you want to filter Contract based on selected supplier?

Could you please tell me the following details:

1). If the Supplier and the Contract column are LookUp or Choice column in your registrated errors list?

2). If the contracts and suppliers are both Choice type in your contracts and suppliers list?

Currently, I think contracts and suppliers are both Choice type in your contracts and suppliers list, and you set the Supplier Combo Box with Choices() function.

I just assume that the Supplier and the Contract column are Choice column in your registrated errors list, here is the formula I set for the Contract Combo Box Items:

Filter( Choices(ListA.Contract), Value in Concat( Filter( ListB, Suppliers.Value = ComboBox17.Selected.Value ).Contracts, Contracts.Value, "," ) )
MM-25111418-0 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi and thanks for the reply!

I will try to answer your questions and mabye try to be more accurate in my question/issue as well.

1. The supplier and contract columns in the list registrated errors are connected to the columns supplier and contract in the list suppliers and contracts through lookup.
2. They are not choices because it will probably be a lot of contractnames in there. It is estimated around 3-400 contractnames. Right now each supplier has it own row for every contract(because it might be connected other columns with information about that contract later on)

This is how the list suppliers and contracts look like right now. Som when the user in powerapps uses combobox to find a suppliers name it will use lookup into this list in the image where Company A-C is listed. If the user chooses Compay A then the other combobox should only show contracts when the selected suppliers name is Company A.

Another option i have also considered is to have one row for each supplier and use columns for each contract they have but i havent figured out how to show all the columns for just one specific row/chosen supplier just yet. I dont know if this option though is a easier way to connect of filter the data but if it is i can use that instead. I just then need some pointers in how to list all columns from one row
v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Devel,

So the supplier and contract columns in the list registrated errors are LookUp column type, right? I am confused this, you should tell me directly about the column type.
MM-25111418-0 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry about that. Yes, both of the columns are lookup types.
Verified answer

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Devel,

Okay, that's easier.

You just need to modify the Items property of the Combo Box corresponding to the contract as below:

Filter(ListB,Suppliers=ComboBox17.Selected.Value).Contracts

You just need to filter in the suppliers and contracts and retrieve the corresponding contracts to the ComboBox selected supplier.

Replace the ListB with the suppliers and contracts
MM-25111418-0 on at

Like
a
(0)

Report
Copy link

Link copied!

That did the trick!

It works exactly how i wanted it to.
Thanks a lot for the help!