Update Excel row using data from email

[dlprentice]

Hi Flow Community!

I have emails that have similar subjects in the form of "[EXTERNAL] VZ Ticket 9123100969806 New Ticket Notification" I've already put the subject into a variable, and split off the first and last parts to end up with 9123100969806. Plugging this number into an Excel sheet from this point isn't hard at all, but my next problem is checking for duplicates before adding the number to the table.

How can I check this value against an Excel table to see if it already exist? I imagine this could be a conditional statement, "If does not contain" then it adds the value, if yes, then do nothing.

My problem is I've been trying to figure this out for hours with no real good result.

The specific column name is "TKTNUM"

Any help on this is highly appreciated! 

[jonathan_rou]

I think doing an apply_to_each to the rows in the excel document and adding a conditional to compare the excel column value in TKTNUM with the subject variable. would get your flow to do what you're expected.

My Proposed Solution:

If you add a compose action after "List rows present in a table" and select the TKTNUM column from the dynamic list, it should auto-create the apply to each. Then, replace the compose with a conditional comparing the selected TKTNUM column and the subject variable. I would make a variable called "Exists" and set it false before the loop, set it to true if the TKTNUM column and the subject variable equal to each other.

If this response helps you, please add a Kudos to my reply.

Thank you!

[dlprentice]

Would you be able to provide a visual example? This is what I got so far, but it creates a new entry for each time its not equal to the variable so I end up with 158 duplicate entries...

[jonathan_rou · Accepted solution]

You're so close! 

You're going to need two variable actions to finish it up.

You'll need an initialize variable, named Exist, type Boolean, defaulting to false.

Replace your "add a row into a table" action with a Set Variable action, which sets Exist to true, if the conditional is true.

After your "apply to each" finishes, you'll need a conditional for whether or not you want to create a row.  

I'll throw together a visual for you momentarily.

Here's a visual to help:

[dlprentice]

I am happy to confirm that this can be considered mission accomplished! I don't suppose there is an easy way to speed this up other than to enable concurrency control, and turn up parallelism to max? Sequentially it takes like 2 minutes for the first "Apply to each" action to complete.

[jonathan_rou]

Glad to hear that it worked. If you could please give Kudos to the right solution and mark the post as solved, it would help me out.

 I don't know of an easier way to speed up the runtime of this solution other than the two options you already mentioned, concurrency control and parallelism(which may help).

[craigyappert]

Yes .. faster way ... When you list rows ... filter by ID in the Excel action...

Check length of array with a collection expression .. and then write if the length is 0.

That way Excel does most of the work .. and you don't have to loop at all.