Calc Field To Generate List Of Dates

[Ron Cates]

I am working on a timecard solution. I have three criteria involved in this question. One is the day of the week the pay period starts on. The second is wether the pay period is weekly or Biweekly. If Biweekly then there is a selection for wether the pay period starts on an odd or even week number. Based on those criteria I would like to create a value list that lists all the pay period starting dates in the current year.

Current selections in bold;

payPeriod - Weekly or Biweekly

payPeriodOddEven - Odd or Even

periodStartDay - Could be any day of the week. Current Selection Thursday

I was hoping I could get some help creating a calculation field based on the above criteria to base this value list on if it's even possible?

With the above selected criteria it would generate a list of dates in the current year that fall on Thursday and only on odd number weeks ( Based on WeekOfYear ( date ) function )

Thanks for any help..

[comment]

I would like to create a value list that lists all the pay period starting dates in the current year.

I suppose generating the starting dates shouldn't be too difficult - once the exact meaning of odd/even, weekly/biweekly are defined. However, "current year" is by necessity "unstored" - so you'll face that obstacle when trying to base a value list upon the results.

Is this even a matter of choice? I mean, assuming there is a date-of-work plus all those other settings - isn't the pay period given by that?

[Ron Cates]

All fields involved are in a single record preferences table so i intended to make it an auto enter with replace field contents so it could be stored and it would update if any of the criteria were to be changed. In addition i store the current year in a field in the same table until first open on a new year when my open script will update the current year and perform some beginning of the year tasks.

Biweekly means it is a two week pay period. Weekly of course is a one week pay period

Odd/Even is wether the first week of pay period starts on is an odd number week or and even number week as returned by WeekOfYear ( date ), which only applies if Biweekly is selected.

So with the criteria I listed for example 5/31/12 would be listed because it is a Thursday, the week number is 22 which is an even number meaning it is the first week of the Biweekly pay period.

If Weekly were to be selected then I would just want all dates of the year that fall on Thursday.

Hope that makes sence..

[comment]

I am afraid I still don't see how this should work. Suppose you have a biweekly pay period: the first period in the current year could start on the first Thursday of 2012 or on the second one. This is given entirely by the starting date of the first pay period ever (which could be back in 1979). Otherwise you will have occasional weeks that are counted in more than one pay period, or perhaps not counted at all.

Note also that the WeekOfYear() function is hardwired to start a week on a Sunday, and that it may split the week of January 1st into two - i.e. different dates in the same week may return different results. Either one is a good reason to look at WeekOfYearFiscal() instead, IMHO.

[Ron Cates]

It may help if i explain that each week is a single record with 3 sets of clock in and clock out fields each with seven repetitions for the 7 days of the week. I started with the starter solution that comes with Filemaker 11 which is set up in a similar way, only with one field for clock in and clock out. I integrated it into my current database and expanded on it substantially.

Each record representing a week has a field for weekBegin which is the date based on the preference of which day the pay week begins on. In our case Thursday ( strange i know but that's how we're set up ). The week number is based on the weekBegin date as well.

I've actually accounted for the last day or two of the year to some extent. A year has 52 weeks with a remainder of 1 day, or 2 if it's leap year. The last day of the year or two on a leap year returns 53 as a week number. I test for week number = 53 and change it to week number 1 of the new year. I can see that this could cause a problem by not listing the first pay period beginning date in the value list if it falls in the previous year. This will also happen when the first week is set to even because the last pay period of the year will begin on week 52 and the second week of that pay period will be week number 1 of the new year. I'll have to figure out how to adjust for this i supose, maybe just by allowing manual entry of the date for such a case or switching the year back to allow access to the date in the list from the previous year. I'll figure that one out..

This is to allow a manager or admin to add a pay period if necessary which will be very rare as I have scripted the pay period to be created when an hourly user logs in whithin a new pay period that has not been created yet. No matter what day the employee logs in i calculate the beginning of the pay period and create the record for one week if set to weekly or for two weeks if set to biweekly accordingly. Either way, the week number for each record is based on the day the week begins.

I explained in another post that I might have done things differently if I were to have built it from scratch. Such as a seperate record for each day or even for each clock in / clock out instance. But I was working within a serious time crunch put forth by my employer and had no time to start from scratch. This configuration allows me to build reports based on weekly or biweekly summaries which is all my employer needs. So it fits our situation just fine. Now i have some time ( not much ) to go back and spice it up a bit and cover any unforseen cercumstances.

By the way, what is the difference between WeekOfYear () and WeekOfYearFiscal() ?

[comment]

each week is a single record with 3 sets of clock in and clock out fields each with seven repetitions for the 7 days of the week.

Well, that's not a good arrangement - as you well know - but it has nothing to do with the issue of when does a pay period begin.

I test for week number = 53 and change it to week number 1 of the new year.

I am not sure what that is supposed to accomplish. I believe you may be sidetracked by trying to assign numbering to the pay periods; this is a purely arbitrary task. I would simply assign each period a serial PeriodID number, with no regard at all for years.

In any case, if your pay periods are weekly or biweekly, and if the periods are consecutive and all of the same length, then you are accumulating a sync error relative to the year sequence. This is a conceptual problem, not a calculation issue - and until you have a clear business rule dealing with it, it's all rather vague.

[Ron Cates]

I am not sure what that is supposed to accomplish. I believe you may be sidetracked by trying to assign numbering to the pay periods; this is a purely arbitrary task. I would simply assign each period a serial PeriodID number, with no regard at all for years.

Each record has a date for the day the week begins and a date for the day the week ends. The only thing the numbering issue has to do with is detirmining wether the week begins on an odd or an even week based on the weekBegin date. It's not used for any reporting or anything else other than allowing me to create drop down menues of dates for selecting which pay period to view. In another layout I use a drop down with a related value list that looks at all the existing week records and shows me only those dates from the records that have an even week number ( again based solely on the day the week begins ). It populates a global with the selected date and the selected date + 7. This allows me to view the two weeks of the pay period in a portal. I can then use goto related employee records on a layout using global fileds that create filtered relationships to the first and second week as well as a relationship to both weeks at once to provide totals for the pay period.

The only purpose of this value list is to add a pay period and have a drop down list of pay period beginning dates to cary to a script that will create one record with the weekBegin date set to the date selected and then another record set to the date selected + 7. Thus giving me two records, one for the first week of the pay period and one record for the second week of the pay period.

In any case, if your pay periods are weekly or biweekly, and if the periods are consecutive and all of the same length, then you are accumulating a sync error relative to the year sequence. This is a conceptual problem, not a calculation issue - and until you have a clear business rule dealing with it, it's all rather vague.

I have no idea what that means

Well, that's not a good arrangement - as you well know - but it has nothing to do with the issue of when does a pay period begin.

I have the utmost respect for you comment, but I think this is more of a difficulty in my explainations than anything else. I believe in using the propper foundation and setup from the start rather than working with flawed structure. But in this case I don't see this as a flawed structure. Maybe not the absolute best, but fully functional and sufficient for the needs of my employer. Besides, it is based on the structure used in the starter file provided by Filemaker, so how far off can it be? ( That's a bit of a joke there ).

I don't know if you'll be able to make heads or tails of the attached file as it is just the beginning of a total rebuild of the current database we have in place with a long way to go. It consists of two files as I am using the data seperation model. But I have it set up to open to the timecard portal layout with full access where you can select the start date of the pay period to look at in the portal. From there if you click "View All" below the portal or the "view Details" button on each row of the portal rows, you'll see the layout where i've used filtered relationships to different TOs to provide the weekly views and summary view. You can also use the print selection to see how I've provided summary reports based on the pay period selected. These are all based on the weekBegin date, not the week number. Only the dropdown is filtered by the week number, odd or even.

Click new in the tool bar from either of those layouts and you'll see where i'm looking to provide the drop down with the appropriate pay period start dates.

You can also click the gear image in the lower right of the screen to view the preferences.

Please be gentle with any criticisms as you explore the file. And remember it is just a very incomplete beginning and I am far less expreienced and knowlegable than you.

Wether you agree with me or not, I am confident in what I've done and that it will meet the needs of my employer nicely. Also, I have no time to back track at this point. Even if you could just help me with a way to create a calculated list of every date of the year that falls on a Thursday I can probably work out the rest from there? And if it ever bites me in the butt I promise I will come back and shout at the top of my lungs "comment told me so!!"

Thanks

Hope you have FM12..

File Removed

[comment]

It would be easy for me to answer your question "as asked" - however, I believe I would be doing you a disservice. In fact, let me start by answering the question as asked with regard to a weekly period: a repeating calculation field =

Let ( [

nY = Date ( 1 ; 1 ; Extend ( gYear ) ) ;

d = nY - Mod ( nY - 5 ; 7 ) - 1 + 7 * Get ( CalculationRepetitionNumber )

] ;

Case ( Year ( d ) = Extend ( gYear ) ; d )

)

will return the dates of all Thursdays in the given gYear.

Note that a year may contain either 52 or 53 Thursdays - and that is precisely my point regarding biweekly periods: the year 2009, for example, has 53 Thursdays. This means that the first biweekly period of 2009 started on January 1st - I presume that's what you would term as "Odd" - while the last one started on December 31.

If you continue the same cycle into 2010, the next period ought to start on January 14, 2010. However, with the Odd/Even parameter set to "Odd", and the year set to 2010, the first biweekly period will start on January 7 - a whole week too soon!

Hope you have FM12..

Alas, not yet.

[Ron Cates]

Good morning comment. A good night's sleep often does wonders. Working too many continuous hours on a concept often makes it difficult to break out from a particular line of thought. I should know by now it is often necessary to take a break and walk away for a period of time to be able to come back later and see it more clearly. After contemplating our ongoing conversation I finally grasped the issue. So, i humbly concur that the odd / even approach is flawed and will not work. Sorry for being so stubborn.

I also remembered your siggestion of giving each pay period it's own id which i will be incorperating into the creation of the two weekly records for each pay period and generate a period id that they will share. I can then number them week1 and week2 thus allowing me to genrate a filtered drop down of dates from weeks1 that will give me the stating date of each pay period.

So now i still need to address the possible need for a manager to create a pay period manually. My hope that prompted this thread in the first place was to provide them with a drop down of all the possible pay period start dates to select from. I am thinking now that i can use the same list generated by the existing records, but then what if the pay period that needs to be created does not have an existing record? If I generate a list that shows the date of every other Thursday in a given year, how would i detirmine which ones to omit and which to show? Hmmmm...

Thanks for your patience comment..

[comment]

I am still puzzled why they need so many choices. Do they intend to keep different pay periods for different people, or what? I would think that once they have decided whether they pay weekly or biweekly and when does the first pay period start, everything else would be given.

[Ron Cates]

I wish it was that easy but the owner of our company is plannig on expanding and openning other offices in different parts of the country involving partners and such. Almost like a franchise of sorts. They will be run independantly under different names and may have different preferences. They will most likely use thier own payroll companies and may have different criteria such as weekly or biweekly pay periods and what day the pay period starts on. So i am building the whole system with the ability for each office to start with a fresh copy of the database and set their own preferences.

[RodSierra]

FWIW, I've found that trying do this with some sort of auto calc will still leave the setting of the exact dates up to the user admin. These dates will most likely be adjusted based on holidays, or other factors by each Company. We set pay periods from a table Start Date, End Date, pay type. Its a simple task done once a year by hand.

[Ron Cates]

As i mentioned earlier in the post. The creation of the pay period weeks will be automated on the users first login within a new pay period. So it just doesn't seem worth spending all this time on something that should almost never be needed. I think I've pretty much resolved to move on and trust that the admin or manager will know full well what date the pay period they may want to add begins on. I would love a way to validate their entry, which might be possible at some point. But right now there are bigger fish to fry. I"ll add a selection in the preferences to set the date the first pay period begins on from when the database is first set up for use and go from there.