Finding Date within a Range

[Jeff M]

I have a few situations where I need to be something specific having to do with dates.

For the sake of this example, let's say we're dealing with pay periods.

 

payperiods table will have 1 record for each pay period. It will have a pay period number, a date the pay period starts and a date the pay period ends. See attachment table.png

 

 

From other tables, when a user enters a date...I need to be able to return the pp_number for which that date belongs.

For instance, entering 7/12/2014 would return 2.  Entering 7/19/2014 would return 3.

 

Nothing is set in stone here, I'm open to a different structure, I just need an efficient way of looking up the pay period number.

 

Suggestions?

 

Thanks in advance!!!

Jeff

[eos]

Nothing is set in stone here, I'm open to a different structure, I just need an efficient way of looking up the pay period number.

 

Some approaches that come to mind:

 

• perform a scripted Find where period start ≤ the user date and period end ≥ the user date

 

• create a relationship, using a global entry field and the same predicates

[comment]

payperiods table will have 1 record for each pay period. It will have a pay period number, a date the pay period starts and a date the pay period ends.

 

What purpose does such table serve? And how will it be populated?

[Jeff M]

What purpose does such table serve? And how will it be populated?

 

I have several solutions in which I need this functionality. One use would be setting up pay periods in advance, so that as records in a time card are added, they auto populate with the pay period number. Another use would be when specific assignments are based on date ranges. For instance, say employe john will handle all requests that fall during this range of dates, while employee jane will handle requests that fall during a different range of dates. Then when a date is entered in a different table, it would auto lookup which employee to which it will be assigned.

 

Looking up this via a relationship will definitely work. Only drawback is I'll have to recreate that lookup in every table I want to use it. A more generic function I could use over and over would be preferable. 

[comment]

One use would be setting up pay periods in advance, so that as records in a time card are added, they auto populate with the pay period number.

 

Auto-populated how? And how long in advance?

 

The reason I am asking is that I suspect this table serves no purpose at all, and that the pay period number can be simply calculated,  e.g. as =

Div ( GivenDate - Date ( 6 ; 24 ; 2014 ) ; 7 )

If you like, you could define this as a custom function, so you wouldn't need to hard-code the initial date in every table and field where this might be needed.

 

 

 

Another use would be when specific assignments are based on date ranges. For instance, say employe john will handle all requests that fall during this range of dates, while employee jane will handle requests that fall during a different range of dates. Then when a date is entered in a different table, it would auto lookup which employee to which it will be assigned.

 

I am afraid I don't understand this too well. It seems to me you should have another table for this, as this has nothing to do with pay periods?

[NLR]

I have a few situations where I need to be something specific having to do with dates.

For the sake of this example, let's say we're dealing with pay periods.

 

payperiods table will have 1 record for each pay period. It will have a pay period number, a date the pay period starts and a date the pay period ends. See attachment table.png

 

table.png

 

From other tables, when a user enters a date...I need to be able to return the pp_number for which that date belongs.

For instance, entering 7/12/2014 would return 2.  Entering 7/19/2014 would return 3.

 

Nothing is set in stone here, I'm open to a different structure, I just need an efficient way of looking up the pay period number.

 

Suggestions?

 

 

Using a relationship between a "Date" field in one table related to the "FinishDate" (for example) in your PayPeriods table will work if you take advantage of Filemaker being able to look up the nearest highest or lowest value when there's no exact match.  I've attached a sample file.

 

Regards

Ralph

PayPeriods.fp7.zip