12 Replies Latest reply on Mar 9, 2017 2:40 PM by philmodjunk

    Converting a repeating Text Field to a single Text Field

    rcbmi1

      Hi all,

       

      We have a layout that has been used since Jurassic times that has a repeating text field. This has caused several headaches over the years. We would like to move the contents of the repeating text field to a single text field for all our records, so that they can be searched, read, and edited easily. What is the best way to accomplish this?

       

      Thanks,

       

      James

        • 1. Re: Converting a repeating Text Field to a single Text Field
          Philip_Jaffe

          You will need to create a related table then create a looping script that pulls the values out of the repetition and creates new related records in the new table.  That's what I would do (no knowing any more about your solution that what you have provided).

          • 2. Re: Converting a repeating Text Field to a single Text Field
            smith7180

            I had to accomplish the same thing recently (change a repeating field into related records (i.e. "tags")).  I used FileMaker's import function which give you the option to "Import values in repeating fields by: Splitting them into separate records".  I chose this option and imported the repeating field along with the key into a temporary table.  Now you have all values as their own records with the foreign key.  Import these into whatever your target table is.  Only took me a couple of minutes.

            1 of 1 people found this helpful
            • 3. Re: Converting a repeating Text Field to a single Text Field
              siplus

              Create a new calc field, List(repeatingField).

               

              And/Or Substitute(List(repeatingField); ¶; " ")

              • 4. Re: Converting a repeating Text Field to a single Text Field
                rcbmi1

                Thanks for the quick replies everyone.

                 

                I have tried using the List() function, and it won't work for this application. It removes empty field values, which were used as paragraph breaks, and doesn't allow us to edit or input new data into the field. I could script a copy/paste into a new field, but the empty records must show up in the concatenated field.

                 

                I will try your idea, Smith, as it seems the simplest way to retain the existing data structure and still have the ability to edit records. Thanks again for the ideas!

                 

                James

                • 5. Re: Converting a repeating Text Field to a single Text Field
                  siplus

                  If you want to be able to edit the field, just define it as a text field and do a Replace Field Contents... indicating the formula.

                   

                  Of course the empty repetitions (used as paragraph breaks....OMG) won't be added.

                   

                  To overcome that you can use the Replace Field Contents with a formula like

                   

                  GetRepetition ( YourRepField ; 1 ) & ¶ &

                  GetRepetition ( YourRepField ; 2 ) & ¶ &

                  GetRepetition ( YourRepField ; 3 ) & ¶ &

                  GetRepetition ( YourRepField ; 4 ) & ¶ &

                  GetRepetition ( YourRepField ; 5 ) & ¶ &

                  GetRepetition ( YourRepField ; 6 ) & ¶ &

                  GetRepetition ( YourRepField ; 7 ) & ¶ &

                  GetRepetition ( YourRepField ; 8 ) & ¶ &

                  GetRepetition ( YourRepField ; 9 ) & ¶ &

                  GetRepetition ( YourRepField ; 10 )

                   

                  if you have 1000 repetitions you can build this formula in a text field by using a Filemaker script or build it in excel, copy, paste.

                  • 6. Re: Converting a repeating Text Field to a single Text Field
                    BruceRobertson

                    Importing and splitting into a related table would be best,

                    But I would think you would suggest the far simpler square bracket approach if creating a calc field within the existing table:

                     

                    YourRepField[1] & ¶ &

                    YourRepField[2] & ¶ &

                    YourRepField[3] & ¶ &

                    YourRepField[4] & ¶ &

                    YourRepField[5] & ¶ &

                    YourRepField[6] & ¶ &

                    etc

                    1 of 1 people found this helpful
                    • 7. Re: Converting a repeating Text Field to a single Text Field
                      siplus

                      Which is correct, when you know the Filemaker version used by the poster.

                       

                      Not knowing it, I used something meant to work even with old FM versions, as Filemaker suggests here.

                      • 8. Re: Converting a repeating Text Field to a single Text Field
                        rgordon

                        You could also use a looping script that increments by 1 for each repeat that it writes to the text field.  Much easier to write than a calc with 1000 lines of repeats (if you have a 1000).

                        • 9. Re: Converting a repeating Text Field to a single Text Field
                          rcbmi1

                          Thanks for the help, everyone. I ended up using a combination of every person's ideas:

                           

                          I created a loop script to Insert() a space in each repetition of the field, so that paragraph spacing was preserved.

                          I created a List() field for the repeating fields.

                          I copied the contents of the list() field into a new text field.

                           

                          A couple minutes of screwing around with the format, and voila!

                           

                          Thanks again,

                           

                          James

                          • 10. Re: Converting a repeating Text Field to a single Text Field
                            philmodjunk

                            in my years of experience, 9 times or more out of 10, combining such entries into a single text field is not a good idea.

                             

                            You get a lot more control over the content if you keep the data in a related record where each record holds the data from one repetition of the original repeating field. You can still merge all the data back into a single entity for clear/pretty printing, but the individual records give you more options for searching, sorting, editing and auditing/change management, that don't work nearly so well when all this data is crammed into a single field.

                            • 11. Re: Converting a repeating Text Field to a single Text Field
                              rcbmi1

                              Thanks for the comment, Phil. What you're saying makes sense; however, in this case, having one field for text is incredibly beneficial. The person who set up the database originally did not understand how to get the price column to line up with a text body, so he simply made the text field a repeating field. This meant we couldn't copy more than one line at a time, and inserting more text in to the repeating field was a nightmare.

                              Ideally, a simple text field would have been much better for this use case, I simply didn't know the best method to get from A to B.

                               

                              James

                              • 12. Re: Converting a repeating Text Field to a single Text Field
                                philmodjunk

                                None of what you say has any bearing on my comments that I can see.

                                 

                                A repeating field and a related table are not the same thing. I am not recommending a repeating field but a related table. You would replace the repeating field with a portal for data entry purposes. There are many advantages to doing so not possible or difficult to do if you put it all in one field.