Saving an Updated Collection back to Dataverse Table

[ameeru18]

Hello community,

I have an App utilizing several Dataverse Tables.

The DV tables are pulled into filtered collections. One collection has parts of the other collections for use in the main Gallery for searching, filtering, sorting etc. This main Gallery filters an action area that shows the other collections depending on what needs to be seen.

In usage the independent Collections are edited and those edits are pushed to an Update Collection built from the same DataVerse Table. I am looking to write these collections back to DataVerse.

Dataverse Table	Working/Viewing Collection	Update Collection
Projects	colProjects	colProjectsUpdate
Tasks	colTasks	colTasksUpdate
Permits	colPermits	colPermitsUpdate
Clients	colClients	colClientsUpdate

I am able to update the Projects and write it back to Dataverse using the below method.

Code to update locally:

Patch(colProjects,ThisItem,
{
cr577_projectid: PcolProjectID.Text,
cr577_projectname: PcolProjectName.Text,
cr577_projectstatus: PcolProjectStatus.Selected.Value,
cr577_projectnotes: PcolProjectNotes.Text,
cr577_Assigned: PcolAssigned.Selected,
cr577_projectaddress: PcolProjectaddress.Text,
cr577_apn:PcolProjectAPN.Text,
cr577_JBAClient: PcolClient.Selected
}
);

Patch(colProjectsUpdates,ThisItem,
{
cr577_projectid: PcolProjectID.Text,
cr577_projectname: PcolProjectName.Text,
cr577_projectstatus: PcolProjectStatus.Selected.Value,
cr577_projectnotes: PcolProjectNotes.Text,
cr577_Assigned: PcolAssigned.Selected,
cr577_projectaddress: PcolProjectaddress.Text,
cr577_apn:PcolProjectAPN.Text,
cr577_JBAClient: PcolClient.Selected
}
);

Code to push it to Dataverse that works for the Project collection back to Dataverse.

ForAll(colProjectsUpdates,
Patch('JBA Projects',
LookUp('JBA Projects',cr577_projectid = colProjectsUpdates[@cr577_projectid]),
{
'Project Name': cr577_projectname,
'Project Status': cr577_projectstatus,
Assigned: cr577_Assigned,
'Project Address': cr577_projectaddress,
APN: cr577_apn,
'Project Notes': cr577_projectnotes,
'JBA Client': cr577_JBAClient
}
)
);

When I try using this same methodology on the other Tables in Dataverse it does not work. I can save locally, but I will not push back to Dataverse.

I am using the additional update collection to only show what is being updated to keep the final save call quick. I have used several ForAll, Patch, Collect, LookUp variations of the above code on the other tables to no avail. I thought it was an ID issue, but the collection have both their primary column and unique ID column included in the Collection build so I have hit a wall.

Any assistance would be greatly appreciated.

[RandyHayes]

@ameeru18 

To start, your formula has the ForAll backward. You are trying to use it like a ForLoop in some development language - which PowerApps is not.  ForAll is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance as your Patch inside the ForAll including a LookUp at the same time will have horrible performance.

Your formula should be the following:

Patch('JBA Projects',
    ForAll(colProjectsUpdates,
    {
      cr577_projectid: cr577_projectid,
      'Project Name': cr577_projectname,
      'Project Status': cr577_projectstatus,
      Assigned: cr577_Assigned,
      'Project Address': cr577_projectaddress,
      APN: cr577_apn,
      'Project Notes': cr577_projectnotes,
      'JBA Client': cr577_JBAClient
      }
    )
);

Now in the above, I am assuming that cr577_projectid is the primary column.  If not, then replace as needed.

Perform the same style of formula for your other tables and pay special attention to any complex column types in your data.

IF you get an error stating that Patch is expected a Record, this is a misleading error...it really means that your record schema in the ForAll is not correct.  So, check all field names and make sure you are supplying the proper real name of the columns and that you are providing the exact type of data expected for the column.  Once you have that correct, the error message will go away and the formula will work properly.

Also note: if you happen to be getting this data in your collection from a Gallery, then there is no need even for the collection - all the data already exists in the gallery and can be retrieved from there, thus eliminating the need to duplicate your gallery data into another table in your app memory.

I hope this is helpful for you.

[ameeru18]

Thank you for the response. Your method sets up without error, but when I run it I get the following error.

"Network error when using Patch function: Conflicts exist with changes on the server, please reload"

This is on my JBA Tasks DV table using the Taskscol and TaskscolUpdates collections.

Currently, I am working off of Collections and I use a Schema duplicate Update collection so that when I send the Patch - ForAll it only has the latest updates to comb through since I Clear the Update Collections every time I Push the final Patch or when I hit the Undo button.

When I run the Code below for this table it works rather quickly. However with my other Collections Tasks and Applications the same setup does not work.

ForAll(ProjectsColUpdates,     
Patch('JBA Projects',
LookUp('JBA Projects',cr577_projectid = ProjectsColUpdates[@cr577_projectid]),
{
'Project Name': cr577_projectname,
'Project Status': cr577_projectstatus,
Assigned: cr577_Assigned,
'Project Address': cr577_projectaddress,
APN: cr577_apn,
'Project Notes': cr577_projectnotes,
'JBA Client': cr577_JBAClient
}
)
);

Clear(ProjectsColUpdates); //Clears the collection to make it ready to accept updates.

I agree the ForAll is backward for PowerFx, but it was the only method that allowed the Update to the DV table without error. I reversed the two by accident after constantly getting the Network error.

This method is not working for my other Tables however and they are set up the exact same way. When I use your method with those tables I get the Network error when using Patch function: Conflicts exist with changes on the server, please reload. Is there a way to perform this reload am I overlooking something else? I have the main column in the run. Thanks

[ameeru18 · Accepted solution]

This is what ended up working after going 12 rounds with this issue. I do my editing and it saves into a Collection from a collection. When all the work is done I hit a final save button to push back to Dataverse. The Tasks side was the problematic side.

ForAll(
TasksColUpdates,
Patch(
'JBA Activity Lists',
First(
Filter('JBA Activity Lists', jba_jbataskid = jba_jbataskid)

),
{
jba_jbataskid:jba_jbataskid,
jba_ProjectID:jba_ProjectID,
jba_projecttask:jba_projecttask,
jba_taskpriority:jba_taskpriority,
jba_taskstatus:jba_taskstatus
}
)
);

//Clears the collection to make it ready to accept updates.
Clear(TasksColUpdates);

[Krishna09]

hello @ameeru18 ,

I have tables in dataverse, I am looking these tables through a gallery in canavas app. I filter gallery and edit the records in gallery. The edited records are saved in collection. I have multiple records in collection. I am using either  unique identifier or primary columns to match and update records in dataverse.

1. APP-> Onstart :- ClearCollect(
updateTest,
ShowColumns(Table(Defaults(Test_BulkUpudate_GMB_Tables)),"zk_actiondate_test","zk_ch_priority_test","zk_project_id_test","zk_sourcetest", "zk_name", "zk_test_bulkupudate_gmb_tableid")
)

2. Gallery-> OnSelect:-

If(ThisItem.Test_BulkUpudate_GMB_Table in updateTest.zk_Test_BulkUpudate_GMB_TableId,
Update(updateTest,
LookUp(updateTest,zk_Test_BulkUpudate_GMB_TableId = ThisItem.Test_BulkUpudate_GMB_Table),
{
zk_Test_BulkUpudate_GMB_TableId: ThisItem.Test_BulkUpudate_GMB_Table,
zk_Project_ID_test: txt_projectID_1.Text,
zk_actiondate_test: ActionDate_1.SelectedDate,
zk_ch_priority_test: dp_Priority_1.Selected.Value,
zk_sourcetest: dp_sourceTest_1.Selected.Value,
zk_name:lb_name
}
),Collect(
updateTest,
{
zk_Test_BulkUpudate_GMB_TableId: ThisItem.Test_BulkUpudate_GMB_Table,
zk_Project_ID_test: txt_projectID_1.Text,
zk_actiondate_test: ActionDate_1.SelectedDate,
zk_ch_priority_test: dp_Priority_1.Selected.Value,
zk_sourcetest: dp_sourceTest_1.Selected.Value

}
))

3. Save button-> OnSelect:- I tried so many formulas.

I followed https://www.youtube.com/watch?v=8I0Pt_8I6k8&t=922s

a:- source - From video I followed. 

error- 

If(CountRows(updateTest)>0,
Patch(Test_BulkUpudate_GMB_Tables,updateTest)
;
Notify("Success",NotificationType.Success));

b:- source :- from your suggestion https://powerusers.microsoft.com/t5/Building-Power-Apps/Saving-an-Updated-Collection-back-to-Dataver...

error :- no change in records of dataverse.

ForAll(updateTest,Patch(Test_BulkUpudate_GMB_Tables,First(Filter(Test_BulkUpudate_GMB_Tables, Name =zk_name)),
{
Project_ID_test: zk_Project_ID_test,
ch_priority_test:zk_ch_priority_test

}));

b1:- error:-

ForAll(updateTest,Patch(Test_BulkUpudate_GMB_Tables,First(Filter(Test_BulkUpudate_GMB_Tables, Test_BulkUpudate_GMB_Table =zk_test_bulkupudate_gmb_tableid)),
{
Project_ID_test: zk_Project_ID_test,
ch_priority_test:zk_ch_priority_test

}));

c:- source- https://www.matthewdevaney.com/7-ways-to-use-the-patch-function-in-power-apps-cheat-sheet/#6.-Upsert...

error.-no change in records of dataverse. 

Patch(Test_BulkUpudate_GMB_Tables,
ShowColumns(updateTest,"zk_test_bulkupudate_gmb_tableid","zk_name")
);
Clear(updateTest)

d:- error

Formulae.-

Patch(Test_BulkUpudate_GMB_Tables,
LookUp(updateTest,
Name=zk_name,
{
Name:zk_name,
ch_priority_test:zk_ch_priority_test,
Project_ID_test:zk_project_id_test,
'Action Date_test':zk_actiondate_test,
sourceTest: zk_sourcetest}
));

e:- creating a new record in dataverse table.

UpdateIf(Test_BulkUpudate_GMB_Tables, Test_BulkUpudate_GMB_Table= zk_test_bulkupudate_gmb_tableid,
{ Test_BulkUpudate_GMB_Table:zk_test_bulkupudate_gmb_tableid,
ch_priority_test:zk_ch_priority_test,
Project_ID_test:zk_project_id_test,
sourceTest: zk_sourcetest

});

Please suggest and help me to solve, where I am going wrong here. I want to update collection with multiple records at same time to dataverse (upsert ) with either unique identifier or primary column.