2 Replies Latest reply on Jan 30, 2012 12:31 PM by dvhoudek

    Calculating from one record to another

    dvhoudek

      Is there a way to calculate the inclusive dates between two records in a table (or even in a related table if that makes it easier)? I need to calculate the dates between "effective_date" for someone's pay so I can check to see if a time record falls within the effective period for a specific pay record. For example, I may have four pay records, each with an effective date and I need to see if the project time I entered falls between the effective date of record 3 and 4 or 4 and today for a given employee. Unfortunately, I have no "end dates" on pay records. A scripted solution would be fine, but I'd rather see if there's a way to do it with calculations.

       

      Thanks!

      Diane

        • 1. Re: Calculating from one record to another
          RayCologon

          dhoudek@chapinhall.org wrote:

          For example,  I may have four pay records, each with an effective date and I need to see if the project time I entered falls between the effective date of record 3 and 4 or 4 and today for a given employee.

           

          Hi Diane,

           

          There are a few different ways you could approach this.

           

          One option - assuming the records are in sequence in the found set, would be to compare a known date value with the dates on the current record and the following record. To do this, you should be able to build an expression using the GetNthRecord( ) function, along the lines of:

           

          KnownDate ≥ CurrentTO::DateField and KnownDate < GetNthRecord(CurrentTO::DateField; Get(RecordNumber) + 1)

           

          Alternatively, if you don't want the calculation result to depend on the found set and sort order, you may be better off using a relationship to establish the period into which KnownDate falls. For example, if you simply set up a relationship that matches the KnownDate field to the DateField using the > relationship operator, sort the relationship in descending order by date, then the first related record will be the start of the period within which the KnownDate falls.

           

          If neither of these suggestions appears suitable, you might need to expand a little on the structure of your solution, so folk here can make some more targeted suggestions.

           

          Regards,

          Ray

          ------------------------------------------------

          R J Cologon, Ph.D.

          FileMaker Certified Developer

          Author, FileMaker Pro 10 Bible

          NightWing Enterprises, Melbourne, Australia

          http://www.nightwingenterprises.com

          ------------------------------------------------

          • 2. Re: Calculating from one record to another
            dvhoudek

            Thanks Ray, this is helpful. 

             

            I think I was getting hung up in the idea that multiple pay records would be effective for a given employee if I used the known_date >= date_effective. I realize that a sorted relationship grabs the first matching record, but I guess I was concerned that wasn't really a "concrete" or "foolproof" solution.

             

            Cheers,

            Diane