4 Replies Latest reply on Sep 29, 2015 5:31 AM by erolst

    Parsing records into a new table (FMP 12)

    brianpatterson

      I have a table based on an imported mailing list.  The mailing list mixes up data on clients and contacts in each line. for a couple, there are two first names in the first name field, two last names in the last name field, etc. One line can represent 1 person, a couple, a couple with different last names, 3 people with an unknown relationship, or a business.  I need to parse this into one line for each person, and then into lines in the CONTACT table and the CLIENT table. The CLIENT table hold information common to a couple, similar information for a single person, and a business. This is usually home or business phone, address etc. The CONTACT table holds cell phone, email address and similar properties which attach to an individual.

       

      I have written a script that identifies each case correctly, and calls separate subscripts for each case.  I have text functions to unscramble the names, phone numbers, etc. I need ideas for a routine to tie it altogether.

       

      So far, I have these questions:

      1.    Should I parse the lines into a new table, one line per person, as an intermediate step, and then parse each line into CONTACT and CLIENT in a separate process  OR

            Go straight through in one big series of scripts.

      2.    Would it help to create a new record in the CLIENTs table, stuff the PK from that into the CONTACTS table, and then use this link.

      3.    If I was doing this in SQL, I'd write something line INSERT into CLIENTS ( field 1, field 3, field 5, field 6) select ( field A, Field G, field C, field D , field X ) from IMPORT_TABLE.  I'd put this in a loop.  As I understand FMP, now I have to open the source layout, move to the next line, open the destination layout, request a new record, assign values from one table to the other using the "Set Field". This all seems very complicated.. How would you do it.

       

      I think I have gotten fr enough into filemaker that I can code this, once I know the path.

       

      I have also gotten far enough that I am having real doubts about FMP, as compared to a procedural of OO language and a separate database. Can you offer any reassurance. I also see a lot I like in FMP.

       

      I will eventually upgrade to the latest version of FMP. Are there new features that might help.

        • 1. Re: Parsing records into a new table (FMP 12)
          sreese

          Greetings,

           

          If you can format and separate the data in the import table, you can go ahead and perform another import to each of the related tables using the import function. To do this you will need to specify the open file as the source file, and then map all of the fields to the correct placement.

          • 2. Re: Parsing records into a new table (FMP 12)
            Suresh

            Hai brianpatterson,

             

            I thought you required for Setting the import action and mapping fields during import records,

             

            Follow this link for import records method explaination > Import Action & Mapping Fields | FileMaker

             

            Good Luck!!

            • 3. Re: Parsing records into a new table (FMP 12)
              keywords

              I had a similar issue (a couple of years ago, so I'm a bit rusty on the details) and I decided an intermediate Imported Data table was the way to go. This had the advantages of (1) meaning that further refinement of the data was done table to table within the same file, and (2) the data as imported was preserved in case needed for future reference.

              If you are able to script the parsing, good on you. In my case I decided it was far easier to handle most of the numerous variations manually—manual finds for all the records with multiple words in the first name field, for example, then deciding in each case whether it needed to be split into separate records (firstName: Marge and Homer; surname: Simpson) or rearranged between the various name fields (firstName: Herbert von; surname: Karajan).

              • 4. Re: Parsing records into a new table (FMP 12)
                erolst

                I think you should split this into separate tasks:

                 

                1. Distribute the entries in the import table into separate records in the Clients table: if you know that each line represents one client, you can write an inner loop (the outer one being the record loop) that simply processes the lines.

                If you code this correctly, you don't need different scripts (or code blocks) for different line counts.

                 

                To avoid all those layout switches, use the Magic Key technique (or a portal) into Clients, so you can create new records directly from the current context.

                 

                Probably best to give the import table a primary key and write that also into the Clients tables as a foreign key, so you can always track to the source.

                 

                2. Once you've got all the Client records, process the contact data.

                 

                brianpatterson wrote:

                I have also gotten far enough that I am having real doubts about FMP, as compared to a procedural of OO language and a separate database.

                 

                Care to elaborate?