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

    Extracting Data From a Field

    rdowning

      Title

      Extracting Data From a Field

      Post

      I hava data that comes preformated in the following manner:

      From: Insurance Company

      To: Office Branch  

       

      ASSIGNMENT PROFILE:

      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....... 

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Extracting_Data.JPG

        • 1. Re: Extracting Data From a Field
          MikhailEdoshin

          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

          07/19/2009

          XXXXXXXXX

          HEATH,ROGER M/PEGGY A 

          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 ) ]

          Loop

          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 []

          Regards,

          Mikhail

          • 2. Re: Extracting Data From a Field
            MikhailEdoshin

            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.