Working around limitations of auto-increment

[pmorse]

I am still trying to make the transition from A4 to A5. This one has hung me up for hours and hours:

I run a business with a long and unique inventory. In A4 field rules I could
assign each item a unique number, but I also had a degree of flexibility which
is lacking in the new A5 auto-increment field rule.

I could skip a region of numbers and go back to it at a later time or I could
insert a number between two existing numbers by assigning 10263A between 10263 and 10264. This flexibility is very necessary to my record keeping, and having talked with support it is simply not available in A5.

The only work-around is to skip the auto-increment function and to assign a default value to my inventory number. I have tried and tried to come up with
a workable expression to make this happen, but with no success.

My inventory number is a 6-digit character. All I want to do is to find the
expression that allows me to take the inventory number from the previous
record and add "1" to it and assign that as the default value (one which I
can override simply by typing over it when the need arised). I simply
can't come up with the expression that makes it happen.

Any help would be much appreciated.

Thanks in advance.

Paul M

[Tom Cone Jr]

I think your best bet is to store the field value most recently used in a separate file or table. During data entry your new record script would retrieve the field value from that external source. The same script would increment the field value and store the incremented field value in the external source. Then when you save your new record the field value actuallyused would be written back to the same source. This thread in the code archive illustrates approaches that are similar to this, though each will require further modification to deal with your requirements. -- tom

http://msgboard.alphasoftware.com/al...auto+increment

[pmorse]

Thanks Tom, but...

Thanks Tom for your solution, but I think I have to take a
pass.

I thought I was upgrading when I went from A4 to A5, but it
seems as though the opposite is true. I wish there had been a
prominent warning to the purchasers of A5 that some of the things which
used to be simple in A4 and now devilishly complex. Instead I was
told "a total novice who wants to build a simple database fast...[has
come] to the right place."

Since I know next to nothing about scripting in A5 and the solution
seems to demand that I modify or write a complex script, I guess
if time means anything to me the most "efficient" thing is to
manually imput each and every inventory number. I probably could
have entered a thousand numbers that way in the time I've spent
learning that I don't know enough to do anything about it.

Yes, I want my inventory number to mean more than one thing: a
sequence but also a chronology and an admission that once in a while
one leaves something out. Is that really asking too much?

And to the developers of A5 I would ask if such a simple function requires
such a convoluted solution. Couldn't they have programed in a second
option for "auto-increment"? or at least offered a patch?

Having looked over the message board I see that I am far from the only
one who has raised this issue.

[Tom Cone Jr]

Paul,

I recognize the lament. Alpha Four and Alpha Five are two different products, designed for two different operating systems. Alpha Five is not an upgrade from Alpha Four, and shouldn't be sold that way in my opinion. Having said that, I hasten to add that I won't be going back to Alpha Four now that I've climbed the learning curve in the Windows environment. I can build a much better interface for the user, and can integrate my application with Windows in a much better way.

While I don't think it's typical, it's not unusual that someone manages over time to build amazingly sophisticated apps with Alpha Four, and then wants to simply convert them over to Alpha Five. These folks are often disappointed to learn that they have sink significant effort and time in the project in order to achieve the same degree of sophistication and control in the Windows environment. You may not be old enough to remember the trauma Windows caused many successful DOS software suppliers, but it was awful.

I sometimes think it's better for someone to come to Alpha Five from a competing Windows database tool, than from Alpha Four. Alpha Five shines, in my opinion, when compared with the other Windows databases in it's price range. The comparative learning curve is lower, and the package has longer legs. At least that's how I see it.

In your case maybe you should consider changing your approach. Database gurus smarter than me will tell you it's a mistake to use a single field in a table for multiple purposes. In my own experience I've learned that permitting the user to enter their own id numbers for transaction records is often a big mistake. If I were designing your app I'd think about using a simple autoinc field to assign unique numbers to each record, and then I'd use a date field to position the record in the correct chronological sequence, when needed.

-- tom

[Tim Kiebert]

Maybe this will work for you

Add a field to your table called record_id,c,8

Do an update operation that assigns serial values to this new field
----right click on the table and choose create operation... and then choose update records -- click the button create using genie -- then on the character tab choose assign serial values -- follow the prompts.

After the update operation has run successfully, in the field rules for record_id change it to auto increment. This will give you a field that ids the record with an otherwise meaningless value.

Right click on the table again and choose define index. define an index based on the record_id field. Name it Rcrdid_

In the field rules for your inventory number go to the data entry tab -- default mode section. Choose simple default expression. For the expression use the following substituting the appropriate table and field name

increment_value(lookupc("F",tablemax("[COLOR="Red"]YourTableName[/COLOR]",".T.","Record_Id"),"[COLOR="Red"]YourInventoryNumField[/COLOR]","[COLOR="Red"]YourTableName[/COLOR]","Rcrdid_"))

This uses three functions: increment_value(), lookupc(), and tablemax().

Tablemax() finds the largest record_Id value which should belong to the last record entered. Lookupc() uses this value to search the index to find that record and return the inventory number of that record (ie the last inventory number you used.) Increment_value() increments by 1. If last used value is 123 you get 124 if 123a you will get 123b.

Hope this helps. It shouldn't take too long to implement and uses resonably straight forward A5 features.

[pmorse]

Eureka!

Thanks so much, Tim. Since you did all the work and all
I had was to slavishly follow your instructions, it took less
than ten minutes. I inserted a few dummy records and
since it was just a default, it works just the way I had
grown used to in A4. You just saved me hours of boring
data entry and trying to solve a problem that was clearly
beyond my ability level.
Paul Morse

[Tom Cone Jr]

Tim, good work. You use the auto-increment field rule with a new field so that the default field rule for the inventory_id field can "find" the most recently entered inventory_id field value. Then you increment it and present it to the user as the candidate id for the next inventory transaction. Very clever.

--tom

[Tim Kiebert]

Thanks so much, Tim. Since you did all the work and all
I had was to slavishly follow your instructions, it took less
than ten minutes. I inserted a few dummy records and
since it was just a default, it works just the way I had
grown used to in A4. You just saved me hours of boring
data entry and trying to solve a problem that was clearly
beyond my ability level.
Paul Morse

Paul, Your welcome. My experience with Alpha started with A5v5 and am now on v7. So I don't know how it compares to A4 and won't be able to fully appreciate your frustrations. I fall into the category that Tom mentioned of coming to A5 from a competing Windows based database program. I can say though that I have been very happy with what I can accomplish in A5 and I believe I have only scratched the surface. I encourage you to stick with it and come back here when your stuck. I have learned heaps from this forum.

[Tim Kiebert]

Thanks Tom, just applying some of the things learned from yourself and others on this forum.