14 Replies Latest reply on Jul 7, 2009 12:06 PM by ninja

    Automating Recalculation

    ninja

      Title

      Automating Recalculation

      Post

      Howdy all,

       

      Environment:

      PC-XP,SP3

      FMP8 (ie. no script triggers)

      Chance of upgrade to FMP10 for a ~40+ license facility in this economy = 0%

       

      I'm looking for a way to update a calculated field automatically...most do, but I seem to have found one that doesn't.

       

      Batch Table: has batch data including total percent volatile per the spec.

      Ingredients Table: related many to one (many ingredients, one batch).  Has "% of batch" field among others.

       

      On Ingredient, it is either volatile or not.  If so, and if the total starting %volatile doesn't match the %volatile on the spec, the %final in batch is shifted (by calculation) on each ingredient so that %volatile matches the spec.

       

      All of this works perfectly.  Now the problem:

      % volatile is in the Batch Table (parent)

      The calculation to adjust batch% based on volatiles is in the Ingredient table (child)

      The final calc I need is in the batch table (parent) and uses the "revisedbatch%" calculated in the child table.

       

      If I change the starting % of an ingredient (in the child table), everything updates fine automatically.

      If I change the %volatiles on the spec, it doesn't update the child table calc. thus doesn't change the final calc on the parent table.  I figure that this happens since my layout is based on the parent table.

       

      If I go to the next record and back, eveything has updated AOK...how can I get it to update without doing that?

      Right now I have a "recalculate" button that goes to another record and then back.  A definite patch-around.  Is there a way that isn't occurring to me to automate this recalculation?  I don't need a better way to script the "refresh", I'd rather not have to hit the button in the first place...it's too easy to forget...and then the final calc shows wrong data.

      I would use a script trigger...if I had one.

       

      Any ideas?  Did I explain well enough?

        • 1. Re: Automating Recalculation
          comment_1
             I am afraid I don't get it. You say that one batch has many ingredients, and that an ingredient is either volatile or not. I understand how you can calculate the percentage of volatile ingredients in a batch, and compare it to a specification - but after that I lost you.
          • 2. Re: Automating Recalculation
            ninja
              

            Fair enough...let me try sticking to the pertinent facts.

             

            Parent table field has a piece of data (% volatile).

            Child table field has more data (starting % of batch).

            A child table field is calculated using both of these peices of data + others (Final% of batch)

            A parent table field is calculated using Child::Final%ofBatch and other info on parent table (FinalTotal).

             

            Layout is based on Parent Table with portal showing items from child table

             

            If I change Child::starting% of batch, the Child::final%of batch updates AND Parent::FinalTotal updates...AOK

            If I change Parent::%Volatile, Child::final%of batch does NOT update in the portal, therefore Parent::FinalTotal does not update.

             

            If I change Parent%Volatile, then go to another record, then go back, everything updates.  Is there another way to get it to update that doesn't require a user action to be taken?

             

            Was that more clear?

            • 3. Re: Automating Recalculation
              comment_1
                 I don't think I can answer your question without knowing what your calculations are (and even more importantly, understanding their purpose).

              I am particularly bothered by your 'starting % of batch' field in the child table. Anything 'of batch' is an attribute of the parent record - so why is it in the child table?
              • 4. Re: Automating Recalculation
                philmodjunk
                  

                Just to make sure the facts are clear:

                 

                These are all calculation fields, none are data fields with an auto-entered calculation?

                • 5. Re: Automating Recalculation
                  ninja
                    

                  OK, example time...(please forgive the metaphors, they're annoying but I can't share any more specific...)

                   

                  Parent Table:

                  Product = RootBeerFloat

                  %IceCream = 35% (from spec)

                  SumofIcecreams = Sum(Ingredients checked as being icecreams)

                   

                  Child Table:

                  Record#1:

                  Product=RootBeerFloat

                  Ingredient1 = RootBeer

                  Ingredient1type = NotIceCream

                  Ingredient1% = 60

                   

                  Record#2:

                  Product=RootBeerFloat

                  Ingredient2 = IceCream

                  Ingredient2type = IceCream

                  Ingredient2% = 40

                   

                  Since I can add more rootbeer easily, but adding more ice cream makes the product different (I want one big pretty scoop), I'll make it higher in icecream at first, then top off with rootbeer until I'm in spec.

                   

                  ChildTable::AdjustedIngredient=

                  If (IngredientType="IceCream"; 

                  (Child::Ingredient% * ( (Parent::%IceCream)/(Parent::SumOfIceCreams) ) );

                  (Child::Ingredient% * ( (100-Parent::%IceCream)/(100-Parent::SumOfIceCreams) ) )

                  )

                   

                  This gives me icecream adjusted to spec level, balance made up by increasing "non-icecreams" to equal 100%.

                   

                  When I change Parent::%IceCream (from spec), the child calculations of Child::AdjustedIngredient do not recalculate.  If they did, all the rest that I need would be OK too.

                   

                  Is that clearer, or did I just make you thirsty?

                   

                  Phil, they are unstored calculation fields, no autoenters going on.  Apparently FMP does not consider the child calculations to "need recalculating" from the viewpoint of a layout based on the parent table when the change in data (%icecream) is also on the parent table.

                   

                  Edit: Note that the real Dbase fields do not have "%" symbols...that isn't the cause...

                  • 6. Re: Automating Recalculation
                    comment_1
                      

                    Not really clear, because if these are batch (production) records, then I would expect users to enter QUANTITIES, not PERCENTAGES.


                    Anyway, you didn't specify how:

                    SumofIcecreams = Sum(Ingredients checked as being icecreams)

                    is being produced, and I suspect that may be the source of your refresh issues. If you define a calculation field in the child =

                    Case ( (IngredientType = "IceCream" ; IngredientPercentage )

                    and sum this field in the parent record, I believe you will see instant refresh.





                    • 7. Re: Automating Recalculation
                      ninja
                        

                      Good thought, but it didn't work.

                       

                      I had a second Child TO joined with the parent table, (ID-ID) and (Global="Icecream" - Ingredient type) and summed the ingredient percentages through this "relationship filter".  Yours does the same without the other TO so I think I'll keep yours anyway...thanks.

                       

                      But your method does not refresh the calculation either...see below.

                       

                      Your post above references production records...I can see now why you were concerned.  These are not production records, but a shop management predictive tool.  We already use this method (have for decades) and it works very well...but they are all done on paper and take way too long...that must end.

                       

                      I dug deeper in and posted every calculation field along the way (yes, I should have done that before posting in the first place...sorry):

                       

                      The Child calculations refresh by either method (2nd TO or Case).  It's the Sum in the parent table that isn't refreshing.

                      The adjusted ingredient % are used to calculate % exposure limits per item in the child table...this works fine.  When the % exposure limit per ingredient is 'Summed' in the parent table...that is the field that isn't refreshing.  I learned that anyway!

                       

                      I'll keep plugging away.  Your input is valued.

                      • 8. Re: Automating Recalculation
                        comment_1
                          

                        Ninja wrote:

                        It's the Sum in the parent table that isn't refreshing.


                        This just gets more and more obscure. What is this sum summing? You never said anything about it.


                        • 9. Re: Automating Recalculation
                          ninja
                            

                          Howdy,

                           

                          I'm trying to explain it out linearly...lets try it again...

                           

                          Parent has spec % icecream

                          Child has starting ingredient %'s

                          Child adjusts starting ingredient %'s based on parent spec % icecream

                          Child calculates exposure limit % per ingredient based on child adjusted ingredient %

                          Parent sums total "adjusted ingredients" from child (always = 100% as it should)

                          Parent sums total of ingredinet exposure limit % from child.

                           

                          When Spec % icecream is changed on the parent table:

                          Child starting ingredient % stays the same (user entry, of course it would stay)

                          child adjusted ingredient % based on parent spec % icecream refreshes (good)

                          Child exposure limit % per item based on child adjusted ingredient refreshes (good)

                          Parent total adjusted ingredients from child is still 100% (can't tell if refreshed or not)

                          Parent total exposure limit % does not refresh (the problem)

                           

                          Change to previous record then come back, all is refreshed.

                           

                          Is that less obscure?

                          • 10. Re: Automating Recalculation
                            philmodjunk
                              

                            I've placed buttons beside such problem fields (or set the field itself up as a button) and used a script to repeat the manual steps that reliably trigger the recalculation. Then, at least, I could trigger an update by clicking something.

                             

                            Things to experiment with in your script:

                             

                            Refresh window

                             

                            Enter layout mode, enter browse mode

                             

                            Go to Layout (child)

                            Go to Layout (original

                            • 11. Re: Automating Recalculation
                              comment_1
                                

                              Ninja wrote:

                              ...

                              Child calculates exposure limit % per ingredient based on child adjusted ingredient %


                              And where are the details of this calculation? I am getting rather tired of this cross-examination, so here's a suggestion: try changing the calculation in the Parent that "sums total of ingredinet exposure limit % from child" to =

                               

                              Let ( trigger =  spec field ; Sum ( Child:: exposure limit calc ) )

                               

                              If that doesn't work, find a way to post a demo file.


                              • 12. Re: Automating Recalculation
                                ninja
                                  

                                comment wrote: I am getting rather tired of this cross-examination,

                                Yep, me too.  Sorry I wasn't able to describe it well enough for you.

                                 

                                Phil, I already have a script-launch button next to the field that simply goes to another record and back.  That reliably refreshes everything.  It is the need for a manual button click that I'm trying to do away with if possible.  Thanks for the assist though.

                                 

                                Enjoy the day.

                                • 13. Re: Automating Recalculation
                                  philmodjunk
                                    

                                  I entirely agree that redefining your calculation so that the refresh issue is eliminated is the best way to go. I've just had a few cases in older versions of filemaker where that wasn't always possible (or I just wasn't creative enough to figure one out :smileywink: ).

                                   

                                  Instead of a button, perhaps you could use a script trigger so that the data entry action triggers the refresh?

                                  • 14. Re: Automating Recalculation
                                    ninja
                                      

                                    I agree Phil,

                                     

                                    This is actually the first time I've gone reaching for a script trigger.  They sound pretty cool, but I've never really needed one for my apps...until now apparently...and only because I haven't figured out how to set up the relationships/FieldLocations/Calculations/Portal/Whatever in FMP8's "refresh/recalc" sweet spot.  I'll figure it out, I was just hoping for a shortcut from the folks in the field.

                                     

                                    It seems weird that a calculation field that is on the layout wouldn't refresh on field modification, but I suppose that's a balance in FMI's programming between utility and speed.  You wouldn't want every calc to recalc every time you touched something, it would take forever.

                                     

                                    The chances of upgrading to FMP10 facility-wide, after just dropping the cash for 8 not very long ago, is somewhere around the chances of winning the lottery.  We still even have extra copies of 8 for new hires so we don't end up with multiple versions running.