Power Platform Community Forum Thread Details

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

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

}

)

Categories:

Connecting to Data

General Questions

The issue you're facing might be related to how you're using the LookUp function within the Patch function or the UpdateIf function. Instead of using LookUp within the Patch/UpdateIf, you can directly use the reference to the current item in the ForAll loop.

Here's an updated version of your formulas using ForAll:

Patch Method:

ForAll(
 CustomerCart,
 Patch(
 'Product Catalog',
 LookUp(
 'Product Catalog',
 ProductNumber = CustomerCart[@ProductNumber]
 ),
 {
 PurchasedStock: PurchasedStock + 1,
 QtyStock: QtyStock - 1
 }
 )
)

UpdateIf Method:

ForAll(
 CustomerCart,
 UpdateIf(
 'Product Catalog',
 ProductNumber = CustomerCart[@ProductNumber],
 {
 PurchasedStock: PurchasedStock + 1,
 QtyStock: QtyStock - 1
 }
 )
)

Please note:

Use CustomerCart[@ProductNumber] instead of ThisRecord.ProductNumber within the ForAll loop. This is because ThisRecord is not necessary in this context, and you directly reference the current item in the collection using CustomerCart[@ProductNumber]

Use ProductNumber = CustomerCart[@ProductNumber] as the condition in the LookUp function or the UpdateIf function. This ensures that you are filtering the 'Product Catalog' table based on the current item in the collection.