8 Replies Latest reply on Dec 11, 2011 10:47 AM by DanielLG

    Enter and update field values by calculation

    DanielLG

      Hi all,

       

      I am stuck with a problem on auto entered (calculated) field values (numbers) depend on other values, which are also re-caluclated and updated after dependent data have changed.

       

      Here a example to explain the case:

      There is a master table, lets call it invoice, and a second related table, lets call it ordered products. The relationship should be clear. The ordered products table has the following fields:

      NumberOrdered:

      TotalPrice:

      PercentageOfTotalOrder.

       

      The invoice table has a field:

      InvoiceTotal:

       

      During entry of the invoice with all the ordered products, i want to have the last field "PercentageOfTotalOrder" been automatically calculated based on the invoice. It's clear that with each ordered products added this value will change for each product for the same invoice, and must also be updated once the invoice data will change.

       

      Is there a way to do this with fmp?

       

      Thanks for any help.

      Daniel

        • 1. Re: Enter and update field values by calculation
          psijmons

          Set a script trigger on the data entry field (OnObjectSave) with a script that forces recalculation in the Percentage field

          1 of 1 people found this helpful
          • 2. Re: Enter and update field values by calculation
            DanielLG

            How to reforce the recalculation ?

            Is the recalculation only done for the subset of OrderedProducts record for the invoice showing, or on all records in that table?

            • 3. Re: Enter and update field values by calculation

              Hi Daniel,

               

              Your PercentOfOrderTotal should be calculation and, since it references parent (Invoices) table, it will be unstored so it will update without being forced.  I am not sure how you are set up but you might take a look at this sample I just created for you.

               

              http://directlinesolutions.com/downloads/invoices.zip

               

              If I have misunderstood your need, please explain more. 

              • 4. Re: Enter and update field values by calculation
                DanielLG

                Hi LaRetta,

                 

                Thanks so much for the sample, it shows exactly what i need.

                 

                Just one more thing. The table Invoice has a value in a field, lets just name it "A". This value is being used to calculate another value in in a field in table LineItems. When i add LineItem records it works very well, but when i change the value of "A", the records of LineItems are not recalculated.

                 

                Is there any script:function to force recalculating of the LineItems, or to refresh the portal?

                • 5. Re: Enter and update field values by calculation
                  BruceHerbach

                  Daniel

                   

                  If you want a field in another table to update with an auto enter calc you need to use a script.

                  The script can do the calculation and set field or add a dependency by adding another field in the table to a Let statement in the calculation and have the script change the value of the field.  Esentially create a trigger field.

                   

                  Bruce

                  • 6. Re: Enter and update field values by calculation

                    Hi Daniel,

                     

                    Before providing an answer, it would help to understand the purpose of this field in Invoices and how it effects, and is used by, a field in LineItem records.  That feels like circular logic to me.

                     

                    Many times, a simpler approach can be used.

                    1 of 1 people found this helpful
                    • 7. Re: Enter and update field values by calculation
                      DanielLG

                      Hi LaRetta,

                       

                      I added a sample which might help.

                       

                      Table HerstellungsProtokoll is the master table.

                      Table HerstellungArtikel is the child table.

                       

                      It's about construcing a produkt (master table) which has one or more ingredients (child table / Artikel). Each product can be made in six different volumes (in ml), which you can set in the master table.

                       

                      In the child table you add all ingredients (from table Artikel), enter a value in the percent column, and the six columns labeled "Inhalt" will be automatically calculated, based on the volume set in the master table and the percentage entered for the particular ingredients.

                       

                      Some additional things will happen on the layout which i think are not interested in this case.

                       

                      One a product with its ingredients is configured, all is correct. When changing the percentage of a ingredient, the six columns are recalculated.

                       

                      Now the problem: When you change the volume above one of the six columns, which is a value in the master table, the corresponding column (or even all) will not recalculate, which makes the whole picture to be wrong. The recalculation is only forced when you change the percentage of a ingredient.

                       

                      Hope this is not too complicated.

                       

                      Regards

                      Daniel

                      • 8. Re: Enter and update field values by calculation
                        DanielLG

                        I got it. When i change the field type of the six columns to caluclation, then it works. I had them set to number with a autoenter calculation before. Since i changed that it works also when changing the volume in the master table.

                         

                        Thanks so much for your help

                        Daniel