Page 1 of 1

auto increment field

Unread postPosted: Sat Mar 05, 2011 4:15 pm
by johan
I wan't to create a field for customer numbers (1, 2, ...), read only and auto increment when adding a new record.
I can't use the primary key for it.
I' ve tried a trigger in mysql but this gives a problem when i want to save my form.

Any idea how i can solve my problem?

I know nothing about php or java.

thankx.

Re: auto increment field

Unread postPosted: Mon Mar 07, 2011 10:56 am
by admin
johan,

I wonder why you want to have an auto-incrementing primary key.

I'd like you to read this..
http://nubuilder.blogspot.com/2010_09_01_archive.html

BUT
if you really want to you can have an incrementing field by populating a field from another table with an incrementing table by doing this.

-table 1 is a sample of a customer table
-table 2 is where the number comes from

Code: Select all

CREATE TABLE `ac`.`testa` (
`testa_id` VARCHAR( 15 ) NOT NULL ,
`ta_customer_no` INT NOT NULL ,
`ta_name` VARCHAR( 200 ) NOT NULL ,
`ta_phone` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `testa_id` ) ,
INDEX ( `ta_customer_no` )
);

CREATE TABLE `ac`.`testa_counter` (
`testa_counter_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`tc_text` CHAR( 1 ) NOT NULL
)




-then put this code into "After Save" on the "Edit Code" tab of the Edit Form.

Code: Select all

if('#clone#' == '1' or '#recordID#' == '-1'){     //-- this is a new record

   nuRunQuery("INSERT INTO testa_counter (tc_text) VALUES ('1')");

   $s  = "UPDATE testa SET ta_customer_no = '" . mysql_insert_id() . "' ";
   $s .= "WHERE testa_id = '#newID#'";

   nuRunQuery($s);

}



OTHERWISE

you will find that nuBuilder WILL support auto-increment Primary Key fields in our next update.

Steven

Re: auto increment field

Unread postPosted: Mon Mar 07, 2011 9:54 pm
by johan
ok it work's fine

problem solved thanks

Re: auto increment field

Unread postPosted: Tue Mar 08, 2011 9:25 am
by admin
Great!

Re: auto increment field

Unread postPosted: Fri Jun 06, 2014 5:41 pm
by WRBailey
I have read all of the posts about auto_increment. I like the idea of not using them except as used in the financial example. I decided I would like to set the next sequence number in the custom code area of my form. Everything works except assigning the resulting value to the form field. I have tried all of the examples I could find. Some just disabled the code I wrote completely. Others just don't work for me. I just want to set the form field to the number I have already obtained. The field in the table is a unsigned integer. Maybe this has something to do with it, but I have spent a lot of time on something that should be very straight forward. nuDisplayError helped get me through because I knew something was really wrong when no message showed up. I only have one last statement to put in code, but everything I have tried either does nothing or it disables all the code in the Before Saving section.

Thanks for any help.

Bill Bailey

Re: auto increment field

Unread postPosted: Tue Jun 10, 2014 6:54 am
by massiws
Bill,
code posted above by Steven works fine: I suggest to follow this example.
Pay attention: this works in nuBuilder version 2; if you are running nuBuilderPro you should edit something in the code.

Max

Re: auto increment field

Unread postPosted: Tue Jun 10, 2014 2:29 pm
by WRBailey
Thanks, Max. I am using nuBuilderPro. Is there more risk with Pro than V2? I am not familiar enough with either version to pick one. I picked the most recent one, thinking the other was around for legacy reasons.

Thanks for your thoughts.

Bill

Re: auto increment field

Unread postPosted: Wed Jun 11, 2014 6:56 am
by massiws
Bill, for new projects, nuBuilderPro is the last and suggested release.

The code above should works also in nuBuilderPro, but there are some differences between two versions to keep in mind:
  • primary keys of all tables in nuBuilderPro are (or should be) varchar(25) instead of varchar(15);
  • different hash variable names (example: "#RECORD_ID#" instead of "#recordID#", see here all options)

For nuBuilderPro support, please ask in the right section of the forum.

Hope this helps,
Max