Filter based on related table/entity

(0) Share

Report

Posted on by Graham

119

I want to show a gallery from table A but filter the results based upon a value of Column4 in a related table B.

eg.

TableA

Column1(PK), Column2, Column3(FK)

TableB

Column1(PK), Column2, Column3, Column4

In SQL, I would write

"select * from TableA inner join TableB on TableA.Column3=TableB.Column1 where TableB.Column4=N'X'"

How should I construct the filter formula in Powerapps?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

v-yamao-msft on at

Like (0)

Report

Hi Graham,

You could add a Dropdown control in your App, set its items property to TableB.Column4.
Then add a Gallery control and set its Items property to If(IsBlank(TableA),Filter(TableA,Title=Dropdown1.Selected.Value)).

Please try if this works for you.

Best regards,
Mabel Mao

Was this reply helpful? Yes No
rgruian on at

Like (2)

Report

Assuming the column names are accurate, that you are looking for an 'N' in Column4, and that there is indeed a name collision between the column names in TableA and TableB, here's how you can accomplish the operation you mentioned:

gallery1.Items = Filter(TableA, Lookup(TableB, Column1 = TableA[@Column3].Id, Column4) = N)

Note the row scope disambiguation syntax TableA[@Column3] is needed only because of the name collision apparent in your requirements. If TableA and TableB do not in fact have colliding column names, you can use the normal, simpler syntax, for example:

gallery1.Items = Filter(TableA, Lookup(TableB, TableBColumn1 = TableAColumn3.Id, TableBColumn4) = N)

This also assumes that the lookup field for joining the tables has an Id subfield of the same type as TableB.Column1. Please feel free to adjust the names, operators, and types as neeed for your scenario.

I hope this helps.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Can someone explain what TableBColumn1 or TableAColumn3.Id mean? I have never seen this syntax.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report

I think there is a typo in the post, the syntax of both your excerpts seems wrong.
Please provide your specifics if you require further help.
Also, once you filter you may run into performance problems, and this post might help.

Was this reply helpful? Yes No