I have two temporary collections (not SharePoint Lists):
ColIrrParts is my collection of parts used on a single job.
IrrigationShoppingList is my collection of all parts used on all jobs for the week.
I'm trying to add the items in my ColIrrParts collection to my IrrigationShoppingList collection by looking up each item. If the item from ColIrrParts is not found in IrrigationShoppingList, then add the item information from ColIrrParts to the IrrigationshoppingList collection. If the item is found, add the quantity in ColIrrParts to the existing quantity in IrrigationShoppingList.
I know I'm not even close with my formula, but here is what I have:
Patch(IrrigationShoppingList, Defaults(IrrigationShoppingList), LookUp(ColIrrParts, IrrDesc = IrrigationShoppingList, IrrDesc), {Quantity: TextInputQuantity.Text+TextInputShoppingQuantity.Text})
Solved! Go to Solution.
Thanks @Nate0173 ,
I need to know which piece of code is generating the error. You have also left out a parameter on the lookup
If(
IsBlank(
LookUp(
IrrigationShoppingList,
IrrDesc = LabelCartDescription.Text,
IrrDesc
)
),
Patch(
IrrigationShoppingList,
Defaults(IrrigationShoppingList),
{
IrrDesc: LabelCartDescription.Text,
Quantity: Value(TextInputQuantity.Text) +
Value(TextInputShoppingQuantity.Text)
}
),
UpdateIf(
IrrigationShoppingList,
IrrDesc = LabelCartDescription.Text,
{
Quantity: Value(TextInputQuantity.Text) +
Value(TextInputShoppingQuantity.Text)
}
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
I found the solution. I grouped the items in a new collection and then combined the quantities together in yet another separate collection. This allowed me to combine the items with the same description together and add the quantities of the matching items together so my final collection showed each item only once with the combined total quantity for each item.
First the code on the button that copies the items from Collection1 to Collection2 (note: this button is only available when Collection2 is empty):
Collect(IrrigationShoppingList, ColIrrParts);
ClearCollect(ItemsByDes, GroupBy(IrrigationShoppingList, "IrrDesc", "Category", "Quantity"));
ClearCollect(ItemCount, AddColumns(ItemsByDes, "TotalQuantity", Sum(Quantity, Quantity)))
Here is another way of seeing the same code without my specific collection and column naming conventions:
Collect(Collection2, Collection1);
ClearCollect(Collection3, GroupBy(Collection2, "ColumnNameDescription", "ColumnNameCategory", "ColumnNameQuantity"));
ClearCollect(Collection4, AddColumns(Collection3, "TotalQuantity", Sum(ColumnNameQuantity, ColumnNameQuantity)))
When Collection2 is NOT empty, a different button appears when the user wants to add additional items to Collection2. This is the OnSelect code:
ForAll(
ColIrrParts,
Patch(
IrrigationShoppingList,
Defaults(IrrigationShoppingList),
{
Category: ColIrrParts[@Category],
IrrDesc: ColIrrParts[@IrrDesc],
Quantity: ColIrrParts[@Quantity]
}
)
);
ClearCollect(ItemsByDes, GroupBy(IrrigationShoppingList, "IrrDesc", "Category", "Quantity"));
ClearCollect(ItemCount, AddColumns(ItemsByDes, "TotalQuantity", Sum(Quantity, Quantity)));
Same code without my specific collection and column naming conventions:
ForAll(
Collection1,
Patch(
Collection2,
Defaults(Collection2),
{
Category: Collection1[@ColumnNameCategory],
IrrDesc: Collection1[@ColumnNameIrrDesc],
Quantity: Collection1[@ColumnNameQuantity]
}
)
);
ClearCollect(Collection3, GroupBy(Collection2, "ColumnNameIrrDesc", "ColumnNameCategory", "ColumnNameQuantity"));
ClearCollect(Collection4, AddColumns(Collection3, "TotalQuantity", Sum(ColumnNameQuantity, ColumnNameQuantity)))
Hi @Nate0173 ,
The below may not be the final solution - I may need to know where IrrDesc is shown on your screen as noted below.
If(
isBlank(
Lookup(
IrrigationShoppingList,
IrrDesc = YourScreenControlWithThisValue
IrrDesc
)
),
Patch(
IrrigationShoppingList,
Defaults(IrrigationShoppingList),
{
IrrDesc: YourScreenControlWithThisValue,
Quantity: Value(TextInputQuantity.Text) +
Value(TextInputShoppingQuantity.Text
}
),
UpdateIf(
IrrigationShoppingList,
IrrDesc = YourScreenControlWithThisValue,
{
Quantity: Value(TextInputQuantity.Text) +
Value(TextInputShoppingQuantity.Text
}
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
Following your model, below is what I have in my code and I'm receiving the error, "Invalid argument type". I'm not sure what I'm missing.
If(
IsBlank(
LookUp(
IrrigationShoppingList,
IrrDesc = LabelCartDescription.Text
)
),
Patch(
IrrigationShoppingList,
Defaults(IrrigationShoppingList),
{
IrrDesc: LabelCartDescription.Text,
Quantity: Value(TextInputQuantity.Text) +
Value(TextInputShoppingQuantity.Text
}
),
UpdateIf(
IrrigationShoppingList,
IrrDesc = LabelCartDescription.Text,
{
Quantity: Value(TextInputQuantity.Text) +
Value(TextInputShoppingQuantity.Text
}
)
)
Thanks @Nate0173 ,
I need to know which piece of code is generating the error. You have also left out a parameter on the lookup
If(
IsBlank(
LookUp(
IrrigationShoppingList,
IrrDesc = LabelCartDescription.Text,
IrrDesc
)
),
Patch(
IrrigationShoppingList,
Defaults(IrrigationShoppingList),
{
IrrDesc: LabelCartDescription.Text,
Quantity: Value(TextInputQuantity.Text) +
Value(TextInputShoppingQuantity.Text)
}
),
UpdateIf(
IrrigationShoppingList,
IrrDesc = LabelCartDescription.Text,
{
Quantity: Value(TextInputQuantity.Text) +
Value(TextInputShoppingQuantity.Text)
}
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
After running my app with the code, it appeared to initially work, then upon further investigation, a couple things are not correct.
Issue #1: Only the first item in ColIrrParts is being searched for, found, and quantity added to the IrrigationShoppingList collection. None of the other items in the ColIrrParts collection are being added.
Issue #2: When adding a new item from ColIrrParts to IrrigationShoppingList, or adding an already existing item, it adds the quantity for that item in ColIrrParts to the quantity located in the first item in IrrigationShoppingList. It is not adding the quantities of the matching items. For example:
IrrigationShoppingList:
ItemA Qty: 3
ItemB Qty:1
When adding ItemC (Qty: 1) into IrrigationShoppingList, this is the result:
ItemA Qty: 3
ItemB Qty: 1
ItemC Qty: 4 (Instead of the result being "1", it is adding quantity from ItemA to the quantity of ItemC in ColIrrParts)
Then, when adding ItemB (Qty: 2), this is the result:
ItemA Qty: 3
ItemB Qty: 5 (Instead of the result being "3", it is adding quantity from ItemA to the quantity of ItemB in ColIrrParts.)
ItemC Qty: 4
Hi @Nate0173 ,
As I mentioned in my original post, my code was only showing the possible required structure based on what you had posted, which was a bit unclear as to what you were trying to do with the code you had started. Now we have working code, I need to get my mind around your process.
You have two collections and want to update one from the other either by merge or new records. What is a bit unclear to me presently is that all the values seem to come off controls on the screen. Maybe a screen shot and a bit of a description as to how and when you want to trigger this process will be helpful.
I attached a pdf hoping that this helps explain a bit better.
Really appreciate you looking into this!
Hi @Nate0173
In order of events:
I cannot test this (and free-typed it so watch the brackets and commas), but can give you the direction that this needs to take - ensure field names are correct.
ForAll(
ColrrParts(
If(
IsBlank(
LookUp(
IrrigationShoppingList,
IrrDesc = colrrParts.IrrDesc,
IrrDesc
)
)
),
Patch(
IrrigationShoppingList,
Defaults(IrrigationShoppingList),
{
IrrDesc: colrrParts.IrrDesc,
Quantity: colrrParts.Quantity
}
),
UpdateIf(
IrrigationShoppingList,
IrrDesc = colrrParts.IrrDesc,
{Quantity: colrrParts.Quantity +
IrrigationShoppingList.Quantity}
)
)
)
Thanks again for your help on this!
I've been working on this code without success. Can you see what I'm missing? I receive "Invalid argument type".
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
200 | |
185 | |
69 | |
42 | |
34 |
User | Count |
---|---|
342 | |
268 | |
120 | |
64 | |
64 |