1 2 3 Previous Next 37 Replies Latest reply on Jul 7, 2017 5:48 PM by user28656

    Aggregate functions oddity (was List() function)

    user28656

      Let(

      aList = List(3;7) ; ValueCount ( aList )

      )

       

      Returns 2, as expected.

       

      But I was surprised that assigning List(3) to aList produced an error message. I was expecting "1". If it was not an integer literal but a field, then ValueCount works as expected.

       

      This complains about a missing field:

       

      Let([

      $x = 3;

      aList = List($x) ];

      aList

      )

       

      So the question is, what is the best way to generate a list of arbitrary values without having to touch the database fields?

       

      FileMaker Pro Advanced 16.0.1.162

        • 1. Re: List() function
          Vaughan

          aList = List( aList ; 3 )

          • 2. Re: List() function
            user28656

            That doesn't work. Complains about a missing table.


            Here's a simplified version:

             

            Let( $x = List(3); $x )

             

            I got the following error:

            Screen Shot 2017-06-20 at 10.53.45.png

            But if I rewrite the expression as:

             

            Let( $x = 3; List($x) )

             

            I get a <Field Missing> result.

             

            Try this in the Data Viewer:

             

            List(3;7) // OK

            List(3) // No output

            List(GetAsNumber(3)) // Same error as above, complains about getting an expression instead of a field

            • 3. Re: List() function
              user19752

              List() is one of aggregate functions, they need field if there is only one parameter.

               

              If it is allowed, List(3) results 3, then there is no need to use the function.

               

              No output on List(3) may be a bug of FM16 data viewer. It claims "you need field there" on other places.

              EvaluationError(Evaluate("List(3)")) is error 1214 (also on FM16)

               

              AVERAGE, COUNT, EXTEND, GETREPETITION, MAX, MIN, NPV, STDEV, SUM, and GETSUMMARY: expression found where a field alone is needed

              This list is missing LIST

              1 of 1 people found this helpful
              • 4. Re: List() function
                user28656

                Seems that this affects all the aggregate functions i.e. If you evaluate Sum(4) or Min(4) or whatever, you will get an 102 or 1214 error, unless the parameter is a field. However, the documentation for Aggregate Functions states that:

                The parameter values can include a numeric constant (for example, 10) or any valid expression.

                The documentation for the List function also states:

                Format

                List(field{;field...})

                 

                Parameters

                field - any related field, repeating field, or set of non-repeating fields; an expression that returns a field, repeating field, or set of non-repeating fields, or a variable.

                Parameters in braces { } are optional.

                Under "About functions":

                Parameters

                Function parameters can be constants (such as 1.07 or "hello"), field references (such as InvoiceTotal), expressions (such as 1 + 12), or other functions (such as the NPV function).

                For me this is extremely inconsistent behavior that should be addressed, but since some of the aggregate functions go all the way back to FM 6 or earlier I don't see how they're going to fix this without seriously breaking a lot of applications. Probably the only feasible workaround is to educate users to be careful when arity is 1 and it has to be a field type and not a literal.

                • 5. Re: Aggregate functions oddity (was List() function)
                  gofmp15

                  Perhaps the issue is that the functions you are testing need two or more values and you are only using one which is kind of weird since you could just use the number without the function. There have been other instances like this but I can't recall the particulars at the present.

                   

                  I had a problem with list ("data" ; "" ; "data1" ) Which resulted in only 2 values rather than 3.  I had to use "*" rather than "" as a parameter.

                  • 6. Re: List() function
                    user19752

                    The sentence in help is confusing (as always), but it says "parameter values" then mean "one of parameters can be constant", I think.

                    And "numeric" shows that is not changed after List() function introduced...

                     

                    Any "Specify calculation" dialog haven't allowed closing it having "List(3)" in calculation, so this is not so problem.

                    1 of 1 people found this helpful
                    • 7. Re: Aggregate functions oddity (was List() function)
                      user28656

                      gofmp15 wrote:

                       

                      Perhaps the issue is that the functions you are testing need two or more values and you are only using one which is kind of weird since you could just use the number without the function.

                      Well, the function works as intended if the single parameter is a field! But this contradicts the implied contract that a function can take expressions, literals, variables and constants etc which is true throughout the whole of FM except for Aggregate functions. And being consistent is very important for any kind of programming framework since people have come to expect the principle of least astonishment. i.e. if you turn the steering wheel left while the vehicle is in motion you expect the vehicle to go left. And not only at certain times of the day!

                      • 8. Re: Aggregate functions oddity (was List() function)
                        user28656

                        First off, thank you for reading and understanding the problem!

                        user19752 wrote:

                         

                        Any "Specify calculation" dialog haven't allowed closing it having "List(3)" in calculation, so this is not so problem.

                        Well, it's cold comfort because how I discovered this issue was through Custom Functions. Custom function fn takes parameter x, returns List(x). Go to Data Viewer and enter fn(3) and the problem rears its head.

                         

                        I'm writing a general recursive function to compare values in a list, which is why I needed the results of ValueCount(List($x)) to determine the termination condition. At least now I know where the problem lies and know how to avoid it.

                        • 9. Re: Aggregate functions oddity (was List() function)
                          philmodjunk

                          Let ( aList = List ( "" ; 3 ) ; aList )

                           

                          Will create a list of one element, 3.

                          • 10. Re: Aggregate functions oddity (was List() function)
                            gofmp15

                            Philosophy: One point that i have stressed with people over the centuries is to test to see what happens and not assume that what you think will happen will happen.

                             

                            Alfred E. Neuman: What you think should happen isn't always what will happen.

                             

                            Me: Test, result, that's the way it works.

                            • 11. Re: Aggregate functions oddity (was List() function)
                              gofmp15

                              As philmod pointed out, adding a null to your List() makes a one element work.

                               

                              So naybe updating your function to include the null will solve the problem.

                               

                              ValueCount (List("";$x)) might solve your problem?

                              or

                              ValueCount ( list($x;""))  <--- this might work best since the null is the last item counted?

                               

                              ValueCount might not count the null but list needs it?

                              • 12. Re: Aggregate functions oddity (was List() function)
                                philmodjunk

                                List automatically omits null elements. Thus, this seems purely a poorly documented syntax wrinkle that the list function needs at least two elements with a work around that we can use a null element to get that list of 1. Definitely a silly requirement and one that needs better documentation judging from the comments here, but once you know, you can work around it quite easily.

                                • 13. Re: Aggregate functions oddity (was List() function)
                                  gofmp15

                                  We need a SuperList() function that notices and keeps the null values... 

                                  I use List() to pass parameters, much easier than the old way of using | and position of |, etc.

                                  If my parameter contains 3 values, I have to remember to use

                                  List("xxx"; "*" ; "fff")  The * must then be accounted for in the script. No big deal of course.

                                  • 14. Re: Aggregate functions oddity (was List() function)
                                    philmodjunk

                                    I use list for that too as it's simpler and easy to set up as "self documenting" for nearly all parameters. But you can now use a JSON as a parameter and there are several other tricks that I sometimes use such as:

                                     

                                    If one parameter might be null, make it the last element in the list. If a parameter might contain text with returns, make it the last element of the list and use RightValues inistead of GetValue to parse the list.

                                     

                                    Send a string that has text such as: "Let [ $Variable = " & table::Value & "; $Variable2 = " & ... and so on" that can then be used with Evaluate to assign values.

                                     

                                    And there are a number of custom functions out there which can also be used--but I'm not a big fan of any that rely on side effects to create variables that are then used in the script unless the developer uses comments to fully document each such variable and the custom function call that creates it.

                                    1 2 3 Previous Next