7 Replies Latest reply on Mar 29, 2012 12:57 PM by RayCologon

    Nightmare Key Field

    AllegroDataSolutions

      I am currently building a database for a nonprofit organization that has been hired by State and federal agencies to distribute benefits to participants under several different programs. The format of the data that is coming into the ogranization from these sources and has to be imported into the FileMaker solution is an absolute nightmare. The agencies will not alter their format, so I have had to script the imports into a temporay table, manipulate the data there, then import it into the "real" tables that have been designed as per the client's specs.

       

      I have gotten everything to work (i.e. all the data can be successfully extracted from the original fields and parsed into the tables where the client wants it) but I am not happy with the performance in one area. The source is a huge spreadsheet (several thousand rows) where the first column contains what appears to be data from several different fields in the original database and is over 100 characters long. That field contains the primary key - which has letters, spaces and numbers, and varies in length. The surrounding text also varies in lenth and is inconsistently entered. So the key field can appear virtually anywhere in the line. I am currently extracting the key with a subscript that loops through a value list containing all possible keys and tests for them using PatternCount(). If the result = 1, the value tested is inserted into the field.

       

      While the above approach works, running the script is time consuming. I am wondering if (a) there is a way to get the same result with a formula and (b) if the formula would actually be any faster. Here's a brief sample of the type of values I have to work with (I am not using actual records in my example because the data is confidential):

       

      "PROGRAM NAME:JSm 001 John Smith $22,365: JSm 18 Line Item 18 Name $450"

      "PROGRAM NAME:JSm 001 John Smith $8,000: JSm 34 Line Item 34 Name $55"

      "COMPLETELY DIFFERENT PROGRAM NAME:JDoe 001 Jane Doe 22,365: JDoe 10 Line Item Name 425"

      "02 ANOTHER BUDGET LINE ITEM: ABChar 001 Able Baker-Charlie $500: ABChar 66 Reimbursed expenses $50"

       

      In these examples, the value following the second colon is what we want:

       

      JSm 18

      JSm 34

      JDoe 10

      ABChar 66

       

      About the only consistancy in the source material is that there are two colons in the rows we are importing (though they can appear in different places in the field).

       

      I'd be interested to see if anyone can come up with a formula that works -- and, if using it as an auto-enter calc is any faster than running the import sub script.

       

      Thanks.

        • 1. Re: Nightmare Key Field
          comment

          Try =

           

          LeftWords ( GetValue ( Substitute ( YourField ; ":" ; ¶ ) ; 3 ) ; 2 )

           

          You can use this in a stored calculation field - no need for auto-enters.

          • 2. Re: Nightmare Key Field
            AllegroDataSolutions

            It works.

             

            Thanks.

             

            However, I'm not sure I understand your use of the paragraph symbol -- or why it isn't in quotes.

            • 3. Re: Nightmare Key Field
              comment

              The idea is to split the text into three paragraphs (according to the colons placement) and use only the the third one.

               

              A single paragraph symbol is exempted from quotes.

              • 4. Re: Nightmare Key Field
                RayCologon

                allegro wrote:

                ...the paragraph symbol -- or why it isn't in quotes.

                 

                Hi Allegro,

                 

                Interestingly, lone pilcrows (the paragraph symbol is called a pilcrow) have not requried enclosing quotes since the days of FileMaker 6.

                 

                Though they've been redundant for the better part of a decade, quite a lot of developers still use them - presumably through force of habit for the most part.

                 

                Regards,

                Ray

                ------------------------------------------------

                R J Cologon, Ph.D.

                FileMaker Certified Developer

                Author, FileMaker Pro 10 Bible

                NightWing Enterprises, Melbourne, Australia

                http://www.nightwingenterprises.com

                ------------------------------------------------

                • 5. Re: Nightmare Key Field
                  beverly

                  Me! I quote them, because that helps me see the "literal" use of them. But I do love the fact that "stand-alone" they need not be quoted.

                   

                  Beverly

                  • 6. Re: Nightmare Key Field
                    AllegroDataSolutions

                    I have been developing FileMaker solutions since version 4.0. I have always used quotes around these symbols, but I have to admit that I don't use them very often (or the GetWords functions, for that matter). I suppose the features that each developer uses depends a lot on his clients and the kinds of solutions they need.

                    • 7. Re: Nightmare Key Field
                      RayCologon

                      allegro wrote:

                      ...I suppose the features that each developer uses depends a lot on his clients and the kinds of solutions they need.

                       

                      Yes - and probably also on their thirst for adventure and/or tolerance for wild rides through unfamiliar territory!

                       

                      But, that said, the xWords and xValues functions can be useful in lots of situations, so it would probably be worth your while to make them part of your repertoire.

                       

                      Cheers,

                      Ray

                      ------------------------------------------------

                      R J Cologon, Ph.D.

                      FileMaker Certified Developer

                      Author, FileMaker Pro 10 Bible

                      NightWing Enterprises, Melbourne, Australia

                      http://www.nightwingenterprises.com

                      ------------------------------------------------