6 Replies Latest reply on Oct 1, 2009 1:17 PM by PROFT

    Help with substitute function

    PROFT

      Title

      Help with substitute function

      Post

      I'm working on a database with addresses.  The source data has the states all spelled out.  I need to convert them to abbreviations.  I went File>Manage>Database and I changed the field to a calculation field and input the following substitute formula.  The data has not changed.  I cannot figure out what I've missed.  Any help would be greatly appreciated!

       

      I have the calculation result as text, number of repetitions as 1, the do not evaluate if all referenced fields are empty is checked.  Here is the function data that's in the box:

       

      Substitute(State; ["ALABAMA"; "AL"]; ["ALASKA"; "AK"]; ["ARIZONA"; "AZ"]; ["ARKANSAS"; "AR"]; ["CALIFORNIA"; "CA"]; ["COLORADO"; "CO"]; ["CONNECTICUT"; "CT"]; ["DELAWARE"; "DE"]; ["DISTRICT OF COLUMBIA"; "DC"]; ["FLORIDA"; "FL"]; ["GEORGIA"; "GA"]; ["HAWAII"; "HI"]; ["IDAHO"; "ID"]; ["ILLINOIS"; "IL"]; ["IOWA"; "IA"]; ["KANSAS"; "KS"]; ["KENTUCKY"; "KY"]; ["LOUISIANA"; "LA"]; ["MAINE"; "ME"]; ["MARYLAND"; "MD"]; ["MASSACHUSETTS"; "MA"]; ["MICHIGAN"; "MI"]; ["MINNESOTA"; "MN"]; ["MISSISSIPPI"; "MS"]; ["MISSOURI"; "MO"]; ["MONTANA"; "MT"]; ["NEBRASKA"; "NE"]; ["NEVADA"; "NV"]; ["NEW HAMPSHIRE"; "NH"]; ["NEW JERSEY"; "NJ"]; ["NEW MEXICO"; "NM"]; ["NEW YORK"; "NY"]; ["NORTH CAROLINA"; "NC"]; ["NORTH DAKOTA"; "ND"]; ["OHIO"; "OH"]; ["OKLAHOMA"; "OK"]; ["OREGON"; "OR"]; ["PENNSYLVANIA"; "PA"]; ["PUERTO RICO"; "PR"]; ["RHODE ISLAND"; "RI"]; ["SOUTH CAROLINA"; "SC"]; ["SOUTH DAKOTA"; "SD"]; ["TENNESSEE"; "TN"]; ["TEXAS"; "TX"]; ["UTAH"; "UT"]; ["VERMONT"; "VT"]; ["VIRGINIA"; "VA"]; ["WASHINGTON"; "WA"]; ["WEST VIRGINIA"; "WV"]; ["WISCONSIN"; "WI"]; ["WYOMING"; "WY"])

       

      I don't receive any kind of error message, but the text in the field is not being substituted. 

        • 1. Re: Help with substitute function
          mrvodka
            

          You can not change the field itself to a calculation. You can either create a new calculation field, or you can use an auto-entry with calculated value.

           

          BTW, you may want to possibly store this info in a states table and then use a lookup.

          • 2. Re: Help with substitute function
            Polarpro
              

            PROFT wrote:

            I changed the field to a calculation field and input the following substitute formula. 


            Hm, this is not quite clear. If you change a text or a number field to a calculation field, then you usually loose the data there. (You get a warning message before.) I guess your field stays a text field and you used an Auto-Enter calculation?
            Two things come into my mind:
            1. If you have already data in a field the Auto-Enter option sometimes is confusing. What was entered gets changed usually only when the data in the field got changed AND the checkbox "Do not replace existing value..." is unchecked. You can try creating a new "real" calculation field and see what happens. (This way you also make sure that you don't loose any data in the original State field.)
            2. The Substitute function is case sensitive(!)
             

             


            • 3. Re: Help with substitute function
              PROFT
                

              Ok.  I think I'm beginning to understand.  

               

              (I had previously changed an address field which had text in it to a calculation field which used this formula:  Substitute ( Trim ( Substitute ( Address ; [ " " ; "§" ] ; [ ¶ ; " " ] ) ) ; [ " " ; ¶ ] ; [ "§" ; " " ] ) and the function worked and altered the text in that field.  That was what I was trying to do here since this is really a formatting kind of issue for printing labels).

               

              Taking the advice, I added another field (State Abbreviations), changed the original State field back to a text field, and put the Substitute formula from the original post in the newly created State Abbreviation field.  Now the state Abbreviation field has the states fully spelled out in it.  So, it clearly referenced the right field, but it did not perform the text substitution.  I must have something wrong in the formula, but I cannot find it.

               

              Thanks for helping this newbie! 

              • 4. Re: Help with substitute function
                PROFT
                  

                I think I found it!  The original source data was traditionally capitalized:  Alabama.  I had formatted the text in all fields to All CAPS since that's what the USPS prefers for shipping labels.  I edited the formula to the original data's capitalization and it worked!  I knew that it was case specific, but did not realize that it would not respect applied formatting.

                 

                Even when placed in the original field, the substitution worked correctly!

                 

                Thanks for you input.  I learned a lot from this little adventure. 

                • 5. Re: Help with substitute function
                  comment_1
                    

                  PROFT wrote:
                  Now the state Abbreviation field has the states fully spelled out in it.  So, it clearly referenced the right field, but it did not perform the text substitution.

                   

                  If you wanted states fully spelled out, you should have reversed the order:

                   

                  Substitute ( State; [ "AL" ; "ALABAMA" ] ; …

                   

                  However, it's more appropriate to keep the states in a table. You don't need any calculation here - enter the abbreviation in the StateAbbreviation field, and get the full name directly from the related record.

                   

                   

                   

                  BTW, that Trim formula looks familiar … :smileyhappy:

                  • 6. Re: Help with substitute function
                    PROFT
                      

                    I didn't make the connection with the trim formula.  It saved me!  I could not figure it out, but it works like a charm and seeing it helped me figure out a bit of the formula syntax I had misunderstood.

                     

                    Many thanks!