Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Leading zeroes in auto increment, character field

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Leading zeroes in auto increment, character field

    If I have an auto increment character field with a width of 6, how can I make it so that it converts it to have leading zeroes?

    Currently, I have 24 records, and they all appear like this:
    0
    1
    2
    3
    4
    ...
    23

    But it should be
    000000
    000001
    000002
    000003
    000004
    ...
    000023

  • #2
    Re: Leading zeroes in auto increment, character field

    for 24 records, I'd type over them (with a clipboard of the proper 0000 in it)

    otherwise you want a calculated expression to reset them.

    padl()
    alltrim()

    would be used..

    Code:
    a="1"
    
    b=padl(alltrim(a),6,"0")
    ?b
    = "000001"
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.
    "Make it as simple as possible, but not simpler."
    Albert Einstein

    http://www.iadn.com/images/media/iadn_member.png

    Comment


    • #3
      Re: Leading zeroes in auto increment, character field

      Running it as numeric as opposed to character overcomes that. Also, if you someday move to sql and are using the autoincrement field as a connection in a set, you will have to go numeric. That's what I been told anyhow.
      Dave Mason
      dave@aldadesktop.com
      Skype is dave.mason46

      Comment


      • #4
        Re: Leading zeroes in auto increment, character field

        Have the increment value a Character field, and in the Data Entry Field Rule, use the Default value 'Simple default expression and use this: "000001" as the default expression.
        Mike W
        __________________________
        "I rebel in at least small things to express to the world that I have not completely surrendered"

        Comment


        • #5
          Re: Leading zeroes in auto increment, character field

          Originally posted by Al Buchholz View Post
          for 24 records, I'd type over them (with a clipboard of the proper 0000 in it)

          otherwise you want a calculated expression to reset them.

          padl()
          alltrim()

          would be used..

          Code:
          a="1"
          
          b=padl(alltrim(a),6,"0")
          ?b
          = "000001"
          Gil Smith

          Comment


          • #6
            Re: Leading zeroes in auto increment, character field

            Originally posted by Al Buchholz View Post
            for 24 records, I'd type over them (with a clipboard of the proper 0000 in it)

            otherwise you want a calculated expression to reset them.

            padl()
            alltrim()

            would be used..

            Code:
            a="1"
            
            b=padl(alltrim(a),6,"0")
            ?b
            = "000001"
            Al, I have a small Db which has grown far quicker than I anticipated. I have an autoincrement field " Custome_ID" which is character. The field length is only 4 charcters.I need to pad the left side of the field to allow the autoincrement to work again. When I got to 1000 records it stopped working correctly. What code would I need to pad 00 to the field and if I did this would the autoincrement work again?

            Thanks in advance,

            Gil Smith
            Gil Smith

            Comment


            • #7
              Re: Leading zeroes in auto increment, character field

              You need to do several things depending on where you stand today.

              I got to 1000 records it stopped working correctly.
              How may records need to be fixed after the 999th ?

              If you need to manually correct some records. (Backup first.)

              Turn off the auto increment for the field. Manually enter the values you want to fix in the field. (In the old four place format)
              Edit the table structure to increase the field width. Create an update operation using Al's suggested expression. The field to be updated is the former auto-increment field, the expression would be padl(alltrim(yourautoincrementfieldname),6,"0") if you increased it to six instead of four. Run the operation.

              Now turn the auto-increment back on. Couldn't hurt to edit the initial value for the auto-increment in the field rules.
              There can be only one.

              Comment


              • #8
                Re: Leading zeroes in auto increment, character field

                Thanks a lot Stan & Al. I only had 20 records over the 1000 and I had entered the ID numbers manually. I did an uopdate as suggested and it worked a treat. Thanks again,

                Gil
                Gil Smith

                Comment


                • #9
                  Re: Leading zeroes in auto increment, character field

                  Originally posted by Al Buchholz View Post
                  for 24 records, I'd type over them (with a clipboard of the proper 0000 in it)

                  otherwise you want a calculated expression to reset them.

                  padl()
                  alltrim()

                  would be used..

                  Code:
                  a="1"
                  
                  b=padl(alltrim(a),6,"0")
                  ?b
                  = "000001"
                  Thanks for the reply, Al. I tried typing over, but it would return to the original value. I am still new at this, since I'm working on my first database. Where exactly would I place that code? Does it go in Field Rules, in the Events tab?

                  I ended up deleting all the records, and after creating a new record, it showed up properly. Now I have the issue of it starting at record 24, even though there are 0 records. Would it be simple to reset record count back to 0?

                  Comment


                  • #10
                    Re: Leading zeroes in auto increment, character field

                    I tried typing over, but it would return to the original value
                    You have to turn off auto-increment first, make your corrections/adjustments, then turn it back on. The update can be done while the auto-increment is off.

                    Now I have the issue of it starting at record 24, even though there are 0 records
                    You need to pack the table after deleting the records. That will let the auto_increment restart from the beginning. (Until you pack the table to remove the deleted records you could undelete them and have a problem with two records with the same auto value.)
                    There can be only one.

                    Comment


                    • #11
                      Re: Leading zeroes in auto increment, character field

                      Technically, I believe/know that a 4 char field can hold many more than 1000 id's.
                      The coding is a different matter.
                      A+1-999 = 999
                      B+1-999 = 999 also
                      Just using the Alpha char at the beginning yields around 25,000 possible Id's.
                      If you then go AA+1-99 and AAA+1-9.

                      Still, time to go howling at the moon again I suspect.
                      See our Hybrid Option here;
                      https://hybridapps.example-software.com/

                      Comment

                      Working...
                      X