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.
Your calculation must be unstored because it uses Get ( CurrentDate ) and this function will not update correctly if your calculation is stored.
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.
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.