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

Auto increment field rule

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

    Auto increment field rule

    I have been running a database with auto incremented order nos on Alpha5 v4.5 for the last 5 years . We have just upgraded to v8 due to Vista and networking issues. When entering a new record on the old version the number generated is 4968 which I woud expect - however when using v8 the number is 999000 - this has nothing to do with networking as I have tested it on my standalone desktop and have the same problem - any ideas??

    #2
    Re: Auto increment field rule

    Katherine

    A change was made in version 5 or 6 (memory fails me at this moment) to correct a situation where a blank record (caused by an error condition) would cause the incrementing to start over. It is a condition that given the reaction your system is showing is one that you need to be aware of.

    So auto-increment now looks at the largest value on file and increments from that value.

    If you look at your data, there is at least one record with a value of 998889 in it.

    If it's only one record, then you can change it's value and the associated children (if any) and you'll be back on track. But it would be highly irregular to have only one. So it may be a little more work to cleanse the data.

    The other option is to write your own 'auto-increment' function and set the value from it. There are examples of these on the messageboard and on a couple of other websites.

    HTH
    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: Auto increment field rule

      Thanks for your quick reply - there was no number in the table greater than 4967 although there was a significant batch of nulls which were from historic data so I have spent most of today cleaning the data, however I am still experiencing the same problem with the next new record still coming up as 999000 -

      Comment


        #4
        Re: Auto increment field rule

        I'd suggest that you open a browse that shows the autoincrement field and then click on the column heading and sort in descending order. That'll show the largest value in the column.

        It sounds like this is a numeric field. Is that correct or is it a character field?
        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


          #5
          Re: Auto increment field rule

          Hi Al - I think this may be part of the problem looking at the field structure it is character which means when I rank it in browse 999 is the highest - this obviuolsy didn't cause a problem with the older version of alpha5 - I have now changed it to numeric and am a very happy bunny!! I will now go out and enjoy the rest of my weekend without this playing on my mind! Thanks

          Comment


            #6
            Re: Auto increment field rule

            I just got the same problem. at 999 it jumped to 99900000 the 99900001. Maybe because it is a character field? This did not happen to me in v7 to the best of my knowledge.

            Since all my acct is numeric and I wish it to stay that way, maybe I should change it to numeric field?

            Dave
            Dave Mason
            [email protected]
            Skype is dave.mason46

            Comment


              #7
              Re: Auto increment field rule

              Originally posted by DaveM View Post
              I just got the same problem. at 999 it jumped to 99900000 the 99900001. Maybe because it is a character field?
              Yes - and I'm guessing both of you have the character field left justified. That's the default.

              If you right justify the data, then a character autoincrement works more like a numeric field.

              Originally posted by DaveM View Post
              This did not happen to me in v7 to the best of my knowledge.
              Same logic is in v8 as in v7 for autoincrement.

              Originally posted by DaveM View Post
              Since all my acct is numeric and I wish it to stay that way, maybe I should change it to numeric field?

              Dave
              Or right justify all of the data..... Make sure you keep the parent and children in synch with each other. You'll have to change the data in the 99900000 and higher to allow the data to right justify properly.

              Keeping it a character field allows more values to be generated than a numeric. 0-9 vs 0-9 and A-Z

              If you use the field as a portion of a compound index, it'll get converted to a string value, so you might as well leave it as a character.
              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


                #8
                Re: Auto increment field rule

                If you right justify the data, then a character autoincrement works more like a numeric field.
                Al:
                What are you talking about? How do you right justify the field in field rules? and how does the justification change the value?

                Dave:
                at 999 it jumped to 99900000
                It did not jump. In a character field, the value after 999 is 9990000 then 9990001.

                Chances are, in the old version, you used a character field with only 3 spaces.

                Comment


                  #9
                  Re: Auto increment field rule

                  Here are a couple of screen shots showing the point Gabriel is making, and illustrating how one might "right justify" character type id numbers by padding each on the left with leading zeros.

                  It's interesting to me that

                  Code:
                  "999         "
                  (3 nines, and 6 blank spaces, which is what you get if only 3 nines are entered into the 9 wide field)

                  increments to

                  Code:
                  "999000000"
                  As a matter of historic interest, now properly classified as trivia, it has not always been thus.

                  In A5v4 for example, even with a 9 wide char type field, "999" would roll over to "000". Was not expected, to say the least.

                  Comment


                    #10
                    Re: Auto increment field rule

                    Originally posted by G Gabriel View Post
                    Al:
                    How do you right justify the field in field rules?
                    I didn't say to use field rules to right justify the data.

                    But you can use an update operation or xbasic code to change existing values in both the parent and child records to .

                    You can use the field rule default value to set the initial value of the id field in the first record to a character field that is right justified or as Tom suggested zero filled right justified.

                    I typically suggest that charcter id fields be filled with characters other than spaces to minimize these situations from occurring. ie letters and numbers....

                    Originally posted by G Gabriel View Post
                    and how does the justification change the value?
                    Code:
                    "123   " <> "   123"
                    or 
                    "123   " <> "000123"
                    as
                    "999   " <> "   999"
                    and
                    "999   " <> "000999"
                     
                    incrementing "   999" results in "   000"
                    so starting a character id field with blanks is not a good idea either. But as Tom wisely suggested, padding on the left with zeroes is a good solution.

                    My favorite id solution is to use a 32 character field and assign the remspecial(api_uuidcreate()) value to it. No dupes, no problems incrementing, and two lists made on 2 different computers won't have any common ids. 2 lists generated in seperate tables on the same computer won't have any dupes either.

                    It does require at least one network card on the computers to insure the non duplication values.

                    It's not a pretty to the eye and is not useful for the user, but an id field shouldn't be. It's for the computer to keep record linkages. Use another field to put values in the the user to find records.....
                    Last edited by Al Buchholz; 07-07-2007, 10:28 PM.
                    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


                      #11
                      Re: Auto increment field rule

                      Al:
                      I knew what you meant but I think the usage of "justify" will confuse a lot of people.

                      a-In the context of this thread, autoincrement is done in field rules.
                      b-In field rules there are no justification in autoincrement.
                      c-When you make the numeral move to the right by adding spaces, that is not right-justification. By doing that, you are changing the value of the character field as you have so eloquently illustrated in your code.
                      d-Justification does not change a value, it changes the display.
                      e-In a character field, 00000001 is not the same as 10000000 and that is not right vs left justification. These are two completely different values.
                      f-Since 00000001 is not the same as 10000000 and if you wish to start from the lowest value, you would start with 00000001.
                      g-Remember, if you have a character field made of 8 characters and you want to start from the lowest value and since zero has the lowest value, you could start with 00000000 or as most people (and as alpha will do by default) start with 00000001.
                      h-As Tom mentioned, you could "pad" with zeros. While "pad" might be a better word to use in this context than "justify", really and truly what you are doing whichever way you call it, you are changing the value.

                      In a numeric field, you could add zeros to the left of the number from here to El Paso and nothing happens, but not in a character field.

                      Comment


                        #12
                        Re: Auto increment field rule

                        G, in the situation facing Katherine I should think that changing the original field values is going to be necessary one way or the other. If you were in her situation, and assuming the field is char type, 9 wide, and the highest previous field value was
                        Code:
                        "999      "
                        what would you recommend to her?

                        Comment


                          #13
                          Re: Auto increment field rule

                          Tom&Gabe&Al,

                          I had the same problem at about the same time as Katherine.

                          8 character field went from "999 " to "99900000". I had to go back and correct all the problems. I then packed/re-indexed the tables to be sure there were no surprises. The field was reset to auto increment and on the first added record, it made a "99900018" which would have been the next record.

                          Solution since the acct field is only used for connecting sets and does not print and is never used in a computation, change it to numeric throughout the app.

                          I did that before close on Saturday. There will be a couple errors on prints that were not caught before I left, but are in non crirical areas that I can quickly repair Monday.

                          If anyone has a BETTER idea, the ears are open.

                          Dave
                          Dave Mason
                          [email protected]
                          Skype is dave.mason46

                          Comment


                            #14
                            Re: Auto increment field rule

                            Tom:
                            That's a good question and I think a lot of people will face this situation either if they are moving from an older version or if they decided to increase the number of characters after they have already added records.

                            But, when you say:
                            in the situation facing Katherine
                            I have no idea what is her situation. She didn't make that clear enough. All we know she had a character field, she converted it to numeric and now all is well.
                            Don't know how many characters in the old nor the new.

                            But assuming that you went from fewer to more characters, then your quesstion becomes how do you fix that so you don't end up with a mess?
                            good question.

                            I will do some testing and get back with you on this, because my thinking is if you have a 9 C field and now you have:
                            Code:
                            "      999"
                            you are out of luck, you reached the end of it unless alpha starts with alphabet. On the other hand, if you have
                            Code:
                            "999      "
                            then alpha will roll to "999000000" incrementing the spaces with zeros.

                            Comment


                              #15
                              Re: Auto increment field rule

                              I actually consider this a bug. I suspect it has been reported.

                              Dave
                              Dave Mason
                              [email protected]
                              Skype is dave.mason46

                              Comment

                              Working...
                              X