More than one auto increment id

Hi,
second reference field in mysql ( INT, not varchar )
before add event
$rstmp = CustomQuery("select max(ReferenceNo)+1 as m from tablename");

$datatmp = db_fetch_array($rstmp);

$values["ReferenceNo"] = $datatmp["m"];
hint: change ReferenceNo and tablename according to your need
in visual editor list view select field ReferenceNo and switch to custom code

here you are able to build mixed number/character appearance
write in custom code window
$value ="i".$value;

You cannot have two autoincrement fields, most databases do not offer such a feature. If you only looking for a human readable way to display IDs, use calculated field in your SQL query.
Assuming that ID is your autoincrement column you can use something like this (MySQL):

Hi Michael,
Thanks for your response.
I think you understand what I am trying to achieve here. The only issue that I'm not sure about is whether max() will read the highest value of an alpha numeric value since the current values in the field (ourReference) from table (invoicesout) exist as I0012 for example.
I have tried to implement your code but the result was i1 when I tried it. It also seems to have altered all the existing entries by adding an 'i' in front of them. For example I0012 has become iI0012 now..
Thanks very much for your help.
Spencer

Hi Sergey,
Thanks for your response, I am aware you cannot have two auto incrementing fields in the table but I am hoping to still update the value automatically when a new record is added through PHP. Purely for the reason that we are currently having to check it manually at the moment and mistakes are bound to be made.
Would it be possible to expand a little on your suggestion as although I am familiar with php I'm no expert and still getting to grips with php runner.
Thanks again
Spencer

Hi Spencer,
let us assume your varchar field named ReferenceNo holds values like il1000, il1001, il 1002 ...
since your are not able to calculate with varchar fields you have to make numeric value by splitting the leading 2 characters
add page - process record values ( so you will have a chance to control the value before saving )
$rs = CustomQuery("select max(mid(ReferenceNo,3,7))+1 as internalNumber from tablename");

$data = db_fetch_array($rs);

$values["ReferenceNo"] = "il".$data["internalNumber "];
Best regards

Michael

Thanks again Michael,
Just to be clear, do I need to do this as an additional step to the other steps you've mentioned?
Sorry to be a dunce!
Thanks
Spencer

Hi Spencer,
nothing to add, only use these lines in process record values and forget my reply before.
Just try it .. if there are more question about that issue so ask again, no problem.
Greetins Michael

OK great thanks will do.
Spencer

Thank you so much! It worked a treat with some minor tweaks to add the missing zeros back on to the string.
Much appreciated!!