Hello all,

I have been tasked with creating an email for each student with a hold placed on their record. This is suppose to be auto-magically sent each day to individual students. I have pulled the data needed and designed a nice report that breaks on the student ID so the output for each would be the body of the email. What I am having a hard time wrapping my head around is how to populate the send to email address with only one email address at a time and have this automatically send an email to each student with their own hold information. I'm sure there is a loop function in there somewhere but not quite sure how to accomplish this.

Has anyone else done anything similar or have any advise for me?

I appreciate any and all help.

Thanks
Michelle

This message has been edited. Last edited by: Kerry,

Michelle, I don't mean to be simplistic (though I know I am) but wouldn't this be easier to achieve by just using Report Caster burst functionality? If your report is already broken down by Student that would be your "burst key". All you need is a distribution list with each student id and email address (which can be created dynamically as a flat file before your job runs).

If you do it this way the whole solution will be using standard WebFOCUS functionality - no looping required ) which will make it easier to maintain in the future.

Do you have ReportCaster? You can do this with a Distribution list and Bursting.

Well looks like we agree!

Thank you so much Prarie and njsden. We do have Report Caster however our administrator is the one that does this. I will get with her and see what we can do. So, in addition to my report I will also do a flat file with the ID and the email. Wonderful! Thank you so much.

quote:

If you do it this way the whole solution will be using standard WebFOCUS functionality

That's always a Plus in my book

This message has been edited. Last edited by: Doug,

The distribution list in a flat file was just an idea to build a dynamic list. If your list with students and email addresses is relatively stable you could actually create it and maintain it directly in Report Caster repository tables and once there just use/re-use them anytime you need.

quote:

Well looks like we agree!

I think my mouse clicks faster!

This message has been edited. Last edited by: njsden,

Thank you njsden

quote:

That's alwasy a Plus in my book

So is in mine, Doug. I've learnt the hard way that customizing solutions when WebFOCUS could do it with out-of-the-box functionality (even with some minor compromises) is just a recipe for future headaches when migrations or requirement changes come. Keeping things within the product just "makes sense" whenever possible and in Michelle's case, way much easier to implement than via a DIY approach.

I like simple but sometimes make life harder then it needs to be. Thanks for keeping me on the ground

But, if your installation is like mine where we do not have Report Caster...

You can go with handy dandy Dialog Manager to manually burst your report.

The main gist of the process would be:
1.) Use TABLE FILE .... SUM STUDENT_ID STUDENT_EMAIL BY STUDENT_ID NOPRINT ON TABLE HOLD to get the list of the target students and their email addresses. You can have a join prior to this if the email address is not in the same table.
2.) Use -REPEAT looping command using the &LINES variable resulting from the TABLE FILE on step 1 to determine how many times to loop.
3.) Use -READ HOLD NOCLOSE to process the hold file one record at a time and store the ID and email in & variables.
4.) Create the Report for one student using WHERE STUDENT_ID EQ &id (& var from the -READ) and ON TABLE HOLD to another hold file.
5.) Use -HTMLFORM to start writing HTML that will send the report via email using Active X or the like... this is where you will use the & var that holds the email address. Also use !IBI.FIL. to put the report in the appropriate spot in the html. Or you can write some javascript instead or any executable scripts that you can run using -WINNT command.
6.) Loop back to step 2 for the next student.
7.) -CLOSE the hold file that you -READ in step 3.

But yeah, use Report Caster if you have it...

This message has been edited. Last edited by: Anatess,