12 Replies Latest reply on Sep 3, 2015 8:23 AM by tays01s

    Reset field and update evaluation

    tays01s

      I have 3 tables: Parent, Calc and Equations, P, C, E.

      E contains a text field (eventually >1) where each record has a different equations.

      C is related to E [ C::_fkE > E::__pk ] and a calc field = evaluate(E::field1).

      P is related to C [ P::__ID < C::_fkP ].

       

      3 problems:

      1. I have considered using C to simply calculate E equations based on whatever is the current P record, ie. no permanently stored Calc records because the data would be pulled up by whatever P record is current. Bad idea?

       

      2. To update Calc::_fkP I have tried using a calc field [ = P::__ID ] or a number field set by a script:

      Go to Layout [ “Calc” (Calc) ]

      Loop

      Go to Record/Request/Page [ First ]

      Set Field [ Calc::_fkP; P::__ID ]

      Go to Record/Request/Page [ Calc::__ID + 1 ]
      Exit Loop If [ Calc::__ID= 50 ]

      End Loop
      Commit Records/Requests
      [ No dialog ]


      Neither resets the Calc::_fkP record number. How might I do this?


      3. Evaluations: These appear to calculate correctly. However, if I update fields used within the calculations, the calc only updates if I click directly into the Calc layout cell in which the evaluation is done. How do I automatically update these evaluations?

        • 1. Re: Reset field and update evaluation
          wimdecorte

          I must admit that I am not following what you are trying to do, perhaps you've abstracted your explanation a bit too much.

           

          But there is a big logical flaw in your script.  The "go to record first" needs to be outside the loop otherwise you're always just updating the first record.

          • 2. Re: Reset field and update evaluation
            Fred(CH)

            1. After 5 readings, still obscure to me... Is this structure correct :

            Parent --< Calc >-- Equation

            Where Calc is acting as a join table allowing a N to N relationship between Parent and Equation ?

             

             

            2. When performing :

            Set Field [ Calc::_fkP; P::__ID ]

            Since the context is Calc, it cannot change the existing value of Calc::_fkP; However, it could force FileMaker to reprocess the lookups and refresh stored calculations on Calc, referring related fields, based upon a relation that already exists.

            Is this the goal here ? If i understood correctly it is not what you expect when you say "Neither resets the Calc::_fkP record number"

             

             

            3. I assume you have got FileMaker Pro Advanced and you are able to check your formulas using Data Viewer. Since results are correct there but not on the layout until you clic on the field you have maybe to refresh the object with the according script step (or first, test the menu command Records > Refresh window).

            • 3. Re: Reset field and update evaluation
              tays01s

              Thanks both,

              Is the below any clearer?

               

              I've corrected the loop error.

              1. Structure: Yes, Parent --< Calc >-- Equation, but as I mentioned, I'd considered not generating new Calc records, simply resetting the Calc::_fpP to = P::__ID and then refresh stored calculations on Calc, referring related fields, based upon a relation that already exists.

               

              2. Set Field [ Calc::_fkP; P::__ID ]: How would I achieve this re-setting?

               

              3. Updating calcs: Yes, I have FMP Advanced and Data viewer shows that as soon as I manually reset the Calc:_fkP the calc updates in the viewer but not the record until I actually click into the cell.

              • 4. Re: Reset field and update evaluation
                wimdecorte

                tays01s wrote:

                 

                1. Structure: Yes, Parent --< Calc >-- Equation, but as I mentioned, I'd considered not generating new Calc records

                 

                From an architectural point of view that seems wrong.   Either a calc is a true child of the parent or it is not.  If it is you should create proper children for a parent.  If it is not, then why have the calc table in the first place?

                 

                As to when what refreshes, read up on this: http://filemakerhacks.com/2014/08/08/shaking-the-dependency-tree/

                • 5. Re: Reset field and update evaluation
                  Fred(CH)

                  If i get it correctly, you may have to capture first the value of P::__ID in a variable before changing the context. Assuming you start from Parent table :

                   

                  Set Variable [ $p__id ; P::__ID ]

                  Go to Layout [ “Calc” (Calc) ]

                  ...

                  Set Field [ Calc::_fkP; $p__id]

                  • 6. Re: Reset field and update evaluation
                    tays01s

                    Fred, of course, yes, that sorts the script and correctly resets the ID.

                     

                    Wimdecorte: When you say, 'true child' you mean create new Calc records for a specific parent? I'd was avoiding this if possible because I only need to view the answers, all the necessary data to re-calc is recorded on other tables, so easy to access and would keep file size down......though I can see that some might want to export the results.

                     

                    Was this what you meant?

                    • 7. Re: Reset field and update evaluation
                      wimdecorte

                      Why is file size a concern?  Would you have millions and millions of calc records?

                       

                      To me your structure does not seem right.  Without knowing where this fits into the purpose of the solution and have some specifics I can 't really put my finger on it.  But the fact that you want to re-use calc records for different parents does not seem like the best way to do this.

                       

                      And why do these need to be calculated fields?  Performance could be horrible if you do have so many records that you are concerned about file size.  A scripted workflow that sets the result in a field instead of having calculation fields may be a better approach.

                      • 8. Re: Reset field and update evaluation
                        tays01s

                        Currently, in Calc there would be 6 calcs fields and per Parent record each would need Evaluating for 50 records. These records would be displayed in a portal on the parent.

                         

                        Were you thinking an Autoenter calc intead? The problem would be that there are field changing on the Parent that would require updating in the Calc.

                        • 9. Re: Reset field and update evaluation
                          wimdecorte

                          tays01s wrote:

                          Were you thinking an Autoenter calc intead? The problem would be that there are field changing on the Parent that would require updating in the Calc.

                           

                          No, I was thinking of a scripted workflow, no calcs or auto-enter calcs.  Since it is scripted you have full control over what you update where instead of having to rely on FM's dependency tree.

                          • 10. Re: Reset field and update evaluation
                            tays01s

                            That would presumably mean having the scrip triggered whenever I commit 1 of several fields. I agree it would give control, but wouldn't that in itself but a burden on performance and complicated to ensure I'd covered everything that needed updating?

                            • 11. Re: Reset field and update evaluation
                              wimdecorte

                              You choose when to start the scripted workflow, whatever works: after each field update or only once on record commit,...

                               

                              Why do you think that would be a burden on performance?

                               

                              Complicated: no more than making sure that you have everything covered in calculations like you have now.

                               

                              Also: you have not answered my question yet on why file size is a concern?

                              • 12. Re: Reset field and update evaluation
                                tays01s

                                Wimdecorte: Brilliant !

                                I've updated my script (crucial part below) and the Calc layout and portal update and synch with the Parent nicely.

                                 

                                Just 1 last query. I've put a record number 1> my last record to ensure all records are dealt with. However, there's always a chance the number of records requiring update will increase/decrease. Is there a more robust method that will future proof the script?

                                 

                                Set Variable [ $nutcalc_ioID; Value:Patient::IO_n ]

                                 

                                Go to Layout [ “Nut_calcs” (Nut_calcs) ]

                                Go to Record/Request/Page [ First ]

                                Loop

                                Set Field [ Nut_calcs::_IDIO; $nutcalc_ioID ]

                                Set Field [ Nut_calcs::Rx; Evaluate(Nut_eqs::Rx) ]

                                Go to Record/Request/Page [ Next ]
                                Exit Loop If [ Nut_calcs::__IDnuts=3 ]