2 Replies Latest reply on Jun 4, 2014 2:31 PM by matttandy

    Separate data from one field and place in separate fields

    matttandy

      Hello!

       

      I am creating a simple tracking dbs and need to pull out information from a field and separate the values into separate fields. So from one field I want to pull out Co-op Number: "284" and put into the Co-op Number Field and so on. Any help on the function to use would be much appreciated!

       

      Field Data:

       

      Co-op Number:284 Customer Number:10xx68 Co-op Grant Used:125.00 Request Date:1/20/2014 Company :COMPANY NAME Contact:CONTACT NAME Address1:123 MAIN ST Address2: City:ANYWHERE State / Region:TX Country :United States Postal Code:77060 Telephone:123-456-1234 Fax: Email:EMAIL@EMAIL.COM

       

      I need to have a separate Field for the Data.

       

      Capture.PNG

        • 1. Re: Separate data from one field and place in separate fields
          Mike_Mitchell

          Matt -

           

          Here's a basic calculation that will extract a single value:

           

          let ( [

          data = "Co-op Number:284  Customer Number:10xx68  Co-op Grant Used:125.00  Request Date:1/20/2014  Company :COMPANY NAME  Contact:CONTACT NAME  Address1:123 MAIN ST  Address2:  City:ANYWHERE  State / Region:TX  Country :United States  Postal Code:77060  Telephone:123-456-1234  " ;

          curField = "Co-op Number" ;

          nextfield = "Customer Number" ;

          startPos = Position ( data ; curField ; 1 ; 1 ) + length ( curField ) + 1 ;

          endPos = Position ( data ; nextField ; 1 ; 1 ) ;

          curVal = Trim ( Middle ( data ; startPos ; endPos - startPos ))

          ] ;

           

          curVal

           

          )

           

          In order to make this work, what you'll need to do is create a return delimited list of all your fields, like this:

           

          Co-op Number

          Customer Number

          Co-op Grant Used

          Request Date

          Company

          Contact

          Address1

          Address2

          City

          State / Region

          Country

          Postal Code

          Telephone

           

          Then you can loop over the list where you set curField to GetValue ( list ; count ) and nextField to GetValue ( list ; count + 1 ). You'll need to modify the endPos value for when you hit the end of the loop (i.e., when the nextField value is empty) so that your endPos is just equal to Length ( data ). But this should get you started.

           

          HTH

           

          Mike

          • 2. Re: Separate data from one field and place in separate fields
            matttandy

            Thank you Mike! I was able to use that and not delimit in any way for my use! I really appriciate your help!

             

            Matt