14 Replies Latest reply on Nov 12, 2016 4:17 PM by fmpdude

    Do I need Evaluate?

    fmpdude

      I have a portal with a text field and a radio button field. If I click the radio button then the text in the corresponding portal row text field should then appear on the layout in a "NOTES" field.

       

      To do this programmatically, I was using INSERT CALCULATED RESULT using the text from a portal. The problem was INSERT CALCULATED RESULT was always inserting the first row of the portal even though the data viewer/debugger showed the correct portal row value. Confusing.

       

      My workaround was to create a variable before the INSERT CALCULATED RESULT and then use that variable in the formula.

       

      Since that variable idea worked fine, I'm wondering why referencing the portal field directly didn't work and if maybe this is a case where you need to use EVALUATE inside the INSERT CALCULATED RESULT so I get the current/correct portal value?

       

      TIA

        • 1. Re: Do I need Evaluate?
          philipHPG

          When using fields in a portal and fields outside of the portal, understanding the context is essential.

           

          If your script is initiated from a button or field (ie. script trigger) that is fully contained within the portal, then the context will be that portal row. However, if the script is initiated from the Notes field or some other button or field that is not fully contained in the portal, then references to the related table (upon which the portal is based) will always reference the first row.

           

          Evaluate will not help you here, but understanding the context will. If you're still having problems, give us more information about how your script is triggered and perhaps a screenshot of the layout (in design mode).

           

          Also, you might look at using Set Field instead of Insert Calculated Result.

          1 of 1 people found this helpful
          • 2. Re: Do I need Evaluate?
            erolst

            fmpdude wrote:

            [ ... ] maybe this is a case where you need to use EVALUATE inside the INSERT CALCULATED RESULT so I get the current/correct portal value?

            Well, nothing in life is really certain, but let me put it this way: no.

             

            If you do this correctly, you don't even need a workaround.

             

            If a script is triggered while you're in a portal row, then that portal row is your context. If the referenced portal row is always the first one, chances are that your script has a Commit Record so you lose focus.

             

            If that's not the case, post your script or your file.

            1 of 1 people found this helpful
            • 3. Re: Do I need Evaluate?
              justinc

              I concur with PhilipHPG and Erolst - it's all about the context.

               

              I can't say that I have used it much, but I recall that 'insert calculated result' requires that you already be in the target field...and that, definitely, thus the target field needs to be on the current layout.  If this target field is in the parent record, then you are out of the context of the portal and the first related record is what will be returned when you run your calculation.  You could just do a Set field to the parent record from the context of the portal, reading the field from the current portal row.

               

              Why are you copying this into a field?  Does the user need to be able to edit this information?  Is it just temporary? Does it get copied to the parent permanently?  Depending on the answers to these, you could use a global field or a global variable to display the Notes that were selected.

              • 4. Re: Do I need Evaluate?
                fmpdude

                I'm copying in some error information into a field, dynamically that will get emailed to a client.

                 

                Setting the variable does work fine.

                 

                The context is the main layout (one side, not the portal side).

                 

                A custom dialog right before the INSERT CALCULATED RESULT always shows the correct portal row/field when referenced.

                 

                Yet, the EXACT same portal row/field is not correct with the INSERT CALCULATED RESULT,

                 

                Similarly, as I said above, in the debugger/data viewer (aka Calculation checker), the portal row's field is also correct. It's only in the context of the INSERT CALCULATED RESULT itself that it's wrong.

                 

                Maybe no simple answer here. That's OK. At least setting the variable ahead of time works.

                 

                Thanks!

                • 5. Re: Do I need Evaluate?
                  justinc

                  Right, because it's the "Insert Calculated Result" step itself that is changing the context.  At least...I assume that you are specifying a field in that script step (otherwise it works on the current field...or if no field, then it does nothing).

                   

                  Try this when you are debugging your script:  put a name on the layout field object that you are inserting the calculated result into.  Put a watch in Data Viewer for "get(activelayoutobjectname)".  The step before the 'Insert...' step, see what the result of this watch is, and then after the 'Insert...' step see what it is.  I would bet that immediately after executing the 'Insert...' step that the activelayoutobjectname is the field - thus, your context has changed.

                   

                  So just set a variable before you leave the portal, as it sounds like you are doing.  Or, just use a 'Set Field' step instead:

                       Set Field [ parent_Table::TargetField ; port_Table::Field123 ]

                   

                  (Assumes "port_table" is the T.O./related table that the portal is based on, and that Field123 is the value of the field from the portal's table.)

                  • 6. Re: Do I need Evaluate?
                    erolst

                    fmpdude wrote:

                    Maybe no simple answer here.

                    That is not correct. Writing values from the current portal row into a local field is usually a trivial matter.

                     

                    See the attached file. But before you open it, read the rest of this post.

                     

                    Two other – and IMO better – solutions come to mind:

                     

                    1. Set a flag in the related record; when it is time to send the mail, collect the text from all flagged related records.

                     

                    2. Use a field in the parent record to collect not the static text from selected related records, but rather their primary keys; when it is time to send the mail, collect the text from all related records whose id is in that list.

                     

                    In both cases you can do this conveniently without an additional relationship by using ExecuteSQL(); also, each method will always give you the current text version, without having to store (and maintain?) the text in two places – namely, the ”original“ within each related record, and the copied version in the parent table. This is, after all, a relational database management system – only copy data if necessary.

                     

                    The attached file demonstrates the second variant. Note that when employing that method, there is a Custom Function that you would need to copy over into your solution, and that you can get rid of the flag field in the child table.

                    2 of 2 people found this helpful
                    • 7. Re: Do I need Evaluate?
                      fmpdude

                      justinc wrote:

                       

                      Right, because it's the "Insert Calculated Result" step itself that is changing the context.

                      Ahhh, that's what's going on. Excellent insight.

                       

                      I could have done this using SQL, but the variable approach seems to be the simplest and probably a bit faster since ExecuteSQL in current FMP versions won't win any speed awards...

                       

                      ** What made this issue more confusing was that the Substitute command works fine. No context change. So, why the INSERT CALCULATED RESULT changes contexts on me (and can't keep things straight and in sync) is mysterious.

                       

                      One of the things that's always confused me about FMP (still) is this: If FMP has a "Relationship" between two tables and it (obviously) is maintaining correct record pointers, why can't it keep values between related tables straight? When copying fields from one table to another, IMHO you shouldn't need to first create variable before you SET LAYOUT to the other table. Why? FMP already has the Relationship. Why do "I" need to create variables before the "context change" when FMP already has the relationship and thus the relationship pointer to the other table???

                       

                      Thanks to all for replies!!!

                      • 8. Re: Do I need Evaluate?
                        erolst

                        fmpdude wrote:

                         

                        One of the things that's always confused me about FMP (still) is this: If FMP has a "Relationship" between two tables and it (obviously) is maintaining correct record pointers, why can't it keep values between related tables straight? When copying fields from one table to another, IMHO you shouldn't need to first create variable before you SET LAYOUT to the other table. Why? FMP already has the Relationship. Why do "I" need to create variables before the "context change" when FMP already has the relationship and thus the relationship pointer to the other table???

                         

                         

                        The most important thing in FileMaker to be aware of is context – and different context means a different look at the world (of your data).

                         

                        Context is determined by the layout, which in turn is based on a table occurrence. Portals are also based on a table occurrence and carve out their own little niche of context.

                         

                        Let's assume a relationship A < B: a record in A has none, one or many related records in B.

                         

                        While you're in the general context of A (somewhere on the layout), the expression B::aField will yield the value of aField in the first related record (as per the sort order for that relationship – which by the way can be set differently for any portal). This works and is true without a portal.

                         

                        Now imagine there is a portal into B. Once you're inside a row of that portal, the expression B::aField will yield the value of aField for the related record that is represented by that portal row, because – for most intents and purposes – you are on that record in that (portal) TO.

                         

                        Since using Insert Calculated Result requires changing the focus to the target field, you need to leave the portal row, and the result expression you use will be evaluated as per the first scenario described above.

                         

                        Whenever you need to store data in one context to use in another context, use a variable. As you've seen, in your case you can avoid using a variable by using Set Field, which does not change the focus to its target field.

                         

                        Hope that helps clearing up this particular confusion.

                        1 of 1 people found this helpful
                        • 9. Re: Do I need Evaluate?
                          fmpdude

                          Yeah, I understand all that. Thanks,


                          I was actually making a larger point: FileMaker should keep track of things better since it already has current record pointers via the established relationship. At the (very) least FM should (behind the scenes) do the same "SET VARIABLE" (or the C++/Obj-C equivalent) FOR ME to keep things in sync.

                           

                          Since, the debugger, the Custom Dialog, and the Calculation Checker (aka, "Data Viewer") ALL showed the correct portal value, there's no reason for FMP to get confused and give me the first value. That may be "how it works", but I would argue it's an area for internal FMP improvement.

                           

                          Appreciate your reply. 

                           

                          Thanks,

                          • 10. Re: Do I need Evaluate?
                            justinc

                            Well, you could argue that FileMaker is doing what you describe - it does retrieve the information that you expected IF your context hasn't changed.  It just happened that the instruction that you were using caused a change in context.  If you used 'Set Field' then it wouldn't have changed context and it would have worked the way you describe.

                             

                            It's also a matter of order-of-operations:  in the case of this instruction there are two operations - it goes to the field defined in the 'Insert Calculated Result' first, and then it evaluates the field reference.  If it had evaluated the field reference first, then it wouldn't have given you a different result.  I'm not exactly sure...but I believe that the 'Set Field' command does exactly that - it evaluates the 'value' expression first and then tries to write that value to the destination.  So to speak.

                            • 11. Re: Do I need Evaluate?
                              fmpdude

                              I would agree if:

                               

                              1. The debugger didn't show the correct portal row

                              2. The custom dialog didn't show the correct portal row

                              3. The Calculation Checker didn't show the correct portal row

                               

                              So, clearly FileMaker KNOWS the portal row. FMP has a pointer to the correct portal row regardless of context. It would HAVE to. So, why dump that chore in my lap to keep things correct? To wit, my changing the context doesn't change those pointers.

                               

                              In any case, I have the answer to my question, just not the one I wanted.

                               

                              Thanks Justin.

                              • 12. Re: Do I need Evaluate?
                                philmodjunk

                                All script steps that start with "insert" as the first word are part of a set of script steps that I consider "Inherently brittle". (This was part of my presentation at digFM at FileMaker Headquarters this week). They are brittle because minor layout changes can interfere with the correct function of a script that uses them.

                                 

                                The insert steps, as pointed out here, change the focus to the specified target field--that's part of what makes them brittle as this can trip script triggers. There is, however, a flexible alternative. Use Set Field instead of calculated result and you will not have any change of focus.

                                1 of 1 people found this helpful
                                • 13. Re: Do I need Evaluate?
                                  erolst

                                  I think you still don't understand.

                                   

                                  If the Data Viewer (not “Calculation Checker”) or a Custom Dialog shows the data from the portal row, that is correct for that context at that time (that pointer). That context is changed the moment the Insert Calculated Result is executed (other pointer):

                                   

                                  If you have related records with values of 3, 7, 9 in a field, then the value of RelatedTable::field is

                                   

                                  Before start of script: 3

                                   

                                  Go To Portal Row [ 2 ] – 7

                                  Show Custom Dialog – 7

                                  Insert Calculated Result [ local field ; RelatedTable::field ] – 3

                                   

                                  because that step switches the context back to the local field.

                                   

                                  I don't see anything illogical or indeterminate here. The only chore in your lap is to know “where” you are, e.g. watch your “scope” – which is true for every programming language.

                                  1 of 1 people found this helpful
                                  • 14. Re: Do I need Evaluate?
                                    fmpdude

                                    Thanks Phil!