1 Reply Latest reply on May 2, 2014 9:28 AM by philmodjunk

    Calculating difference between previous and current readings



      Calculating difference between previous and current readings


           I need to calculate the difference between the current and previous readings for a tenant's utility use at their site. I am new to FM Pro and have no idea how to go about doing this.  I have attached a copy of my relationships graph to assist.  I need to display the result on my tenants layout.  I already have a portal on the tenants layout which shows the readings by date order, filtered for that tenant.

           Any assistance would be greatly appreciated.


        • 1. Re: Calculating difference between previous and current readings

               There are potential issues with your current design. What will you do if you should ever have three tenants for the same site? Setting up one record for each tenant--they can each  match to the same Sites record is more flexible. And if there is any chance that the same tenant can rent more than one site, this creates a need for possibly an even more complex data model.

               But that's a change you can consider independently of the issue at hand.

               I'll focus on just the Electricity Readings Data table, but the same methods can be used for the water meter (and there are even ways to record both readings in the same table...)

               The previous record will be a record with the largest date that is also less than that of the current record, but that also has the same site id value.

               In Manage | Database | Relationships, use the duplicate button (two green plus signs) to make a new table occurrence of Electricity Readings Data. Double click it to open a dialog box where you can rename it as: Electricity Readings|Previous. Drag from Site ID Match field to Site ID Match Field to link them in a relationship. Then double click the relationship line to specify more details. Add another pair of match fields and change the default operator to produce this relationship:

               Electricity Readings Data::Site ID Match field = Electricity Readings|Previous::Site ID Match field AND
               Electricity Readings Data::Date > Electricity Readings|Previous::Date

               In this same dialog box, click the Sort button on the Electricity Readings|Previous side and specify a sort order that sorts these records by Date in descending order. This makes the most recent previous related record, the first related record.

               Now define this calculation field to compute the difference of the current reading less the previous reading:

               Reading - Electricity Readings|Previous::Reading