5 Replies Latest reply on Apr 6, 2012 2:39 PM by philmodjunk

    Using A List in Calculation

    AlexXander

      Title

      Using A List in Calculation

      Post

      I'm trying to find a way to apply addition to a list values in a field.

      Field A have the values listed with the List Function as: 1

                                                                                           2

                                                                                           6

                                                                                           4

      Field B has the value = 12

       

      What I am trying to do it add the "12" from Field B to all of the values in the list in Field A

      So Field C will list: 

      13

      14

      18

      16

       

      If anyone knows of a way to do this and can explain it to me that would be great.

        • 1. Re: Using A List in Calculation
          philmodjunk

          Simplest to understand is to use a script. (This can also be done with a recursive custom function if you have FileMaker Advanced )

          Loop
             Set Variable [$K ; Value: $K + 1 ]
             Exit Loop If [ $K > ValueCount ( yourTable::Field A ) ]
             Set Variable [ $AddList ; value: List ( $AddList ; GetValue ( YourTable::Field A ; $K ) + YourTable::Field B ) ]
          End Loop
          Set field [Yourtable::Field C ; $AddList ]

          • 2. Re: Using A List in Calculation
            AlexXander

            I do have advance, how would this be done using "recursive custom function"

            • 3. Re: Using A List in Calculation
              philmodjunk

              // AddToListItems ( TheList ; Addend )
              //
              // TheList  : A return separated list of values
              // Addend  : The value to be added to each item in the list
              //
              Case ( ValueCount ( TheList ) = 0 ; "" ;
                         let ( [ FirstItem = GetValue ( TheList ; 1 ) ;
                                  ListRemainder = RightValues ( TheList ; valueCount ( TheList ) - 1 )
                                ];
                                List ( FirstItem + Addend ; AddTolistItems ( ListRemainder ; Addend ) )
                              ) // let
                       ) // case

              • 4. Re: Using A List in Calculation
                AlexXander

                Ok now im not quite sure on how I use this. I thought File>Mange>Custom Functions but this is a first for me. 

                • 5. Re: Using A List in Calculation
                  philmodjunk

                  Yes, go to file | Manage | custom functions.

                  Click new. You can paste all the text in my last post directly into the New Function box but then you'll need too further copy and paset AddToListItems into the Function name box. Then you must also separately copy and paste TheList and Addend parameters into the Function Parameters box, clicking the + button after pasting each parameter name.

                  Then you can Click Ok and when you next specify a calculation, you'll find AddToListItems listed with the other functions you can use to construct a calculation.