Alpha Video Training
Results 1 to 19 of 19

Thread: upgrade problems

  1. #1
    Member
    Real Name
    Mary E. Bannister
    Join Date
    Oct 2000
    Posts
    97

    Default upgrade problems

    I have several order entry databases that I've just upgraded to Alpha 5 v 5.

    With two of the three sets, when I go to "enter new record" they increment exactly as they are supposed to do ... which is exactly as they have always done.

    However, in the 3rd [and, of course, the one I use the most] when I go to enter a new record, I get the "000" ... and even when I enter the correct [next] number and save this ... the next time I get the same "000". This is a character field that is set at "auto increment" in the field rules.

    I figure I'm just "missing" something very basic to correct this ... but what?

    Is there a way to "repair" this database/set?

    Thanks

    Mary Lea-Bannister

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

    Default RE: upgrade problems

    Mary

    Sounds like a record in the table is set to the maximum value for the field size and then the autoincrement rolls over to 000.

    You may need to turn off the autoincrement field rule and then manually change the suspect value and turn back on the autoincrement field rule again.

    You may also need to increase the size of the field.

    You can't over ride an autoincrement field without turning off the field rule.

    It's usually easy to find the offending record. Open a browse with the autoincrement field on it and right click on the column and sort descending. You'll see the largest values as the first records.

    Let us know how you fare with this.

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

    Default RE: upgrade problems

    Mary

    btw, A5v5 uses an index to determine the largest value in an autoincrement field and then increments using the largest value.

    Previous versions looked at the last record in the actual data file and just incremented from that value. Unfortunately, that lead to some duplication in some tables.

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

    Default RE: upgrade problems

    Mary,

    How wide (in table structure) is the character field that's giving you trouble?

    I had trouble in A5V4 using a 5 char wide character type field, and the auto increment field rule.

    My last entry was '9999', and I expected it to roll over to '10000', but it went to '0000' instead.

    I learned from this that if I use character type fields for a numeric autoincrement value, I must pad the beginning number with leading zeros, as placeholders, to assure that all available 5 digits get used.

    i.e. first entry should have been '00001', instead of '0001' in my case.

    Might this be your situation, too ?

    - tom

  5. #5
    Member
    Real Name
    Mary E. Bannister
    Join Date
    Oct 2000
    Posts
    97

    Default RE: upgrade problems

    Hi Al,

    The "positive number" field is a 10 space character field ... so, on the surface, it's not a "maximum" problem ... although it certainly looks like it is.

    The highest number I have in that field is 4635 ... no "space holding" zeros ...

    The "index" number when I open the database/set is correct and shows 4636 as the next number ... but still "000" shows up. Very frustrating ... especially since the other two databases, both of which are the same basic format, are working just fine!

    Should I rebuild my indexes? I'm at a loss to know how to "fix" this particular set of databases so that it increments properly!

    How else can I make my "index" correspond to the next incremented number?

    Thanks so much for any help!

    Mary

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

    Default RE: upgrade problems

    Mary

    Reindexing won't do any harm, but may not find the solution.

    Did you look to see if any values are out of sequence and very large, or corrupted?

  7. #7
    Member
    Real Name
    RT
    Join Date
    Apr 2000
    Posts
    726

    Default RE: upgrade problems

    Hello Mary:

    [The highest number I have in that field is 4635 ... no "space holding" zeros]

    Hmmmmmmmm, no space holding zeros?

    You’ve gotten some good advice so far, but one thing I would definitely do if I were in your place. As Tom suggested, I would pad all of the fields with zeros. This is very easy to do via an update operation, it should take about 1 minute to do such and it may very well resolve your problem. Even if it doesn't I think you should still run the update.

    If you decide to do it, we can help with the procedure. All we need is the name of the field and for you to confirm it is a character field with a width of 10.

    Robert T.

  8. #8
    Member
    Real Name
    Mary E. Bannister
    Join Date
    Oct 2000
    Posts
    97

    Default RE: upgrade problems

    Hi Tom,

    Thanks ... yes, it's a 10 space character field ... with numbers beginning with 1 and ending with 4635.

    So ... there's a simple update expression to fix this?

    Thanks

    Mary

  9. #9
    Member
    Real Name
    Mary E. Bannister
    Join Date
    Oct 2000
    Posts
    97

    Default RE: upgrade problems

    Oops ... that should have been "Hi Robert" ... sorry about that!

  10. #10
    Member
    Real Name
    Mary E. Bannister
    Join Date
    Oct 2000
    Posts
    97

    Default RE: upgrade problems

    Oops ... also forgot the FIELD NAME! Brain's not functioning at 100% today I'm afraid!

    The field name is: POSNUM

    The field length is: 10

    The field type is: character

    Thanks a bunch!

    Mary

  11. #11
    Member
    Real Name
    RT
    Join Date
    Apr 2000
    Posts
    726

    Default RE: upgrade problems

    Hi Mary:

    I can’t say for sure it will resolve your Auto Increment issue, but it might. And once again Mary, I think it’s something you should have done from day one.

    I did this rather quickly, so I hope the following steps are correct.

    Remember to always have a BACKUP before you run any updates !!!!!!
    ---------------------------------------------------

    · Temporarily remove the auto increment field rule
    · Open the A5 Control Panel
    · Click on the Operations Tab
    · Right Click in any blank space and select “New Operation”
    · In the Operations Column on the left, select the very last option which is “Update Records”
    · In Step 2 on the right, click on the name of your table
    · Click Create [Do NOT select Create Using Genie]
    · In the Field Column, select the name of your autoincrement field
    · In the expression column, insert the expression shown below. Please replace Your_FieldName with name of the Field you want to update)

    RIGHT("0000000000" +ALLTRIM(Your_FieldName),10)

    Make sure everything is correct, ensure that you have ten zeros, and then click the icon to run the update.

    If the update fails to run, as stated above, you will probably have to remove the auto increment field rule and you might have to remove any indexes on this table. You can rebuild the indexes after operation. I don’t know if the temporary index removal will be necessary.

    Good Luck,
    Robert T

  12. #12
    Member
    Real Name
    RT
    Join Date
    Apr 2000
    Posts
    726

    Default RE: upgrade problems

    Hi Mary:

    I just posted the procedure. Replace Your_FieldName with POSNUM so it looks like this.


    RIGHT("0000000000" +ALLTRIM(POSNUM),10)

    Robert T

  13. #13
    Member
    Real Name
    Mary E. Bannister
    Join Date
    Oct 2000
    Posts
    97

    Default RE: upgrade problems

    Thanks Robert!

    I'll try it and let you know what happens!

    Mary

  14. #14
    Member
    Real Name
    RT
    Join Date
    Apr 2000
    Posts
    726

    Default RE: upgrade problems

    Hi Mary:

    Yes, please give us an update.

    Once again Mary, please remember to turn off your autoincrement field rule before you run the operation. You can turn it back on if the Update Records Operation works.

    I'm not bubbling over with confidence, but I'm fairly confident this will resolve your autoincrement issue.

    Robert T.

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

    Default RE: upgrade problems

    Robert

    I also looked at that for Mary and came up with the same type of fix.

    I used padl(alltrim(posnum),10," ") for blank filling
    or padl(alltrim(posnum),10,"0") for zero filling

    and both worked.

    The interesting side note that I found was that an update procedure would change the value of the field even if I didn't turn off the auto increment. I'd imagine that it deals with whether field rules are honored or not, but was surprised when I ran the first one (following the rules of turning off and on the auto increment) and then ran the zero fill formula and it worked without first turning off the auto.

  16. #16
    Member
    Real Name
    RT
    Join Date
    Apr 2000
    Posts
    726

    Default RE: upgrade problems

    Hello Al:

    Good job! There is one constant we can usually count on with Alpha Five, there is almost always more than one solution for the same problem.

    And yes, I'm surprised the update worked with AutoIncrement active.

    Robert T.

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

    Default RE: upgrade problems

    Robert

    You solved it without seeing it. I had Mary email me a copy of the database. Made it a lot easier to do.

    As far as the auto increment and the update being allowed, it takes an abnormal sequence for it to occur. Normally the operation comes back and says zero records updated. But in design mode and pressing the run icon 2 or 3 times, it will change the data in the auto increment field.

    Strange, but could be Ripley Believe It or Not material.

    I'll sent it to Selwyn.

  18. #18
    Member
    Real Name
    RT
    Join Date
    Apr 2000
    Posts
    726

    Default RE: upgrade problems

    Hello Al and Mary:

    Once you get your auto increment working, there are two other issues that must be addressed.

    [1] If the highest number in the current table is 0000004675, you don't want the next number showing up as 4676, obviously you want 0000004676. I believe the autoincrement rule will take care of that for you. If not, there are ways to force [pad] zeros.

    [2] We did not talk about the issue of linking fields in sets. If you have parent or child fields linked to this table we just updated, you lost your links. You will have to run the same operation we ran earlier on this table [with the autoincrement field rule] on those tables that are linked in one to many sets.

    Why? Because 4675 will is not a link with 0000004675. The numbers in both the parent and child must have all the zeros padded.

    Robert T.

  19. #19
    Member
    Real Name
    Mary E. Bannister
    Join Date
    Oct 2000
    Posts
    97

    Default RE: upgrade problems

    Hi Al & Robert,

    SUCCESS! Al very kindly emailed me an expression for updating the fields ... and Robert posted another one here.

    I tried both ... and both worked ... except ... that initially, while Al's permitted me to continue on without having zero place holders, when I went to isolate a given "order" for printing, I couldn't "find" with that number ... it looked instead for the recno.

    That would have been fine for me ... but not for the person who generally does the input.

    Our database is slightly different in that not all orders are "new" orders. We have lots of "repeat" orders wherein the only thing we do is change the "due date" and the item details ... so it's necessary sometimes to isolate a given order for printing.

    I played with Rob's expression, which was great for replacing the spaces with zeros ... but, naturally THAT wasn't good enough for me ... I wanted leading letters ... like "TTS-" and "Customer_#" etc and Rob's expression didn't permit that. However, when I tried this formulation with Al's expression, it worked ... beautifully!

    And thanks for the "child database" reminder Rob. I did update all the appropriate fields in the appropriate sub-databases.

    For the benefit of anyone else who is having this problem, the expression I used was the "padl" expression.

    padl((alltrim(fieldname)),#spaces,"desired_prefix")

    Thanks for all the help! I'd never have figured this out myself!

    Mary E. Lea-Bannister

Similar Threads

  1. Problems after Upgrade - HELP!
    By Rich Hartnett in forum Web Application Server v6
    Replies: 3
    Last Post: 07-07-2005, 01:48 PM
  2. Upgrade to Upgrade prices??
    By patelxyz in forum Alpha Five Version 5
    Replies: 7
    Last Post: 07-27-2002, 11:24 PM
  3. Upgrade
    By James Peterson in forum Alpha Four Versions 7 and 8
    Replies: 4
    Last Post: 04-29-2002, 10:04 AM
  4. Upgrade
    By James Peterson in forum Alpha Five Version 4
    Replies: 4
    Last Post: 05-08-2001, 07:27 AM
  5. Problems after 4.5 upgrade
    By bcsinc in forum Alpha Five Version 4
    Replies: 10
    Last Post: 02-09-2001, 08:10 AM

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
  •