10 Replies Latest reply on Jun 3, 2017 9:18 AM by piaccounting

    Display of number with padded leading zeros

    piaccounting

      Hello all

      I have a situation whereby I want a number field within a table to be sequential but only when a number is required for that record. When the number field is populated I want it displayed with leading zeros - such as 000111, 000112, 000113 etc. on a given layout.  As an auto-enter serial number I can pad the number as required on the Manage database screen and it seems to displays as required. However I cannot seem to achieve the same on a number field through the Inspector Data formatting screen. I cannot see a way to specify a new format.

      Would appreciate if anyone can advise whether this is possible. If it is not then I can possibly relate it to another table with the auto-enter serial number as and when required. You might tell me I should do this anyway, but thought I'd ask.

       

      Thank for your help as always.

      Dave

       

      Message was edited by: Dave Fields Header caption set to lowercase as requested.

        • 1. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
          erolst

          Aaah, please don't use ALLCAPS ...!!!

           

          Where were we?

           

          If you just need that padded number for display, add a one-segment button bar to the layout, set it to only display the label, and in the label calculation, specify this:

           

          SerialIncrement ( "000000" ; yourNumber )

           

          and if you only want to see that/any result if there is a number, use a Hide when condition like

           

          IsEmpty ( yourNumber )

          1 of 1 people found this helpful
          • 2. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
            TomHays

            If you have a number in a Number field MyNumber, you can create a new Text field PaddedNumber with the calculation:

            Right("000000" & MyNumber;6)

            to give it an appropriate number of zeros on the left.

             

            If your original number field should have leading zeros, perhaps you should make it a text field and have an Auto-Enter to change its format like:

            Right("000000" & GetAsNumber(Self);6)

             

             

            -Tom

            1 of 1 people found this helpful
            • 3. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
              philmodjunk

              Leading zeroes is not an available data format. You will have to use other means.

               

              a) There are ways to assign unique serial numbers "on demand" rather than when the record is created.

              b) Calculation fields can display the contents of a different number field with leading zeroes added using this calculation:

               

              Right ( "0000" & NumberFieldHere ; 5 )

               

              So there are two alternatives you might consider.

              • 4. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
                piaccounting

                Hi erolst

                Sorry it would not let me edit the Header

                Yes where were we; I was asking about formatting numbers. I'm still fairly new to Filemaker so I thought I'd try your suggestion as I'd learn something new too. I can get it to work, in principle, except I cannot find where to put the label calculation. I could not find an option under the button set up. On the inspector (Data) tab I could "Hide the object" as required but the "Placeholder text" is greyed out so presumably not the place.

                Thanks for your help.

                Dave

                • 5. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
                  piaccounting

                  Hi Tom

                  Thanks for your suggestions. I got the first method to work but not the second. It just displays the entered number without padding. I'm probably doing something wrong but the first option is more than sufficient. Thanks

                  Dave

                  • 6. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
                    erolst

                    Do you have a button or a button bar segment? Only the latter lets you calculate a label (and you find that in the Button Bar Setup dialog, not in the Inspector). You need FM v14 or newer.

                     

                    There is also a known trick to have a spare field (could be a global) that you only use as a field object for its Placeholder text. (Also from FM v14)

                     

                    Should those options not be available in your FM version, or if you think it's just too much hassle - use the time-honoured method of creating a calculation field. Make sure to set it to result type text.

                    • 7. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
                      TomHays

                      piaccounting wrote:

                       

                      I got the first method to work but not the second. It just displays the entered number without padding.

                      The second method has two parts:

                      1. Make the field a Text field instead of a Number field.
                      2. Add the Auto-Enter calculation to reformat the user-entered value to have leading zeros.

                       

                      I suspect that you didn't do the first part.  If you really need the field to be a Number field, creating a second field as a Calculation (or similarly using one of the various techniques for a display-only-on-layout calculation) is necessary.

                       

                      -Tom

                      • 8. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
                        piaccounting

                        philmodjunk wrote:

                         

                        Leading zeroes is not an available data format. You will have to use other means.

                         

                         

                        Thank you - that answers my question and will stop me looking any further!

                        (a) is intriguing but for another day and (b) as also suggested in the replies works perfectly.

                        Dave

                        • 9. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
                          piaccounting

                          Hi Tom - I did do both parts. I rechecked my calculation which was correct but as I closed the dialogue boxes I spotted the "Do not replace existing..." box was checked. So removed that and then new entries updated okay and existing ones did once the field was edited. Thank you.

                          Dave

                          • 10. Re: DISPLAY OF NUMBER WITH PADDED LEADING ZEROS
                            piaccounting

                            erolst wrote:

                             

                            Do you have a button or a button bar segment? Only the latter lets you calculate a label (and you find that in the Button Bar Setup dialog, not in the Inspector). You need FM v14 or newer.

                             

                            There is also a known trick to have a spare field (could be a global) that you only use as a field object for its Placeholder text. (Also from FM v14)

                             

                            Should those options not be available in your FM version, or if you think it's just too much hassle - use the time-honoured method of creating a calculation field. Make sure to set it to result type text.

                            Hurray! FM15. I took a one segment button bar to be a single button (doh!) - worked a treat - thank you. Never too much hassle - my view is I only have to solve the problem once (remembering how I did it is another matter).

                            Dave