Update Excel row using data from email

(0) Share

Report

Posted on by dlprentice

626

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!

Screenshot 2020-10-09 110943.png

All responses (6)

Answers (1)

craigyappert 57 on at

Like (0)

Report

Re: WRe: Update Excel row using data from email

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.
jonathan_rou 24 on at

Like (0)

Report

PleRe: WRe: Update Excel row using data from email

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).
dlprentice 626 on at

Like (0)

Report

Re: WRe: Update Excel row using data from email

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.
Verified answer

jonathan_rou 24 on at

Like (1)

Report

Re: WRe: Update Excel row using data from email

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 626 on at

Like (0)

Report

WRe: Update Excel row using data from email

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 24 on at

Like (0)

Report

Re: Update Excel row using data from email

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!