3 Replies Latest reply on Jan 28, 2014 9:12 AM by philmodjunk

    Split fields into several records

    JelleLisson

      Title

      Split fields into several records

      Post

            

           Hi Everybody, I am new to this forum.
            

           I have a table with 5 columns:
           - a number (“Source_ID”)
           - Name (“Per_Nr”)
           - Category (“Cat_Nr”)
           - Year (“Year”)

           The “Cat_Nr”-column contains different numbers, seperated with “; ” (a semicolon, followed by a space)

           e.g.:

           1          Tom     2; 12; 26; 37    2012
           2          John    12; 17               2009

            

           I need to split the information in the “Cat_Nr”-column, but the other columns have to stay the same

           e.g.:

           1          Tom      2         2012
           1          Tom     12        2012
           1          Tom     26        2012
           1          Tom     37        2012
           2          John    12        2009
           2          John    17        2009

           Since I have about a 1000 records, I was hoping someone could help me find a way to do this automatically?


           Kind regards,
           Jelle

        • 1. Re: Split fields into several records
          philmodjunk

               It sounds like you need two tables here, one for the first two and last columns and one for the data you currently have as a comma delimited list. That way the other data need only be stored once instead of once for each value in the list. (But you can still list out your data with that data repeated for every value in the list of values.)

               How new are you to FileMaker?

               A script can set a variable to this list, substituting returns for commas:

               Set Variable [ $ValueList ; value: Substitute ( Cat_Nr ; "," ; ¶ ) ]

               A script can then loop through those values creating one new related record for each value in $ValueList.

               Please post back if you need more detail on that.

          • 2. Re: Split fields into several records
            JelleLisson

                  

                 Hi PhilModJunk,

                 Thanks for the quick response!

                 I just starting using Filemaker, so I'm not familiar with the technical side at all. I understand the idea behind your explanation, but I'm not quite sure how to make it happen. Is it possible to tell me precisely what to do (and why I am doing it, so I can learn) ?

                 Kind regards,
                 Jelle

            • 3. Re: Split fields into several records
              philmodjunk

                   First the "why": You don't want to enter "1" ; "Tom" and "2012" 4 different times as shown in your example. Should you determine that this data needs to be changed, you have track down and identically update all 4 records. But if all four records are linked to the same record in a related table where you have fields for these three values, you need only edit this data a single time in that single record--that's much faster and less likely to result in data errors in you database if the update process doesn't work correctly.

                   Depending on how SourceID get's a value in your system, it may be a special case where you do need this field in both tables to use in a relationship. If you set up your database to generate this ID value, that's how it should be used. If it comes from some source outside your database, then a different field should be added for this task.

                   For sake of simplicity, I will assume that SourceID is an ID number generated by FileMaker each time you add this data to your tables. If it comes from an external source, just add one more field to your tables and give it these settings instead of setting up SourceID with these field options:

                   Open Manage | Database | Tables and define two tables: Source, and Categories. Click over to the Source table and define these three fields with these field options:

                   __pkSourceID: Type: Number, Auto-entered serial number (click options or double click the field definition to open field options where you can select this.)
                   Name : Text
                   Year:  Number

                   Define the following in Categories:

                   _fkSourceID   Type: Number
                   Category: Type Number

                   Click over to Relationships and drag from SourceID in one table occurrence "Box" to SourceID in the other. This links the two tables in a relationship. Double Click the relationship line to open a relationship details dialog and click the "allow creation of records via this relationship" for the Categories table occurrence.

                   Click OK to dismiss Manage | Database. Enter layout mode and use the portal tool to add a portal to Category on your Source layout. "Portal" is a topic well worth looking up in FileMaker Help or any training resources that you have.

                   This is just the "getting started" point. With it, you can create a new record on the source layout and simply enter data into the Category field to add a record to the portal. FileMaker will copy the value of __pkSourceID into the _fkSourceID field of the new portal record in order to establish a link between this new category record and your Source record.

                   The next step would be to move any existing data into the correct records of this new design. But first get the portal working for you.