Custom ID generation

[gofoe]

Hi All,
Thanks for the good work you are doing. I am coming from oracle psql background and am new to nuBuilder but found it to be very interesting to build applications with.
I have some application developed in Oracle Application Express but now want to port the applications into nubuilder. However, i was wondering how i could implement custom id or primary key generation.

I have a table named member as below:
memberid varchar2(10) primary key
mem_name
mem_dateofbirth
sex
In a form built on the member table in Oracle Express, i have a trigger attached to the member table which is then attached to the form so that upon saving records into the database table, it generates a custom id for each member. Here is the code:

if :NEW."MEMBERID" is null then
select TO_CHAR(SYSDATE,'YY')||'EPC'||LPAD(:NEW."ID",5,'0') into :NEW."MEMBERID"
from dual;

sample memberid

12EPC00001
12EPC00002
12EPC00003
.....

How do i implement this id generation concept in nubuilder?

The composition of the memberid is:
1st 2 characters stands for the year
the next three characters is a constant code "EPC" and the next 5 digits represents the serial number.

Kindly help me implement this concept in nuBuilder so i will be able to import all my data from oracle into nubuilder.

Thanks

Godwin.

[admin]

Godwin,

nuBuilder generates its own unique Primary Key IDs 14 characters long for each record added via an Edit Form.

So would you still need to use this method?

Steven

[gofoe]

Hi Steeve,
Thanks for the reply. I will still want to use this method because I have records already having this id format in oracle that I will port into nuBuilder or MySQL. Besides, this is the format being used by my church in assigning new membership ids.
Any helps Steeve?

Thanks

[admin]

Godwin,

To be really honest with you, neither of those reasons is very good.

Help me try to understand

Why have you hardcoded EPC into the middle of a string that needs to be unique?

Does anyone ever see this ID?

this is the format being used by my church in assigning new membership ids.

Does this mean people are generating the number first, perhaps on a sticker, and then being entered into the database.

Why do you put the year in the id and not a separate field?

Would you be happy making the field varchar(15)?

Steven

[gofoe]

Hi Steeve,
Thanks for the reply. I guess we can separate the year from the Id field. Its purpose is to indicate the year the member is registered with the Church.
Now, on the id field, the number is manually generated under the old system( non-computerized). But now it must be automated so that the next id is generated based on the last member id in the database.

Thanks

[gofoe]

Steeve,

The field can be varchar(15) as suggested.

Thanks

[admin]

Godwin,

I cannot figure a way to do it.

nuBuilder uses only the id I spoke of earlier or an AutoID.

You could add a code as well as the ID and populate that with your ID.

It would work better with a Lookup Object as well.

http://wiki.nubuilder.com/tiki-index.ph ... uilderDocs

Steven

[admin]

Godwin,

I do have a hack..

This goes in After Save

Code: Select all

if('#recordID#' == '-1' OR '#clone#' == '1'){

  $d = date('y');
  $n = str_pad(nextNum(), 5, "0", STR_PAD_LEFT);
  $c = 'EPC';
  
  nuRunQuery("UPDATE staff SET staff_id = '$d$n$c' WHERE staff_id = '#newID#'");
  $recordID = "$d$n$c";
}

function nextNum() {

            $sql1  = "CREATE TABLE IF NOT EXISTS next_number (next_number_id int(11) NOT NULL AUTO_INCREMENT, nxn_key varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`next_number_id`)) ";
            $sql1 .= " ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5000";  //-- set AUTO_INCREMENT to where you want to start
            nuRunQuery($sql1);

            $newid             = uniqid();
            nuRunQuery("INSERT INTO next_number SET nxn_key = '$newid'");

            $rs             = nuRunQuery("SELECT next_number_id FROM next_number WHERE nxn_key = '$newid'");
            $obj            = db_fetch_object($rs);
            $nextno         = $obj->next_number_id;

            return $nextno;
}

See if this makes sense.

Steven

[gofoe]

Thanks Steeve,
Its very helpful.

[admin]

.