cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

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

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

 

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:

113.PNG

Details in Apply to each:

114.PNG

 

Best Regards,

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

View solution in original post

15 REPLIES 15
Highlighted
Dual Super User III
Dual Super User III

@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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

@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

Highlighted

@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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

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

 

Any help?

 

Osmand

Highlighted

@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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

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

 

Osmand

Highlighted

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

 

Osmand

Highlighted

 

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:

113.PNG

Details in Apply to each:

114.PNG

 

Best Regards,

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

View solution in original post

Highlighted

@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

Highlighted

 

Hi @OsmandFernando ,

 

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

 

Best Regards,

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

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

 

I am much gratefull 

 

Osmand

Highlighted

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

 

Osmand

Highlighted

 

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

 

 

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

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

Highlighted

 


@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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (13,084)