Date range status field

[jnmorrison]

I have a payroll database which divides dates up into different pay periods. Each of the first 14 days are marked as pay period #1, the next 14 are pay period #2. For each record I want a field which tells me the last date in the pay period.

Example: 1/1/03 thru 1/14/03 are listed as pay period #3. When I look at the record for 1/8/03, I want to see that it is part of a pay period which ends on 1/14/03. So, all records in this example would read 1/14/03. The items in the next pay period would obviously have a different ending date.

It would be great if this were possible through a calculation, but I can't think of a way, so a script would be fine. Any suggestions on the best way to get there?

Jeff

[Pinky]

Hi there,

I assume your payslips have a date field DATE_FIELD.

So do this :

to get the day of the pay, type Date(DATE_FIELD).

so in the script (or set PAY_PERIOD as a calculation when you define fields), you would have:

SetField(temp_number,Date(DATE_FIELD)

If(temp_number < #Period1)

SetField(PAY_PERIOD,"1")

EndIf

Do this for all your periods (remenber to put else so that it doesnt go in the other IF statments, or start at the top; if period 3 = 30, period2 = 20, period 1 =10 then:

If(temp_number < #Period3)

SetField(PAY_PERIOD,"3")

EndIf

If(temp_number < #Period2)

SetField(PAY_PERIOD,"2")

EndIf

If(temp_number < #Period1)

SetField(PAY_PERIOD,"1")

EndIf

Hope it helps...

[jnmorrison]

Thanks for the reply. I'm not sure you understand what I need...so let me try to explain better. I have a filemaker database called Pay_periods.fp5. In that database are records, each only having 3 fields: uniquieID (this is auto-numbered), date (date field) and pay_period (number field).

In this example, each pay period has 14 (bi weekly) consecutive days. So, January 1 thru 14 (14 total records) all have "1" in the pay_period field. January 15 thru28 have "2" in the pay_period field. I have a record for each date for the next 10 years, approx. 3650 records...all divided up into 260 different pay periods.

I want to add a fourth field which will display the highest date in the pay period. So, in pay period "1" it would display 1/14/2003 in all 14 records. For all records belonging to pay period "2" it would display 1/28/2003, and so on. For the sake of this discussion, we'll call this new field Last_in_period.

It would be great if I could make this a calculation field so that it wouldn't require running a script, but I can't think of a way to do it. A script would also work that could loop through all 3650 records and assign the Last_in_period field.

[BobWeaver]

A script shouldn't be necessary just a calculation:

EndDate=

(Int(1+(Date - 10)/14) * 14) +9

[jnmorrison]

One more thing I should have mentioned. In this example it is 14 day pay periods. I also want it to work for 7 day or even bi montly or monthly.

[BobWeaver]

Weekly:

2+Int(1+(TheDate - 3)/7) * 7

BiWeekly:

9+Int(1+(TheDate - 10)/14) * 14

SemiMonthly:

Case(Day(TheDate) > 15, Date(Month(TheDate)+1, 0, Year(TheDate)), Date(Month(TheDate), 15, Year(TheDate)))

Monthly:

Date(Month(TheDate)+1, 0, Year(TheDate))

[jnmorrison]

Ok, at this point you must be getting very tired of me, but once again, here I am. There seems to be a minor disconnect in the information I'm explaining. Bob's solution is great except that it assigns the dates within the pay period for me which i don't want. I've already assigned the pay periods in the database...I now just need to evaluate all items in pay period one and determine which is the ending date, then populate the ending date field in each of them with it. In other words, I may need to change the pay period assignments at any time, meaning I'll need to execute another script to re-evaluate. Today pay period 1 may include 1/1/2003 thru 1/14/2003, but it may change so that pay period 1 includes 1/5/2003 thru 1/19/2003. This script (or calculation) needs to evaluate the pay period fields rather than the date fields. The ending date is simply what I want to copy once the script finds it.

Jeff

[BobWeaver]

The pay period formulae I gave you don't care about the pay period number, they simply find the end date of any arbitrary weekly, biweekly, semimonthly or monthly period in which the given date occurs. For the biweekly and weekly ones, you can shift the date range that the period covers by changing the constants in the formulae. I.e., change the 2 and 3 in the weekly formula and the 9 and 10 in the biweekly formula. You only have 7 different possibilities for the weekly one and 14 different possibilities for the biweekly one. The semimonthly and monthly ones will never change.

If you are trying to group together all records that fall into the same pay period, you can use these formulae for a calculated field that will give you the same period end date for all the records that fall into that period. You can then use that calculated field to summarize the data. But, it isn't all that clear from your posts what you are trying to do exactly.

[jnmorrison]

Sorry I haven't been clear...let me try one more time...

I want the end-user to be able to change the pay periods to whatever they want them to be by giving me a begin date and the type of pay period. I then have a script which loops through all the records and assigns the pay period. So, you see, pay period one could start on January 1 or it could start on May 12th. The user will define. It could be any day of the week and any month. I have a script which takes care of assigning the pay period based on the input I receive from the end user.

So here's the issue...I want to identify each record by when the pay period ends. So, from another table, I want to lookup the date and see that it is part of a pay period which ends on a certain date. Every date will be associated with an ending pay period date.

Now, the end user may want to change this, which requires my script to renumber all the pay periods. That's no problem. The issue remain re-assigning the payperiod ending date field.

I hope this makes better sense. Sorry I've been so unclear.

Jeff

[BobWeaver]

I get the general idea of what you are doing, but it seems to me that you are making things overly complex. Why do you need to look up the period end date from another file when you can simply calculate it with a formula? If you look it up from another file, you will need to have a record for every possible activity date. With the formula I gave the end date will immediately calculate when the user enters the activity date. I do understand that you need the ability to change the range that the pay periods cover, so I have generalized the previous formula to the following:

Mod(gFirstPeriodStartDate -1, gPeriodLength)+Int(1+(ActivityDate - (1+Mod(gFirstPeriodStartDate -1, gPeriodLength)))/gPeriodLength) * gPeriodLength

You can create two fields, a date global field gFirstPeriodStartDate which contains the start date of the first pay period, and a number global field gPeriodLength which contains the length of the period in days. Given this information which the user can enter, and the activity Date, the period end date will calculate automatically, and you don't need to look anything up. You can also combine the above formula in a case function with the ones I gave earlier for monthly and semimonthly to handle all situations based on the users' input.

Case(

gPeriodLength=30,Date(Month(ActivityDate)+1, 0, Year(ActivityDate)) ,

gPeriodLength=15,Case(Day(ActivityDate) > 15, Date(Month(ActivityDate)+1, 0, Year(ActivityDate)),

Date(Month(ActivityDate), 15, Year(ActivityDate)))

Mod(gFirstPeriodStartDate -1, gPeriodLength)+

Int(1+(ActivityDate - (1+Mod(gFirstPeriodStartDate -1, gPeriodLength)))

/gPeriodLength) * gPeriodLength

)

With this formula, a period length of 15 will give semimonthly end dates, a period length of 30 will give monthly end dates, and for all other period lengths, the end date will be according to the length and first period start date.

If this doesn't work for you, then there's still something you're not telling me.

[jnmorrison]

Bob,

The lightbulb finally came on and I understand what you are getting at. I new there had to be a better way than needing the extra table to keep up with. You were right, I was making it way too complex. Thanks so much for hanging in there and helping so much.

Can you explain what the Mod function does...I can follow the rest of what you are doing, but this one is not quite clear. I don't want to simply copy your code...I want to understand why it works. Thanks so much for all your help.

Jeff

[BobWeaver]

The mod function gives you the remainder that you would get when you divide one number by another. For example If you divide 17 by 5 you get a quotient of 3 and a remainder of 2 likewise with Mod(17,5) you get 2. This is the strict mathematical definition of a mod function, and doesn't necessarily give you much of an idea about what you can do with it. It comes in very handy when doing calculations on things that repeat such as days of the week etc. If you have the formula Mod(AnyDate,7) it will give you a number between 0 and 6 which will correspond directly to the day of the week on which that date falls.

It is also helpful to know that internally Filemaker stores dates simply as the number of days since 1/1/0001 (actually the day before 1/1/0001, whatever that was). So it's easy to add or subtract days and dates to get new dates, or subtract one date from another to get a period of days.