Assigning a number to a record based on time range

[JariV]

Hi,

I have a database (Workshifts.fp5) consisting of records containing data about individual workshifts (fields include employee ID, start date/time, end date/time, hours etc.)

There's also another related file (PaymentPeriods.fp5), where users can define salary payment periods (fields include PeriodStart(date),PeriodEnd(date), PeriodNumber(serial))

There can be 12-30 salary payment periods/year depending on the user. I'm trying to figure out how to assign automatically PeriodNumber to new records in Workshifts.fp5 file when they are created, based on which used defined time slot they belong in PaymentPeriods.fp5.

Any ideas how to approach this are very much appreciated.

JariV

[transpower]

Presumably, a new workshift record will be using the last payment period; so a calculation field PeriodNumber_fk = Last(PaymentPeriod::PeriodNumber_pk) should work. (This assumes that new PaymentPeriods records are created when needed, and not beforehand.)

[JariV]

Thanks for your reply.

Unfortunately, most users create their PaymentPeriods beforehand, sometime before the beginning of the year, so that would not work in most cases.

Just to clarify my previous post, the records are usually created (imported) in batches of 7-28 workdays, and PeriodStart(date)-field is the one determining which PaymentNumber should be assigned to each record. So, in many cases, records created at the same time may belong to different Payment Periods.

JariV

[transpower]

In version 7 you would create a non-equijoin relationship between Workshift date and PaymentPeriod date, using >= AND <=.

In version 6 you may have to take the workshift date and compute the beginning date of the month and relate that to the PaymentPeriod file, assuming that PaymentPeriod records each begin at the beginning of the month--if that is not the case, then you must use whatever other relationship you have.

[JariV]

Thanks again Transpower,

There are quite many users still runnning Win98, so with this solution I'm stuck with Dev6 for quite a while...

To make this more challenging, the Payment Periods are pretty much random in terms of their location within a calendar month.

Otherwise I probably would have given up on this already, but the ability to assign those payment period numbers to workshift records would make so many things so much easier elsewhere, that I still hoping to find a way to do it.

Currently I'm out of ideas, so any help is appreaciated.

JariV

[Ender]

How about a lookup by Start Date?

Payperiod Relationship:

Workshifts::EmpID_Start Date_Link = Payment Period::EmpID_Start Date_Link

where EmpID_Start Date_Link is a calculation in each file = EmployeeID & " " & Start Date

The trick then is changing the Lookup for PP# to 'if no exact match, use next lower'.

Note that this could pull in an incorrect match if there are no Payment Periods for an employee.

[JariV]

Thanks a lot Ender,

A lookup by StartDate using 'if no exact match, use next lower' does exactly what I was looking for! Simple and brilliant!

JariV