Set dropdown based on a filtered table on a screen.

[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

[v-yutliu-msft]

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]

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]

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]

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]

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]

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..