AnsweredAssumed Answered

Parsing records into a new table (FMP 12)

Question asked by brianpatterson on Sep 28, 2015
Latest reply on Sep 29, 2015 by erolst

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.