4 Replies Latest reply on Nov 15, 2012 8:32 AM by Terri

    Add Zero if Field Contents are Only One Character

    Terri

      Title

      Add Zero if Field Contents are Only One Character

      Post

           Hi,
           I do an import from a CSV each day and currently there is a field that will have, for example, 1, 10, 11 as field contents for the category number. In order for my scripts to work I would like to have any category that is only one character to automatically add a 0 at the beginning. I was hoping to incorporate this into my script step or perhaps it is validation option (I only see Maximum length). Any help is appreciated.
            
           FMPro 11 Advanced
            
           Thanks!

        • 1. Re: Add Zero if Field Contents are Only One Character
          philmodjunk

               Why does the absence of a leading zero keep your scripts from working? We can add that leading zero a number of ways, but a better approach may be to modify your design so that a leading zero is not necessary.

               Immediately after importing from the CSV, you can use Replace Field Contents with this calcualtion--use your field name in place of mine, can add in the leading zero if this is a field of type text:

               Let ( N = YourField ; If ( Length ( N ) < 2 ; Right ( "00" & N ; 2 ) ; N ) )

               Note that this also puts "00" into any records where the field is empty.

               You could also use this method in an auto-enter calcualtion in field options if you enable all auto-enter options during import. That method is not always practical, but is a good simple option if it works for you in this situation.

          • 2. Re: Add Zero if Field Contents are Only One Character
            Terri
                 The reason it affects my scripts is because if a person chooses from the drop-down menu 2, and I'm using that field in a "set field" step in my script it will return records that have 2 as well as 22. I need it to return only 2. My first work around was to include in the set field step "=" & the global category field.
                  
                 Same is true if a person does a manual find. If they choose from the drop-down menu 2, it will return 22 as well.
                  
                 So on my value list (that populates the drop-down menu) I had this bright idea to change the category numbers to two-digit. But now it will return nothing because I forgot my imported data is one or two-digit.
                  
                 By the way, this field will never be blank.
                  
                 Maybe my solution with the "=" the best. Or I could set up the auto-enter option, I don't have any problem with that either. I guess it is probably 6 of one or half a dozen of another.
            • 3. Re: Add Zero if Field Contents are Only One Character
              philmodjunk
                   

                        The reason it affects my scripts is because if a person chooses from the drop-down menu 2, and I'm using that field in a "set field" step in my script it will return records that have 2 as well as 22. I need it to return only 2. My first work around was to include in the set field step "=" & the global category field.

                   I presume that you are describing setting criteria for a scripted find. This will not happen if the field is of type number instead of type text. Then specifying "2" for the criteria will not find records with "22".

                   I suggest putting numbers in fields of type number instead of text whenever possible.

              • 4. Re: Add Zero if Field Contents are Only One Character
                Terri

                     I always feel so silly when the issue is caused by a simple mistake on my end...but live and learn.

                     Thank you!