Batch Update 3 sharepoint list tables if the code identifier changes which is text changes in the master_list

[JD_26]

I have 4 tables all fed from Sharepoint List

The master table is the list of companies , each company has a unique code in text format

There are 3 additional tables , that are all linked by the code value , which internally is the Title column.

I am trying to find a solution to if the code is changed in the master table , then that code would be updated with the new value in all of the other tables.

Is this possible.

Example

Master Table

Name              Code

Bobs Burgers   BB1

Type Table

Code    Type

BB1       Food Vendor

Contact Table

Code Email

BB1    bob.burger@bobsburger.com

Activity Table

Code     Date             Message

BB1       01/01/2022   New order of 10,000 quarter pounders

BB1       03/03/2002   New order of 10,000 cheese slices

If BB1 was changed to BB2 in the Master Table edit form , then I would need all the data to look like this

Master Table

Name              Code

Bobs Burgers   BB2

Type Table

Code    Type

BB2       Food Vendor

Contact Table

Code Email

BB2    bob.burger@bobsburger.com

Activity Table

Code     Date             Message

BB2       01/01/2022   New order of 10,000 quarter pounders

BB2       03/03/2002   New order of 10,000 cheese slices

[PowerRanger]

@JD_26 you have several options here.

1. Redesign your SharePoint Lists and use LookUp Columns in your additional lists then you don't have ToDo anything as the tables are linked with each other using the SharePoint ids

2. Use Power Automate and listen on the Master List. Whenever the code column changes update the additional lists with the new value.

3. Using Powerapps. When you submit the Form of your Master List and the code column has changed you need to Patch the additional lists accordingly.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[pandadev]

Thanks , is it possible to see how the patch code looks and how to apply it please , as I have only been working with Power Apps for 4 days , so everything is new to me , I have been trying to watch as many videos as I can , but this is the issue I am struggling with , along with trying to filter the tables to show all the rows in the master table that does not have a matching code in the other tables.

[PowerRanger]

@pandadev @JD_26 please find below an example. Not sure if it will fit into your app but I guess you will get the idea behind:

1. My screen look like this

2. The Forms Item property is set to

Gallery2.Selected

3. The Gallery OnSelect property is set to 

Set(varSelectedItem,ThisItem)

4. The Form OnSuccess property is set to

If(
    Self.LastSubmit.Code <> varSelectedItem.Code,
    Patch(
        TypeTable,
        ForAll(
            Filter(
                TypeTable,
                Title = varSelectedItem.Code
            ),
            {
                ID: ThisRecord.ID,
                Title: Form3.LastSubmit.Code
            }
        )
    ),
    "DoNotPatch"
);

So on OnSuccess we check if the Item Code which we have selected in the Gallery is different to the one from the Forms LastSubmit. If it is different we will Patch the TypeTable. We will Patch all records (ForAll...) which have the Code value from the record we initially selected from the Gallery (this is the OLD value before the submit). We then just Patch the Title column with new Value.

You have to duplicate the Patch command for all tables you want to update.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[pandadev]

I have added this code and there are no errors showing on the on success , but the contacts_list is not being updated with the value Title , which is the code field

If( Self.LastSubmit.Title <> varSelectedItem.Title, Patch( Contacts_List, ForAll( Filter( Contacts_List, Title = varSelectedItem.Title ), { ID: ThisRecord.ID, Title: Form_Account_edit.LastSubmit.Title } ) ), "DoNotPatch" );Notify("Request successfully submitted",NotificationType.Success);Refresh(Account_Profile);Back()

[PowerRanger]

@pandadev I had this issue also once and the reason was that the lists were out of sync already. So the Filter didn't return any records. 

1. Verify that Master List and Contacts List have the same code value in sharepoint.

2. Refresh your datasources in PowerApps

3. Add this Code infront the If-Statement in the OnSuccess property

ClearCollect(colContactsToUpdate,Filter( Contacts_List, Title = varSelectedItem.Title ))

4. Select the Master-Item in the Gallery

5. The Edit Form should show the selected item

6. Change the Code Value

7. Submit the Form

8. If the contacts list was not updated check if the collection colContactsToUpdate contains any records

With the Patch() approach you might run into this issue that the MasterList get's updated but not the other lists. If something fails after the submit of the form your lists are out of sync.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[pandadev]

one final question as your instructions fixed the issue , but I am now struggling to apply the 2nd patch , here is my attempt , but it is showing as bad formula 

If( Self.LastSubmit.Title <> varSelectedItem.Title, Patch( Master_Profiles, ForAll( Filter( Master_Profiles, Title = varSelectedItem.Title ), { ID: ThisRecord.ID, Title: Form_Account_edit.LastSubmit.Title } ,Patch( Activity_List, ForAll( Filter( Activity_List, Title = varSelectedItem.Title ), { ID: ThisRecord.ID, Title: Form_Account_edit.LastSubmit.Title } ) ), "DoNotPatch" ;

Notify("Request successfully submitted",NotificationType.Success);Refresh(Master_Profiles);Back()

[PowerRanger · Accepted solution]

@pandadev You have to separate the Patch Commands with a ;

So it will look like:

If( Self.LastSubmit.Title <> varSelectedItem.Title, Patch( Master_Profiles, ForAll( Filter( Master_Profiles, Title = varSelectedItem.Title ), { ID: ThisRecord.ID, Title: Form_Account_edit.LastSubmit.Title } ;Patch( Activity_List, ForAll( Filter( Activity_List, Title = varSelectedItem.Title ), { ID: ThisRecord.ID, Title: Form_Account_edit.LastSubmit.Title } ) ), "DoNotPatch" ;


Notify("Request successfully submitted",NotificationType.Success);Refresh(Master_Profiles);Back()

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[PowerRanger]

@pandadev If this fixed your issue please accept my answer as solution. Thanks