Set dropdown based on a filtered table on a screen.

(0) Share

Report

Posted on by Fparker61

This is all in SQL Server

I am wanting to set a dropdown so I can change values based on a table on the screen that is already filtered.

It is a 3 tier hierarchy that uses a text input to filter the top table. A selection in the top table then filters the 2nd table. A selection in the 2nd table then filters a 3rd table. Selecting an item in the list may need the reference value from table 2 edited to another valid value in table 2. I was able to get the entire list in the drop down but I only want the values from the 2nd table to show up.

The hierarchy is:

CompanyMaster

Company (references CompanyMaster key value)

Account (References Company key value) which is what I want in the dropdown to be able to change

If I use dbo.company and place the company key value as the default of the selected record the dropdown then shows all values from the company key values in the database.

How do I get the dropdown filtered?

TIA

Categories:

Building Power Apps

All responses (6)

Answers (0)

Sort by

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Fparker61 ,

Do you want to set a drop down's Items based on a related table?

Do you mean that

1)in Company table, there's a key field named Company

2)in Account table, there's a primary key field named Company reference Company key value in Company table?

If so, you just need to set the drop down like this:

the drop down's Items:

Filter(Account,Company =tablecompany.Selected.Company)

the drop down's Value: Account primary key

the drop down's Default:

LookUp(Account,Company =defaultcompanykeyvalue).Accountprimarykey field

//please replace with your tablename, default key value, primary key field.

Best regards,
Fparker61 17 on at

Like
a
(0)

Report
Copy link

Link copied!

This isn't quite working. By making the items Filter ('[dbo].[Account]', CompanyID = CompanyTable.Selected.GUID) the drop down shows the GUID of the Accounts in the account table, not the GUID (CompanyID from Account) of the Companies in the company table.

Here is the hierarchy and names.

The dropdown is in the Account form. Should I move it out? The idea is to change the relationship of the account to the company to another company.
v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Fparker61 ,

Could you describe more clearly about the filter rule and what data do you want to get?

Do you want to firstly filter Account table based on selected Company key value, then filter CompanyTable based on the filtered Accountprimarykey field?

If so, try this:

set the drop down's Items:

Filter(CompanyTable,GUID in Filter ('[dbo].[Account]', CompanyID = CompanyTable.Selected.GUID).CompanyID )

Best regards,
Fparker61 17 on at

Like
a
(0)

Report
Copy link

Link copied!

Here is the layout.

I can filter this by company master, selecting one gives me a list of companies. Selecting a company gives me a list of Accounts. There are some company masters that have more than one company. I want the dropdown to have the list of GUID's in the company table with the default being the selected company. The idea is I want to change the associated company of the account.
v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Fparker61 ,

Could you show me your error message?

Actually, based on your screenshoot, I do not think there's any problem.

Maybe you could consider transferring all the field to text type to compare:

Filter(CompanyTable,Text(GUID) in AddColumns(Filter ('[dbo].[Account]', Text(CompanyID) =Text(CompanyTable.Selected.GUID) ), "CompanyID2",Text(CompanyID) ).CompanyID2 )

Best regards,
Fparker61 17 on at

Like
a
(0)

Report
Copy link

Link copied!

All of the fields are text.

Here are the 2 errors, Invalid argument type and filter has some invalid arguments. It doesn't seem to like the items..