Answered

Forall Patch Error

(0) Share

Report

Posted on by tcariola

I have an app that is supposed to write into SQL tables from data in 2 collections using ForAll Patch functions. Below is the OnSelect statement for my submit button. It was working until recently when I began getting errors for the first ForAll function (second ForAll is still working correctly). Sometimes it patches all rows fine, sometimes it patches a few rows and misses a few rows, and other times all rows fail. It shows as a runtime error saying "Field 'CHECKBOOK_ID' is required" but I can see that this field is populated for all rows in the collection before I hit submit. Any suggestions would be greatly appreciated.

ForAll(CashReceipts,Patch('[dbo].[CLAIMED_PAYMENTS]',Defaults('[dbo].[CLAIMED_PAYMENTS]'),{CUSTOMER_ID:CustomerID,CURRENCY_ID:CurrencyID,CLAIMED_AMOUNT:Value(Amount),CHECKBOOK_ID:CheckbookID,Payment_Purpose:PaymentPurpose,Qty_Orders_Processed:Value(OrderQty),Date_Orders_Processed:OrderDate,Month_Year_Fees:MonthlyFees,Additional_Info:AdditionalInfo,Payment_ID:PaymentID}));ForAll(OMSIAdjustments,Patch('[dbo].[OMSI_ADJUSTMENTS]',Defaults('[dbo].[OMSI_ADJUSTMENTS]'),{OMSI_CLIENT:OMSIClient,OMSI_PROGRAM_LEDGER:OMSIProgram,OMSI_PRODUCT:OMSIProduct,OMSI_ACCOUNT_TYPE:OMSIAccountType,Add_Remove:AddRemove,OMSI_Amount:Value(OMSIAmount),CUSTOMER_ID:OMSICustomerID,PAYMENT_ID:PaymentID}));Patch('[dbo].[PAYMENTS]',First(Filter('[dbo].[PAYMENTS]',PAYMENT_ID = ThisItemID.PAYMENT_ID)),{Claimed:Today()});Navigate(PaymentsReceived_Screen,ScreenTransition.None)

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

GarethPrisk 2,828 on at

Like
a
(0)

Report
Copy link

Link copied!
Can you format that formula in the Studio, and then paste it in a reply here using a code snippet/sample? It's really hard to read, as currently formatted. 😣

At a glance, looks like the processing order is
ForAll ( CashReceipts, (Create new row in '[dbo].[CLAIMED_PAYMENTS]') )
ForAll ( OMSIAdjustments, (Create new row in '[dbo].[OMSI_ADJUSTMENTS]') )
Create new row in '[dbo].[PAYMENTS]'
Navigate
Now about those Collections:
How did you generate the CashReceipts collection?
How did you generate the OMSIAdjustments collection?
As far as patterns go. There's a chance you won't have to do a ForAll approach, and may be able to patch a list of records directly. This can help limit the overhead, and potential for errors/locks.
Otherwise, something to note about the ForAll function is that it doesn't loop from top to bottom, as you might expect. It's loosely documented that it basically processes the array of records in (some) order, but no guarantee it's first-to-last. This can give you some mixed troubleshooting.
You have the mapping from Collection to SQL, so you can consider Collecting instead of Patching in your ForAll, to get a preview of what would have been submitted. Then you can (in theory) patch that new collection directly to SQL.

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @tcariola :
According to your description (the code can run normally before), we can first rule out the problem of the code. I currently think of two situations that cause problems:
Case1: Some records in collection ‘CashReceipts’ are missing the value of CheckbookID.
Because we can only see the first 5 items in the collection preview, I suggest that you can check the collection by adding a table control and setting its items property to CashReceipts.
In addition, could you tell me how the CashReceipts is defined ?(preferably with screenshots)
Case2: Throttling Limits.
Because the patch is used multiple times, the number of times the API is called exceeds the limit.
I think this link will help you a lot:
SQL Server
If this is the case, I suggest you use collect function instead of forall function and patch function.
Best Regards,
Bof

Was this reply helpful? Yes No
tcariola 11 on at

Like
a
(0)

Report
Copy link

Link copied!

Appreciate the feedback...I figured out the issue, though. One of my SQL table fields is a date field but is not required as sometimes it can be NULL. The collection was storing as "" when no date was selected, rather than storing as a blank. I updated so this collection field would either be a date or completely blank (not "") and it seems to have fixed the issue. That explains why it was working sometimes (whenever a date was selected) and not others (when no date was selected). So simple but the error message was throwing me off since it was referring to a different field.

Thanks!

Was this reply helpful? Yes No
Verified answer

tcariola 11 on at

Like
a
(0)

Report
Copy link

Link copied!

Appreciate the feedback...I figured out the issue, though. One of my SQL table fields is a date field but is not required as sometimes it can be NULL. The collection was storing as "" when no date was selected, rather than storing as a blank. I updated so this collection field would either be a date or completely blank (not "") and it seems to have fixed the issue. That explains why it was working sometimes (whenever a date was selected) and not others (when no date was selected). So simple but the error message was throwing me off since it was referring to a different field. Thanks!

Was this reply helpful? Yes No