Create Unique Record for a SharePoint List

[hanbots]

Hi All, 

I need to create a single record in a SharePoint list when a file has been loaded to another SharePoint List.

Example: 

I have a SharePoint List called commission file data which is populated by an excel spreadsheet via a flow. It has 2 fields called Provider and StatementID.

I want to create a single record in a different SharePoint list that shows only unique values for Provider and Statement to show up in this list.

Does anyone have a recommended solution?

Thank you, 

Hannah

[JohnAageAnderse]

Hello @hanbots 

Create your list so you have one column in which you can fill with the concatenated values of the Provider and StatementID.

Make the column unique. This will make your flow action fail if it tries to insert a new item with the same Provider and StatementID in the unique column.

In the list, add any other columns as needed - probably would be a good idea to have one for Provider and one for StatementID.

In your flow, you then have to ensure that your flow can handle the failure by configuring the next action's Configure run after settings.

Kind regards, John

[v-litu-msft]

Hi @hanbots,

Has your problem been solved?
If your problem has been solved, You could go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Best Regards,
Community Support Team _ Lin Tu

[mark90]

Thanks @JohnAageAnderse this works a treat. However, can you please help;

1) If I have a duplicate value, how do I set it so it still runs to continue to add all items which are unique? I have 500 duplicates but 40 unique items I want to ensure these get added?

thanks for the help!

[JohnAageAnderse]

Hello @mark90 

If I understand you correctly, then you want to know how the Power Automate flow can continue after the action that inserts the new item and fails!

In the following action, open the context menu (3 dots) and use the Configure run after! Check the "is successful" and "has failed" boxes.

Kind regards, John 

[mark90]

Thank you @JohnAageAnderse that seems to be working but does it matter that the flow fails? It's loading the new items but failing on duplicates, will that mean it may not run on a schedule job as it continues to fail?

Thanks again,

[JohnAageAnderse]

Hello @mark90 

Can you show us the flow and the issue with one or more screenshots? That way we may better be able to assist.

Kind regards, John

[mark90]

Thanks @JohnAageAnderse 

I actually think it's not an issue but wanted to confirm,

I want to run the flow everyday. If theres no new items created, the flow will fail (as per picture). Because it failed, will that be an issue for the flow working tomorrow?

If not, then it will work perfectly as when a new item is added, it creates it in the list as per the requirement.

Thanks again!

 

[JohnAageAnderse]

Hello @mark90 

The loop "Apply to each 5", what are you doing inside the loop that registers as a fail? Can you expand the loop and show us?

Kind regards, John

[mark90]

Thanks @JohnAageAnderse 

see photo below, I'm simply creating an item in Sharepoint. It's failing because the column can only accept unique values and when getting items there were none. 

In my scenario I want the flow to run everyday and we may or may not have unique values. 

Thanks again for all  the help, really appreciate it!

[mark90]

Hi @JohnAageAnderse 

Wondering if you have any advice for me? the Flow keeps failing each day even though there's new items to add which are unique,

Thanks 

[JohnAageAnderse]

Hello @mark90 

Open your failed run and expand action "Apply to each 5". In the expanded action, use the "Next failed ..." link, then expand the failed action, take a screenshot of it and post it!

Kind regards, John

[mark90]

Thanks so much @JohnAageAnderse for continuing to help!

image uploaded it. It looks like it's failing because duplicate values not allowing it to save. Following the above, I thought it would still upload all new records and fail on duplicates?

cheers

[JohnAageAnderse]

Hello @mark90 

Let's clarify the situation - you get items, then in a loop you insert items in another list. For some of these inserts, the action fails due to the new insert is a duplicate of an existing item in the list.

The inserts that are not duplicate of existing items will be inserted in the list!

Is the above what you wanted?

Even though your flow is marked as failed, it does not mean that it actually failed, just that for some actions, there was a failure.

Kind regards, John

[mark90]

@JohnAageAnderse that's exactly the scenario I'm going for. 

The items aren't uploading in the list, I might try again in a new created list!

Thanks

[JohnAageAnderse]

Hello @mark90 

Ok 🙂 In the list, which field/column is defined as unique? If the field/column is shown in the last screenshot, then open the list, filter the list on the field/column and see if that value already is in the list! If that is so, then your flow works.

Kind regards, John