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.