2 Replies Latest reply on Jan 17, 2016 3:42 PM by user20603

    Find text from related records

    user20603

      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.

       

      Thanks.

        • 1. Re: Find text from related records
          MaxEh

          It looks like your PK really isn't a PK. The combination of Style ID, Header ID and Sequence uniquely identifies the text in "Standard". Add another field consisting of a calculation that combines those 3 fields. Create another table in Standards (Standards_BB) to use to import the txt file. It should alo have a calculation field that combines the 3 ids. This will then allow for matching between the two tables. Once that is working adding and updating specific records becomes easy.

           

          There are however issues with the BB txt file. The csv export forma is cobining records/rows likely due to missing quotes around number and text field. See if you can get a tab delimited file instead and see if the data is cleaner. If you can't my skill level can't provide further guidance in terms of parsing the data. I found 10 records with the BB txt file when I imported it into a copy of the Standard filed you sent - looking into each of those you can see the 'missing' style header sequence id's. This is what suggests to me stray characters finding there way into the csv file.

           

          Hope that helps,

          • 2. Re: Find text from related records
            user20603

            Max, thanks for this. I'll give it a try this week and post back how I made out. Sounds very promising.