2 Replies Latest reply on Jul 8, 2011 12:02 PM by GregW_1

    Using Max function (or other) to return the value of another field in the same record

    GregW_1

      Title

      Using Max function (or other) to return the value of another field in the same record

      Post

      I'm developing a purchase order module with two related tables - PurchaseOrders with a one-to-many relationship to PurchaseOrderLines.  Basically the user creates the PurchaseOrder record, then uses a portal to the related PurchaseOrderLines to add/update/delete the related line-items.  I am trying to create a field in the PurchaseOrder table that records the account name of the user that last updated either the PurchaseOrder record or its related PurchaseOrderLines records. 

      I've been able to successfully create a calculation field to the PurchaseOrders table that records the latest timestamp from when either the PurchaseOrder or related PurchaseOrderLines have been modified -- I used the Max function to return the latest timestamp of the related PurchaseOrderLines, then an If statement to return Max (PurchaseOrderLines::modifiedTime) if that value is greater than PurchaseOrder::modifiedTime, and PurchaseOrder::modifiedTime otherwise.

      Now what I would like to do is create a field in the PurchaseOrders table that returns the value of PurchaseOrderLines::modifiedBy (the account name) from the same record with the value Max (PurchaseOrderLines::modifiedTime).

      I am using FMP9, so having a script that gets triggered when exiting any of the portal fields is not an option.

      Thanks for any suggestions.