1 Reply Latest reply on Jun 17, 2014 2:08 PM by philmodjunk

    Functions help!



      Functions help!




           I am creating a reconciliation database for water meters, with a lot of help from this forum I have been able to get it up and running!  I have different location numbers for each water meter and entries which record each water meter reading.  I am currently using the Last function to calculate the total amount each location has used but have ran into a problem. We have some entries that were not recorded from April and now when we put those into the database it is not grabbing the first date for each location... Is there another function that would work better? Or a way to modify the Last function to get it to take the most recent date-the oldest date?

        • 1. Re: Functions help!

               Here's how this works and then I'll explain the fix you can put in:

               Say you have this relationship:


               Meters::__pkMeterID = MeterReadings::_fkMeterID

               Imagine an unsorted, unfiltered portal MeterReadings. The relationship only matches to readings for a specific meter. If, from the context of your table of meters, you simply refer to a field in MeterReadings, you get the value of the First related reading, the oldest MeterReading record that matches to your current Meter record and this would be the reading that appears in the first row of this portal. Last returns the value of the last related record where the specified field is not empty, so that would the the very bottom row of this same portal assuming that the field specified in the function is not empty for any of the related records. When you skipped some readings and then entered them out of order, they became the last records entered and your results were screwed up.

               But if you were to specify a sort order for this portal that sorted the records by the date when the reading was actually made, the most recent reading would be in the bottom portal row even when the readings are entered out of order for that meter. Unfortunately, the Last function ignores any such sort order specified in Portal set up as it goes directly to the related table to access data. BUT, you can double click the relationship line between Meters and MeterReadings to bring up a dialog where you can sort the relationship itself. That sort order WILL work and you can then use the Last function to access the most recent reading even when records are entered out of order.

               So Open Manage | Database | Relationships. Find the relationship line and double click it. Specify a sort order for your table of meter readings that sorts these records in descending order by date.