4 Replies Latest reply on Oct 4, 2010 8:56 PM by ScottMacKenzie

    Help please: Calculating elapsed time from related records

    ScottMacKenzie

      Title

      Help please: Calculating elapsed time from related records

      Post

      Hi there, and thanks for your help! I'm new to FMP and trying it out.

      I'm trying to calculate the length of time that an item spends in a location but having difficulties because I'm using sorted portal rows to perform the calculation (I think).

      Table: Items, contains item ID (unique) and name.

      Table: Locations, contains item ID (link), movement ID (unique), movement date, location and duration.

      I want to calculate how long an item spends in each location. The start date is the date in the same record but the end date is the date in the next record if it exists, sorted by movement date in descending order, or the current date.

      Here's my calculation:

      If ( Get ( RecordNumber )=1 ;

      Get ( CurrentDate )-Movement Date ;

      GetNthRecord ( Movement Date ; Get ( RecordNumber )-1 ) - Movement Date )

      1. It works in the layout with the sorted portal when it's unstored but not when it's stored. It think this is because when it's stored it performs the calculation based on the unsorted table rather than the sorted portal.

      2. It doesn't work _reliably_ in the table, sorted or otherwise, because the first record for one item may not be the first record in the table.

      Pointers, ideas?

      Do I need to a script triggered by entering a new movement? Do I need to use a self-join in Locations using item ID and Movement Date?

      Thanks again

      Scott

        • 1. Re: Help please: Calculating elapsed time from related records
          brisance

          My solution to this problem is to create an "asset movement" table that gets updated when the asset's location is changed. It stores the asset ID, the location, the date of the movement and the user who entered the asset movement information. After that it's just a simple matter of using the find functions to restrict records matching the asset ID. The benefit to this method is that you have far more flexibility when running reports on asset movement; the main disadvantage is an increase in storage but that is not a big deal since in my situation, every action needs to be logged anyway.

          • 2. Re: Help please: Calculating elapsed time from related records
            philmodjunk

            Your calculation must be unstored because it uses Get ( CurrentDate ) and this function will not update correctly if your calculation is stored.

            • 3. Re: Help please: Calculating elapsed time from related records
              ScottMacKenzie

              Hi brisance, thanks for your reply.

              My solution to this problem is to create an "asset movement" table…

              I think my Locations table is that same as your asset movement table. I should have made that clear by calling it "Item Movements" rather than Locations, since that is what it tracks. It stores the Item ID, the Movement Date, the new Location and the Movement ID, a unique number to identify an individual movement.

              The problem is the calculation. Because it references the Movement Date in the previous record the result changes according to the found set and the sort order. It works fine in a portal which only displays movements for one item sorted in reverse date order. However if I want to report on how long items spend in one specific location the result changes because the date in the previous record changes.

              Would welcome any further thoughts.

              Regards, Scott

              • 4. Re: Help please: Calculating elapsed time from related records
                ScottMacKenzie

                Hi PhilModJunk, thanks for your reply.

                >Your calculation must be unstored because it uses Get ( CurrentDate )…

                I agree with your statement, but it wasn't causing my problem in this instance.

                I understand what the problem was with the method I was using: I was using a calculation that referenced values based on their location in a list. Naturally if the order or contents of the list changed my results could change which is undesirable.

                The problem: trying to reliably find the next occurrence by date of an item in a list. The item may be in the list many times on different dates.

                What I'm doing now: relationship to a second occurrence of the table, match by Item ID = Item ID AND Movement Date < Movement Date, sort by Movement Date.

                So, now my calculation is:

                If ( IsEmpty ( Locations 2::Movement Date ) ;

                Get ( CurrentDate ) - Movement Date ;  

                Locations 2::Movement Date - Movement Date )

                Since it depends on a relationship the result doesn't change depending on the layout/found set/sort order. It seems to be working well.

                Since the relationship is sorted it presents the next date for the calculation.

                I'm not experienced enough with FMP to foresee if this will cause me any problems in future.

                Would appreciate any thoughts on this.

                Thanks again

                Scott