Is there anyway to stop saving an item due to duplication in a calculated column from Flow?

[OsmandFernando]

Hi, 

I have a list with a calculated column, There is no option to prevent duplicate in the calculated column set up, can this be achieved from Flow?

Or is there any other solution?

Osmand 

[efialttes]

@OsmandFernando 

What do you mean with "prevent duplicate in the calculated column set up"? Can you elaborate a bit more the problem, adding an example if possible?

AS per my understanding, Microsoft Power Automate (aka Flow) can replace "calculated Comuns" logic.

Hope this helps

[OsmandFernando]

@efialttes Thank you for your reply

Let me provide you more details on my requirement

• I have a List called Invoice sending
• In that I have 2 fields, Invoice number and Supplier name
• I want to prevent submitting duplicate invoice numbers for the same supplier E.g Supplier A has an Invoice as 001, we should not be able to create an item again for the supplier A with a Invoice 001. 
• I created a calculated column as [Invoice number] & [Supplier Name]. But it does not have an option to enable the unique values. 

Hope this is helpful, 

Is there a way to find solution from Flow or any other idea?

Osmand

[efialttes]

@OsmandFernando 

What's the item input method? Are you guys adding items directly on the list? Are you using a Power App form?

AS per my understanding, Microsoft Power Automate (aka Flow) cannot by itself prevent adding a new item.

Implementing a PowerApp form requires a bit more effort, but can do the validation before inserting the new item and report the error via real time popup, this last featured cannot be achieved by Power Automate (aka Flow) itself

Hope this helps

[OsmandFernando]

@efialttes  we do not have powerapps, that the thing, we directly input data in SP. 

Any help?

Osmand

[efialttes]

@OsmandFernando 

I see your point.

The problem is, how to show a warning to users that are about to duplicate the pair Supplier Name-Invoice Nr.

And, the same Invoice Nr can be used with two different suppliers, right?

I can think of a possible workaround, by means of two lists, but not sure if this approach fills your requirements:

1st list: storing Supplier Name (1st column)-Invoice Nr (2nd column)-Concatenated value by means of Flow (3rd column, text)

So you define a Flow to be executed whenever an item is added to this first list. THe flow calculates the concatenated value, if there is a previous match, it deletes the new item, if not updates the new item with the calculated value

2nd list: includes a lookup column referencing Concatenated values from the 1st list. On this second list you add whatever additional info required for your project. According to Sharepoint documentation, you can enforce unique values to a Lookup column

Please also note there is another approarch that is to use Sharepoint designer as explained here. But, as far as I know, Sharepoint Designer is to be replaced by PowerApps.

As I mentioned, not sure if any of these workarounds fills your requirements

Hope this helps

[OsmandFernando]

@efialttes  thank you for your reply. Let me try that out and come back

Osmand

[OsmandFernando]

@efialttesin the 2nd list lookup, Does the user need to select the value manually?

Osmand

[v-bacao-msft · Accepted solution]

Hi @OsmandFernando ,

Do you mean that the same user cannot submit a duplicate Invoice number?

If so, you can try the following method.

• When a new item is created, check whether the corresponding invoice number already exists.
• Use variables to record items with the same invoice number.
• Compare whether Created by email is the same and compare whether invoice numbers are equal.
• If it is, the variable is incremented by one. Finally, determine whether the value of the variable is greater than 1. If so, delete the newly created item.

You can also send a message to the creator as a reminder after deleting the item.

Image reference:

Details in Apply to each:

Best Regards,

[OsmandFernando]

@v-bacao-msftThank you so much for your reply. The same user should be able to submit the same invoice number as different suppliers can have the same number. Here my requirement is the Same supplier can not have the same number. I have Supplier Name column in the list as well

Osmand

[v-bacao-msft]

Hi @OsmandFernando ,

You seem to just change the Created by email in Condition to Supplier Name.

Best Regards,

[OsmandFernando]

@v-bacao-msftThis is wonderful, let me try this out. Please stand by for me if possible

I am much gratefull 

Osmand

[OsmandFernando]

@v-bacao-msftThe delete Item ID should be from "Get Items" or "When Item is created"?

Osmand

[v-bacao-msft]

It should be from "When an item is created".

[OsmandFernando]

This has worked @v-bacao-msft by including supplier name, Also I further developed to send an email also to delete another list Item based on the initial list

Much much thankful

Osmand

[efialttes]

---

@OsmandFernando wrote:

@efialttesin the 2nd list lookup, Does the user need to select the value manually?

---

If the creation of items in the second list is manual, yes. You can also automate the creation of the item in the second list by means of PowerAutomate (aka Flow).

Hope this helps