Looping Script for Email Notification

[Bill_misc_IT]

I've written a looping script to run through the records of an employe database to email me a notification if its their birthday.  I'm looking for the best practice for handling the IF statement for the last record.  For years, my EXIT LOOP condition has been if the $LastRecord variable (set at the beginning of the script) = the last record no.  The problem is that the loop will perform the IF statement for all records, except the last (which it exits upon entering).  My workaround, has been to perform the IF statement for the last record, when setting the variable, however, it seems redundant.  I thought I saw an example where Omit record was used as each record was processed, allowing only one instance of the IF statement in the Loop.  Can someone please shed some light on the best practice?  Script is below:

Thanks,

Bill

Employee: Email Alert for Employee-10 years

Set Error Capture [ On ]
#Show Current Records and Sort
Go to Layout [ “Employee-List View” (PERSONNEL RECORDS) ] Show All Records
Enter Find Mode [ ]
Set Field [ PERSONNEL RECORDS::Employment Status; "Active" ] Perform Find [ ]

Sort Records [ Keep records in sorted order; Specified Sort Order: PERSONNEL RECORDS::LastName; ascending PERSONNEL RECORDS::FirstName; ascending ]
[ Restore; No dialog ]

Go to Record/Request/Page

[ First ]

#Check for 10 Year Anniversary

Go to Layout [ “Employee-Summary” (PERSONNEL RECORDS) ]

Go to Record/Request/Page

[ Last ]

Set Variable [ $recordno; Value:PERSONNEL RECORDS::_kp_EmployeeNo ]

Go to Record/Request/Page

[ First ] Loop

If [ Get ( CurrentDate ) =
Date ( Month(PERSONNEL RECORDS::DateOfHire)-1 ; Day(PERSONNEL RECORDS::DateOfHire) ; Year(PERSONNEL

RECORDS::DateOfHire)+10 ) ]

Send Mail [ Send via SMTP Server;
[ No dialog ]

End If
If [ Get ( CurrentDate ) =

Date ( Month(PERSONNEL RECORDS::DateOfHire) ; Day(PERSONNEL RECORDS::DateOfHire) ; Year(PERSONNEL

RECORDS::DateOfHire)+10 ) ]

Send Mail [ Send via SMTP Server;

[ No dialog ] End If

Go to Record/Request/Page

[ Next ]
Exit Loop If [ PERSONNEL RECORDS::_kp_EmployeeNo=$recordno ]

End Loop 

 

[eos]

I've written a looping script to run through the records of an employe database to email me a notification if its their birthday.  I'm looking for the best practice for handling the IF statement for the last record.  For years, my EXIT LOOP condition has been if the $LastRecord variable (set at the beginning of the script) = the last record no.  The problem is that the loop will perform the IF statement for all records, except the last (which it exits upon entering).  My workaround, has been to perform the IF statement for the last record, when setting the variable, however, it seems redundant.  I thought I saw an example where Omit record was used as each record was processed, allowing only one instance of the IF statement in the Loop.  Can someone please shed some light on the best practice?

The placement of any code within the loop block relative to the “Exit if” depends on what you want to achieve.

If you want to act upon every record, then in each iteration of the loop you must …

• enter the record
• perform some function
• check the exit condition.

Your code, OTOH …

• enters the record
• checks the exit condition
• performs some function

That's why your last record is MIA; it's entered, checked and, since it does satisfy the exit condition, the loop is exited before the function is performed.

As to your exit calculation itself: if placed at the correct place, it would work, but it's unnecessarily complicated.

Use either the “Exit after last” option of Go to Record/… [ next|previous ]; if you use FM Server and want to prevent the error log entry that this step causes on exiting, you can use Get ( RecordNumber ) = Get ( FoundCount )

It is also a good idea to enter Find mode before you switch layouts, so you don't unnecessarily load any records that will be excluded from the search result anyway; and “Show all Records” before “Perform Find” is not required.

Lastly, if you don't need to present the records (and don't have any script steps that require their target fields to be on the layout), then the layout itself isn't important – just the table occurrence (TO) that determines the context; so there is no need to switch between layouts from the same TO.

One more thing: note how Let() helps you in simplifying a calculation.

See here: 

Set Error Capture [ On ]
# [ Show Current Records and Sort ]
Enter Find Mode [ ]
Go to Layout [ “Employee-List View” (PERSONNEL RECORDS) ] 
Set Field [ PERSONNEL RECORDS::Employment Status; "Active" ] 
Perform Find [ ] 
# [ Should properly trap for an empty found set, but we can assume there will always be staff members that are active ]
Sort Records [ … ] 
Go to Record/Request/Page [ First ] 
# [ Check for 10 Year Anniversary … didn't you say Birthday?]
Loop 
  If [ Let ( dateHire = PERSONNEL RECORDS::DateOfHire ; Get ( CurrentDate ) = Date ( Month ( dateHire ) - 1 ; Day ( dateHire ) ; Year ( dateHire ) + 10 ) ) ]
    Send Mail [ Send via SMTP Server; No dialog ] 
  End If 
  # [ If[] block in original script was identical to the preceding one; or was that supposed to be the Birthday check? ]
  # [ use either of the following, not both ]
  Go to Record/Request/Page [ Next ; Exit after last ]
  # Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) ] 
End Loop 

PS: if you copy script code from a PDF, it's not particularly well formatted; be so kind and do some post-processing on that text, so others don't have to.

Edited August 12, 2015 by eos

[comment]

I've written a looping script to run through the records of an employe database to email me a notification if its their birthday. 

Wouldn't it be much simpler (and faster) to find those records?

[OlgerDiekstra]

 

PS: if you copy script code from a PDF, it's not particularly well formatted; be so kind and do some post-processing on that text, so others don't have to.

I was wondering what those '[first]' and '[last]' were...

Another thing I noticed in the DB I inherited is how loops can sometimes be confusing. The guy that made this mess would do the below:

Go to Record/Request/Page [First]
BlockofCode
Loop
   Goto Record/Request/Page [Next; Exit after last]
   BlockofCode
   End loop

Where "BlockofCode" can be a block of code of any size, just two lines or 50 or 60 (not kidding). I'm assuming he never caught on to the fact that his "Goto Record Next" (which was always the first command entering the loop) was in the wrong place (needs to be right before the "End Loop") cause the scripts are littered with this. He didn't even place all the code in a separate scripts to improve readability and serviceability. The old DB had 1500+ pages of scripts (when printing to pdf), whereas our 'new' DB has 'only' 1000+ pages (though there's still quite a bit legacy stuff in there) but it has probably 500% more functionality. So I'm OK with that. :-D

[dwdata]

I agree with COMMENT. Something like:

Set Allow User Abort [Off]
Set Error Capture [ON]
New Window[name=process, height = 20, width = 20, top= 1, Left = -100 // open off screen
Go to Layout [ “Employee-List View” (PERSONNEL RECORDS) ] 
Enter Find Mode [ ]
Set Field [ PERSONNEL RECORDS::Employment Status; "Active" ] 
Set Field [ PERSONNEL RECORDS::DateOfHire; Get(CurrentDate) + 10 ] // Looks 10 days in the future
Set Field [ PERSONNEL RECORDS::Email_Address; "*" ] // Email Address exist (asterisk)
Perform Find [ ] 
If[Get(CurrentFoundCount) > 0]
    Send Mail [ Send via SMTP Server; Found Set, No dialog ] 
EndIf
Close Window[current]

Hope this helps!

[comment]

I agree with COMMENT.

Comment does not agree with you on this one:

Set Field [ PERSONNEL RECORDS::DateOfHire; Get(CurrentDate) + 10 ] // Looks 10 days in the future

This attempts to find "employees" that will be hired exactly 10 days from today, which of course makes no sense.

Instead of looking into the future, you need to be looking into the past. This may actually be more problematic than it seems, because if you look for employees whose anniversary is exactly today (or tomorrow, or 10 days from now), you will miss those whose anniversary falls on a weekend or a holiday.

It may be preferable to do this once a week and look for employees whose 10th anniversary is sometimes next week, by setting the DateOfHire field to =

Let ( [
nextSun = Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 7 ;
start = Date ( Month ( nextSun ) ; Day ( nextSun ) ; Year ( nextSun ) - 10 )
] ; 
start & ".." & start + 6
)

 

 

 

Edited August 13, 2015 by comment

[OlgerDiekstra]

It may be preferable to do this once a week and look for employees whose 10th anniversary is sometimes next week, by setting the DateOfHire field to =

Let ( [
nextSun = Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 7 ;
start = Date ( Month ( nextSun ) ; Day ( nextSun ) ; Year ( nextSun ) - 10 )
] ; 
start & ".." & start + 6
)

I think the filter (when searching for matching records) needs to be that calc, not the ::DateOfHire field. The ::DateOfHire field should be a static field in the DB that contains the date the person got hired. If you put that calc in the field, the DateOfHire will constantly change. ;-)

[dwdata]

Thanks Comment. You are correct - I read the calculation wrong (human factor and fatigue kicking in)

From the original post - the calculations in his script were:

If [ Get ( CurrentDate ) = Date ( Month(PERSONNEL RECORDS::DateOfHire) -1 ; Day(PERSONNEL RECORDS::DateOfHire) ; Year(PERSONNEL RECORDS::DateOfHire)+10 ) ]

If [ Get ( CurrentDate ) = Date ( Month(PERSONNEL RECORDS::DateOfHire) ; Day(PERSONNEL RECORDS::DateOfHire) ; Year(PERSONNEL RECORDS::DateOfHire)+10 ) ]

So here is my revised response:

I would recommend you running this daily, but to do so you need to track the last time it run.

Do you have a GLOBAL PREFS table (where you store global settings and preferences) If not, create one. If so, you define a field called "anniversary_last_run_date" (date result).

Then add a few mods to my original script using that field to track whether it need to run:

Set Allow User Abort [Off]
Set Error Capture [ON]
New Window[name=process, height = 20, width = 20, top= 1, Left = -100 // open off screen
Go to Layout [ “Employee-List View” (PERSONNEL RECORDS) ] 
Loop
Exit Loop If [Global_Prefs::anniversary_last_run_date = Get(CurrentDate)]
Set Variable["$aDate", Date ( Month(Global_Prefs::anniversary_last_run_date) - 1 ; Day(Global_Prefs::anniversary_last_run_date) ; Year(Global_Prefs::anniversary_last_run_date) -10 ) ]
Enter Find Mode [ ]
Set Field [ PERSONNEL RECORDS::Employment Status; "Active" ] 
Set Field [ PERSONNEL RECORDS::DateOfHire; $aDate ] 
Set Field [ PERSONNEL RECORDS::Email_Address; "*" ] // Email Address exist (asterisk)
Perform Find [ ] 
If[Get(CurrentFoundCount) > 0]
    Send Mail [ Send via SMTP Server; Found Set, No dialog ] 
EndIf
Set Variable["$aDate", Date ( Month(Global_Prefs::anniversary_last_run_date); Day(Global_Prefs::anniversary_last_run_date) ; Year(Global_Prefs::anniversary_last_run_date) -10 ) ]
Enter Find Mode [ ]
Set Field [ PERSONNEL RECORDS::Employment Status; "Active" ] 
Set Field [ PERSONNEL RECORDS::DateOfHire; $aDate ] 
Set Field [ PERSONNEL RECORDS::Email_Address; "*" ] // Email Address exist (asterisk)
Perform Find [ ] 
If[Get(CurrentFoundCount) > 0]
    Send Mail [ Send via SMTP Server; Found Set, No dialog ] 
EndIf
If[Get(FoundCount = 0]
    Show All Records
End If
Set Field['Global_Prefs::anniversary_last_run_date', Global_Prefs::anniversary_last_run_date + 1]
End Loop
Close Window[current]

IMPORTANT: To start, you will want to enter today's date in the "Global_Prefs::anniversary_last_run_date" field. This will make sure that the DB does not fire off a bunch emails because of an empty date field.

You can add this new script as a sub-script to an opening script so you can be sure that it runs most work days. Since you base the running of this script on this date field, spans of days which the script is not ran (no access to DB on weekends and holidays) will made up the next time you open the DB.

Good luck to you...
 

[comment]

If you put that calc in the field, the DateOfHire will constantly change. ;-)

I am afraid you're missing the point here. My suggestion was to find the relevant records. And the way to find them is to enter Find mode and then set the DateOfHire to the calculated value.

The DateOfHire remains constant, but each time you run the script, it will find the employees whose 10th anniversary falls in the next week (relative to the day on which the script is run).

 

 

 

So here is my revised response:

To tell the truth, I did not read your revised script. The only reason I am responding is this: earlier I said:

Wouldn't it be much simpler (and faster) to find those records?

Your suggestion does not seem to be. And I still believe it can be.

 

 

Edited August 13, 2015 by comment

[OlgerDiekstra]

I am afraid you're missing the point here. My suggestion was to find the relevant records. And the way to find them is to enter Find mode and then set the DateOfHire to the calculated value.

The DateOfHire remains constant, but each time you run the script, it will find the employees whose 10th anniversary falls in the next week (relative to the day on which the script is run).

 

My apologies, I had to read a few posts back to get the context right again, you are correct.