Need help using collection operation to compare two seperate lists

[jpayne]

Hi,

I have a list of timesheets and that I need to compare those against an employee list in order to see who has not submitted  timesheets for that week and then send email based on the results.  I thought of using collection operations and query list. I have my 2 query's set up and pulling the employee numbers then a "For Each" with a "collection variable" that uses the "Exist" method however not sure what comes after the collection variable or if there is a better way to solve the requirement? Any help would be greatly appreciated

[patrick_hosch · Accepted solution]

You are on the right track. Below is a screenshot of how you should approach your scenario:

1. Query Employee List - Get ALL employee numbers from your employee list and store them in a collection. If you have a large number of employees you might want to consider splitting them in batches to increase performance.
2. For each - For each employee in your collection, store the employee number (assuming this is your reference point in the timesheet list) and query the timesheet list for any entries matching that number
3. Run if - if the result of the timesheet list query is blank the employee hasnt submitted a timesheet
4. QUery Employee List - using the employee number, query the email or username from the employee list and store the result in a variable
5. Send Email - using the variable above, send an email to the employee reminding him/her to submit a timesheet
6. Set variable - in case the timesheet list query returned a result, you will have to reset the variable to a blank value otherwise the Run if action will never trigger as the result will be inaccurate

Hope that helps

[jpayne]

Thanks that helps so much, I was just getting myself mixed up....