This is a public Forum  publicRSS

Forum post

    Shawn Amann
    Calculated Value vs Calculation FieldAnswered
    Forum post posted July 30, 2010 by Shawn Amann , last edited February 9, 2012
    522 Views, 2 Comments
    Title:
    Calculated Value vs Calculation Field
    Post:

    I am having some conceptual issues with the above. I have found that the calculated value never seems to do what I want it to do (although it seems like it should) while the unstored calculation always hits the spot. 

    What I am doing is having a list table that will store purchase orders and Request for payements. These are two seperate tables. They are feeding into the list table through a realtionship that can create records (I am setting the field in the list table to get the record created)

    Now I would like the date created to be my next field in the list. I set that as a date field, calculated value and uncheck the do not replace existing value. The calculation is

    Case(
    not IsEmpty(PURCHASE_ORDERS::date_created);
    PURCHASE_ORDERS::date_created;
    not IsEmpty(REQUEST_PAYMENT::Date Created);
    REQUEST_PAYMENT::Date Created
    )

    This does not work as a calculated value. Works fine as an unstored calculation field. 

    So I dont get why that is. The PURCHASE_ORDERS::date_created is auto entered and thus exists prior to this calculation being evaluated. (prior to the record it is in even exists) Therefore when the record is created and this calculation runs there should be a value where it expects it. Do calculated values not work across relationships?

    So why do I care is the calculation field would work? It seems that unstored calculations would use more system resources as they are always looking to update (not sure if true or if it even matters with a small database) and I want the data to remain unchanged even if the original record is altered. 

    Any thoughts. Thanks in advance. This forum has helped tremendously.

    Best Answer

    PhilModJunk

    Unstored calculations do slow down searches and sorts as well as screen refreshes if you have a lot of them such as in a list or table view where you may display the same field many times. Whether this is a significant concern depends on the quantity of records involved and the speed of your system. (Single machine with a fast processor, shared over a LAN, over a WAN, using a Filemaker GO as a client....)

    Auto-entered calculations do not auotmatically update if a related record's value referenced in the calculation is changed. That's the most critical limitation to keep in mind. In your situation, I suspect that the date is not being entered because the record in the related table where the date has just been auto-entered has not yet been committed. Commit the record via script or by clicking on a blank part of the layout first and then enter data in your line item record and see if that works for you.

    You can automate the process with a script trigger that commits the record. You may have to play with different triggers to find the best option here though an OnObjectEnter or OnObjectExit trigger will probably do the job for you.

    Answer

     

    • PhilModJunk

      Unstored calculations do slow down searches and sorts as well as screen refreshes if you have a lot of them such as in a list or table view where you may display the same field many times. Whether this is a significant concern depends on the quantity of records involved and the speed of your system. (Single machine with a fast processor, shared over a LAN, over a WAN, using a Filemaker GO as a client....)

      Auto-entered calculations do not auotmatically update if a related record's value referenced in the calculation is changed. That's the most critical limitation to keep in mind. In your situation, I suspect that the date is not being entered because the record in the related table where the date has just been auto-entered has not yet been committed. Commit the record via script or by clicking on a blank part of the layout first and then enter data in your line item record and see if that works for you.

      You can automate the process with a script trigger that commits the record. You may have to play with different triggers to find the best option here though an OnObjectEnter or OnObjectExit trigger will probably do the job for you.

    • Shawn Amann

      Once again right on the money. 

      I created a script that commits the purchase order record, then sets the field in the lines table which creates that record and allows the calculated value to work as I pictured it.

      Take home message *Committing a related record must be done first* for a calculated value to work. 

      Thanks again.