Alpha Video Training
Results 1 to 17 of 17

Thread: Auto increment field rule

  1. #1
    Member
    Real Name
    Katherine Watson
    Join Date
    Jul 2007
    Location
    Hertfordshire, England
    Posts
    12

    Default 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. #2
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default 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.

  3. #3
    Member
    Real Name
    Katherine Watson
    Join Date
    Jul 2007
    Location
    Hertfordshire, England
    Posts
    12

    Default 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 - :confused:

  4. #4
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default 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.

  5. #5
    Member
    Real Name
    Katherine Watson
    Join Date
    Jul 2007
    Location
    Hertfordshire, England
    Posts
    12

    Default 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

  6. #6
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default 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

  7. #7
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default Re: Auto increment field rule

    Quote 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.

    Quote 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.

    Quote 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.

  8. #8
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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.

  9. #9
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default 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.

  10. #10
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default Re: Auto increment field rule

    Quote 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....

    Quote 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 at 11: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.

  11. #11
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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.

  12. #12
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default 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?

  13. #13
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default 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

  14. #14
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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.

  15. #15
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default Re: Auto increment field rule

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

    Dave

  16. #16
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Auto increment field rule

    OK, here is what I am getting:
    1-If you start with 3 C field and reach 999 then increase to 9 C field, the next increment will be "999000000"

    2-If you start with 9 C field and your initial value is
    Code:
    "      001"
    after you get to:
    Code:
    "      999"
    crazy stuff will happen.. Alpha will start using alphbet, but will put the "a" in the next field not the autoinc field! confirming my suspicion that you reached your limit but as alpha is still incrementing, it adds to the next field in the table!

    3-If you start with 9 C and your first value is:
    Code:
    "        1"
    right after 9 alpha will roll to 000000009
    Not quite as messy as the second scenario, but still messy.

    So, to answer your question, what would I do to fix it?

    I would select all values less than 999000000 trim them then pad them with zeros from the left.
    Hopefully you haven't added a lot of records starting with the 999000000 because if you have, you left a gap the size of the Grand Canyon between 000000999 and 999000000.
    If you haven't added alot, change them manually. If you have, then we are off to some expressions to change 9990000000 to 000001000 and to change 999000001 to 000001001 and so on.
    It shouldn't be a complicated expression, but whatever you do with the parent you do with the childrern.

    That's one good reason why I either do my own autoinc or use numeric.
    Last edited by G Gabriel; 07-08-2007 at 04:08 PM.

  17. #17
    Member
    Real Name
    Katherine Watson
    Join Date
    Jul 2007
    Location
    Hertfordshire, England
    Posts
    12

    Default Re: Auto increment field rule

    In my situation I had a 10 character field, with the the numbers starting at 1 going through to 4967 . I also had a conditionally required rule set as only required for orders made after 01/01/2002 as older orders didn't have a reference number.

    The end user stopped entering data when we upgraded to v8 and saw that the numbers went out of sync so I have deleted that record, reset the field rule to numeric and now when reenetering the deleted record it allocates the expected ref no.

    At the average rate of 1000 orders per year I don't see running out of numbers as an issue here.

    I can now concentrate on the other things that haven't translated from v4.5 to v8 so well. (such as on-push commands for printing invoices)

Similar Threads

  1. Calculated increment Field rule help
    By Mike Wilson in forum Alpha Five Version 7
    Replies: 15
    Last Post: 08-01-2006, 06:07 PM
  2. field auto increment
    By Josh Curtis in forum Web Application Server v6
    Replies: 0
    Last Post: 05-02-2005, 04:09 PM
  3. Auto Increment Field Rule
    By Sigrist in forum Alpha Five Version 6
    Replies: 9
    Last Post: 09-17-2004, 09:04 AM
  4. Auto Auto-Increment Field
    By Ken McEntee in forum Alpha Five Version 5
    Replies: 8
    Last Post: 05-12-2004, 01:12 PM
  5. AUTO INCREMENT FIELD
    By Mary ellen decker in forum Alpha Five Version 4
    Replies: 5
    Last Post: 09-26-2000, 08:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •