Instead of simply having record ID's be from 1 to infinity, I want to identify records by a two letter then five number code.

Example:
TY00001, TY00289, or TY92341

I need the ID to generate leading zeros where required.

The two letter prefix would need to be defined in the .cfg file as other DB's would use their own prefixes.

i.e.
TY12345
BK54321
CD13579
TP97531
DV24680
VC02468
Etc ...

I tried simply adding the two letter prefix before {'ID'} in the add form but for add error form, modify form, and modify error form the prefix is added again each time the form is used. I could use an IF statement to limit the addition of the prefix to the add form only, but that wouldn't help with the leading zeros I'd like, requiring javascript assistance to fill in the zeros.

I'd prefer to modify the cgi to handle the creation of unique ID's according to my needs.

Any thoughts?

Thanks,
beetleman

Marcus L. Griswold

Since it seems that this is also tied into the multiple database idea in the other thread, then maybe something like this:

record 00001 in the main database would be created as 100001,(just set your count file to 100000) then when the record is added to the second database the ID becomes XX00001 using some sort of concanocation (sp.).

This assumes a few things, like the second database has it's records added only from the first, no direct adding or adding from some other database, (unless the 2 sources share the same count file).

Okay, I found the subroutine that gets the record id number.
See Below:
In db.cgi

The highlighted section above indicates where the ID can be modified.
Unfourtunatly, I haven't been able to join letters with numbers here. Either numbers work or letters work but not together.
I know how to join numbers and letters in javascript, but I'm lost in perl.

Eventually, I'll be able to use IF/ELSE statements to fill in leading zeros between the prefix and the value stored in the count file.

i.e.
if ID < 10 add 000;
if ID < 100 add 00;
if ID < 1000 add 0;
if ID < 10000 add nothing;
if ID > 10000 check me into the loonytunes hotel! :)

To finish off the Mod, $prefix = 'XX'; will need to be added to the .cfg file.
And called like so:

So all I really need is a way to combine the alpha and numeric to create my alpha-numeric ID.

So close, but still so far away.
Thanks,
beetleman

Marcus L. Griswold

This thread provides the solution for padding the ID number.

Automatic next ID question
lukebrenner Mar 24, 2000

There is also a great thread which talks about adding a prefix depending on the database used. I use this for a classifieds database in which I have a different prefix automatically added to each of the 5 databases.

I'm searching my notes, but haven't come across the thread yet. I'll keep looking and let you know when I find it.

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/

Okay, the orignal thread I believe is this one. Although it was using dates .. the solution also worked for adding a prefix number the the record ID.

Figuring out how old files are
(auto-increment my strange hyphenated $db_key)
Lauren Stegman - January 20, 1999

--------
These are the changes I made to add a prefix to my ItemIDs:

In my .cfg file (for each relational database) I defined my prefix using a variable:

$count_prefix ='AU'; ### used as prefix for ItemID count number

In .cgi file - sub get_defaults I made the following changes:

if ($db_key_track) {
open (ID, "<$db_id_file_name") or &cgierr("error in get_defaults. unable to open id file: $db_id_file_name.\nReason: $!");
if ($db_use_flock) { flock(ID, 1); }
$count = substr(<ID>,3) + 1; ### new line +1 = increment by 1
$default{$db_key} = "$count_prefix-$count"; # Get next ID number .. chg'd line ##
##### $default{$db_key} = <ID> + 1; # Get next ID number - original
close ID;
}
return %default;
}

Hope this provides what you need.

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/

Thanks for the help. That is exactly what I needed for the Record ID field. But as we all know, most answers in this world usually lead to more questions.

After using the code you gave me, I was able to create an alpha-numeric ID and save it to the database. Unfortunately, the alpha-numeric value was also written to the count file. So upon addition of a second record, the sub get_defaults tries to increment to AA5 which has a numeric value of 0 (What's with me and zero), so the value returned is always AA1.

I looked at sub add_record and found the problem. Instead of the sub getting the value from the count file, adding 1, then returning the new value to the count file; the value in the ID field is returned to the count file.

I figure I have 3 options.
1. Strip all letters from the ID field value before returning to count file.

Would limit or complicate future modifications.

2. Increment the stored value in the count file then return.

Fine as long as the default ID field value is used, otherwise several ID values could be skipped.

3. Get values from both options 1 and 2 and compare before returning a value to the count file.

This could perform the double check originally written into the script to prevent unnecessary incrementing of the count file value.

So if anyone can help me modify sub add_record to accomplish one of the options above that would be great.

Thanks,
beetleman

Marcus L. Griswold

Okay, so I stumbled my way through the first option I listed before.

In db.cgi sub add_record I changed:
To:

Additionally, $count was added to variable declarations.
Changed:
my ($output, $status, $counter);
To:
my ($output, $status, $counter, $count);

Future changes to include check for $prefix defined in .cfg, strip leading zeros from ID, possibly compare count file value + 1 to $count before updating count file value.

Current output: (Where X = any Letter and # = any Number)
To Form Field : XX#####
To Database : XX#####
To Count File : #####

Once the entire project is finished, I'll be happy submit an entire mod either to the forum or resource area.

Thanks in advance for and help or ideas you can give me.

Thanks to all that have helped thus far.
beetleman

Marcus L. Griswold

I see now. The "$count = substr(<ID>,3) + 1;" Didn't work because I removed the hyphen and I didn't have the leading zeros in yet!

I understand now, substr(<ID>,3) refers to the third digit in the ID field. (0 being the first digit place)

So for me, creating AB12345, it should read substr(<ID>,2).

Because:
AB12345's first number is 1 at the #2 place (0,1,2 => A,B,1)

Thanks for all of the help.
beetleman

Marcus L. Griswold