4 Replies Latest reply on Apr 13, 2017 12:21 PM by howardh

    unstored calculation not working as desired


      Totals::Total Equipment (unstored calculation; sum of equipment::price)

      equipment::price (lookup of price from "ALL Equipment")

      I am going to be making my portal add records by outside button (removing the ghost line, and making things look overall nicer). I am unsure if my calculations are off somehow, or there is something wrong with the relationship (Which to me, looks fine). The portal, currently ran with "allow creation from relation" has several values with price fields successfully filled (through lookup). It just shows blank.



      (Sorry if it looks messy. I didn't want to create a lot of Bid relations, and stuck to one single instance. I also seperated a lot out to save room on tables, because there is over 100 fields to be used in this project bidding app)

        • 1. Re: unstored calculation not working as desired

          So, in a pure accident, I found the problem - which continues to be a problem. When I add new items through portal, it does not update the calculation (I used the field in a different calculation, with it being added with several other things, and it updated properly then).


          Now when I change price values(by changing the item of the portal slot), the sum calculation field does not update 100% of the time (almost every other changed item it will update)

          • 2. Re: unstored calculation not working as desired

            You need to tell us the exact calculation used, the table in which it is defined and exactly what "context" is specified for it.


            You may just need to commit records after making a change that affects the calculated total, but other issues may also come into play for some methods that you might have used here. (You can test the commit records option by just clicking a blank area of the layout outside of any portal after entering/editing data. If that updates your calculated value correctly, then you can script a commit records from a script trigger to make it automatic.)

            • 3. Re: unstored calculation not working as desired

              If you have a portal with a parent record and related children records,


              and the parent record has a calculated sum field that totals an amount field in the children records,


              and you display the calculated sum field on parent layout,


              and you make changes to the amount fields in the child portal layout,


              and you do NOT commit the changes,




              (1) the calculate sum field will correctly display the results, but


              (2) the calculate sum field itself will NOT update (most of the time) and can not be used in another calculation.


              Regarding (2), you can even see this lack of updating in the data viewer.  I ran into this problem when trying to verify that the portal records net out to zero (e.g. debits = credits) before committing the record.


              So far, I found the only reliable way to update the field is to commit the records every time a portal record is updated, but that defeats the attempt to use a "transactional" method in updating multiple related records.


              I have read some suggestions in writing a script to calculate the total of the records in the portal, by looping through the records.  I also read it may be something to do with triggering the calculation engine.   Regardless, there is a bifurcation between what is displayed and what is stored, and I do not know of a method to directly access the amount that is displayed.



              • 4. Re: unstored calculation not working as desired

                It is likely the lookups that are the problem.

                If a price is changed and you do not tell the database to relookup (is a script step) for the total then it will still show the old data.


                Also lookups have other issues.


                Also you will find it slower for calculating numerous price totals each from their own table occurrence.


                It looks like you want to show a bid (bid table occurrence) using the totals and descriptions from the table occurrences to its right. It is best to make a new table called Bid Header and to use the existing Bid table as a lineitems table.

                The reason for this is because this is a many to many relationship you are building. Therefore you need to have a join table or lineitems table.

                The bid header table will have the main header type data. name, bid number, date, customer it is being submitted to, from information...

                The bid table occurrence will have a record for each of the table occurrences to its right that you want included in your bid.

                To understand this easily, open the FileMaker template called invoices that comes with your program. and use it and see how it works. Then you will likely be able to make your bid db work the same.

                It is important, in my opinion not to take shortcuts and use this method otherwise you will nto always get the exat results. Many complications can happen and errors. But using the method as shown in the invoices template you will get it right every time.


                Hopefully this helps.