5 Replies Latest reply on Oct 14, 2015 3:52 PM by

    Move data from middle of field to another field

    cliftz

      <h2>ACDelco AB2045L Li-ion 18V 2.0Ah Battery Features: </h2><ul>

      <li>Thermal protection circuit prevents charging battery cells from damage caused by extreme temperature conditions. </li>

      <li> Li-ion battery cells provide excellent performance: higher Cell Capacity per Unit Weight, Low Self-discharge, No Memory Effect.</li>

      </ul>

      <h2>ACDelco AB2045L Li-ion 18V 2.0Ah Battery Specifications: </h2><ul>

      <li>Manufacturer: ACDelco </li>

      <li>Part No: AB2045L</li>

      <li>Weight: 1 lbs. </li>

      <li>Type: Li-Ion</li>

      <li>Power: 18V 2.0 Ah</li>

      <li>Certification: CE</li></ul>

      <h2>ACDelco AB2045L Li-ion 18V 2.0Ah Battery Includes:</h2><ul>

      <li>(1) ACDelco AB2045L Li-ion 18V 2.0 Ah Battery Pack</li></ul>

       

      The above data is in a field call ProductFeatures.

      I'd like to have help making a calculation/script that will pull the data (in red only for illustrative purpose) and move it to a field called TechSpecs. (I don't want the red data to remain in the ProductFeatures field.) I have approximately 12000 such instances. The data in red is of variable length. It could have upwards of a dozen or more <li></li> lines and contain different information. The constant would be it is the second <h2> in the field ProductFeatures.

      Thank you for your assistance to this noob

      FM13

       

      Message was edited by: clif hoke added FM13

        • 1. Re: Move data from middle of field to another field
          erolst

          You can calculate the relevant portion of the text like this in a calculation:


          Let ( [

            pf = YourTable::ProductFeatures ;

            pos1 = Position ( pf ; "<h2>" ; 1 ; 2 ) ;

            pos2 = Position ( pf ; "<h2>" ; 1 ; 3 )

            ] ;

           

          Then add

            Middle ( pf ; pos1 ; pos2 - pos1 )

          ]

           

          to retrieve the text and place it into another field, or

            Replace ( pf ; pos1 ; pos2- pos1 ; "" )

          ]

           

          to delete the text from the original field.

           

          You can modify all your existing records either with two Replace Field Contents and the above calculations, or a script like

           

          Show All Records

          Go to Record/Request [ first ]

          Freeze Window

          Loop

            Set Variable [ $pf ; YourTable::ProductFeatures ]

            Set Variable [ $pos1 ; Position ( $pf ; "<h2>" ; 1 ; 2 ) ]

            Set Variable [ $pos2 ; Position ( $pf ; "<h2>" ; 1 ; 3 ) ]

            Set Field [ YourTable::TechSpecs ;  Middle ( $pf ; pos1 ; pos2 - pos1 ) ]

            Set Field [ YourTable::ProductFeatures ; Replace ( $pf ; pos1 ; pos2 - pos1 ) ]

            Go to Record/Request [ next ; exit after last ]

          End Loop


          Make sure to have a recent backup handy before you try this.

          • 2. Re: Move data from middle of field to another field

            Thank you so very much. We're working on it today. I'll be back again to let you know how we did.

            • 3. Re: Move data from middle of field to another field

              2015-10-14_15-20-00.jpg

               

              It's saying that a number, text constant, field name or "(" is expected here...

              If I understand what you gave me, it's three different operations, done one at a time. Identify the fields in question, move a field to another and delete the field from the PF...

              I did a search for the word 'specifications' in the ProductFeatures field and actually came up

              with 6000+ examples where I need to separate the information.

              Just thinking, maybe I oughta create another field called "Correction" and relate it to the 6000 instances,

              and try the calculation on it? Thank you for your patience. I'll go home and chew on this...I'm most likely not doing something very basic...

              • 4. Re: Move data from middle of field to another field
                erolst

                Guest wrote:

                 

                2015-10-14_15-20-00.jpg

                 

                I'm most likely not doing something very basic...

                 

                Oops – you're trying to redefine that ProductFeatures as a calculation field that uses itself; that won't work (circular definition) – and if it did, you'd effectively be deleting its current contents!

                 

                You need to use TechSpecs (or create an entirely new field) as a calc field, and define it as

                 

                Let ( [

                  pf = ProductFeatures ;

                  pos1 = Position ( pf ; "<h2>" ; 1 ; 2 ) ;

                  pos2 = Position ( pf ; "<h2>" ; 1 ; 3 )

                  ] ;

                Middle ( pf ; pos1 ; pos2 - pos1 )

                )

                 

                And yes, that final bracket from my suggested code must be a parenthesis; sorry about that.

                 

                Don't forget to set the data type of the calculation field to text!

                • 5. Re: Move data from middle of field to another field

                  Thank you again. I was thinking the same thing as I was leaving work. well, almost the same ha ha. I'll see how it goes here in a minute. Chowing down on some delicious wifey made dinner. hmmm.