7 Replies Latest reply on Aug 10, 2017 7:21 AM by gisguy

    data parsing/cleaning



      I am a novice to FileMaker, with my background in GIS.



      I've inherited a FileMaker database where one table has three fields as such:


      record_id          project_code               project_id

      1234               12589, 17058                  257842

      1235               11387, 25871                  358740


      I need to restructure the above to this ideal:

      record_id          project_code                  project_id

      1234                 12589                              257842

      1236                 17058                              257842

      1235                 11387                              358740

      1237                  25871                             358740


      record is a serial auto entered number and project relates to a different table. table contains ~45000 records.


      Looking through help, some script seems appropriate. My struggle is where to begin......can anyone please point me in the best direction?


      Thanks,  Mike

        • 1. Re: data parsing/cleaning

          Do you know how to create a script in FileMaker? A script could use two nested loops, one to  loop through the records and one to loop through the comma separated values in the project_code field, creating additional new records from any listed codes added after the first and then removing those extra codes from the original record.


          Your example only shows two codes, is that always the case? If the number of codes were always two, you might be able to use an extra table and manually export/import this data to generate the needed records. But if there can be large numbers of listed codes, this won't be very practical.

          1 of 1 people found this helpful
          • 2. Re: data parsing/cleaning

            Thank you for the reply.


            Yes, I am beginning to understand FileMaker scripting. Would you recommend using the script to write to new fields or even new fields in a new table? Being very novice, I fear munging the table. To more clearly state, the goal is to move each project_code to a new row while also writing the project_id to that row. The record_id is easy enough to auto increment.


            Yes, the project_code field always has only two 5 digit numbers. Always comma separated.

            • 3. Re: data parsing/cleaning

              As long as the project_code field has exactly two comma separated numbers, this script will do the job.

              The sort step sorts the records by record_id -- important for the logic of where the 'next' record will be relative to the duplicate.

              Sample file attached.


              Screen Shot 2017-08-10 at 12.46.15 pm.png


              Cheers, David

              1 of 1 people found this helpful
              • 4. Re: data parsing/cleaning

                I fear munging the table.


                This is what back up copies are for. Make a copy of your file just before you try any thing that modifies large numbers of records if one is not made for you automatically (as can be scheduled by hosting from server). If you munge the table, you can replace the current field with the back up, or import the data from just this one table from the back up to the current file.

                • 5. Re: data parsing/cleaning

                  Welcome gisguy !

                  If you are new to FileMaker, this set of tutorials may help you:

                  FileMaker tutorials on how to create an app | FileMaker


                  • 6. Re: data parsing/cleaning


                    backup, Backup, BACKUP! before doing anything that changes data on a massive scale.


                    • 7. Re: data parsing/cleaning

                      Thank you David.


                      This worked perfectly.


                      And will be useful in my continuing to build understanding of FileMaker and scripting.


                      And thanks to all whom responded. Appreciated!