Prevent Duplicate in SharePoint using Composite Key when saving from a Powerapps Collection

[Anonymous]

Hi Power Automate community:

I have a collection generated within PowerApps and I would like to save the records to SharePoint ensuring there are no duplicates.  In the SharePoint  list I created a calculated field called "Composite" which consists of 2 fields separated  by a pipe "l".

The following is the Automate flow I have been trying generates the following error:

 InvalidTemplate. Unable to process template language expressions in action 'Get_items' inputs at line '1' and column '2874': 'The template language expression 'json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline']['connectionId']' cannot be evaluated because property 'shared_sharepointonline' doesn't exist, available properties are ''. Please see https://aka.ms/logicexpressions for usage details.'.

The Automate Flow has the following components:

Can someone assist me with correcting this  flow please.

Barry

[ChristianAbata]

To prevent duplicate data in sharepoint you can edit your Column to enforce yes.

[Anonymous]

Thank you for your response but I wish to prevent duplicates across two columns in SharePoint.  I have created a calculated field that combines the 2 columns but it is impossible to have this calculated field as "Unique" in SharePoint.  That is why I am resorting to Flow.  If you have a Flow solution for this I would be please to see it.  The following is my flow elements that generate the error

[ChristianAbata]

@Anonymous oh maeby what you want is this

Try with Union(Array,Array) Thats get only the diferent values from array.

See this solution to get more details.

https://powerusers.microsoft.com/t5/General-Power-Automate/microsoft-flow-get-all-items-with-distinct-Assigned-to-User-and/m-p/114429#M42528

[v-bacao-msft · Accepted solution]

Hi @Anonymous ,

For InvalidTemplate errors, you could refer to this similar thread:

https://powerusers.microsoft.com/t5/General-Power-Automate/Flow-Error/td-p/352714

Also about Prevent Duplicate in SharePoint, you can refer to the following method.

You can add a parallel branch to handle the two cases of Update item and Create new item.

Update item:

Create item| Use the Filter array to determine if there are already eligible items in the List. Use the length() function to get the number of items filtered by the Filter array. If 0, create a new item.

Replace Title with the field you want to compare.

Hope this helps.

Best Regards,

[Anonymous]

Many thanks for your incisive response _ I will give it a try  and advise 

Barry

[Anonymous]

Also I checked my PowerApps button OnSelect command

SaveAuditCompileToFindings.Run(JSON(AuditInfoComposite))

This command registers an error in PowerApps as follows: 

1st Error = Invalid number of arguments - received 1 expected 2

2nd Error = Invocation of unknown or unsupported function

[Anonymous]

• v-bacao-msft

Hi again,

Perhaps it will be better if I use an auto SharePoint trigger "When a new item is created or modified" rather than a PowerApps button.

If so, I do not know how to setup the Flow for this.  Could you help with creating the Flow - Barry

[v-bacao-msft]

Hi @Anonymous ,

So, is the condition triggered by Flow that the item is newly created or changed?

If so, you only need to add When an item is created or modified as a trigger in the method mentioned in my last reply.

Please take a try.

Best Regards,

[Anonymous]

Hi v-bacao-msft,

Thank you for your continued response - No the Trigger is a button from Powerapps not "item is newly created or changed".   It would be better if I could use "When Item is created or change" but I did not know how to compare my powerapps collection with my SharePoint List with "When Item is created or change" trigger.  So can demonstrate using the elements of my flow what you have in mind? 

[v-bacao-msft]

Hi @Anonymous ,

It seems that you only need to add the PowerAPPS button as a trigger to the method I provided.

I follow the method provided by the screenshot of your previous Flow configuration. The configuration on the Parse JSON action should be the same.

To parse the records in the collection and then use the Filter array to filter, you need to replace the Title field with the field you want to compare.

Finally, determine whether to add or change items.

Hope that makes sense.

Best Regards,

[Anonymous]

Again thank you for your response 

I rebuilt the flow as you suggested but it now fails at Get Items 2.  Can you please check that I have created the elements correctly:

[v-bacao-msft]

Hi @Anonymous ,

Adding Compose to Apply to each is to add two branches, it is only used as a transition, it does not matter what is filled in the Input.

In addition, you can check my screenshot again and add two branches to Apply to each, instead of outside of Apply to each.

I have shown the details of each branch, and the main structure is shown in Apply to each.

Please try again.

Best Regards,

[Anonymous]

Dear  v-bacao-msft,

The work flow ran - for the first time in 3 months and over 100 attempts - Thank you - I am so elated.

However the workflow is not preventing Duplicates being written to SharePoint.

The following image is that of the Collection that is Parsed to Flow

The following image is of the SharePoint list after the Flow has run

Note the Composite column has duplicate values - therefore duplicate records

Can you advise how we can stop the Duplicate being saved to SharePoint

Many thanks 

Barry

[v-bacao-msft · Accepted solution]

Hi @Anonymous ,

I looked at these two tables and thought that my previous method should work. You just need to replace the fields in the Filter array that you want to compare with Composite Key.

Or have you considered avoiding this problem from the data source? Is there a function in PowerApps that can remove the same records, then there will be no duplicate records in the collection. Perhaps this is also a solution.

Best Regards,

[Anonymous]

OK - As suggested I changed the order of the comparison items in the Filter Array and - BINGO! it works - no duplicates.  This is fantastic work - I am so indebted to you for your excellent knowledge and support.  Thank you so very much

Barry

[v-bacao-msft · Accepted solution]

Hi @Anonymous ,

I'm glad to help you.

If so, please click "Accept as Solution" on the reply that describes how you were able to solve your issue!

This makes your post much easier for other members of the community to see and find when looking for a solution!

Best Regards,