I am trying to update rows in a Dataverse table ('Product Catalog',) based on whether they appear in a Collection ('CustomerCart'). The Collection is created in my PowerApp and represents an online shopping cart. Several items can be added to the cart from a gallery that is connected to the Dataverse table and contains items that can be purchased.
The user would then checkout, and any items in the collection would represent a change to the Dataverse table, specifically incrementing the 'PurchasedStock' column by 1 and reducing the 'QtyStock' column by 1. I am trying to iterate through the collection, as there could be multiples of the same item within the Collection, or indeed none of other items. I have used a Patch function and UpdateIf function, both wrapped in a ForAll function but neither method is working and my formulas are clearly not resolving as expected.
My logic is if I iterate through the Collection with a ForAll function, I can grab the ID of the product, and perform a change on the Dataverse table record with the matching ID. E.g if the customer wants to buy 2x FLPFLP-016 and 1x CNVAS-011 the Collection would look as follow:
ProductNumber
FLPFLP-016
FLPFLP-016
CNVAS-011
For each of these records a Patch of UpdateIf would be performed to modify the correct record in the Dataverse table. So for this the FLPFLP-016 would be modified, then that same record would be modified again, and then finally the CNVAS-011 would be modified. This would represent a total of:
-2 QtyStock/+2 PurchasedStock for the FLPFLP-016 record,
and -1 QtyStock/+1 PurchasedStock for the CNVAS-011 record
as this is what appears in the Collection.
For the Patch method, this approach worked when I hardcoded in the ProductNumber value (i.e. FLPFLP-016) for the comparison. But fails to make any changes to the Dataverse table when the ThisRecord.ProductNumber value is substituted in (which I thought would resolve to FLPFLP-016, the ProductNumber anyway).
As I couldn't get that method working I tried UpdateIf.
For the UpdateIf method, the approach was the same. This time though, when I run the formula every record in the Dataverse table is impacted, and it is impacted once for every record in the Collection! So if the Collection had 3 records in it then 3 would be added to PurchasedStock and 3 would be subtracted from QtyStock in the Dataverse table, for every record in the table.
There are no errors in the code, and like I said - it works as expected for the Patch method if I hardcode in a string for the ProductNumber comparison. It is only when I switch to comparing to the ProductNumber from the Collection using 'ThisRecord' that the formula stops working.
Patch method:
ForAll(
CustomerCart.ProductNumber,
Patch(
'Product Catalog',
LookUp(
'Product Catalog',
ProductNumber = ThisRecord.ProductNumber
),
{
PurchasedStock: LookUp(
'Product Catalog',
ProductNumber = ThisRecord.ProductNumber,
PurchasedStock
) + 1
},
{
QtyStock: LookUp(
'Product Catalog',
ProductNumber = ThisRecord.ProductNumber,
QtyStock
) - 1
}
)
)
UpdateIf method:
ForAll(
CustomerCart.ProductNumber,
UpdateIf(
'Product Catalog',
'Product Catalog'[@ProductNumber] = ThisRecord.ProductNumber,
{
PurchasedStock: PurchasedStock + 1,
QtyStock: QtyStock - 1
}
)
)
