Duplicate Invoice Check BEFORE processing with AI Builder?

[TheAvo1]

Good Day,

I need assistance with the following.

Scenario:

I have a flow which checks for new mails that have attachments and have "Invoice" in the subject line, if these parameters are true, the Attachments are sent to the AI Builder to extract invoice content and write them too a SharePoint List (I will start on a approval workflow thereafter etc - to be done).

Problem Statement:

I want to confirm that the invoice received is NOT a duplicate (Sometimes that can happen if a mail is sent twice or some person just attaches the wrong invoice etc) before I send too the AI Builder (cost considerations) using the Attachments name. If it has not been seen before proceed, if it has been seen before, send mail back to the original sender and state that the Invoice appears to be a duplicate.

[SanmeshG · Accepted solution]

Hi @TheAvo1 ,

Before you write them to a SP List , you should have a condition to check if an item exists in the SP List with the same invoice number , this can be retrieved by using the get Items action and performing a filter query on the InvoiceNumber column.

Then you can use the length function to retrieve the length of the value array that is returned from the get items action f the length of value array is greater than 0 , then you shouldn't create the item else you should create the item.

Hope this heps!

Thanks

[SamLed]

Hi,

No need to check if file already exists, Create file action fails when you try to create a file with same name, you just have to use the run after feature of a following Condition action to deal with the above action success/fail and send mail according to the situation, see bellow flow to illustrate:

Set Condition run after setting: 

And in Condition check if "ItemId" dynamic content from Create file equals null.

Result when file doesn't exist:

And when it does exist:

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[TheAvo1]

Perhaps I am not quite understanding (Apologies, very new to Power Automate etc), My flow wait's for a mail, when a mail is received, it takes the attachments and runs them through the AI builder to extract information. I then have several lists that need to be populated with the extracted information. The lists are written, but if the same invoice is dropped it creates that list item again.

Attached is the flow and the list item showing a duplicate entry.

Ideally in my scenario, if the Invoice Number and the Supplier Name already exist in that SP List, don't create the record.

If anyone can help with some screenshots on a way around this?

[SamLed]

Hi,

Don't apologie, I replied to wrong topic, sorry.

Regarding your issue, I would configure my InvoiceNumber field as unique in sharepoint list settings. So SharePoint will raise an error when you'll try to create an item with same value. In your flow just manage those cases with run after as shown in my reply to send mail to submiter.

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[schithambaran]

Hi Sam

Good Day!!

Thank you so much for the solution. But when I tried this solution am receiving Invoice Registered Message only.

screen shot shared for your reference. Waiting your feedback.

[SamLed · Accepted solution]

Hi @schithambaran,

You're using the wrong dynamic content in condition. Use "ID" from "Create" Item":

Your "Invoice ID" column is set up using "Enforce unique values":

When you create an item with an already existing invoice ID, the action fails with following message: 

"The list item could not be added or updated because duplicate values were found in the following field(s) in the list: [InvoiceID]..."

But as your condition run after is configured like this:

Item creation failed, Item ID is null, and you get this:

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[schithambaran]

Thank you so much. It worked.

[SamLed]

Hi @schithambaran,

Glad it worked, feel free to accept the answer that helped you solve your issue, so other user with same issue knows how to spot the solution.

Best regards,

Sam