Answered

Need Help with Patching Sharepoint list from 2 different collections

(0) Share

Report

Posted on by Digitrony64

I am building an offline powerapp for our sales department to use when internet connectivity is limited. I have managed to build an app where customer data and contacts are added to two collections (i.e. colCustomerData, and colContactsData). They are tied to two SP Lists called Customers and Contacts. The one-to-many relationship is established between the two lists using ID from the Customers and RefID from Contacts. Everything works as expected within the app when users add customers and contacts, however, the problem I'm having is when it comes time to sync (patch) the data back to the datasources, in this respect the two SP lists (Customers, and Contacts), and maintain the relationship.

This is the desired result I am seeking and hopefully one of you gurus can help.

When the user enters more than one customer in the app, and associates several contacts to that customer, when they are online and sync the data back to SharePoint, I want the app to be able to enter the customer and contacts for each customer and its associated contacts. Additionally, I am using an add and update button to add or update the record in the collections.

Contacts Add Button:

OnSelect: Collect(colContactsData, {Title: Contact_DataCard1.Update, Contact_x0020_Phone: 'Contact Phone_DataCard1'.Update, Contact_x0020_Email: 'Contact Email_DataCard1'.Update, RefID: varRecord.ID});SaveData(colContactsData, "SavedContactData");ResetForm(ContactsEditForm);NewForm(ContactsEditForm)

Customer Add Button:

OnSelect: Collect(colCustomersData, {Title: Customer_DataCard2.Update, Address: Address_DataCard2.Update, PhoneNumber: Phone_DataCard2.Update, Salesperson0: Salesperson.Selected, ID: CountRows(colCustomerData)+1});ResetForm(CustomerEditForm);SaveData(colCustomerData, "SavedCustomerData");NewForm(CustomerEditForm)

I have tried several Patch, ForAll, Lookup combinations but can't seem to wrap my head around how to get this to work. Here is what works for one record:

Set(varID,Patch(Customers,Defaults(colCustomerData),CustomerEditForm.Updates).ID);Patch(Contacts,ForAll(colContactsData,{'Ref ID': varID, Title: Title, ContactPhone: ContactPhone, ContactEmail: ContactEmail}))

I am trying to get the app to work for all records. I am new to this forum so please excuse me if this has already been addressed. I have been Googling for days and can't seem to find the precise answer to my problem. Thanks for all that you do. Best Regards.

Categories:

Building Power Apps

All responses (2)

Answers (1)

Sort by

Digitrony64 13 on at

Like
a
(0)

Report
Copy link

Link copied!

Allow me to add more insight to what I am trying to accomplish with your help of course.

There are two lists (really three but for simplicity's sake we'll keep it to two), this is the Customers list:

and Contacts list, respectively:
The RefID should match the Customer list "ID" when patching from a collection to SharePoint. There are two collections: colCustomersData and colContactsData
In the Powerapps, when adding contacts, the RefID is the same as the customers ID using the variables varRecord i.e. If(ContactsEditForm.Mode = FormMode.New, varRecord.ID, ThisItem.RefID)
colContactsData collection:
However, this is the challenge I am facing...
When patching the data back to their respective SharePoint lists, I can't seem to figure out how to maintain the relationship between the two lists. Hence, the Customers list is the Master List and the Contacts list the the Child. It's patching the data back to SharePoint and maintaining the relationship is where I am finding very challenging to say the least.

What does work is using this formula:
Set(varID,Patch(Customers,Defaults(colCustomerData),CustomerEditForm.Updates).ID);Patch(Contacts,ForAll(colContactsData,{'Ref ID': varID, Title: Title, Contact_x0020_Phone: Contact_x0020_Phone, Contact_x0020_Email: Contact_x0020_Email}))

Using the ForAll statement and changing the RefID Datacard on the ContactsEditForm to

If(ContactsEditForm.Mode = FormMode.New, varID, ThisItem.RefID)

Works as intended when patching the data back to SharePoint. But I can't use this function in an Offline app. That is the conundrum I'm facing. When I patch the Customer data back to the SharePoint list, the ID field is automatically generated. But when I patch the contact data, it does not match the auto generated customer ID and so the relationship is broken. I should also mention that the SharePoint lists is also being used by an Online app that works great. It's getting the Offline app to sync the data nicely to maintain the relationship were I'm having trouble. If anyone can help shed some light on this matter, I would be greatly appreciative of your efforts. Please let me know if there is additional information I can provide that would be helpful. Best Regards.
Verified answer

Digitrony64 13 on at

Like
a
(1)

Report
Copy link

Link copied!

Hello Community,

Well I finally found the answer. The question? How do you get the ID's to match for a one to many relationship in an offline app? The answer came from this post Power Apps Offline Mode: A Step-By-Step Tutorial - Matthew Devaney. In short, you cannot get the ID's to match using the auto generated ID from a SharePoint list. The trick was to create your own ID column (in my case, called OfflineID), and use this command in the OnSelect property when creating new records:
Set(varNewOfflineGUID, GUID());

Then in the OfflineID card in the form add:
If(FormMode.New, varNewOfflineGUID, Parent.Default)

The rest was a matter of following this from the amazing Reza: (3) Power Apps working with SharePoint List Relationships - YouTube.

I now have a working CRM app that is both offline and online. Great stuff!