1 of 1 people found this helpful
I do not see why you even need self joins. I would loop through copying the primary key field to be stored in the appropriate tables. I do this kind of stuff with web service APIs all the time. And FileMaker always lets you create new fields. What you are describing is commonly done on XML and JSON imports all the time. Do you have some sample data and the import script that you can share with us?
Thanks for the reply Taylor. My name is Larry Hyder and have been a SQL Server mechanic for over 25 years so you see I am in a deep rut. I do not have a website and so can't send you a link. I could attach a small Filemaker 12 file with one table that shows what I am up to. If that is OK, I will link my email address here at work.
As I said, this is easy for me to do in MS Access and SQL Server but I have got to learn Filemaker thinking.
Your help is greatly appreciated.
Good morning Taylor,
Attached is a small FM 12 database with one table that shows RecordType “A", “B", “C", “M", and “S". M is not used. RecordTypes are identified by the first character of the field RawData. RecordTypes “A", “B", and “C" have data that applies to the load and “S” records have data that applies to each log segment in the load. There are two loads represented in the data set. Scroll down and you will see data for the second load.
In the Browse view, you will see fields Cert#, MWLSBBrand, and MWLSBTicket. These fields are present in every data row and relate rows belonging to the same load and location. SellerLoadNum is only in rows of RecordType “A". GrossVol is only in rows of RecordType “B". Logger is only in rows of RecordType “C". I must gather this, validate it (log scalers make entry errors.) and post it to an archive header table. Then the data in the RecordType “S” has to be gathered, validated, and posted to an archive data table.
I would like to import data once, process it and post it. As I said, I get it done in in MS Access and store in SQL Server. Filemaker “thinks” differently. Thanks for looking into this.
Confederated Tribes of Warm Springs