2. PHPRunner General questions
  3. [SOLVED]  Show prefix before field
This topic is locked
[SOLVED]

 Show prefix before field

 		4/4/2011 7:45:06 PM
PHPRunner General questions
S
 Stucco author

Hi all.
I have an INT database field representing an externally referenceable ID. I have my own internal ID field, so this is not the PK. The customer wants this to now be prefixed with a 3 character text string, to distinguish the ID from other systems.
I can think of three ways:

  1. Display layer (probably use events, pre-display and pre-write)

    -This seems best, but I have four views for this table and it will require a lot of updates and maintenance
  2. Semi-Display layer (change select query to append upon selection, adjust event pre-write)

    -This seems worst, because it is a hybrid solution
  3. Logic Layer (store Prefix + ID in the database)

    -This seems ok, but the next ID is generated so I will have to strip the txt before calculating the maximum, and the prefix will be able to be updated to change the prefix
    Does anyone have any other suggestions?
    Thanks!
Sergey Kornilov admin 4/7/2011

I vote for method 3. There is only one point that needs to be changed - where new ID is added to the database.
Calculating the next ID should not be difficult.

  1. Get the max existing ID
  2. Strip first three characters
  3. Increment the number
  4. Add prefix back
    This is it.
S
 		Stucco author 4/10/2011

I took your advice and made the following changes:
I changed the field to a varchar.

I made the field read only on the edit page.

I added the following event code to the 'add' page, before record added:



/* Set incremental */

$sql="SELECT MAX( stripped_s_id ) AS max_s_id FROM (SELECT REPLACE( s_id, \"ABC\", \"\" ) AS stripped_s_id FROM s) stripped_s_ids";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$maxid=max($data["max_s_id"],3000) + 1;

$values["s_id"]="ABC" . $maxid;


If anyone else approaches this, be sure to either edit the field in PHPRunner or sync the database after updating it. PHPRunner will not write a non-integer value to a field it thinks is an int.
Thanks!