I hope I am posting this properly and apologize if I haven't.
I have a problem retrieving the previous entry of a field in related record, to be used in a calculation. In "DipEntry" it works very similar to an invoice, with a portal to the child table "LinesDipEntry" on DipEntry layout.
It is used to enter customer tank dips daily, in order to monitor their inventory for each of their tanks.
I would like to do a comparison calculation showing daily changes in inventory levels, so I require a field in the portal that shows the previous "dipMeasure". Records might not be entered in proper sequence, so must find last entry by date which is in DipEntry, not LinesDipEntry (joined by DipEntryID).
On the layout, outside of the portal, I can bring up the previous DipEntry date and ID via a relationship to second TO "DipEntryLast" joined by "date less than" DipEntry and sorted in descending order by date (also joined by CustomerId).
I also created a second TO to DipEntryLast of its child table (LinesDipEntryLast), as well as yet anotherTO to that, LinesDipEntry by tankid (since deleted) in order to try and get each portal row to reference the proper tank. LinesDipEntry does not have a date field to sort on, only its parent does.
The problem is, I cannot wrap my head around how to bring up the previous dip for each tank (each line of the portal LinesDipEntry).
I've tried simply putting the field I need, LinesDipEntryLast::dipMeasure in the portal, as well as using a calc field in LinesDipEntry using Last(LinesDipEntryLast::DipMeasure) to varying results. In particular, I either get results that aren't specific to the tank, or each DipEntry record shows lines in the portal specific to the tank, but not a previous date (are all the same data for each record date), or nothing at all.
I need to use this number to calculate the difference between the two values to better forecast deliveries, as the manager enters dips. I have warning on levels, but rates of changes are very important to us.
Is using a relationship like above the most efficient (least work for the database)? In which case, what am I doing wrong?
Or, is a calculation in a field in the portal best?
Or, is ESQL the best (still I guess a calc in the field)?