Getting most recent non-blank value from a portal field??

[kaiser715]

I'm new to FM, just got version 10 a few weeks ago. I'm proficient in an old legacy database program (filePro), but it's time to move on.

I have a database of equipment. A portal relationship (to "servicehistory" table) holds the service history for an item. When available (i.e. the info gets written on the work order), there is a field for the hour meter reading, "hourmeter". I want a field in the main (equipment) table set to the most recent, non-blank, hour meter reading entered into the portal records.

I spent several hours on it today. Everything else about FM has come easily, except this.

Portal records are sorted by date, with the most recent appearing at the top of the listing. But...the first five or more records in the portal may have nothing entered in the "hourmeter" field.

Max(servicehistory:hourmeter) will not work, as the hour meter on the equipment is occasionally reset when certain maintenance is performed, or it rolls over at 999.9 hours. So, for example, a service report from a year ago might show 750 hours, one last month might show 50, and last weeks service report might not have an entry. I want to set a field in the main table to, in this example, "50".

How???

Thanks!!!

[comment]

The Last() function was designed specifically for this purpose - but you must make sure your relationship is not sorted (you may still sort your portal however you wish).

BTW, you want to use an unstored calculation field for this, not to set a field.

[kaiser715]

BEAUTIFUL!

Worked on the first try! :

Thank you for the help...the more I learn, the more I find that there is yet more to learn about FM!

Thank you!!

Edited April 7, 2009 by Guest