PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 09/21/2017 2:52 PM by  Julia
PA Reviews notification via email from IPA
 5 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Julia
Senior System Analyst
Memorial Medical Center
Basic Member
(14 points)
Basic Member
Posts:6


Send Message:

--
04/18/2017 2:18 PM

     

    We are trying to create emails to send to supervisors for their upcoming reviews.  I have the query written to gather the data to send and have it is a action node in a message, but I am have trouble determining the best way to parse that data and send to the supervisors their rows of data. Should I be putting this information into a file and parsing that way, should I be able to loop through the email node with using the variables.   I am self taught and have been reading the manuals, reading forums and looking at examples.  I am sure there is something I am missing or not interpreting correctly.

    Thank you for any help in advance.  I always learn something from every post I read. 

    Thanks, Julia

    David Williams
    Private
    Private
    Veteran Member
    (3061 points)
    Veteran Member
    Posts:1031


    Send Message:

    --
    04/18/2017 2:33 PM
    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.
    David Williams
    Julia
    Senior System Analyst
    Memorial Medical Center
    Basic Member
    (14 points)
    Basic Member
    Posts:6


    Send Message:

    --
    04/18/2017 2:42 PM
    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
    Julia
    Senior System Analyst
    Memorial Medical Center
    Basic Member
    (14 points)
    Basic Member
    Posts:6


    Send Message:

    --
    09/21/2017 10:15 AM

    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 

    Dave Curtis
    HRIS Lead
    University of Maryland Medical System
    Veteran Member
    (325 points)
    Veteran Member
    Posts:113


    Send Message:

    --
    09/21/2017 2:37 PM
    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.

    Julia
    Senior System Analyst
    Memorial Medical Center
    Basic Member
    (14 points)
    Basic Member
    Posts:6


    Send Message:

    --
    09/21/2017 2:52 PM

    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 

    You are not authorized to post a reply.