Auto adjust date based on preference field

[Ron Cates]

I have a timecard solution. I'm using a filtered relationship with a global dropdown clendar date field for selecting the beginning date of the pay period week to view that weeks timecard. Our pay period runs from Thursday to Wednesday. I have set up the Employees::gWeekBeginSelect field to auto adjust to the Thursday before any date selected.

 

Calc

Case (
DayName ( Self ) = "Friday" ; Self - 1 ;
DayName ( Self ) = "Saturday" ; Self - 2 ;
DayName ( Self ) = "Sunday" ; Self - 3 ;
DayName ( Self ) = "Monday" ; Self - 4 ;
DayName ( Self ) = "Tuesday" ; Self - 5 ;
DayName ( Self ) = "Wednesday" ; Self - 6 ;
Self
)
 

I would like to be able to do this without hardcoding the days. I have a 1 record preferences table with a Preferences::payWeekBeginDay field with Thursday entered. I would like to use a calculation to adjust the selected date according to what day is entered in Preferences::payWeekBeginDay so if the beginning day changes the date field will adjust accordingly.

 

Any help is greatly appreciated.

Thanks

[Opotoc]

Hi,

 

It's easier to work with DayOfWeek ( date ), this will return 1 for Sun, 2 for Mon,...

then you can use Mod giving you the remainder.

 

So Thursday prior to yourDate should be:

 

yourDate - Mod ( DayOfWeek ( Get ( yourDate ) ) - 5 ; 7 )

 

Haven't really tried it, pls double-check.

 

Best,

Alexander

[Ron Cates]

Worked great!! I created a field in preferences to convert the dayname to the daynumber for the day selected so my calc looks like this

 

Self - Mod ( DayOfWeek ( Self ) - Preferences::weekStartDayNum ; 7 )

Thanks Alexander

[Ron Cates]

Well, here's a follow up. I also need to dynamically identify what day in the pay week today is based on the settings in Preferences. So in this case where Thursday is the first day of the pay period I would like a calculation that returns 1 if today is Thursday, 2 if it's Friday and so on. I am currently doing this with the calc below.

 

Case ( DayName ( Get ( CurrentDate ) ) = "Thursday" ; 1 ;
DayName ( Get ( CurrentDate ) ) = "Friday" ; 2 ;
DayName ( Get ( CurrentDate ) ) = "Saturday" ; 3 ;
DayName ( Get ( CurrentDate ) ) = "Sunday" ; 4 ;
DayName ( Get ( CurrentDate ) ) = "Monday" ; 5 ;
DayName ( Get ( CurrentDate ) ) = "Tuesday" ; 6 ;
DayName ( Get ( CurrentDate ) ) = "Wednesday" ; 7  ;


)

 

Again I am looking for a way to get these results without hardcading the day names but rather based on a field in the Preferences table.

 

Thanks in advance..

[Ron Cates]

Well I wrote a custom function for it

 

/**  By Ron Cates
 * =====================================
 * aDayNumberShift ( dayDate ; startDay )
 *
 * PURPOSE:
 * 		Returns the day of the week based on a chosen first day of the week
 * 		Used to determine what day of the pay week a certain day is based on what day   
 *                             the pay week starts.
 *
 * RETURNS:
 * 		Adjusted day number
 *
 * PARAMETERS:
 * 		dayDate - A date to determine the adjusted day of the week number
 * 		startDay - The day number to be adjusted to the first day of the week
 *
 * EXAMPLES:
 *		aDayNumberShift ( "1/30/2013" ; "5"  )
 *			will return 7 as the adjusted day of the week as 5 indicates Thursday should be
 *                                             the first day of the adjusted week and 1/30/2013 is a Wednesday
 */

Let ([

dayDate = GetAsDate ( dayDate ) ;
~dayDateNum = DayOfWeek ( dayDate ) ;

~day = ( ~dayDateNum - startDay ) + 8 ;
~day = Case ( ~day > 7 ; ~day -7 ; ~day )

];

~day

)