Jump to content

Finding Date within a Range


Jeff M
 Share

This topic is 2593 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

post-81640-0-96377800-1403880372_thumb.p

 

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

attachicon.giftable.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

Link to comment
Share on other sites

This topic is 2593 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Similar Content

    • By Sinky
      Hi FM people!
      I have one problem which is probably simple to solve but so far solution eluded me.
      I have a dropdown text field which offers a list of years (Table::Years). This is global field.
      I have very simple find script:
      Enter Find Mode [Pause: Off]
      Set Field [Table::Order Date; Table::Years]
      Constrain Found Set [ ]
      Table::Order Date is a date field.
      Idea is to perform find according to the value chosen from a dropdown list. However, this does not work. I'm getting "provided find criteria not valid".
      But, if I enter manually one of the years from the list, It works. Same is valid if I write down a year in the script e.g. Set Field [Table::Order Date; 1980].
      Set Field [Table::Order Date; Table::Years] itself works when in browse mode, but in find mode, nope
      This is I guess a format issue, so I tried several combinations changing fields to text or date but without success.
      Any help is appreciated
    • By wfcperrine12
      I have searched the web for a Custom Function that solves this to no avail…  There are many CF’s that “almost” do what I need but nothing as explained below.
      OBJECTIVE:
      Calculate a DATE x days AFTER a given date skipping weekends AND bank holidays.
       
      GIVEN:
      StartDate
      NumbOfDays
      Result Desired = Date that skips weekends and bank holidays.
       
      Any year’s HOLIDAY dates can be gleaned from pre-existing Custom Function “HolidayListByYear (yearNumber)” written by Jonathan Mickelson at www.briandunning.com (also uses another Custom Function he wrote called “DateByDayOccur”).
       
      Banking holidays should always include:
      * When a holiday falls on a Saturday, it is usually observed on the preceding Friday. When the holiday falls on a Sunday, it is usually observed on the following Monday.
      New Years Day
      Jan 1
      Martin Luther King Day
      Jan 20
      President’s Day
      Feb 17
      Memorial Day
      May 25
      Independence Day
      July 4
      Independence Day (observed)
      ?
      Labor Day
      First Monday in September
      Columbus Day
      Second Monday in October
      Veterans Day
      Nov 11
      Thanksgiving Day
      4th Thursday in November
      Christmas Eve
      December 24
      Christmas Day
      December 25
      New Year’s Eve
      December 31
       
       
      EXPECTED RESULT EXAMPLE 1:
      StartDate = 12/30/2020
      NumbOfDays = 2
      Result would be= 1/3/2021
      NOTE: Must skip Dec 31 (New Years Eve/weekend day), Jan 1 (New Years Day/weekend day)
       
       
      EXPECTED RESULT EXAMPLE 2:
      StartDate = 1/15/2021
      NumbOfDays = 2
      Result would be= 1/20/2021
      NOTE: Must skip weekend AND Jan 18, MLK Day
       
       
      EXPECTED RESULT EXAMPLE 3:
      StartDate = 7/2/2021
      NumbOfDays = 2
      Result would be= 7/7/2021
      NOTE: Must skip weekend and July 5 (July 5th is the observed day for Sunday, July 4, 2021)
       
      SPECIAL NOTE:  The custom function must be able to handle year crossovers such as EXAMPLE 1 above (going from 2020 into 2021.)
    • By Mafia2020
      I have a calculation that goes like that, I input a number on "Base Imponibile" (say 100), it adds 15% (it gets me 115), it then calculates 4% to the previous result (115) and it gets me 4,60, I then add 2 and the total is 121,60 which is perfect.
      And that's the part that works just fine. But I then want to make a reverse calculation, where I input the expected result of Total Calculation and tells me all the other values and here it's where things go awry.
       
      I'm attaching the initial calculation that works as a reference and the reverse calculation which doesn't work as an example. The "2" that gets subtracted in the reverse calculation is a fixed value of a small tax that I have to deduct (imposta di bollo).
      I have a feeling that the reverse calculation goes crazy because of some unwanted rounding somewhere, but I am not to sure, so I seek the opinion of more calculation versed persons around.


    • By Guy_Smith
      This should be easy, but my brain just won't cooperate!
      I'm moving a bunch of stuff and want to inventory what is in each moving container.  I have a very simple parent-child relationship with the parent record having the container number and category of parts/equipment/supplies and the child records describe each piece of equipment in the container.  I have a portal with the child records showing for each container, but want to put anatto-entered item number for each piece that has the container number followed by a dash and then followed by a serialized number for each item in that particular container.  For example, I would have container 1 labeled "Glassware" and Container 2 labeled "Chemicals".  In Container 1's portal I'd like to see:
      Item    Description          Qty
      1-1      Beakers, 500ml    4
      1-2      Beakers, 250 ml   2
      and in Container 2's portal I'd like to see:
      Item    Description          Qty
      2-1      Potassium           4 g
      2-2      Sodium                26 g
      I'd like to auto-fill the item numbers, but can't figure out how to reset the serialization for each parent record.
      Any help is greatly appreciated.
      Thanks and keep yourselves safe!
       
    • By stuee
      Hi, in the same table i have 3 fields.
      Name   (this is the drop down of names)
      Timecompleted
      Datecompleted
       
      I want to have so when a person selects their name in the drop down to complete this task that the current time stamp is filled in.
      I cant seem to get it to do that but ideally i would like just one field with timestamp   15:30  21/02/2019
       
      I also have about 50 tasks which people can do so hoping a more simple way instead of a fll script for each one.
       
      Hope that made sense
       
      Cheers
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.