1 2 3 Previous Next 69 Replies Latest reply on Jan 25, 2015 1:33 PM by keywords

    learn to do more with Let

    robear

      Is anyone familiar with a repository of LET statements (i.e. Brian Dunning's site: Custom Functions) ?

       

      Alternately, any good Training material for developing Let statements is appreciated!

       

      rl

        • 1. Re: learn to do more with Let
          mrwatson-gbs

          I try to format my let statements in the same way I would write a custom function or even a script: with parameters, with settings, with a calculation part and a result.

          Let([

           

          /* Parameters */

           

          pText = Trim( Substitute( MyTable::InputField ; "¶" ; "" ));

          pItemNr = GetAsNumber( MyTable::Item ) ;

           

          /* Settings */

           

          vDelimiter = "|" ;

           

          /* Vars */

           

          vTextWithDelimiters = vDelimiter & pText & vDelimiter;

          vPos1 = Position( vTextWithDelimiters ; vDelimiter ; 1 ; pItemNr );

          vPos2 = Position( vTextWithDelimiters ; vDelimiter ; vPos1 ; 2 );

          vResult = Middle( vTextWithDelimiters ; vPos1 + 1 ; vPos2 - vPos1 - 1 )

           

          ]; /* Result */

           

          Case( vPos1 and vPos2 ; vResult )

           

          )

          I use the prefix p for parameters and the prefix v for variables. This makes it MUCH easier to distinguish between references to inputs, variables and fields in the main part of the calculation AND avoids clashing with reserved words (vDay, vYear, vDate...).

           

          For the sake of readability, I also tend to calculate too much - I mean more than is actually necessary. In the above example, for example, it is not necessary to calculate vPos2 or vResult if vPos1 is zero, but if you start embedding case statements in let statements in case statements you soon can't understand what is going on at all.

           

          If you wish to optimise the code you could avoid unnecessary calculations using an "inline case" in the following manner:

          Let([

           

          /* Parameters */

           

          pText = Trim( Substitute( MyTable::InputField ; "¶" ; "" ));

          pItemNr = GetAsNumber( MyTable::Item ) ;

           

          /* Settings */

           

          vDelimiter = "|" ;

           

          /* Vars */

           

          vTextWithDelimiters = vDelimiter & pText & vDelimiter;

          vPos1 = Position( vTextWithDelimiters ; vDelimiter ; 1 ; pItemNr );

          vPos2 = Case( vPos1 ; Position( vTextWithDelimiters ; vDelimiter ; vPos1 ; 2 ) )

           

          ]; /* Result */

           

          Case( vPos2 ; Middle( vTextWithDelimiters ; vPos1 + 1 ; vPos2 - vPos1 - 1 ) )

           

          )

          I tend to use shorthand when testing numbers/booleans:

          Case( vPos1 ; ... )

          instead of the longer

          Case( vPos1 > 0 ; ... )

           

          but that is just a question of taste.

           

          I also tend not to indent the contents of the Let statement, because so long as you space the formula vertically using ¶¶ you can read it clearly without indents - just like the equivalent script.

           

          If you need to debug your Let statement then just add lines to set $$-variables or a $$log:

           

          Let([

           

          /* Parameters */

           

          pText = Trim( Substitute( MyTable::InputField ; "¶" ; "" ));

          pItemNr = GetAsNumber( MyTable::Item ) ;

           

          /* Settings */

           

          vDelimiter = "|" ;

           

          /* Vars */

           

          vTextWithDelimiters = vDelimiter & pText & vDelimiter;

          vPos1 = Position( vTextWithDelimiters ; vDelimiter ; 1 ; pItemNr );

          $$Pos1 = vPos1 ; //DEBUG

          vPos2 = Case( vPos1 ; Position( vTextWithDelimiters ; vDelimiter ; vPos1 ; 2 ) ) // NO semicolon here

           

          ]; /* Result */

           

          Case( vPos2 ; Middle( vTextWithDelimiters ; vPos1 + 1 ; vPos2 - vPos1 - 1 ) )

           

          )

           

          ...and finally remember to leave out the semicolon after the last variable...:-)

          1 of 1 people found this helpful
          • 2. Re: learn to do more with Let
            beverly

            I like what mrwatson-gbs did with the commenting (as I do!)

            Here's a hint that helped me remember to write Let() correctly:

             

            SINGLE STATEMENT AND RESULT:

            Let (

            one_statement

            ; return_calc

            )

             

            MULTIPLE STATEMENTS AND RESULT:

            Let (

            [ first_statement

            ; second_statement

            ; third_statement

            ] ; return_calc

            )

             

             

            with the ";" at the beginning of the line, I never missed them. However placement of comments may be more difficult.

             

            Beverly

            • 3. Re: learn to do more with Let
              DrewTenenholz

              rebear --

               

              Like Beverly, I put my semicolon for the 'next' line in front of that line.  I use the multi-item Let() almost exclusively, because I always include a line for the final calculation within the multiple items, like this:

               

              Let ( [

              a= some calc // and some comment

              ; b= some calc // and another comment

              ; c= same calc // and another comment

               

              ; result= If (a > 2 ; b ; c )

              ];

              result

              ) // end Let

               

               

              Now, I can drop this same statement into the data viewer and begin figuring out where I went wrong.  I can throw a // in front of any line to remove it from the calculation, and I can easily replace the final 'result' with any of the intermediate values to see what is being calculated for any one of them.  It is usually pretty easy to then copy/paste the updated statement into the place where it needs to live within the database.

               

              Unlike mrwatson, I will occasionally use indenting, especially if I use a Case() or complex If() in the definition of a calculation variable.  Like him, I also use the /* */  comment format when trying to describe what a certain section is doing.

               

              I've gotten as long as a hundred or so lines in a Let(), that was mostly where I needed to gather information about a patient (e.g. a medicine they are taking that includes a product name, well-known drug database ID, dosage , when they started taking it, etc. etc) and put in into a rather complex XML format where the text result of the calculation for a single drug would be 15-20 lines of text.  Using the method above means that I can quickly and easily tweak the calculation as the need arises because I can see again what I was thinking and how I got there.

               

              If I need to use a script or global variable within a Let(), I'm more likely to actually declare it within my calculation variables:

               

              Let ( [

              a= some calc

              ; scriptVar1= $scriptVar1

               

              ; result= If ( scriptVar1= "yes" ; a ; 0 )

              ];

              result

              ) // end Let

               

              I know this is a bit redundant, but if I ever use that $scriptVar even more than once, I've saved a few cycles, and, even more importantly, I see early on that this result depends on $scrioptVar1, which might not be set properly.  That saves me a few cycles figuring out what went wrong.

               

               

               

              -- Drew Tenenholz

              1 of 1 people found this helpful
              • 4. Re: learn to do more with Let
                jormond

                One good place to look at to get some good ideas is filemakerstandards.org.  Matt Petrowsky and some other contributors have bee working on creating some 'standards' that can help the community and create some uniformity within our coding.  There is a lot there...one place I know talks about Let statements is below.  There are also some examples in a sample file he and Jeremy Bante put together.

                 

                http://filemakerstandards.org/display/cs/Calculation+Variables+-+Let%28%29+function

                1 of 1 people found this helpful
                • 5. Re: learn to do more with Let
                  robear

                  Thank you MrWatson, Drew, Beverly & Josh for your tips and suggestions.

                   

                  Having reviewed your posts carefully, the first question that comes to mind is pre-planning.

                  In data modelling, Strategic planning may include developing Entities & Attributes while Tools may include a Spread Sheet or Graph paper for the ERD...

                   

                  What strategies or tools (if any) are useful in the planning process for efficiently building Let statements?

                   

                  robear

                  • 6. Re: learn to do more with Let
                    ibrahim_bittar

                    My take is like Drew's but with some indenting:

                     

                    Let ( [ Var1 = MyField.ID , //Comment 1

                             Var2 = MyField.TimeStamp , //Comment 2

                             Var3 = MyField.UserName ] , //Comment 3

                     

                    "Record Nº: " & Var1 & "¶" &

                    "Date & Time: " & Var2 & "¶" &

                    "Created By: " & Var3 )

                     

                    In this way I can have a clear undestanding of what's going on.

                     

                    One much more complex example of indenting and commenting can be found in this custom function:

                     

                    http://www.briandunning.com/cf/551

                     

                    Hope it helps.

                     

                    Ibrahim.

                    • 7. Re: learn to do more with Let
                      jormond

                      The main strategy/tool I use...keep it DRY (Don't Repeat Yourself).

                       

                      • I you have to use an expression more than once in a calc, it's a canidate for using a caclulation variable via Let.
                      • If you calc is hard to read, it is also a canidate for the Let statement (for example, having to next calcs inside of others can become hard to read).
                      • If you want to set a Local or Global variable through a calculation, without a scipt.  You can do it with let.  Although, there needs to be care with this, since you can declare/change a variable you already have set elsewhere.

                       

                      While I plan things to track the variables I'm using, I just stick to creating them as needed or in specific use cases.  I may use Expression Editor at time to get the syntax highlighting.

                      • 8. Re: learn to do more with Let
                        tech_liaison

                        These are great Let tips! I think this thread is becoming the repository! One of my favorite uses for Let is to reduce the number of Set Variable script steps I use in my scripts. Typically I will define one variable using the Set Variable Name option, and then use a Let statement in the Value calc to initialize the values of the other variables the script will use, and also return the initial value of the variable defined by the Name option.

                         

                        Often I use this strategy to control Loop statements. I define the variable that counts loop iterations in the Set Variable Name option; typically I name this variable, $loop_iterator. Then, in the Value calc I use a Let statement something like this:

                        Let([

                         

                        $max_loops = ValueCount( relationship_name::field_name );     //     the maxium number of related records

                        $cur_rec_pk = __kp_REC_ID     //     the primary key for the current record

                         

                        ];

                         

                        1     //     return the initial value of $loop_iterator

                         

                        )

                         

                        I can then use a similar strategy in the Exit Loop If script step like so:

                        Let([

                         

                        $loop_iterator = $loop_iterator + 1     //     increment the loop iterator

                         

                        ];

                         

                        $loop_iterator > $max_loops     //     return true to exit loop if we have exceeded maximum iterations

                         

                        )

                         

                        So, using Let in this way allows me to manage three different variables but use only one Set Variable script step in the script. Hope you find this useful!

                         

                        Best,

                        Dave

                        • 9. Re: learn to do more with Let
                          sporobolus

                          on 2011-12-12 21:50 robear wrote

                          Is anyone familiar with a repository of LET statements (i.e. Brian Dunning's site: Custom Functions) ?

                           

                          Alternately, any good Training material for developing Let statements is appreciated!

                           

                          it seems this thread is aiming to become such a repository; here are some

                          examples of how i use Let() ...

                           

                          this example ("mapexpr" on briandunning.com) shows:

                          • commenting to define CF parameters

                          • indentation to express structure of Let() as well as internal logic (note

                          that i double-indent the variable section

                          • dynamic composition and evaluation of a Let() expression

                           

                          /*
                          mapexpr (expr, l)
                          
                          apply an expression to every value in a value list
                          
                          expr = an expression string suitable for Evaluate() with let variable _v used 
                          where each value should be referenced
                          l = a valuelist (return separated text items)
                          */
                          Let ([
                               n = ValueCount (l);
                               lambda = "Let (_v =" & Quote (GetValue (l; 1)) & "; " & expr & ")"
                             ];
                             Case (
                               n=0; "";
                               n=1; Evaluate (lambda);
                               Evaluate (lambda) & ¶ & mapexpr (expr; RightValues (l; n-1))
                             )
                          )
                          

                           

                           

                          this next example i have used extensively, but it has also been criticized

                          quite a bit; the description is essential, so i won't copy it into this post;

                          it illustrates:

                          • automatically decomposing a parameter string into local script variables

                          • how a default value can be used if the parameter string omits the variable

                          • testing for failure in an evaluated Let()

                           

                          <http://www.briandunning.com/cf/453>

                           

                           

                          this last example comes from a CF in a system where users navigate among

                          modules (you don't need to understand the purpose in detail); it illustrates:

                          • indentation

                          • manipulation of global variables / usefulness of Let() without returning a value

                          • unfolding short loops with a series of assignments instead of recursing or

                          looping

                          • in my sparing use of global vars, i use $$ALLCAPS names to distinguish them

                          easily

                           

                          (note that there are long lines below which Jive will mess up)

                           

                          /*
                          for improved user experience, reduce cyclical paths in the crumbtrail 
                          represented by the module stack:
                          delete repeated doublets and triplets at the top of the stack
                          no params, no return value, just manipulates $$MODULE_STACK as a side effect
                          */
                          Let ([
                               full_stack = $$MODULE & ¶ & $$MODULE_STACK;   // adjust for current module 
                          not present in stack
                               n = ValueCount (full_stack);
                               full_stack = Case (   // suppress repeated triplets
                                 (n ≥ 6) and Exact (LeftValues (full_stack; 3); MiddleValues (full_stack; 
                          4; 3));
                                   RightValues (full_stack; n - 3);
                                 full_stack
                               );
                               n = ValueCount (full_stack);   // remeasure stack height
                               full_stack = Case (   // suppress repeated doublets
                                 (n ≥ 4) and Exact (LeftValues (full_stack; 2); MiddleValues (full_stack; 
                          3; 2));
                                   RightValues (full_stack; n - 2);
                                 full_stack
                               );
                               n = ValueCount (full_stack);   // remeasure stack height
                               full_stack = Case (   // suppress repeated singlets (such as might occur 
                          after zapping a doublet, or from certain intra-module navigation
                                 (n ≥ 2) and Exact (LeftValues (full_stack; 1); MiddleValues (full_stack; 
                          2; 1));
                                   RightValues (full_stack; n - 1);
                                 full_stack
                               );
                               n = ValueCount (full_stack);   // remeasure stack height
                               $$MODULE_STACK = Case (   // remove current module to normalize stack
                                 n  ≤ 1; "";
                                 RightValues (full_stack; n - 1)
                               )
                             ];
                             ""    // no return value
                          )
                          

                           

                          • 10. Re: learn to do more with Let
                            robear

                            Hi Dave

                             

                            Am finding the growing tips here fascinating having ignored Let () for too long.

                             

                            Perhaps some tips will make it to the FileMaker Training Series, since the FTS is quite obscure on Let () and Custom Functions.

                             

                            robear

                            • 11. Re: learn to do more with Let

                              This is a wonderful list of ideas!! Thank you all for contributing!  I would add two and a half more occasions where we have used Let(). 

                              • If the table::fieldnames referenced in a calculation are very long then we might reassign them to variables just so they are easier to read within the calculations. Some complex solutions have long naming conventions. (this is really your second point, Josh, but I thought another example would be helpful).
                              • If purpose of the table::field is unclear, it is easier to add a // comment to it within Let().  Some solutions or external sources (out of our control), use field names which are illogical and do not provide the purpose nor the data type. 

                               

                              The half:  If I am unclear on how to approach a calculation, I use Let() as a whiteboard, breaking the requirements into steps and writing the calculation for each.  This also easily allows me to take each variable and drop it into the result for testing.  Usually by the time I have finished with the Let(), I have clear vision of how the functions I am viewing can be rearranged or grouped to produce an efficient calculation yet maintain clarity.  Sometimes the Let() disappears; sometimes not.

                               

                              Message was edited by: LaRetta

                              • 12. Re: learn to do more with Let
                                jormond

                                I really like to use the let statement within Conditional Formatting.

                                 

                                I place a single object on the layout, placed to be the first item that "draws".

                                • I include a conditional format so it only shows in Layout Mode ( or more accurately, doesn't show in Browse Mode or Find Mode ).
                                • I include another condition that use Let to set a global variable.  These are primarily User Interface items.  Whenever the screen redraws, it has to "redraw" that object, and run the Conditional Formatting calc and set the variable.
                                • Then I can place Merge Variables on the layout and never use a field for UI-only data display.

                                 

                                This has really become one of my favorite ways to reduce the number of fields and scripts that I'm using.  And I can even throw all the variables into a Custom Function and just copy one object and place it on all layouts.  If I need to change, or add a variable I can either alter the Custom Function or be really slick and use a Variables table and store all my variable names and formulas there.

                                • 13. Re: learn to do more with Let

                                  We too use a lot of merge variables using conditional formatting.  We have begun to place a text object 'I declare variables" (picked up from Michael Horak.aka Comment) at the top of most layouts, sent to the bottom of the stack   We list all our merge variables within this text object; it is handy to have them all together.  We also name it 'dummy' so we can Go To Object [ dummy ] to remove cursor from field without committing. The 'I declare variables' conditional format calculation would look something like:

                                   

                                  Let ( [

                                  $var = value1 ;

                                  $var2 = value2

                                  ] ;

                                  Get ( WindowMode ) < 4

                                  )

                                   

                                  ... then set the conditional format to font size 500 so it disappears except in layout mode (4).  Depending upon fields referenced, it can save a lot of calculations.

                                  • 14. Re: learn to do more with Let
                                    jormond

                                    That is very similar to how I use it.  It's a great tip...from a great source.

                                     

                                    I think the only difference for me is that I don't actually list the variables in the text object.  But that could be very handy.  I was using a custom function included in every calc that adds/removes new variables to global variable with all the variables listed.  And Bruce's Virtual List to show them in a separate window that I leave open all the time.

                                     

                                    Let Statements, Virtual Lists, Custom Functions...oh my!!!

                                    1 2 3 Previous Next