10 Replies Latest reply on Apr 23, 2012 9:17 AM by philmodjunk

    What is the FMP calculation order?

    ronm

      Title

      What is the FMP calculation order?

      Post

      The values i'm storing are out of step with calculation field whose value i want to store. The calculation involves the Sum of values from 2 portals.

      What is FMP calculation order? Is a Record Commit script step done before a calculation field sum is executed?

      i have a calculated field, sale::outstanding, that subtracts a sum of values in two portals and dispalys it:

      sale::outstanding= Sum(sale items::amount)-Sum(transaction::amount)

      the displayed value is always what i expect as i test it.

      i have a script trigger On Record Commit that takes this value and stores it in a field:

      Set Field [sale transaction:amount;sale::outstanding]

      the stored value rarely matches what is displayed, it's value is one step behind what is displayed. it's out of sync.

      any ideas as to why this is?

      how do i force a sync?

        • 1. Re: What is the FMP calculation order?
          philmodjunk

          the stored value rarely matches what is displayed, it's value is one step behind what is displayed. it's out of sync.

          Has the record been committed since the last edit to a portal record's value?

          Try making a habit out of clicking a blank area of the layout each time you modify a portal record's value.

          If that keeps things in synch, you just need to use a different script trigger such as onObjectSave instead of OnRecordCommit.

          • 2. Re: What is the FMP calculation order?
            ronm

            thanks phil,

            i noticed that clicking in a blank portal field sync'd things, but why? why do i see one value, the calculated one, the correct one, but the stored one is a step behind? things like this drive me nuts. I'll try the OnObjectSave. What is the difference between commiting and saving?

            • 3. Re: What is the FMP calculation order?
              ronm

              i attached the script to the OnObjectSave for the portal, it was never executed. I attached it to the OnObjectSave of a portal field. It was executed, the sync problem still exists. there is an open dialog in the script for debugging. the dialog display's the calculated field value i'm interested in. the value displayed in the layout and the value in the dialog box don't match. 

               

              btw, when the script is atteched to a portal field, clicking a blank portal line no longer sync'd the values

              • 4. Re: What is the FMP calculation order?
                ronm

                i noticed that when the script is attached to OnRecordCommit it ran twice, possilbly once for the portal record and once for the parent record. this may be where the problem lies. i'll see if i can see which runs first and what it's values are.

                • 5. Re: What is the FMP calculation order?
                  ronm

                  OK fixed it... with little insight from the forum i'm sorry to say. i created summary fields in each of the 2 tables, then did my math on the summary fields instead of using a "Sum()" function in the calculation field. the summary field values always seems to be up to date.

                  i am new to FMP and database design, so i probably made a rookie mistake using the Sum(). does anyone know why Sum behaves differently than a Summary field

                  • 6. Re: What is the FMP calculation order?
                    philmodjunk

                    Sum is actually what I prefer to use to compute portal totals. The reason I prefer them is that they update without requiring any script to do so.

                    By any chance, did you use a number field with an auto-entered calculation instead of a calculation field?

                    • 7. Re: What is the FMP calculation order?
                      ronm

                      I tried both.  Same results. I built a little test database to simplify the situation. I get the same unpredictable behavior. putting "RefreshWindow[ flush joined...] fixed the test database. I'm using sum fields now.

                      • 8. Re: What is the FMP calculation order?
                        philmodjunk

                        No script at all should be needed.

                        Take this example:

                        You define this relationship:

                        Invoices::InvoiceID = LineItems::InvoiceID

                        You then define this calculation field, cInvoiceTotal, in the invoices table:

                        Sum ( LineItems::amount )

                        Put cInvoiceTotal on your layout below the portal and it should update smoothly as you add, remove, edit records in a portal to LineItems. No scripting will be needed to get it to update correctly.

                        • 9. Re: What is the FMP calculation order?
                          ronm

                          hi phil,

                          thanks for the quick reply.. the situation you described works fine. where i got into trouble was this:

                          i made a table called transactions. every exchange of money is a transaction. a sale has two relations to transactions, the total owed for the items sold, a single trasaction record, and the payments received for the sale, a transaction record for each payment received. 

                          the sync problem came up here: whenever a payment is made, i tried to reduce the total owed record using a calcutaion. I think having 2 relations into the same table, even though they were two different instances in the graph, created an update delay. anyway, using sum fields and changing the structure a little seems to have fixed it.

                          i'm new at this, so it's alot of trial and error. i come from a programming background. i think too much like a programmer and not a database designer. i'd rather code what it want to happen rather than use solid database design.

                          BTW- is there a way to simplify the relationship graphs? folders or block bundles or something to reduce the clutter?

                          • 10. Re: What is the FMP calculation order?
                            philmodjunk

                            BTW- is there a way to simplify the relationship graphs? folders or block bundles or something to reduce the clutter?

                            Take a look at this article: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/