Holiday Shifting Black Box...

[Ocean West]

this is a bit out side the norm of "scheduling" but I am trying to derive a set of dates that match this logic and trying to some way make it as a black box approach as possible a recursive custom function because i need to possibly use it in a calculation in virtual list for a series for records.

 

I have a table of known holidays with a flag that determines its a "major" holiday were no work is done and lesser holidays where some work can be done.

 

My date cycle is a set of 4 inspections which i could pass in as a list, or pass a single Date (the actual treatment date)

theDate - 3
theDate - 1
theDate
theDate + 1

7/1/14
7/3/14
7/4/14
7/5/14

Should a MAJOR holiday occur on exam 3 the it must be shifted 1 day forward along with exam 4

Should a MAJOR holiday occur on exam 1 OR exam 2 these can bet shifted one day prior, but they don't need to move in tandem.

 

If a minor holiday occurs on exam 1, 2, or 4  the dates are fine but if a minor holiday occurs on the 3rd exam then it will be shifted forward one day along with the 4th exam.

 

So for example this week the result would be this returned list of dates:

7/1/14
7/3/14
7/5/14
7/6/14

I possibly could pass in a valueList of the known holiday data derived at an earlier point so that the function has all it needs so that it doesn't have to do an SQL query. ( i am thinking if I need to put this function in a calculation on my virtual list. 

 

HolidayShift ( theDate ; MajorHolidayList ; MinorHolidayList ) 

 

Thanks

Edited July 6, 2014 by Ocean West
fixed typos

[ggt667]

This approach* will make sure you have fresh flags, without calculation fields involved: http://wethecomputerabusersamongst.blogspot.com/2012/11/filemaker-syncing-data-across-timezones.html

 

*This approach makes sure a calculation is only performed when modid changes.

 

choose( holidayFlag; "minor"; "major" )

[Ocean West]

thanks but this doesn't have any thing to do with timezones. 

[LaRetta]

theDate - 3
theDate - 1
theDate
theDate + 1

So for example this week the result would be this returned list of dates:

7/2/14
7/3/14
7/5/14
7/6/14

 

I'm not sure I understand the need, Stephen.  In this example, the first entry shows -3 then -1 but the resulting dates are -2 and -1 and there is no holiday in those first two dates.  Also you say, "If a minor holiday occurs on exam 1, 2, or 5..." but I do not see a fifth date.  Can you take another look at the example and possibly explain just a bit more for us?  :-)

 

Would it make more sense to have a 7-day (weekly) slider according to the current date (or date you specify) as 3 prior then the current date then 3 after?

[Ocean West]

The known value is a treatment say 7/4 date 3 days prior they do an inspection and 1 day prior there is a walk thru then the treatment, then a 1 day post follow up.

However 7/4 is a holiday so the third exam and fourth need to be shifted forward. My bad the dates should be 7/1 7/3 7/4 7/5. Before shift and 7/1 7/3 7/5 7/6 after shift.

[LaRetta]

Hi Stephen,

 

The problem I run into is that you have an ever-moving target with inconsistent-to-a-computer requirements.  I could not succeed in creating a custom function as you wish because of it.  But we CAN use script and the benefit of script is that the business rules can be clearly indicated on each of the four exams.  To make it re-usable, I used a global for the starting date (current exam before adjusting for holidays).

 

When comparing a date to a list of dates and looking for the pattern matching, and since these comparisons are TEXT, OS settings might be an issue.  For example, 7/1/2014 would not match text date of 7/01/2014.  Because of this, I turned all dates into number and performed the comparisons that way.  I used a cartesian join just for simplicity to observe the results while I tested but I believe there is enough here that you can modify it as you wish if you end up using this approach.  Using number dates seemed safer to me.

 

The key, I think, is that the CurrentExam must be the starting point (your BASE) and then go forward (for the followup) and go backward (for the walkthrough and inspection).  It is an example where I wish we could fold space at this point and minus backward dates and plus forward dates.  Alas, I failed in the custom function for it and maybe others can provide you with one.

 

In all, if I had this request, this is what I would end up doing (sample fp7 attached) so if all else fails and you don't work out a custom function, maybe this will help you.

 

I am sure there are better ways and I look forward to see what they are.   

Exams.fp7.zip

Edited July 6, 2014 by LaRetta

[Ocean West]

Thanks LaRetta I'll check it out - BTW I made a blog entry http://fmforums.com/forum/blog/3/entry-823-a-little-sql-with-a-dash-of-virtual-list/ part of the process for this.

actually.

 

What I have in the customer table is about 1/2 dozen fields and a relationship to a "holiday" table that facilitate this at the present, it works but just feels kludgy it would be nice to be able to pass parameters and have the function figure it out. I may take another stab at it once the coffee reaches the creative button in the brain.

[Ocean West]

Ok I think i have it.

 

Required 2 Custom Functions:

 

 

shiftDay ( theDate ; holidayList ; direction ) 

Let ( [ 

a = theDate + direction ; 
b = PatternCount ( ¶& holidayList &¶ ; ¶& theDate &¶ ) ≥ 1 ;

] ; 

If ( b ; shiftDay ( a ; holidayList ; direction ) ; theDate )

)

HolidayShift ( theDate , minor , major )

Let ( [

~td = GetAsDate ( theDate ) ; 
~a = ~td - 3 ;
~b = ~td - 1 ;
~c = ~td ;
~d = ~td +1 ;


~all = List ( minor ; major ) ; 


~aa = shiftDay ( ~a ; major ; -1 ) ;
~bb = shiftDay ( ~b ; major ; -1 ) ;
~cc = shiftDay ( ~c ; ~all ; 1 ) ;
~dd = shiftDay ( ~d ; major ; 1 ) 


];


List ( 
Case ( ~aa < ~bb ; ~aa ; shiftDay ( ~aa - 1 ; major ; -1 ) ) ;
Case ( ~bb < ~cc ; ~bb ; shiftDay ( ~bb - 1 ; major ; -1 ) ) ;
~cc;
Case ( ~dd > ~cc ; ~dd ; shiftDay ( ~dd + 1 ; major ; 1 )) 
)


)

I am sure a brilliant mind could consolidate this into one function?