PA Reviews notification via email from IPA

I have done this before and what I did was to create two arrays, one of the supervisors with employee reviews due and another with the supervisor, employee, etc. data to send out. After I have gathered my data and built my arrays I used a DataIterator to cycle through the supervisor array and then a JavaScript expression to find the matching employee review records (from that array) and wrote that out to a variable to add to my email. It's not too complicated but you have to be comfortable with JavaScript.

I am comfortable taking examples and making my own, but definitely not a JavaScript expert, but I will see what I can do with your suggestion. Sometimes it is getting a start that is the biggest help. Appreciate the response. Thanks, Julia

So I have finally made my way back to this flow  I now have the data all in one record and need to run this through to send the emails.  Does anyone have an example I can use that takes the email address from sql data returned record- and send an email to that person with that data.  I think I am missing something simple and overthinking this process.  I cant decide between the data iterator and the for loop. Any help would greatly be appreciated.  Thanks, Julia 

If I am reading your posts correctly - I think this is what you have.
You are using a SQL query to pull the reviews due (assuming from the REVIEW table [PA26]) and you have, in that query the manager and their email.

You need to collate the reviews for that specific manager into one email (as apposed to one email for each person).

Assuming that is correct - I am going to ask one thing first... Do you have Smart Notes? If you have Reporting Services with Smart Notes - you can do this very thing within SN and you might find it a bit easier.

That aside. What David W. has presented is a good option and would be one way to do it.
However - if your are not comfortable with javascript (which can be picky) you could do it all within a SQL node loop, using a branch (maybe two) a message builder and a few other items.

If I were to be putting together an IPA flow to do this I would put it together with a SQL node and do all the collating and sending within the SQL node loop.

I would start with the SQL node, then go to a branch node. In the branch node I would asses if the record was the first record in a series (you could do this with a numbering in the SQL query by the manager) , if it is record 1 of the query it would go to a message builder and it would start building an HTML table to be put into the email, it would continue to do this until it came to the next series. In the branch node, if it hit the next series it would use the collected message builder to build the email to the manager it just completed and send the email, it would then clear the message builder variable and move on to the next record. This would continue through all of the records.

I have done very similar items with output of processes, I have some IPA flows that collect data about actions being taken in the flow (updates to PR14 etc.) and it collects all of this and sends HTML email messages to users based on the information they need to know about. Not the exact thing but very similar.

You could do your evaluation of when it moves to a new record by holding a variable of the previous record and comparing or you could do all of that in your SQL query. I personally would do it in the SQL query and just do a record count by supervisor, this way when the loop hits a 1 it knows it is time to release the email for the previous person and start a new one.

That is a great idea - I appreciate all of the information that is posted because it really helps when you are learning a new tool.  Thanks, Julia