Sending Notices

[LaRetta]

We have Permits which will expire.  We want to create a Notice record as the time draws near ... when 30 days away, then again when 15 days away, and again at 5 days away (as indicated by the Actions table which is established by the client).  There are many approaches and I keep going round and round about how best to handle it (which usually indicates I know my ideas are not the best solution).  

An existing Notices process is in place and we'd rather not touch it so my plan is to add an Actions table and then use filtered portal to display either the existing process (left-side portal) or new Actions (right-side portal) as in the attached fp7 file.

My question is in regards to the logic of the script or whether I should use relationships to isolate whether to send a notice.  Script will run nightly as server schedule and once a Permit has been sent a notice at 30 days, it would not receive another notice until 15 days out if still in status of Issued so it will (I think) need to look at the Notices table (for that Permit) to see when a Notice was last sent, between 30 and 15 and then if a notice has been sent, look at between 15 and 5?  This is where my logic breaks down and I need a nudge.

I am hoping you can see where I am going with this.  I considered using a record for each Action/DaysOut instead of repetition but since it is an 'admin' record used for scripting and not for data-entry, I believe repetition would work just fine and I will need to loop the Permits.  Can I get others' high-level opinion on approach?

Notices.fp7.zip

Edited July 26, 2015 by LaRetta

[dwdata]

Interesting... I, too, would like to hear a bit of feedback on how other would handle this.

I have done something similar to this in a membership solution. I established a members table and a table of membership_years which contained the member_id and membership_year. The client wanted to email reminders to members 30, 15, 5 days prior to, on the date, and 3 day past the the member's next years anniversary date if they did not have the next membership year in that table.

Since I could calculate their current membership_year_anniversary, I opted to just store those dates in their record. If there was not a related record for next year in the membership_years and those dates hit, I simply generated a LOG record in another table along with the reminder email. This was done in daily batches, so I produced a summary report of reminders sent out to responsible parties. The daily batch routine, had the logic to determine if any reminders were already sent.

Not sure if this helps. When I get some time, I will take a look at your file closer. If something constructive hits me, I'll let you know. 

Good luck ;o)

[comment]

I am having trouble reconciling these two statements:

when 30 days away, then again when 15 days away, and again at 5 days away (as indicated by the Actions table which is established by the client). 

it is an 'admin' record used for scripting and not for data-entry,

So when is this established by the client? If it's now and it's not supposed to change (read: not supposed to change without the intervention of a developer), then I would hard-code this into the script/s that generate/s the notices.

OTOH, if the client is allowed to change these at will, then the question of data-entry does come up.

[LaRetta]

Thank you both for the input!

This solution is used by multiple cities and each city will want a different setting in their copy of the file so there will be only a single record in the file for each type of action and their Management will set these records' defaults originally but they may change the values down the road although rare and some cities may leave these values blank on certain Actions meaning script should not run that action at all.  

Server schedule will run middle of night to generate Notices.  The next day, Users will review notices and can choose to act on one or a group (such as sending email or placing a call) and then Notice will be flagged as read and disappear from the portals.  Notices and city response to the Notice must be kept for legal and historical purposes.

I am not opposed to adding a field to Permit (with the 'last date ran' for example) but since there could be multiple Actions for a single Permit, that didn't make sense here.  There are so many ways of approaching this problem and your clarity is invaluable.

[LaRetta]

I'm practicing concise but I should also mention two things:

1) there may be additional fields added into Actions (thinking ahead), such as Subject and Body template so they might need to be repetitions as well (to match the NoticedaysOut_R and Subject on 30 days would differ from Subject on 5 days out etc) but I do not see that as a problem and 2) if Management changes those numbers, it could throw off the nightly script.  I am not sure this is a serious problem and I believe that, as long as Management is aware, it shouldn't be an issue.

If I am incorrect on either of these points, I appreciate the correction!

[comment]

they may change the values down the road although rare

"Rare" is not "never". In fact, "rare" is so much different from "never" that "every day" and "rare" appear to be the same thing when compared to "never". Which to me means you will have to provide them with a UI to make/change these settings.

The question then becomes what do you gain here by using repeating fields instead of individual records. I can't say I see any advantage - but I do see a potential drawback: the script will be more complex when it has to loop among records and repetitions, instead of just records.

This is just a wild idea, but I would look into organizing the Actions table along the lines of: DatefieldName, NoticeText, DaysBefore. Then the script could be even more generic, I think (I haven't really thought this through, so it could be more trouble than it's worth).

[dwdata]

LaRetta,

Take a look at this schema and routine in the attached file. It was a quick and dirty mashup.

Hope this helps!

dwdc_notices.zip

[LaRetta]

I've had to put this off for a bit but I'll address it again very soon.