4 Replies Latest reply on Nov 10, 2015 6:33 AM by weedonpaul

    How do I count the number of characters in a field?

    weedonpaul

      Hi

       

      I made a booboo and whilst importing records into my data base I accidentally put job titles into name prefix field. I didn't notice at the rime and several imports later I have a large number of records with job titles in the wrong place.

       

      I would like to see if the field has more than 4 characters in it and if so copy the contents to job title, then move on to the next record.

       

      any ideas?

        • 1. Re: How do I count the number of characters in a field?
          jbrown

          Hi.

           

          You can use

          Length (YourTable::YourField) to find the number of characters in a field.

           

          Here's the description: Length

           

          In your case do something like this in a script maybe:

           

          Go To Record [First]

          Loop

          If [ Length (YourTable::Prefix) > 4

                set field [ YourTable::JobTitle  value: yourTable::Prefix]

                    set field [ YourTable::Prefix  value: "" ] //clears out this field.

          End If

          Go to Record [ Next. Exit after last ]

          End Loop

          1 of 1 people found this helpful
          • 2. Re: How do I count the number of characters in a field?
            TomHays

            The number of characters (including spaces, line breaks, etc.) is counted using the Length() function.

             

            So you could write a script to do what you want, but you might also do this one-time task manually using an appropriate Find and then "Replace Field Contents..."

             

            To find records with more than 4 characters in a field, enter Find mode and then enter @@@@@* as your search string in that field.  This will find five characters followed by any number of characters.  Browse the records to see that it found the ones you needed and didn't include any lengthy prefixes, e.g. The Honorable.  (If you discover patterns of lengthy prefixes that are being included in your search, you can exclude them by adding on additional search requests for those prefixes and make them Omit instead of Include.)

             

            Then use Replace Field Contents... with a calculated result on the job title field.  Make the calculation be just the prefix field so that it will copy the value from one field to the other on all of the records in the found set.  When that is done you can now use Replace Field Contents... to make the prefix for these fields empty (since the value is now in the title field).

             

            -Tom

            • 3. Re: How do I count the number of characters in a field?
              skywillmott

              You could try doing a find for @@@@@* in the name prefix field which will find records with 5 or more characters in that field, and then click into the 'Job title' field and choose 'Replace Field Contents…' from the 'Records' menu. In the dialog that gets shown, choose to replace with a calculated result and make that calculation to be the name prefix field… Be very careful when doing this and make sure you make a backup first, just in case you make a mistake with the replace field contents step!

              • 4. Re: How do I count the number of characters in a field?
                weedonpaul

                I am propper chuffed with that, thank you so much