Gallery - Only display Table Columns that are associated with another Table

(0) Share

Report

Posted on by CCEP_Mike

219

Hello,

I've been trying to work the below out for the past 3 days and i seem to be getting nowhere, so I'm coming to the Power Apps community for some guidance/help please.

I have 2 Dataverse Tables called:

Products:

Product_Code	Product_Name	CONV	Order_Conv	FASTFOOD	Order_Takeaway	Value_Type
12345	Coca-Cola 500ml	TRUE	20	TRUE	10	Available
67890	Monster Energy 500ml	FALSE		TRUE	30	Available
55555	Dr Pepper 330ml	TRUE	10	FALSE		Number
21212	Sprite 500ml	TRUE	30	TRUE	20	Available

Audit:

ID	Account_Number	Environment	12345	67890	55555	21212
194cf38c-5ac2-ee22-1234-000d2aad864f	13412	CONV	1		1	0
194cf38c-5ac2-ee22-1234-000d2aad824f	43256	FASTFOOD	1	1		1
194cf38c-5ac2-ee22-1234-000d2aad874f	65433	CONV	0		1	1

What I'm trying to do is display a record from the Audit table within a Gallery, but only display Columns from the Audit table that are within the Products table.

For example, if we look at the first record in the Audit table then we can see the "Environment" is set as CONV, so here we want to load this record within the Gallery but only display the column names where they're within the Products table that have CONV set as TRUE.

So in that example to output would display

12345 = 1

55555 = 1

21212 = 0

I've managed to create a Gallery which pulls in the Products that match an Environment and have them display in order. But obviously this doesn't bring the current values of a record from the Audit.

If(
 varRecordID.Environment = "Convenience",
 Sort(
 Filter(
 Products,
 CONV = true
 ),
 Order_CONV,
 SortOrder.Ascending
 )
)

Below is an example I've created I'd like to have which ideally would work off the Audit record and display the values. I'm just a little lost on how best to do this. Any help would be greatly appreciated and thank you in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

sgtsnacks64 322 Super User 2024 Season 1 on at

Like (0)

Report

You might have some issues achieving this with PowerFx, as column actions such as ShowColumns and AddColumns usually require literal text values to work (So they won't accept variables as input).

You might however have better luck using Power Automate to do this, as you might have some more freedoms using the Select action and addProperty 😉

Was this reply helpful? Yes No
markinwaukesha 91 on at

Like (0)

Report

DataVerse has improved but I also ran into the same issues when you want to have relationships between tables,
I eventually moved to SQL Server so I can have a view to fetch the values. Instant flow route as sgtsnacks64 mentioned above would be an option by Passing in that selection and providing the filtering however the latency may be an issue because you'd have to kick off and wait for a flow -
If I had to tackle this I would:
Similar to what you initially did, do but do a ClearCollect put those into a Collection
then I'd ClearCollect again to pull those from the Audit data -- once you have those two Collections you
have some flexibility on either trying to merge those based on your gallery
It's also nice for debugging because you can see them in the variables panel - I've just usually put a button
on a page to test it out easily to verify my collection is how I expect -
Maybe this gives you some ideas, but I think you're on the right track

Was this reply helpful? Yes No