Create or update CDS when Excel in SharePoint is modified

[KED]

Hi there, we have an Excel stored in SharePoint that is updated daily with new rows. These rows represent loan inquiries and contain account and personal contact information and information about the loan inquiry itself. We would like to make a flow to create new records in a CDS entity when a record in Excel is first added. (From there, values need to be appropriately sorted into the Account, Contact, and Case entities, but that's a separate issue). However, if you list rows in the Excel table in an apply to each, and create a record in CDS, any time the file is modified, the flow creates every single record in the Excel table again in CDS. 

By the way, the documentation says "You can leverage the update a record action to provide upsert actions, which updates the record if it already exists, or creates a new record" (https://docs.microsoft.com/en-us/power-automate/connection-cds-native). However, the flow seems to just hang and not do anything if instead of create a record the action is just update a record.

[abm]

Hi @KED 

In your update method are you passing the identifier as GUID?

Thanks

[v-alzhan-msft]

Hi @KED ,

I'm afraid that it is not easy to check which record is new created.

And I would suggest to when a file is created or modified, delete all the records in the CDS entity, and add all the rows from the excel table to the CDS entity, this would be a effective solution to your requirement and you needn't update the record in CDS.

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KED]

@abm It requires that I pass the ID from the Excel spreadsheet. That ID just gets written into the CDS entity as a regular field, no relation to the entity GUID. Btw I thought about writing an expression saying if UID in CDS ≠ Excel UID, then create new record. But (and I'm new to Power Automate) I think I'm discovering that you're limited to calling variables to whatever was in the trigger, which here is the Excel. Is there anyway to call a variable from another source?

[v-alzhan-msft]

Hi @KED ,

Have you take a try with my suggestion?

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KED]

Hi @v-alzhan-msft you had suggested having the flow delete all records in the target CDS entity each time I need to write a new record into that entity from the source Excel. The problem with this is that several of the fields in the target entity are getting updated inside of CDS, so their values need to be preserved.

Right now my plan is to just be veeeeeery careful when I click save on the Excel, and to only have that Excel hold the latest records. So every time I will need to update the Excel, I will go in, delete the last batch's records, and paste in the new batch. Any time the Excel is in any way modified, even an accidental save, the flow writes every single of the hundreds of records into the target entity again regardless of whether or not they are already in there, and I have another flow that creates Accounts, Contacts, and Cases from it. I'm expecting at some point I will generate duplicates in multiple entities through an accidental save. Even as it is, sometimes I save that Excel and the flow doesn't start, and sometimes I save it and the flow does start. I never know if it's going to run or not, so sometimes I sit there for several minutes waiting.

I just feel this is a common use case - there's this one platform with a broken API where I can do a daily data dump to .csv. I just want to stick the new records from the .csv in an Excel sitting in SharePoint that's serving as the datasource for this CDS entity. If I'm thinking about this wrong please someone give me an idea.

[abm · Accepted solution]

Hi @KED 

Thanks for the discussion and finding the solution for you.

Following are the steps:

1. Added a new column  (CDSUpdate)  in excel sheet

2. Filter the new column CDSUpdate is not Yes

3. Finally update the Excel sheet CDSUpdate column to 'Yes'

Thanks a lot

[KED]

Thank you so much @abm! An elegant solution and clear and thorough explanation.

How to screenshots 👇

After this runs the value of the CDSUpdate column in Excel is Yes, and the next time you add new records in Excel it will not write that one again into CDS.

[abm]

Hi @KED 

Thanks for the screenshot update. This will definitely help the other community members when they were looking for solution.