CODEWITHSUNDEEP
relational-databasepowerapps
Aidan Williamson
Aidan Williamson

Reputation: 125

PowerApps merge related tables

I'm new to PowerApps and am exploring relational databases before starting a project in PowerApps (involving relational databases). I'm trying to do a simple join of two entities (drink and human), where the drink has a BuyerID field which relates to a "human"'s Primary ID. So I tried ForAll(Filter(drink, (name = "Americano") And (drink.BuyerID = human.PrimaryId) ), Collect(AmericanoDrinkers, {FirstName: human.firstName, LastName: human.lastName})) to get a collection of humans who brought an Americano, but I get an Invalid argument type error with drink.BuyerID = human.PrimaryId. Any ideas? I've tried changing = to in and stuff like drink.BuyerID to drink[@BuyerID]. Note that the drink.BuyerID datatype is "Lookup" and human.PrimaryId is integer, so could I be getting this error because of the different types? Also I'm certain there's nothing wrong with the data because when editting the drink entity in excel, the powerapps add-on shows the related humans when a BuyerID is selected.

Upvotes: 0

Views: 2858

Answers (3)

Ritveak
Ritveak

Reputation: 3758

You can try using Gallery inside a gallery.... I have used Gallery inside a gallery many times, it is kinda confusing to work with it, but seems very logical and worked fine for me.

Upvotes: 0

Aidan Williamson
Aidan Williamson

Reputation: 125

The problem was just the setup, I just created a new field (BuyerID2) in the drinks entity and set it as a number value rather than a lookup like BuyerID was, and all 3 (my initial one, and the two Meneghino provided) solutions worked fine (although I had to change drink.BuyerID = human.PrimaryId to BuyerID2 in human.PrimaryId).

Upvotes: 0

Meneghino
Meneghino

Reputation: 1021

What you want to do in theory is this:

ClearCollect(AmericanoDrinkers,
    Filter(human,
        PrimaryId in Filter(drink, name="Americano").BuyerID
            )
        )
    )

In practice, depending on what your data source is for drink and/or human you may run into delegation issues. I do not recommend the use of ForAll if you can avoid it, since you risk data source calls for every row, whereas a formula like the one I suggest will be processed in parallel (delegation permitting).

If the performance of the above is not satisfactory, you could also try this two-step approach:

ClearCollect(FilteredIds, GroupBy(Filter(drink, name="Americano"), "BuyerID", "AllRows"));
ClearCollect(AmericanoDrinkers, Filter(human, PrimaryId in FilteredIds.BuyerID))

Upvotes: 1

Related Questions