Auto Increment Help...

[jbk]

Hello, I have several salesman that have a sales program that we created for their sales calls. Each salesman has a "unique" sales id that started with the first initial of there first, middle and last name plus 000101 (ie Steve Micheal Brown started out as SMB000101). Every week the sales staff emails there sales call table to myself at which time I append each table to my pc. One of the ways I'm able to append over 20k records from several different salesman with unique id's, is to set the "Extended field types" in the table's field rules to "User Entered" therefore keeping each sales call unique to each salesman and keeps the sales id matching what is on there computer, which allows data to be appended weekly.

But by setting the field rules of the table on the sales id to "user entered" has one draw back, it doesn't allow me to enter my own sales calls on the same database. I would like the ability to enter new sales calls. Is there anyway that when I press a "New Sales Call" button in our sales program that it would allow me to create a new sales call starting with my unique ID (JBK000101) and auto increment each time I enter a "new" sales call.

Again, I need to continue to have the ability of appending data from several salesmen with there own IDs and would like enter my own sales calls.

Thank you in advance....

[Stan Mathews]

Re: Auto Increment Help...

Not with an autoincrement rule. You'll have to code the local button to find the maximum sales id in the jbk series, increment that, and use the new value as the new sales id.

my_id = tablemax("sales_call_table_name","left(sales_id,3) = 'JBK'","sales_id")
new_id = increment_value(my_id)

[jbk]

Re: Auto Increment Help...

Stan, current maximum sales id for jbk series is "jbk002415". Do I need to put this max sales id for jbk in anywhere in you supplied code? If so where?

[jbk]

Re: Auto Increment Help...

Using the attached database as an example, I have entered a few example sales records, with "unique sales id" for the salesman. What I would like to accomplish is by presses "new button" create a new record for salesman "jbk" which would be "jbk00105" and increment from this. As I indicated in my starting thread, I can't change the "sales call id" in the table's field rules to auto increment due to the fact of appending sale call data from other salesmen on a weekly bases. But I would like to enter sales calls with the same computer / database I append to. If someone could please show me using the attached database, it would be appreciated....

PS: Stan thanks for the code in your previous code, I just can't seem to make it work, thus the example database.... JON

[Stan Mathews]

Re: Auto Increment Help...

Sample.Auto Increment.zip

[jbk]

Re: Auto Increment Help...

Good Morning Stan, thank you for the code and zip file. I downloaded you example and tried it but it stills is not auto incrementing the JBK sales id. If you create a new sales record, it will start the next sales id as JBK00105 and thats exactly what I was looking for but if I save that sales record and start another, and its keeps the sales id at JBK00105 and it should be JBK00106 and the next one should be JBK00107 etc.... Thanks again.... JON

[Stan Mathews]

Re: Auto Increment Help...

Change all your records to JBK instead of jbk and it will work.

[jbk]

Re: Auto Increment Help...

Works great!!! Thanks Stan... JON

[Stan Mathews]

Re: Auto Increment Help...

The issue was that tablemax() was finding jbk0104 as the maximum each time and it was incremented to JBK0105. When all jbk records are changed to JBK then the tablemax() finds the right JBK record and the process works properly.

[Mike Wilson]

Re: Auto Increment Help...

Just to have it be shown, you can choose to change the field rules from one to another and back, so you could suspend the autoincrement, import, and then revert it back using the attached code. It is a dialog that the user selects tables and fields in the app to switch from one data entry type to another. (BTW, I'm pretty sure Stan helped me get this together a while back. Thanks Stan!)

Later:
You can't change the field rules for a table that a form is based upon so this would have to be called from else where like from a menu button. The code actually doesn't allow you to choose an open table. You can run it in IW and it works from there.

[jbk]

Re: Auto Increment Help...

Thanks Mike... Stan's solution work for me but I'll give your code a test drive to see what it does differently....JON

[SNusa]

Re: Auto Increment Help...

A related question regarding the merits of different approaches to setting up unique fields:

Looking for a good argument as to why I shouldn't create a "user entered" character based calculated (unique) field with dbf's.....
(As opposed to a numerical or character+number [character based] auto-incrementing field.)

Due to "flexibility" considerations (update-ability and formatting/characters [3 vs. 00003] etc.): I would think it would be "generally preferable" to create a "user entered" aka: code generated unique field (character based with coding that guarantees uniqueness.) ~ Unless of course you're worried about records being deleted by accident or malicious intent.....

Notes:
This is for a top level table.
It will unlikely ever become a child/linked table. (Although that definitely is a consideration with regards to being able to change the value of the field at some point while linking etc.)
This field is also being assigned by a user behind a PC. (Another consideration relative to it being "user entered.")
It is possible that this table will at some point be ported to SQL.
(I suspect there are still issues/incompatibilities when using "non numeric compliant" fields with most back end databases if you're trying to achieve "auto-incrementing" via the database engine itself.)

First I was thinking about about TRX-000001 TRX-000002 TRX-000003 etc., as apposed to the numbers 1,2,3 etc. for the unique field.
Then began thinking about a schema like: vc_DateAndTimeStamp + chr(45) + vc_UserID for uniqueness in a hand coded "user entered" field.

I presume the only reason to go with a standard auto-increment numerical field (as opposed to an auto-increment field like TRX-00001) is related to theoretical limits on numbering schema?
(Setting TRX-01 as the default value would only allow 99 entries.)

And if you intend to link it as a child table using a an expression like left(ChildTable->UniqueID,3) then a character based auto-increment field would be preferable?
(Which in this case could effectively link all the TRX-????? child table records to one parent record entry of "TRX." ~ Very flexible!)

The thing is, creating a unique "user entered" field something like: vc_UniqueID = DateAndTimeStamp_fnc(now()) + chr(45) + vc_UserID provides the future opportunity to update that field at a later point in time. TONS OF FLEXIBILITY!?! (Only downside I see is if you decide to write code so that a computer begins adding records instead of a user. ~ Potential uniqueness issues.)

Maybe I've answered my own questions here.... I just wonder what I'm missing in terms of aspects I may not be thinking about. (efficiency etc.) ~Bizarre questions to ask, particularly when the "desktop part" of this application is close to being finished, and in a "version 2" state!


*

[DaveM]

Re: Auto Increment Help...

Robert, I am working on an app right now where the ai fields were character. The first thing I did was to switch those to numeric. I do use them for things like table connectors and lookups behind the scenes. They are not viewable by the user. This app has several tables with over 300,000 records.
They have other fields that HAVE to be user enter in some way. Some are not editable once filled in.
The speed of the app has been increased a lot with simple index and ai record uses. Especially when it comes to queries and filters.
When you start adding fields together and adding date and time, how do you use that for table connections in a set? Just think about it.

[SNusa]

Re: Auto Increment Help...

Hi Dave!
So you're saying a simple auto-increment (numerical) field has proven itself significantly faster than alpha-numeric ai (and not auto-incremental) fields when you aren't thinking about adding in "trickery?" (trickery ie: linking fields based on an expression etc.)

You reply just reminded me of something else..... I thought there was a multi-user locking issue (Not only when concurrent users have to wait until initial save/topparent.commit()), but ALSO other possibilities for timing conflicts when 2 users try to start/save a new parent record simultaneously) with auto-incremental fields. (Regardless of whether they are simple numerical or alpha-numerical ai fields.)

So my followup question is: If this is the case.... Wouldn't a unique primary field assignment like like the following (user generated type) make more sense? (and possibly even speed things up in multi-user scenario's)

TIME("yyyy-MM.dd:0h0m.s3") + chr(45) + Var->vc_UserID
(where vc_UserID is a global variable assigned at login)

This is what you get with that expression: 20150129:173304.473-RWT (Where the RWT represents the appended initials of the user logging in as pulled from a user account table.)
This guarantees uniqueness in a multi user scenario.

The added benefits (beside the field lengths) is you get so much extra visually recognizable data, right down to the user who was logged in.
I know it's a big field in terms of character length, but it sure has some appeal IMO.

I just remember years back so much had been said about the dangers of using auto-incrementing fields. Just thinking out loud here.

[Mike Wilson]

Re: Auto Increment Help...

The rule of thumbs as has been shared with me by mature, seasoned database developers is that the field that you use for establishing uniqueness is the field you use for uniqueness and linking child records, and that's it. If you want some type of identification for who generated the record, when and where, then make a field for that, but don't combine a field for record uniqueness and a field for something else together.

I personally use a table (I name it QID for uniQue ID) that has one record for each table in the database with the record holding the next unique number for the next records in that table. Then I have qid_get(tablename as C), a UDF that acquires the next record ID from the QID table (RW_exclusive), increments it, replaces the acquired QID with the increment, and delivers the acquired QID to the script that called qid_get() which is making the new record. I use exclusively character fields for my ID because I prefix the ID with three letters of the table name for a second layer of uniqueness ( QID for the Projects table is PRJ.0000001, Customer table is CUS.0000001 ). It works very well!