Answered

Combining Totals if Department and Items match

(0) Share

Report

Posted on by ZhangAndy209

Hi everyone, I am having trouble figuring how to to implement this into my app. Right now I have an ordering app where people from various departments can order items for their department. I want to make it between different orders, if the departments match and if the items match that it will add up the total items bought into one transaction. For example:
Person a is from department a and orders 5 black pens.
Person b is from department b and orders 5 black pens.
Person c is from department a and orders 5 black pens.

So I would want to figure out how to combine Person a's order and Person c's order to combine the 5 black pens from both their orders into 1 line that says 10 black pens, but not Person b's order as they are part of a different department.

Right now I have all the information saved into two collections
This is the collection that contains all the information about the order: (Name of person, which department, the date and any comments/concerns)

'Cart Orders', Defaults('Cart Orders'), {Title: Name_TInt.Text, Department: Department_Dropdown.SelectedText, Date: Date_TInt.Text, Comment: Comment_Box.Text})

And this is the collection with all the items ordered: (Name of item, how many pieces and bundles they want and the total amount of pieces they are ordering).

 (cart, {Title: ThisItem.Description, Qty: ThisItem.'Qty per Bundle', Pieces: Value(Pcs_Nd_TInt.Text), Bundles: Value(Bndl_Nd_TInt.Text), Total: ((Bndl_Nd_TInt) * ThisItem.'Qty per Bundle' + Pcs_Nd_TInt)});

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

rubin_boer 4,841 Super User 2024 Season 1 on at

Like (0)

Report

hi @ZhangAndy209

Welcome to the community.

to get to your answer you need to group the items by department and add a column that gives you the total number of items. something like this:

add a gallery to see the result, its Items =

AddColumns(GroupBy('Cart Orders', "Department", "By Department"), "Total ordered", CountRows("By Department"))

Was this reply helpful? Yes No
ZhangAndy209 27 on at

Like (0)

Report
Hi,

It is not recognizing the department column. I think its because I had to already use AddColumns to create the 'Cart Order Items' collection:
Collect('Cart Order Items', AddColumns(cart, "OrderID", varRecordID ,"Department", Department_Dropdown.SelectedText), DropColumns(cart, "Pieces","Qty","Bundles"));
I did not call Cart Orders as it is made via Patch:
Set(varRecordID, Patch('Cart Orders', Defaults('Cart Orders'), {Title: Name_TInt.Text, Department: Department_Dropdown.SelectedText, Date: Date_TInt.Text, Comment: Comment_Box.Text}).ID);
It does not have the items attached to it.

I currently have these collections being saved into SharePoint and the department column is a choice type as I am using a dropdown control, but GroupBy seems to want text values. Is there a way to call the department column?

Was this reply helpful? Yes No
Verified answer

svijay_001 Microsoft Employee on at

Like (0)

Report

Let's Assume :

You have the below data :

{dept:"Department1",prod:"pen",qty:10,person:"Person1"},{dept:"Department2",prod:"pen",qty:1,person:"Person2"},{dept:"Department1",prod:"pen",qty:10,person:"Person3"},{dept:"Department1",prod:"pen",qty:10,person:"Person4"}

Now the needed output

To achieve this you could use the below :
AddColumns(GroupBy(_prodCollection, “prod”,”dept”, “subgroup” ), “Sum”, Sum(subgroup,qty) )

First group the data by “prod”,”debt” and stores grouped values in the subgroup column.

Now we add a column “Sum”, which will be resulting sum of all values under “qty” within a “subgroup” for each prod,dept combination.

I have detailed an explanation here .

Was this reply helpful? Yes No
ZhangAndy209 27 on at

Like (0)

Report

Hi,

Thank you for the very coherent explanation it makes a lot of sense. However, my department column is a Choice type because it is from a dropdown control. Is there anyway to GroupBy a choice type, if not is there any way to implement a dropdown menu with the single type of text type?

Was this reply helpful? Yes No
svijay_001 Microsoft Employee on at

Like (0)

Report

Can you please elaborate a bit ? Sorry, I did not understand you.

Was this reply helpful? Yes No
ZhangAndy209 27 on at

Like (0)

Report

Hi, I figured that part out, but when I use ClearCollect it tells me my specified data source cannot be used with this function. I am trying to save it into a SharePoint list and it won't ClearCollect into the SharePoint List. When I use Collect instead of ClearCollect it doesn't delete the previous entries.

Was this reply helpful? Yes No