12 Replies Latest reply on Dec 30, 2016 10:18 AM by jfletch

    Let Function Error - "List usage is not allowed in this calculation"

    MorkAfur

      Title

      Let Function Error - "List usage is not allowed in this calculation"

      Post

      Since I couldn't find a way to have FMP 13 easily tell me which week of the month it is (1, 2, 3, or 4), I decided to just create a LET function to do a rough approximation based on the Day of the month (if DayOfMonth ≤ 7, then 1, etc.).

      However, when trying to start the very simplest Let function, I immediately ran into a confusing error. Regardless of which of the two variables below are uncommented, I get the error "List usage is not allowed in this calculation" when I try to save the code -- see screenshot attached.

      The syntax seems straightforward for the Let function so I'm wondering why it's failing.

      Let
      (
        [
           // multiplier = 0;
           dayOfMonth=Day(Get(CurrentDate));
        ];

        // Calculation below to create week multiplier
       // calc here….
      )

      I've also tried creating a simple calculation to return, but that didn't make any difference: same "list" error.

      So, what's going on?

      Thanks in advance for suggestions.

      - m

      image.jpg

        • 1. Re: Let Function Error - "List usage is not allowed in this calculation"
          FentonJones

          It is because the last line (with text, in the List) which is not turned off ends with ; , then has the ];
          The last of this kind of List (ends with ]; ) doesn't need that ; , as it's the one in ];
          The above problem often happens if you're turning on and off lines in a List()

          1 of 1 people found this helpful
          • 2. Re: Let Function Error - "List usage is not allowed in this calculation"
            MorkAfur

            Thanks Fenton.

            I'd also like to have a CASE Statement that lets me set a variable I could use later inside a Let statement. However, the examples I've seen indicate that the CASE statement returns the value arrived at.

            Something like below (in pseudo-code):

            Let

            [

               result = 0;

               someVariable=SomeCalculatedResultBasedOnField;

               Case

                  someVariable > 0; result = 5;

                  someVariable < 10 result = 10

                 result = 20

            ] ;

            // calculation

            result * 12;  // for example

            )       

             

            ----

            I also don't like it that there's no regular IF-ELSE syntax when writing a calculation in a LET statement in particular. Having to nest multiple "immediate IF" statements is cumbersome.

            • 3. Re: Let Function Error - "List usage is not allowed in this calculation"
              philmodjunk

              To use Case inside of Let, do it this way:

              Let ( SomeVariable = Case ( A  = 5 ; first result
                                                        B = 6 ; next result ;
                                                      ) ; // case
                      Expression using SomeVariable goes here
                    ) // Let

              I also don't like it that there's no regular IF-ELSE syntax when writing a calculation in a LET statement in particular. Having to nest multiple "immediate IF" statements is cumbersome.

              Don't do that. Use the Case function instead of nesting If functions.

              • 4. Re: Let Function Error - "List usage is not allowed in this calculation"
                MorkAfur

                Okay, I see. Thanks Phil.

                Excellent reply as always. :)

                - m

                • 5. Re: Let Function Error - "List usage is not allowed in this calculation"
                  philmodjunk

                  Ironically, I accidentally put in a ; after the last ) in my example and that would also trigger a syntax error. I've since edited the above example to remove it.

                  • 6. Re: Let Function Error - "List usage is not allowed in this calculation"
                    MorkAfur

                    I noticed that, but I just looked at your code as pseudo code - which was perfect as is for explaining your point. :)

                    Thanks,

                    - m

                    • 7. Re: Let Function Error - "List usage is not allowed in this calculation"
                      FentonJones

                      The Case function will stop, and return, as soon as it matches one of its tests. So, especially if using ">" tests, it makes more sense to "start at the top and go down".

                      Also, you (usually) only have to use the Variable you're trying to set at the beginning, as the rest is (usually) either using fields or other variables.

                      Let ( [
                         return =
                          Case (
                              table::field > 10; 20;
                              table::field > 5; 10;
                              table::field > 0; 5;
                              0 )
                          ] ;

                      result * 12
                      )

                      RE: If
                      I very (very) seldom use the "If" function, as I find Case to be easier and more useful. I do however use the "If" Script Step [there is no Case script step].

                      P.S. You don't need "table::" with the field if its in the same table you're in. You do not need the [ and ] if you have only one Variable in the Let(). You could however use a 2nd Variable to get the value of the field, instead of having to say it multiple times; may make it a tiny bit faster (I suppose).

                      • 8. Re: Let Function Error - "List usage is not allowed in this calculation"
                        MorkAfur

                        Thanks Fenton.

                        This was my first actual attempted use of the Let function so I missed the (obvious, I guess) idea that you could just assign a variable equal to the Let's result.

                        Appreciate your great replies.

                        Thanks,

                        -m

                        • 9. Re: Let Function Error - "List usage is not allowed in this calculation"
                          philmodjunk

                          Let ( $$Variable = 5 ; 1 )

                          BTW, will assign 5 to a global variable. This expression can be placed inside a Conditional format or Hide Object When Expression and it will assign the specified value to the global variable each time that it evaluates.

                          I use this trick to populate a large list of variables inside an improved (over the iOS version) date picker that I use in my FM GO solutions.

                          • 10. Re: Let Function Error - "List usage is not allowed in this calculation"
                            FentonJones

                            I use Let() quite often, if things are long and complicated. It is often easier to read, especially if there are "relationships" involved, whose full name can be both long and difficult to read, and which you don't really need to know more than when you choose it.

                            I also use Let() when there's a lot of calculation functions involved. Like, get some value, remove some text, do some further with that result, then more, more, etc., until you get the final result.
                            I have several "names" I use often, like "some_list" (a list of whatever related table::field), "some_list_cnt" (that list count), etc. ("some" is just pseudo-name).

                            You can also name the Variable in a Let() with $ (or even $$, as Phil did). The $ will let you SEE even multiple variables which are used within a script step, when viewed by the Data Viewer of FileMaker Pro Advanced; that is, you can see the result of every one you want to, while running a single script step. That will often show you where the problem is [ like "a variable does not even exist!, no wonder the entire result is wrong].

                            P.S. It is important to name your variables as well as you can; but as short as you can. On of the main problems is to use one once, then mistype it when calling again. There is no error to tell you that you've typed it wrong the 2nd time; it's just another variable, as far as FileMaker is concerned.

                            1 of 1 people found this helpful
                            • 11. Re: Let Function Error - "List usage is not allowed in this calculation"
                              MorkAfur

                              Wow, excellent info Fenton!

                              Thanks again for your additional reply and follow-up information. :)

                              - m

                              • 12. Re: Let Function Error - "List usage is not allowed in this calculation"
                                jfletch

                                Good, job, Fenton. +1 on all the above! One further way I describe Let ( ) is as a "mini-script" where you can perform multiple steps, like what you described.

                                 

                                One other tip is to set up templates for some various standard Let ( ) use cases. I have a handful of standard things I do with Let ( ) that I have set up templates for in a text substitution utility. So I can type "let (" and it will setup a basic Let format with the carriage returns and indents how I like them:

                                 

                                     Let ( [

                                            ~%| ;

                                            ~result =

                                     ] ;

                                            ~result

                                     )

                                 

                                (The "%|" is the symbol in TextExpander for "leave the cursor here.")

                                 

                                Typing "letl (" will enter my standard Exit Loop If calculation:

                                 

                                          Let ( $counter = $counter + 1 ; $counter > $%|Count )

                                 

                                 

                                And my favorite time saver is to type "lets (" to get my ExecuteSQL template:

                                 

                                     Let ( [

                                          sqlQuery = "SELECT " & GFN ( %| ; "" ) & "

                                          FROM " & GTN ( <field> ) & "

                                          WHERE " & GFN ( <field> ; "" ) & " = ?" ;

                                          fieldSeparator = "" ;

                                          rowSeparator = "" ;

                                          arg1 = "" ;

                                          result = ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator ; arg1 )

                                     ] ;

                                          result

                                     )

                                 

                                There are times when I can literally write a basic ExecuteSQL calc in less than 20 seconds with this.