In this blog post, I will try to cover how to handle the groupBy and other summary functions within PowerApps development.
Scenario: The app allows the user to select various pallets from received batches.
Each pallet has a pre-defined molds and associated weight. In the below screen, we need to present to user how many pallets are available per batch, along with sum of molds and weight (image 1).
Upon selecting received-batch from image 1, underlying pallets that are linked to the received-batch are shown ( image 2)
Below is the logic to generate screen that looks like as shown in image1.
First Collect the detailed data that is available from “pallets”. This collection clt_unConsumedPalletDetails below is what will be used to group.
ForAll(
Filter(Pallets, 'Pallets (Views)'.'Unconsumed Pallets'),
Collect(
clt_unConsumedPalletDetails,
{
clt_fld_unconsumed_pallet : mb_palletnumber,
clt_fld_unconsumed_batchID : mb_batchidtext,
clt_fld_unconsumed_numberofmolds : mb_numberofmolds,
clt_fld_unconsumed_moldRemain : mb_moldsremaining,
clt_fld_unconsumed_wtRemain : mb_weightremaining,
clt_fld_unconsumed_moldsUsed : mb_moldsused,
clt_fld_unconsumed_moldsUsed_original : mb_moldsused,
clt_fld_unconsumed_moldsUsedNow : 0,
clt_fld_unconsumed_status : mb_consumed,
clt_fld_unconsumed_moldRemain_Original : mb_moldsremaining,
clt_fld_unconsumed_wtRemain_Original : mb_weightremaining,
}
)
);
The next step is to group on the first collection using one of the fields named clt_fld_unconsumed_batchID
ClearCollect(clt_unConsumedPalletDetails_ByBatch,
GroupBy(clt_unConsumedPalletDetails, "clt_fld_unconsumed_batchID","ByBatchPallets")
);The above code block has generated collection named clt_unConsumedPalletDetails_ByBatch. Now we will use this collection, to add new columns to the collection named clt_unConsumedPalletDetails_ByBatch_Sum_NoRowID using Sum function as shown below.
ClearCollect(clt_unConsumedPalletDetails_ByBatch_Sum_NoRowID,
AddColumns(clt_unConsumedPalletDetails_ByBatch,
"SumMolds", Sum(ByBatchPallets,clt_fld_unconsumed_moldRemain),
"SumWeight", Sum(ByBatchPallets,clt_fld_unconsumed_wtRemain),
"CountPallet", Sum(ByBatchPallets,Value(clt_fld_unconsumed_moldRemain)/Value(clt_fld_unconsumed_moldRemain))
));We used clt_unConsumedPalletDetails_ByBatch_Sum_NoRowID in image1 as shown above. It has the SUM functions that provides sum of molds and sum of weight. To get CountPallet, we used another summary function as shown above.
The above code shows how to created grouped or summary based collection from a detailed collection.
Finally, whenever user clicks on magnifying glass, it loads clt_unConsumedPalletDetails by linking the pallets from image1 selected batchID.
Feel free to contact us for any questions. Let MIBAR help your business do more with Microsoft PowerApps, part of the Microsoft Power Platform that lets you easily build the business apps you need and extend or customize the apps you already use. Schedule a free consultation to learn more.
Additional PowerApps Resources
Passing Parameters in Microsoft PowerApps
What’s Coming to PowerApps and Power Automate in 2020 Release Wave 1?
