Autoincrement field

[massiws]

Hi,
I was trying to use the solution implemented in nuBuilder for auto-increment fields with #TT# variable; I several times read the wiki and seen video tutorial, but maybe I'm missing something.

What happens if the invoices form is launched and closed without generating an invoice record (eg if user remember having to do something else before)?
I observed that the #TT# variable is instantiated (and a new number generated and saved in the database) every time you open the edit screen, also if you are modifying an existing record.
This is also visible in the video tutorial: a record is created with invoice n°31 and then the number 32, without having first saved n° 31.
I don't think this is the right method.
Have I forgotten something?

Also, what if 1000 invoices are generated per month? The next_numbers table increase exponentially!
And if I want every January 1 restart the numbering?

To overcome this I decided to implement a different solution:

1) - in phpmyadmin I created a table "numerations" with this structure:

• Num_id: key / table name (repairs)
• Num_rep_number (field of the main table where to store the new number)

In this table I store the last generated and used number; if I want restart the numbering, I just put 0 (zero) in the rep_number field.

2) - in CustomCode tab - BeforeSave of the form I have added this code:

Code: Select all

/*
 * Get a new number from numerations table and assign to repair table
 */

// Check if it's a new record
if('#recordID#' == -1) {

    // Get last assigned number from numerations table
    $result = nuRunQuery("SELECT num_repair FROM numerations WHERE num_id = 'repair' ");
    $row = db_fetch_array($result);
    $num = ($row[0] ? $row[0] : 0);
    $num++;

    // Update numerations table
    nuRunQuery("UPDATE numerations SET num_repair = " . $num . " WHERE num_id = 'repair' ");  

    // Update form field
    $_POST['rep_number'] = $num;
}

The new number is generated and stored in the database only when the user press the Save button.
What's your opinion about this solution?
Thank you.

[shane]

I prefer to run a function like this on AferSave

Code: Select all

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=229862";
        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;
}

[massiws]

Hello shane,
thanks for the feedback.
One question: why put the function in the AfterSave tab? To update the main table it will be necessary another UPDATE sql on the record you just saved...

And in order to restart the numbering each January 1st it will be necessary TRUNCATE TABLE next_number, it's right?

[shane]

yes you will need to do an update in the after save.

I am not sure why you would need to restart the number after a particular date, if you have a date field with each record yo can filter by dates?

the sample code I supplied :

Code: Select all

        $sql1 .= " ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=229862";

sets the auto number to begin at 229862, you can change change this to start at any number you choose. the first three lines of code in the function are not really need after the first time the function is called, but you could use it to create a new next number after a particular date?

[massiws]

yes you will need to do an update in the after save.

Not necessary, if I put the UPDATE sql in AfterSave tab...

I am not sure why you would need to restart the number after a particular date...

I'm from Italy: here the law imposes to restart the invoice number each January 1st...

Thanks a lot.

[admin]

massiws,

You are doing it the hard way.

I found this on a forum

Italian law requires the invoice number to start from 1 each year so tha invoice numbers look like 2006-01...2006-1234....2007-01

Shane's suggestion, concatinated to the year will create the unique id.

Even if you use an autoid (which you shouldn't do) on your invoice table you will still at some point have to concatinate it to the year and put that string in another field. Meaning you will also end up with a field on each invoice record taking up space that was only ever used once for creating a number.

here are some other reasons why you shouldn't use autoids..
http://nubuilder.blogspot.com.au/2010/0 ... -keys.html

Steven

[massiws]

admin,

sorry but I had not read your answer before.

Maybe I explained badly, but I didn't mean to use the generated numbers as primary key of my table: it is just a number (auto-incremented) that is assigned to each record, but the primary key is a varchar field, managed by nuBuilder; here the structure:
table repair
- rep_id : varchar(15) , primary key
- rep_number: integer (auto-increment)
- rep_date : date
- ...

I found this on a forum

Italian law requires the invoice number to start from 1 each year so tha invoice numbers look like 2006-01...2006-1234....2007-01

It's a convention: we can use "2012-38" or "38/2012" or "num. 38 of 26/06/2012".

I reported an aspect of #TT# variable which, in my opinion, is dangerous, if you need consecutive numbers.

The solution I found, as well as Shane's suggestion, work properly.

Thanks you very much for all suggestions.

[admin]

massiws,

You wrote..

I reported an aspect of #TT# variable which, in my opinion, is dangerous, if you need consecutive numbers.

You are right.

And I think Shane's answer can help you.

Steven