Server side script based on "start" date field AND loop script steps
I have a two-fold question:
1. I intend to launch a server-based script that, on a daily basis, will search for scheduled-audit records that have a start date upcoming in 10 days, then email both the assigned auditor, and the process owner(s). The three fields referenced are IA_DateStart, IA_Auditor, and IA_Owner. What is the best way to script this? I could probably clunk something out, but I'd like to get a veteran's advice, if possible.
2. My second part is, sometimes there will be multiple names in either or both IA_Auditor and IA_Owner fields. I have set these up as repeating fields, up to four repetitions. What is the best way to script a loop to check each repeat that is not empty, and add that name's email address to the email script?
The IA table is related to another table that holds email addresses (by the name field). Occasionally, an "Owner" will not exist in the Name table, and I don't want the script to freak out if it can't find a matching email address; just ignore that "Owner".
Thanks in advance for your guidance!