11 Replies Latest reply on Jan 25, 2012 11:47 AM by philmodjunk

    Trim and Remove Question

    bookalaka_1

      Title

      Trim and Remove Question

      Post

      What would be the best way to script this solution?

      I have  multiple records that I need to break out the "account code". Some  records end with a number and others don't. The number or "account code" needs to be removed from the "Name" field and moved to an "AccountCode Field".  The "Name Field" gives me information two ways  1. Test_T1234  and 2.Test   The script needs to remove the character and numbers  after the underscore,  remove the underscore and set the  Account code field with the "account code" leaving the account name in the name field.

      I have been able to place the Account code using right text functions but have not correctly trimmed the underscore and have had some inconsistency.

      Thanks for the help.

        • 1. Re: Trim and Remove Question
          philmodjunk

          Set Field [ YourTable::NameField ; Let ( p = Position ( YourTable::NameField ; "_" ; 1 ; 1 ) ; If ( p ; ( Left ( YourTable::namefield ; P - 1 ) ; YourTable::namefield ) ) ]

          • 2. Re: Trim and Remove Question
            bookalaka_1

            I have gone into script Set Field and entered the calculation keeps giving me an error "A number, text constant, field name or “(” is expected here."

            I tried brackets and parenthese didn't work?

             

            [ Address Book::Account_Code; Let ( p = Position ( Address Book::Last Name_Company ; "_" ; 1 ; 1 ) ; If ( p ; ( Left ( Address Book::Last Name_Company; P - 1 ) ; Address Book::Account_Code ) ) ]

            • 3. Re: Trim and Remove Question
              philmodjunk

              When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

              • 4. Re: Trim and Remove Question
                bookalaka_1

                ok this is what I get now. There are more “)” than there are “(” or there are more “(” than there are “)”.

                 

                 this is what i have in the specified calculted result  Let ( p = Position ( Address Book::Last Name_Company ; "_" ; 1 ; 1 ) ; If ( p ; ( Left ( Address Book::Last Name_Company ; P - 1 ) ; Address Book::Last Name_Company ) ) ]

                My set field is Address Book::AccountCode

                • 5. Re: Trim and Remove Question
                  philmodjunk

                  Sorry for the typo. Remove the ( shown in red.

                  Let ( p = Position ( Address Book::Last Name_Company ; "_" ; 1 ; 1 ) ; If ( p ; ( Left ( Address Book::Last Name_Company ; P - 1 ) ; Address Book::Last Name_Company ) )

                  • 6. Re: Trim and Remove Question
                    bookalaka_1

                    Thanks Phil this works in reverse of what I need. I want to get the 5 values to the Right of the "_'  and eliminate the underscore and set the AccountCode field.

                    ABC COMPANY_A1115  set field AccountCode to  A1115.   many fields dont have a code  appear as DEF COMPANY so I cant use the text right function.

                     I  tried changing the solution you gave me to the right but that of course isn't the correct codeing. Curios to see solution.

                     

                    Thank you again.

                    • 7. Re: Trim and Remove Question
                      philmodjunk

                      Thought you had indicated that you already could get the text to the right and just needed to trim it off of the original value.

                      Let ( [ name = Address Book::Last Name_Company ;
                                 p = Position (  name ; "_" ; 1 ; 1 ) ;
                                 L = Length ( name ) ] ;
                                 If ( p ; Right ( name  ; L - P ) ; name ) )

                      • 8. Re: Trim and Remove Question
                        bookalaka_1

                        im stumped again. I may have mis lead you with what I thought I had correct. The calculation above seperates the TEST_123 and moves "123" to the code field leaving the name field with TEST_123 which is not the end result needed. The end result needs to be Name field: "Test" and the code field: "123".  i played around with the substitute function to eliminate the _123 which works fine although the hitch is when I don't have a _Code after a name I get the Name in the code field

                        Substitute ( Name; "_"  & RightWords (Name; 1 ); "" )

                        I believe this is a "CASE FUNCTION" i just haven't been able to figure it out.  If there is information after the underscore move the information to the code field and delete the underscore. If there isn't an underscore leave the code field blank.

                        Thanks again for all  the clarification. 

                        • 9. Re: Trim and Remove Question
                          philmodjunk

                          Use both calculations that I posted, one to put "123" in the code field, one to remove it from the name field.

                          • 10. Re: Trim and Remove Question
                            bookalaka_1

                            Thank you. I have gotten everything to work with the one exception and this is why I thought a case situation must be used. When the account name "ABC Company" isn't followed by the underscore and number the account name"ABC Company" is then set to the account code field which really should be blank?

                             

                            • 11. Re: Trim and Remove Question
                              philmodjunk

                              If you are checking your email for the posts there, please disregard the last two posts which had silly errors in them.

                               

                              Let ( [ name = Address Book::Last Name_Company ;
                                         p = Position (  name ; "_" ; 1 ; 1 ) ;
                                         L = Length ( name ) ] ;
                                         If ( p ; Right ( name  ; L - P ) ) )

                              Will return an empty string if there is no _ in the field. (last clause in the If function was removed.)