Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
This is related to a few comments in issue #606250, though focused on a single aspect: The ability to specify the starting value for the serial field.
I've created a patch that adds a configuration screen allowing you to specify the starting value for the serial field. Though at the moment it only works for sites using a MySQL DB since I don't have access to a Postgres DB to test with.
Basically what it does is at the time of the field creation, it asks the user for the starting value and then runs an Alter Table command to change the starting value of the auto increment field.
Comment | File | Size | Author |
---|---|---|---|
#4 | serial_1031542_starting_number.patch | 6.68 KB | drclaw |
serial-autoincrement-start.patch | 6.35 KB | shawn_smiley |
Comments
Comment #1
theroyal CreditAttribution: theroyal commentedThanks that was a very great idea and need.
but now a days everyone is about porting this module to Drupal 7, Do you think there is a solution for that in the core D7, or we have to wait someone to port it to D7since that we are not developers and afraid to spoil everything.
all I need to move to D7 is a solution for the serial field module.
Comment #2
bryan julius CreditAttribution: bryan julius commentedParse error: syntax error, unexpected T_STRING, expecting '(' in C:\xampp\htdocs\saintjohnbosco\sites\all\modules\serial\serial.inc on line 40
i have an error..
Comment #3
JayKayAu CreditAttribution: JayKayAu commentedWould you be able to re-roll this patch for the 6.x-1.0 version?
Comment #4
drclaw CreditAttribution: drclaw commentedThis worked for me for 6.x-1.0
Comment #5
colanNew features must go into the D7 version first. For anyone that's interested in working on this, there's an alternate D6 patch in #1114224: Start autoincrement from 1001, which I'm marking as a duplicate of this issue.
Comment #6
colanI was recently approached by bhauff on IRC to inquire about a feature request to specify the auto-increment values explicitly. This got me thinking. Why not always allow users to specify the value if they wish, but if they didn't specify, then they would get the next auto-increment value?
This would solve two problems. You could specify any value you wanted for a particular instance, and as a result, you'd be able to specify the initial value, solving this particular issue.
The auto-increment system is set up to accept provided values if there are any. If a value isn't specified, is NULL or "0", the system will provide the next auto-increment value for you, but it does allow you to specify your own.
If we went this route, you would be able to, for example, explicitly state your auto-increment values as you were importing them from another system through Feeds. This was the idea behind the above-mentioned feature request.
So we could have an additional widget (other than the default hidden automatic one) that will by default be unset, but could be set by editors. To keep it out of the way in most cases, it could be in a collapsed fieldset. This will allow users to set it only the first time, to solve the issue here, and then never do it again. Or it could be set every time if importing a huge amount of pre-existing records.
Thoughts?
Comment #7
bhauff CreditAttribution: bhauff commentedI suggested this request, let me lay out the exact use case to ensure that we cover everything.
We have an existing ticket system, and each ticket has an integer number with it. We need to move all of these tickets out of the current system, and represent them with nodes in the new system. Once all of the tickets have been imported through Feeds (with the ticket number mapped to a serial field, meaning we would be specifying the ticket number for each imported ticket, not just a starting value) we would want new nodes (new tickets) being created to auto-increment where the imported tickets left off. So if the last ticket number imported was 2879, we would want the first ticket (node) created in Drupal to automatically get ticket 2880. All subsequent tickets should automatically keep increasing.
I think a different widget type makes sense.
Comment #8
emcniece CreditAttribution: emcniece commentedI desperately need to specify a start number for my serials. The code in this module is impeccably commented, but I'm having a hard time getting this to work.
I'm more than willing to help out and make this patch happen for D7, but I need a hint! Has anyone found a way to hack or force a starting digit through the database or the module?
Comment #9
emcniece CreditAttribution: emcniece commented@bhauff, Did you ever get this working?
Comment #10
MrPhilbert CreditAttribution: MrPhilbert commentedYou can do this in phpmyadmin, navicat etc. It only take a couple of minutes and you're off and running.
1. The increment number is created in a table called "serial_nameofyourfield_field". This is a helper table that is used for the increment function. It contains two values: the increment number then the nid.
2. The incremented number is stored in a table called "field_data_field_nameofyourfield".
After creating your first node of the serialized content, change the value for that node in both of those tables to whatever you would like (1000, 10000 etc.) and the subsequent nodes will increment from there.
Just remember to flush your cache after changing the values on the first one.
Sometimes the direct approach is the best approach.
Comment #11
emcniece CreditAttribution: emcniece commentedAwesome, thanks MrPhilbert... I tried doing the phpMyAdmin route, but must have not flushed the cache (damn you a thousand times, cache)! Will try again.
Comment #12
ilechcod CreditAttribution: ilechcod commentedPlease is this patch for Drupal 7or 6?
How do you install this?
By copying it to sites/all/modules?
Comment #13
bennos CreditAttribution: bennos commentedthe first patch is for D7.
And here it is backported to D6 http://drupal.org/node/1031542#comment-4161392
Comment #14
ilechcod CreditAttribution: ilechcod commentedHello All, I get this error after I create a new field of type Serial, and save. The field gets created successfully, but this error prevents me from seeing the Serial Settings screen - so I cant for instance access the starting values settings - which was the main reason I installed this module.
Here is the error:
This presently happens on the most recent version of the module listed on the project page (7.x.1.2).
This is one of the few things remaining for me to complete a VERY urgent web project.
Any help please?
Comment #15
colanPlease do not hijack this feature request. Open a support request instead.
Comment #16
Mercurie6 CreditAttribution: Mercurie6 commentedMrPhilbert
I had tackled the MySQL table directly just as you said. I had actually done it prior to reading your entry because this simple method is how to change the start increment in D6. Problem is that I made this change in all the serial fields, cleared the cache and it still had no effect. For the life of me I can not figure out where in what table, the auto increment value is, after editing all three tables.
I am wondering if there is a storage table somewhere that is storing the last increment value issued. Even if I have a node with a serial of 25 for example, and then search the entire database for the number 25, it turns up these three tables and that is it, aside from node and taxonomy tables. SO I can't figure what the problem is. I am positive that changing the database directly, is the easiest answer for setting a start serial.
My other thoughts are that since I am using a MAC Lion server, I am wondering if that has an effect. The reason is that there is a distinct noticeable difference in some template layouts if I take a site that looks awkward and move it to a CentOS machine instead. Beats the crap out of me why.
Comment #17
ilechcod CreditAttribution: ilechcod commented@drclaw,
Just wondering if this patch has been committed to the codebase?
Since this problem seems like something quite easy to solve by rolling out a new release
Perhaps there could be an option like "Increment existing Serials" for handling scenarios like Mercurie6's above. That would definitely be safer than doing that directly from DB.
Regards All
Comment #18
aggentle CreditAttribution: aggentle commentedI have been trying to get MrPhilbert's db fix to work (post #10) in D7 and came across the same problems. I change the db values in serial_nameofyourfield_field and in field_data_field_nameofyourfield, attempting to set the serial number back to 1 without success.
Here's the likely problem and solution - not throughly tested but initial checks show that it works.
The problem is that the SID field in the serial_nameofyourfield_field table is set to the "autoincrement" property. MySQL keeps a separate tab on the latest autoincrement value of that field. So even if we set the last SID value, the autoincrement counter ignores it and uses the MySQL internal value.
You can change the autoincrement value for the SID field through MySQL. Read the link here for details on changing it: Changing the autoincrement value
Obviously if you are changing the serial number down you should take care to remove content with the same SID. I'm using this to clear out all content to create a clean install.
Hope this helps.
Comment #19
MrPhilbert CreditAttribution: MrPhilbert commentedSorry if I haven't checked here for a while. I've been playing in the dotnet software world for a while building distributed systems.
If you need to start the auto increment value before any content is added, use a query like this:
ALTER TABLE "DatabaseName"."Table_Name_Here" AUTO_INCREMENT = 1001;
For instance, I created a sample database called (creatively)"Sample" followed by a table called "user". This table has four fields:
ID Type = int Auto_Increment = 'true'
Fname Type = varchar(50) allow_nulls = 'false'
Lname Type = varchar(50) allow_nulls = 'false'
Email Type = varchar(50) allow_nulls = 'true'
I could have added an auto increment value as 1000 instead of true here but this is about adding the value after table creation.
So, my alter statement would be: "Alter Table sample.user AUTO_INCREMENT = 1000;"
MrPhilbert
P.S. My previous post was done in D7.
Comment #20
BicycleBob CreditAttribution: BicycleBob commentedI have not tested this idea, but if you are trying to start your serials at a specific number (lets say 100 instead of 1) you could use the devel module to generate 99 dummy pieces of content, and then delete them. The next properly created node would have a serial of 100. Also to note- I did verify that even after deleting content, your 'place' in the serial numbers is not reverted back to 1, so this SHOULD work until this feature is added to this module. I just hope you don't want to start at 10000000000000 :)
Comment #21
DrupalDan CreditAttribution: DrupalDan commentedwill the patch be committed to D7?
Comment #22
andrew_lokko CreditAttribution: andrew_lokko commentedthx for this tip, fixed my problem.
to #19
Comment #23
ikeigenwijs CreditAttribution: ikeigenwijs commentedKeeping track of this
Comment #24
cutmedia CreditAttribution: cutmedia commentedYes this is the easy way to do do it in phpMyAdmin or from the command line:
ALTER TABLE serial_job_per_template_field_reference AUTO_INCREMENT = 2013;
There is no admin for the module so this is the quickest way.
Comment #25
zwerg CreditAttribution: zwerg commentedFile serial-autoincrement-start.patch does not work for me (7.x-1.2).
Is it possible to upload the working patched files?
Comment #26
ikeigenwijs CreditAttribution: ikeigenwijs commentedport patch to D7
A second solution would be to implement a settings page like fields permissions
that lists all the serial fields and there wanted starting point.
by form update hook the sql command alter increment can be executed
with a test that increment value can not be smaller than maximum value already present.
Comment #27
kaizerking CreditAttribution: kaizerking commentedWe need to be able to set start number as well as end number .
Use case: all business documents are numbered , different documents types will have different number ranges this number ranges are identified for a financial year and this is declared by the company to the government before the start of financial year
if serial number 1 is Purchase order number then number 2 cannot be an invoice number or sales order number that is why we need the facility to define ranges this range can also be identified with a pre-fix like AA-1 to AA-1000000000000 POs AB-1 to AB-1000000000000 invoice so on. this pre-fix thing also overcomes DB limitations, this number range can be assigned to a content type for financial year i.e after expiry of financial year the range too expires and left over numbers cannot be used in the next financial year.
Comment #28
MrPhilbert CreditAttribution: MrPhilbert commentedFor prefix and postfix on numbers, you might want to add each as a field with selectable values. If not, you would have to create your own custom module with either javascript or php logic to concatenate your pre and post logic.