7 Replies Latest reply on Apr 21, 2016 1:16 AM by erolst

    Evaluate - Newbie Question

    RichardScales

      Greetings all,

      I have been using Filemaker for many years (V3.xx onwards) but have never come across the Let and Evaluate functions.

      I need to patch up a legacy database for a client (which is why I need to stick with repeating fields for this question) and I would like to copy the contents of certain repeating fields from one table to another.

      There are 24 repetitions and 3 fields.

      I can see that I can use 3 groups of 24 set field statements to achieve this but was wondering if I could use a loop and somehow create the command that I want to execute for each repetition by using the evaluate / let functions.

      Am I even close or shall I stick with the 72 set field statements?

        • 1. Re: Evaluate - Newbie Question
          beverly

          You might search for 'FileMaker Extend function'. It may or may not help, but worth a research.

          beverly

          • 2. Re: Evaluate - Newbie Question
            erolst

            RichardScales wrote:

            create the command that I want to execute for each repetition by using the evaluate / let functions

             

            You could simply use Set Field by Name[], as in

             

            Set Variable [ $i ; 0 ]

            # only necessary if this is part of an outer loop

            Loop

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

              Set Field by Name [ "SomeTable::newIndividualField" & $i ; GetRepetition ( SomeTable::oldRepeatingField ; $i ) ]

            # [ or simpler: SomeTable::oldRepeatingField [ $i ] )

            End Loop

             

            RichardScales wrote:

            There are 24 repetitions and 3 fields. […] I can see that I can use 3 groups of 24 set field statements […] Am I even close or shall I stick with the 72 set field statements?

             

            But if you simply convert m repeatingField[n] into m * n fields, you're not really better off, structure-wise; numbered fields that store variants of the same data type should become individual child records with fields like orderNumber (the original repetition, if you want to keep that information) and value.

             

            You could either do this in a looping script, or even using an Import that creates new records from repeating values.

            • 3. Re: Evaluate - Newbie Question
              RichardScales

              I can see how I can specify the value using GetRepetition with a variable to control the repetition number but am uncertain how to specify the repetition value in the target field using the Set Field by Name function.

               

              I do see how the name of the field can be calculated but not the repetition value. In my case the target is also a repeating field.

              • 4. Re: Evaluate - Newbie Question
                RichardScales

                Hang on, I think I am getting there - calculate the target field name to include '[x]' where x is the repetition no.?

                • 5. Re: Evaluate - Newbie Question
                  erolst

                  RichardScales wrote: do see how the name of the field can be calculated but not the repetition value. In my case the target is also a repeating field.

                   

                  I seriously recommend you consider my thoughts re using repeating fields.

                  • 6. Re: Evaluate - Newbie Question
                    RichardScales

                    I got there!

                    I am using Set Field by Name but had missed the fact that I need to include the field name in quotes - so it was using the contents of that field in the calc rather than literally using that string.

                    I completely get and appreciate the problems associated with repeating fields and the benefits of doing it properly using relation ships and tables etc but in this situation I needed to provide a patch to copy some data around. A re-write was not an option for me.

                    Thank you all for helping me out this morning.

                    • 7. Re: Evaluate - Newbie Question
                      erolst

                      RichardScales wrote:

                       

                      I got there! […]

                      I completely get and appreciate the problems associated with repeating fields […] A re-write was not an option for me.

                       

                      Glad to hear you got it working.

                       

                      I understand that sometimes practical considerations have to trump the ideal way …