4 Replies Latest reply on Aug 3, 2017 1:30 PM by philmodjunk

    Can I use a script to copy a field of a found set to a different database field that is a repeating field? What commands can I use?

    PamelaSnyder

      I want the script to "populate" a repeating field.  I also need it start at the second repetition, not the first. The data being moved is not in a repeating field.  I know repeating fields have some limitations - so I admit I am asking up front.  Thanks!  To be clearer.  these are two separate databases.

        • 2. Re: Can I use a script to copy a field of a found set to a different database field that is a repeating field? What commands can I use?
          philmodjunk

          these are two separate databases.

          That's not as clear as you might think in FileMaker solutions. Some people say that to indicate that the data is in two different tables. Others that it's in two different files. It doesn't make a huge difference here, but it's a detail to keep in mind.

           

          Sounds like if you have a found set of 5 records, you want to move that data into repetitions 2 thru 6 of a field in a different table, probably a table in a different file.

           

          With a relationship and use of a calculation field as your repeating field, you might use ExecuteSQL to extract this data into the repetitions without use of a script at all.

           

          You might also research a method known as a "virtual list"--there are a number of demos on the technique as it might serve for this depending on what your end results beyond just the data in this repeating field might be.

           

          If those don't serve, you can either:

           

          a) Loop through the found set and use either a relationship or a perform script call (with the needed data in a script parameter) to set your repetitions one at a time in the other table. or

           

          b) Loop through the found set to build a return separated list of the data to pass as a parameter to a script in the other file that loops through that list to set repetitions to the elements of that list in the script parameter. (Could be a JSON as parameter in FMP 16, BTW.)

           

          c) Use a "list of" summary field to capture a list of the values from the found set and then loop through that list to set the values in the other field.

           

          Note that with an external data source reference, you can set up a table occurrence of a table in another file and link it into your relationship graph as though the table were part of the same file.This is why you might not need to perform a script in the other file to do this.

          • 3. Re: Can I use a script to copy a field of a found set to a different database field that is a repeating field? What commands can I use?
            PamelaSnyder

            You are correct – they are two different files.  You are also correct that it is a fairly small number of records in the found set (16 to 32).  I already have a relationship between the two files.

             

            That makes your option a) attractive -

             

            I am currently trying “the long way” but with only one record in the found set.  But I am evidently leaving something out.  I have attached a screen shot.

             

            Sorry – I’m a med tech – not a developer – so it is pretty slow going for me.

            • 4. Re: Can I use a script to copy a field of a found set to a different database field that is a repeating field? What commands can I use?
              philmodjunk

              I see several issues, the first of which is that there are better options than copy/paste. The next is that your script makes no effort to select the correct found set in the first layout nor the correct found set in the second. There's nothing even here to make sure that it starts on the right layout.

               

              And perhaps you should explain why you want to use a repeating field in this way in the first place? Repeating fields have their uses, but for "newbies" I often find that they are using a repeating field when another option, such as a portal to a related table, makes for a better choice.

               

              To move data from place to place without using copy/paste--which destroys any data that the user might have previously copied:

              Set Variable in place of copy

              Set field [Table::Field ; $VariableNameHere ] in place of paste

               

              And with a relationship in place, you may not even need to change layouts nor use set variable. Just Set Field [RelatedTable::Field [Repetitionnumber] ; Table::field ]

               

              may work.

               

              But I suspect that a portal or maybe a value list might better serve for what you want here. so please take a moment to provide the big picture here.