I receive a text file from a supplier for import into Filemaker that repeats a primary key field (sample attached, Standards_BB_forum.txt). When I try to use a calculation to extract specific text from this new database into another database, Filemaker will only give me the text from the first related record with that primary key field. I understand from my reading that that's all Filemaker has to offer at this point (or is it?).
The PK is "Style ID". I need to get the information from the new database from Standards.fmp12 (attached) into another database. I can't use a portal, because I then use another database to search the output of the "Standard" fields. I want to 'assemble' all of the information in one database, with the Style ID as the match.
Here's the other problem. There are up to 200 'Sequence' fields for each Style ID. In order to do what I want, it looks like I'll have to re-format the text file so that all of the Style ID fields (with subsets defined by the 'Sequence' numbering) into a comma delimited file for import, with blanks for any missing sequence number. This won't work because there are 135000 records in the text file, and the information is updated nightly. I need to find a way to use the existing text file and be able to get text from the 'Standard' field in "Standards_BB_forum.fm12" into my newly assembled database. I have two other text files formatted the same way that I also have to be able to search.
Theoretically, I thought I could simply write a case that identifies the "Style ID", "Sequence" and fill in the information from "Standard" as all of this data in the file is unique. Except it only returns the information in the first record.
I'm using Filemaker Advanced 13, and will happily upgrade to 14 if there's no solution using 13.
Thoughts are welcome. I've never written a script, used ExecuteSQL and though I've been using Filemaker for years, my programming skills are limited.