Answered

Match Excel value and send email base on Excel master file

(0) Share

Report

Posted on by nitisak

162

Hi.
1. you have file name Topoffer that stored AccNo AccName ReturnReason in this file on AccNo column you have a lot Account number that might duplicated
2. you have another file name SaleMaster that store in SP as well and this file have stored all sale detail that take care of customer refer by AccNo Sale email
3. you requirement to create automate to match Accno in Topoffer with SaleMaster if in Topoffer have Accno match to AccNo in Sale master copy all record with that Accno out and add to another excel file and match Sale Email and send to Sale responsible

EX. If in Topoffer Accno 12345 that have like 6 rows and in SaleMaster founded Accno 12345 that match to salename Test@gmail.com copy all of 6 rows and add to another excel file and send Email to Test@gmail.com

P.S in Topoffer might have more than 1 Accno that should be handle of the excel file that will send to sale

All responses (3)

Answers (1)

Verified answer

Codesidian 33 on at

Like (0)

Report

Re: Match Excel value and send email base on Excel master file

You don't want to set processedAccNos during the first loop. It should be initialized at the top of the flow, and then appended to as each accno is processed.
Your condition also needs to check that the array contains the accno. Not that it's equal to.
As we processes each topoffer row, we check to make sure we haven't seen it before (avoiding duplicates). We do that by appending it to the processedAccNos array. It will store a list/array of all the accnos that we've processed during our loop through topoffer. To add the accno to the array, we need use the append action not the set action.
So something like:

Your expression will most likely need to be different to mine when referencing your accno value, but the logic is the same:
nitisak 162 on at

Like (0)

Report

Re: Match Excel value and send email base on Excel master file

Hi @Codesidian
This is correct ?
Codesidian 33 on at

Like (0)

Report

Re: Match Excel value and send email base on Excel master file

You could try something like:

// Contain processed accnos to avoid processing duplicates
Initialise a "processedAccNos" array
// Store if we actually created an excel for the current accno
Initialise a "writtenToExcel" bool
// Set to your sale email once a match is found
Initialise a "recipient" string
Create a for each over topoffer
Conditional - is topoffer accno in "processdedAccNos"?
No:
Append topoffer accno to "processdedAccNos"
Set writtenToExcel to False
Set recipient to an empty string.
for each loop over salemaster.
Conditional on if the topoffer account number matches the salemaster account number
  Yes:
Conditional writtenToExcel is True
No:
Create Excel
  Set recipient to Sale email from salemaster item
Copy current record data from salemaster item to excel.
// Now our loop is finished, we can check if we just created an excel for the current topoffer accno.
Conditional is writtenToExcel true?
  Yes:
Send the excel file to recipient.

The idea is to store a list of processed accno to avoid duplicates, and check for matches in salesmaster while iterating over your topoffer file. Then email the file once you've completed your checks. Make sure to name the excel file with the accountno and date, or something like that to avoid conflicts.