3 Replies Latest reply on Jan 24, 2017 10:41 AM by beverly

    Script to convert data format


      my original data comes in the following format


      ID#field 1field 2field 3
      11111data1data 2data 3
      11112data1data 2data 3
      11113data1data 2data 3
      11114data1data 2data 3


      and I would like to convert it to a table that has the following format


      ID#name of field1data of field 1
      11111field 1 namedata 1
      11111filed 2 namedata 2
      11111field 3 namedata 3
      11112field 1 namedata 1
      11112field 2 namedata 2

      So in essence there would be one record for each occurrence of data but including a field that would contain the field name (header) from the original table.  I need to script it as this will be a reoccurring process.


      Possible?  Can the script run on Filemaker Server schedule?

        • 1. Re: Script to convert data format

          You would have to script the import of the data a temporary table and then build the new records in the actual table by looping through the imported data, switching to a layout of the "good" table, add the data, and then switching back to the temp table layout get the next record. Repeat until done.


          It should be able to be run as a scheduled script.

          • 2. Re: Script to convert data format

            You can use import records to import from your current table into the new table three times, one for each of the three fields. (Each time, you can map a different field from the original field to the "data of field 1" field in the new table.)


            Immediately after import records, the imported records forms a found set. Use replace field contents each of the three times to assign the field name text to the "label" field.


            No intermediate table is necessary.

            • 3. Re: Script to convert data format

              how many fields? do they change "name" at anytime? and how many records? if there a timestamp field? does the data in the field change (modified) once created?

              I might use ExecuteSQL() for this. Or I might use XML/XSLT. Or I might just push the data from your table into the other table via script. In any case, you'd need to know what was already converted.