Job Number

[Chimp8471]

i need to create a job/ticket number for my helpdesk database, can anybody point me in the right direction preferably without using the autonumber.

 

[ifstar]

i need to create a job/ticket number for my helpdesk database, can anybody point me in the right direction preferably without using the autonumber.

Create a table and have it in a single field with NextTicketNumber / Number
any time you wish to create a new number you need to go and read from this field then update it by 1.

Why don't you want to use an autonumber though?

 

[Mile-O]

i need to create a job/ticket number for my helpdesk database, can anybody point me in the right direction preferably without using the autonumber.

On the BeforeUpdate() event of a form you use the DMax() domain aggregate function to assign a new value by looking at the current numbers and adding 1 to it.

 

[Chimp8471]

i want to preceed the ticket number with 2 letters, and i was told that this couldn't be done with an autonumber

 

[The_Doc_Man]

Whenever you do something like this (mixed-format identifier) and want to deal with it programmatically, you have few choices.

I tend to be blunt, so forgive me if this sounds callous: If your alphabetic prefix is constant, omit it. You don't need it. Rethink your design.

OK, the softer side: If your alphabetic prefix is NOT constant (perhaps because it contains some encoding of problem type or charge codes), then ok, you might well need it. But the only way to do this RIGHT is to split the identifier (and thus, the prime key) into two parts. One of which is alphabetic and the other of which is numeric.

Having done this, you would have to write some function that looks up the DMax of the numeric part for all call records (or for all records with that specific alphabetic part - you have to decide how your numering scheme will run, it's not my problem). Then add one to the result of the DMax. Then store the alphabetic code and the numeric part in the record as the two components of the compounded prime key.

Now, the reason I said you have to split it and treat it as a compound key is because of its mixed format AND the fact that you want to do math on the numeric part of the key (i.e. add one to the highest existing problem with that index). If either of those facts had not been true, my answer would not have included the need for a compound key.