Struggling with what combination of Sum, Group By, and Sum Array to use...

[Anonymous]

I need to present totals/information within my Power App of the data that it is writing.  I can easily think through how to do this with SQL, but just keep losing the battle in trying to do it with Code.  PLEASE HELP 😞

There are 3 SharePoint Lists in play - 2 of them are item details and 1 is the transactions list.  I need to summarize the data that my app is writing into the transactions list, in a few different ways actually.  Each location will report their remaining "On Hand" totals at the end of each day.  Here's a simplified structure of what I can't quite seem to figure out.

LOCATIONS

ID	Location
1	home
2	office
3	school

MEDICINE

ID	Medicine
1	Aspirin
2	Tylenol
3	Ibuprofen

TRANSACTIONS

ID	MedID	LocID	Amount	Effective
1	2	1	50	1/1/2022
2	2	1	30	1/5/2022
3	2	3	75	1/3/2022
4	2	2	10	1/8/2022
5	2	2	0	1/15/2022

What I need is the total amount of Tylenol across all locations and to find that I need the Amount values from the max Effective date row per all locations entered.  This would be very easy for me to write in SQL, but I have battled longer with this in PowerApps than I'd ever care to admit.

[RandyHayes · Accepted solution]

@Anonymous 

You zeroed in on Tylenol, but my guess is that you need this for all the medicines. 

Luckily you are not writing code as PowerApps is a no-code platform, so, you just need to devise the datashaping and evaluation formulas to return what you want.

You didn't really mention the resulting record you wanted from the 3 tables, so there are a LOT of ways to produce results.

In the below example, I took the approach of a record that has a column with the Medicine name in it, and then a column that has a table of the locations and their most recent date and amount.

So, the formula for that would be:

ForAll(
    GroupBy(TRANSACTIONS,
        "MedID", "_data"
    ),
    {Medicine: LookUp(MEDICINE, ID=MedID, Medicine),
     Locations: 
         ForAll(
             GroupBy(_data, "LocID", "_records"),
             With({_items: Sort(_records, Effective, Descending)},
                 {Location: LookUp(LOCATIONS, ID=LocID, Location),
                  Amount: First(_items).Amount,
                  Effective: First(_items).Effective
                 }
             )
         )
    }
)

As mentioned, this gives you a record with a Medicine column.  Then a Locations column that has a table with records of the Location name, the most recent date, and the most recent amount.

I hope this is helpful for you.

[Anonymous]

I haven't had a chance to apply the solution you provided, but I HUGELY appreciate it as I suspect I can get what I'm after from what you provided.  I haven't exactly thought through the explicit record structure I desired in the end (though I see value in doing so for my overall goals), but my plan for the actual app is:

We have 5 specific medications that we are tracking, so on the main App landing page I have written out those 5 medication names and then am attempting to display the calculated on-hand value next to them.  Just below that main section of the landing page is a DDL that has all the locations to choose from (about a dozen), and when the user chooses a location the app will open a page specific to just that Location where the individual volumes are listed out along with their "As Of" Effective date.

Like I said, I'm fairly certain that what you have provided will get me to my end goal and when it does I will definitely come back and mark your response as the Solution.  Thanks again!!