8 Replies Latest reply on Jun 4, 2017 9:24 AM by philmodjunk

    Set Repeating Field Values from List

    MikeWood

      I need to populate a repeating field with unique values from another field on a related table, with each value being its own repetition.

       

      I tried:

       

      Set Field [Table1::RepeatField ; List ( Table2::Data) ]

      And that pulls all of the items from Table2::Data and puts them all into the first repetition of Table1::RepeatField.

       

      I'd like to know how to:

      1) Return only unique values from Table2::Data

      2) Put each unique value into its own repetition in Table1::RepeatField rather than all into the first one.

        • 1. Re: Set Repeating Field Values from List
          erolst

          1. If you've FM16, use

           

          Set Variable [ $dataList ; UniqueValues ( List ( Table2::data ) ) ]

           

          otherwise

           

          Set Variable [ $dataList ; ExecuteSQL ( " SELECT DISTINCT ( Data ) FROM Table2 " ; "" ; "" ) ]

           

          2. Try

           

          Set Variable [ $dataCount ; ValueCount ( $dataList ) ]

          Loop

            Exit Loop if [ Let ( $i = $i+ 1 ; $i > $dataCount ) ]

            Set Field [ Target field: Table1::RepeatField ; Repetition: $i ; Value: GetValue ( $dataList ; $i ) ]

          End Loop

          • 2. Re: Set Repeating Field Values from List
            TomHays

            If you want to use defined Calculation fields and no scripting...

             

            Define two Calculation fields fields

             

            (1) DataList =

            UniqueValues( List( Table2::data) )

             

            In its calculation settings, make DataList return Text.  Use a custom function or a plug-in function for UniqueValues() if you are not using FileMaker Pro 16.

             

            (2) UniqueRepetitions =

            GetValue( Extend(DataList); Get(CalculationRepetitionNumber) )

             

            In its calculation settings, make UniqueRepetitions return an appropriate number of repetitions.

             

             

            Note that I was unsuccessful in my attempts to generate the desired output using only one calculation and avoid needing the second defined Calculation field.  I'd be interested to see if it can be done.

             

            -Tom

            • 3. Re: Set Repeating Field Values from List
              erolst

              TomHays wrote:

              Note that I was unsuccessful in my attempts to generate the desired output using only one calculation and avoid needing the second defined Calculation field. I'd be interested to see if it can be done.

              Dont use Extend().

              2 of 2 people found this helpful
              • 4. Re: Set Repeating Field Values from List
                TomHays

                Thank you erolst, that did the trick.

                 

                UniqueRepetitions =

                GetValue( UniqueValues(List(Table2::Data1)); Get(CalculationRepetitionNumber) )

                 

                 

                 

                I thought I tested that previously, but apparently not.

                 

                -Tom

                • 5. Re: Set Repeating Field Values from List
                  TomHays

                  I forgot to add the standard admonition.

                  "Don't use repetitions."

                   

                  Given that your related table can have an arbitrarily high number of unique values and your calculation is defined to have a specific number of repetitions, the technique you describe breaks simply by adding records to a table.

                   

                  "[since FileMaker went relational in FileMaker 3] repeating fields have been used as workarounds for things other than relationships (by experienced developers) and sometimes used instead of relationships (by inexperienced developers)" -- Dwayne Wright

                   

                   

                  -Tom

                  • 6. Re: Set Repeating Field Values from List
                    philmodjunk

                    Use a custom function or a plug-in function for UniqueValues() if you are not using FileMaker Pro 16.

                     

                    There's no need for a CF or plug in for that. both the ValueListItems function and ExecuteSQL can be used to get a list of unique values. ValueListItems works if you define a conditional value list based on the same relationship you would use for List ( RelatedTable::Field ), but unlike list, only unique values are returned. (Used that trick to get unique values long before we had the other options in FileMaker....)

                    • 7. Re: Set Repeating Field Values from List
                      TomHays

                      philmodjunk wrote:

                       

                      Use a custom function or a plug-in function for UniqueValues() if you are not using FileMaker Pro 16.

                       

                      There's no need for a CF or plug in for that.

                       

                      Yes, indeed there are alternate ways to generate unique values.  But to get my expression to work as written, one needs a functioning UniqueValues().  FM16 provides that natively.  If you are not using FM16, another way of providing that function is needed.

                       

                      That custom function may use one of the techniques you described.

                       

                      -Tom

                      • 8. Re: Set Repeating Field Values from List
                        philmodjunk

                        True, but creating the CF requires advanced. The expression to get a unique values list in Pre 16 versions is pretty simple to set up, so I have provided an alternative for those that need it.