2 Replies Latest reply on Aug 18, 2010 1:29 AM by MikhailEdoshin

    Extracting Data From a Field



      Extracting Data From a Field


      I hava data that comes preformated in the following manner:

      From: Insurance Company

      To: Office Branch  



      Type: Normal



      Claim Rep: Insurance adjuster


      Date of Loss: 07/19/2009


       Claim Number: XXXXXXXXX


      Insured Name: HEATH,ROGER M/PEGGY A 

      Evening Phone: (408) 266-4802

      Cause of Loss: DWL  

      Location of Property: 2489 BACHMANN CT, SAN JOSE, CA 95124

       I need the information Labeled in Blue color to go to specific fields named as the label of each info string.... Currently, I highligh each piece of info I need and enter it into each field by copy and past using a script....I need this operation in one button script that would transfer all the data at once....... 


















        • 1. Re: Extracting Data From a Field

          Hello Rudy,

          If this example is representative, I'd suggest another way. Of course, there is a custom function (GetStyledWords) that extracts styled text from a string and returns a list of what it found, but, you see, even if you get it to work, it will return something like that:

          Insurance Company

          Office Branch  

          Insurance adjuster




          And how are you going to tell which piece goes to which field?

          I'd try to loop over lines in the field and check, if a line starts with a known label, and if yes, extract the remainder of the line and put it into the appropriate field. Like that:

          Set Variable[ $counter, ValueCount( My Table::My Text ) ]


          Exit Loop If [ $counter = 0 ]


          Set Variable[ $line; GetValue( My Table::My Text, $counter ) ]

          If [ Left( $line, Length( "From: " ) = "From: " ) ]

          Set Field[ My Table::From, Middle( $line, Length( "From: " ) + 1, Length( $line ) ) ]

          Else If [ Left( $line, Length( "To: " ) = "To: " ]

          Set Field[ My Table::To, Middle( $line, Length( "To: " ) + 1, Length( $line ) ) ]

          Else If [ ]

          # And so on for all fields you need to parse

          End If


          Set Variable[ $counter, $counter - 1]

          End Loop

          If you have FileMaker Advanced, you might want to write two custom functions to wrap the formulas above, e.g:

          If [ StartsWith( $line, "From: " ) ]

          Set Field[ My Table::From, SubstringAfter( $line, "From: " ) ]

          Else If []



          • 2. Re: Extracting Data From a Field

            Correction: I've just noticed the real sample on the screenshot: it seems the "From: ", "To: " and other labels are followed not by a space, but by multiple spaces. In this case you'd have to modify the formulas somewhat and trim the extra spaces using with, say, the Trim4 function.