Ideas for an Auto Increment Field

4 posts by 2 authors in: Forums > CMS Builder
Last Post: August 21   (RSS)

By pacolvin - August 20

I have read some posts but could not find exactly what I need.

I need to have a field in a section that contains the year '2024' a dash, then an auto incremented number.  This number could be from the Record Number field and I would set that to start at a certain value using the console.

The field also needs to be editable by an admin in the event the wrong number somehow was saved and the admin can then change it.

I'm just not sure the cleanest way to do this.  I imagine this can be done with a Generated Column field type, but I have not been successful yet.

Thanks

Phil

By Tim - August 20

Hi Phil,

MySQL iteslf doesn't allow generated columns to reference an auto incremented field (or use functions that are nondeterministic... aka have different values each time you call them like NOW()). You can see these rules at https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html 

Other than that, you can use a generated column for building other values based off other non-auto incrementing fields. For example this generated column would get you most of the way there, just not with the 'num' field. Below is an example of building something similar to what you want based on the createdDate year and a column called referral_code that you might have created to have a unique referral code.

CONCAT(YEAR(createdDate), '-', referral_code)

Let me know if you have further questions. :)

Tim Hurd
Senior Web Programmer
Interactivetools.com

By pacolvin - August 21

Tim,

You replied before I was able to update my post.  I have been able to achieve what I wanted adding a trigger with phpMyAdmin.  The trigger is below.  I still need to test some things in the production enviroment to make sure I don't get any duplicates, but this should work.

BEGIN 

-- Increment the num value and store it in generatedNum 

SET NEW.generatedNum = (SELECT IFNULL(MAX(num), 0) + 1 FROM cmsb_oca_log); 

-- Set the lcwc_num field using the incremented generatedNum 

SET NEW.lcwc_num = CONCAT( YEAR(NEW.createdDate), '-', LPAD(NEW.generatedNum, 4, '0') ); 

END

I'm open to any ideas how to make things better so all suggestions from anyone is welcome.

Phil