Power Platform Community Forum Thread Details

I have a single page app for data entry which pulls values from two SQL server tables and then pushes data back to one of these tables.

I copied someone else's (working) app and changed the SQL server tables from theirs to mine.

However I'm getting errors all over the place and I can't figure out why.

I think most of the errors relate to the patch statement OnSelect of my Submit button, which should push all changes made to a record into a SQL server table.

My syntax for the Submit Button is :

If(
 CountRows(CollectUpdate) > 0,
 Patch(
 'Powerapps.CRM_SCHEDULE_OF_SERVICES',
 CollectUpdate
 );
 Notify(
 "Success",
 NotificationType.Success
 )
);
Clear(CollectUpdate);

The red zigzag lines start at the word 'Patch' and stop at the first semi colon.

The error message seems to be asking for a record, and not a table - but the existing app references a table and is fine.

It looks at five fields for changes and the field types are 3 x drop down, 1 x text input (number) and 1 x checkbox. Plus two meta data fields. The five input fields all have an identical formula for OnChange which is also broken at the point I mention 'Collect'.

Here is that code:

If(
 ThisItem.ACCOUNT_ID in CollectUpdate.ACCOUNT_ID,
 Update (
 CollectUpdate,
 LookUp(
 CollectUpdate,
 ACCOUNT_ID = ThisItem.ACCOUNT_ID
 ),
 {
 ACCOUNT_ID: ThisItem.ACCOUNT_ID,
 SERVICE_WEIGHTING: Value(NEW_SERVICEWEIGHTING.Selected.Value),
 FREQUENCY_AGREED: Value(NEW_FREQUENCYAGREED.Selected.Value),
 VOLUME_AGREED: Value(NEW_VOLUMEAGREED.Text),
 SCHEDULE_ACHIEVED: Value(NEW_SCHEDULEACHIEVED.Selected.Value),
 USER: User().Email,
 LOADDATE: Now()
 }
 ),
 Collect (
 CollectUpdate,
 {
 ACCOUNT_ID: ThisItem.ACCOUNT_ID.Text,
 SERVICE_WEIGHTING: Value(NEW_SERVICEWEIGHTING.Selected.Value),
 FREQUENCY_AGREED: Value(NEW_FREQUENCYAGREED.Selected.Value),
 VOLUME_AGREED: Value(NEW_VOLUMEAGREED.Text),
 SCHEDULE_ACHIEVED: Value(NEW_SCHEDULEACHIEVED.Selected.Value),
 YEAR: Value(SCHEDULE_YEAR.Selected.Value),
 USER: User().Email,
 LOADDATE: Now()
 }
 )
)

Can anyone help me fix this?

Categories:

Building Power Apps

Hi,

To fix the Patch statement and Collect related issues you're encountering, you need to make sure of a few things:

Schema Consistency: Ensure that the schema of the table you're patching in your app ('Powerapps.CRM_SCHEDULE_OF_SERVICES') matches the schema of the table in the SQL server. This includes column names and data types.
Patch Function Usage: The Patch function typically requires three arguments when updating: the data source, the record you want to update, and the changes to apply. The error indicates that it's expecting a record, not a table, because the second argument should be a specific record or a record reference, not an entire collection or table.
Collect Function: Collect creates a local collection, and it seems you're trying to use CollectUpdate as if it's a buffer for changes which you're then trying to Patch to your SQL table. This might not be working if CollectUpdate is not formatted correctly as a single record or if it's a collection of records.

Here’s a simplified outline to correct the process:

When the user changes data on the app, instead of using Collect, you might want to use a local collection to store all changes temporarily.
Upon clicking the Submit button, iterate through the local collection of changes and patch them to the SQL server one by one.

Here is a simplified version of the code for the Submit button:

ForAll(
 CollectUpdate as _Update,
 Patch(
 'Powerapps.CRM_SCHEDULE_OF_SERVICES',
 LookUp(
 'Powerapps.CRM_SCHEDULE_OF_SERVICES',
 ACCOUNT_ID = _Update.ACCOUNT_ID
 ),
 _Update
 )
);
If(
 Errors('Powerapps.CRM_SCHEDULE_OF_SERVICES').Count = 0,
 Notify("Success", NotificationType.Success),
 Notify("

Make sure that CollectUpdate has the same structure as your SQL table and that you're using LookUp to find the corresponding record in your SQL table to patch.

For the OnChange of your input fields, instead of using Update or Collect directly to CollectUpdate, you might want to use Patch to update a single record in the collection, ensuring the local collection maintains the same structure as the SQL table.

Remember, Collect adds a record to a local collection, Update modifies an existing record in a local collection, and Patch can either modify an existing record or add a new record to a data source.

You'll also want to check that all the field values you're using match the expected data types in the SQL table. If a field in SQL expects a number and you're passing text, this will cause an error.

Finally, check if there are any permissions issues or connectivity problems that might be causing the failure.

Best Regards,

Hassan Raza