Field type: year-id

[MILORD65]

Hello,

In a list I would like to create a field that is of type year-id where year is the year at the time of creation of the record and id a number. I would also like id number to be reset when the year changes so for example to have the series 2017-0001, 2017-0002, .... 2017-5231 and then from 2018: 2018-0001, 2018-0002 , Etc.
I suppose this is possible with a calc field but I don't know how.
Do you have an idea ? Thank you for your help.

JP

 

[cheesegrits]

Yes, that could be done with a calc element, although it's not entirely trivial, and not something I can write for you in Community support. If you'd like to take out a sub for a month, I'll write the code for you.

-- hugh

 

[MILORD65]

Hello,

I found a solution that seems to work.
First I made a list "t_numero" which is incremented when a new record is created in the other list "t_registre".
This is done via a php plugin in the form of the "t_registre" list:

Process script: Start of form submission

PHP code:
$id = '{t_register___id}';
if ($id == '')
//"If " for no update of the year if the record is edited
{$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->insert('t_numero')
->set('user = ' . $myDb->quote('{t_register___user}'));
$myDb->setQuery($myQuery);
$myDb->execute();
$myId = $myDb->insertid();

// Update form data with insert id

$formModel->updateFormData ('t_register___numero', date('Y').'/'.sprintf("%05d",$myId));}

The result is therefore a number of the format "year / number" as "2017/000235" which is created the field element "t_register___numero" in the table t_registre.

When switching to a new year a scheduled task runs once a year on January 1 at 00:00:01 with the PHP code:

defined('_JEXEC') or die();
$db =& JFactory::getDBO();
$query= "TRUNCATE TABLE t_numero";
$db->setQuery($query);
$db->query();

This allows you to reset the id of the list t_numero

There is just one small problem is that with UTC time there are two hours difference in France for the scheduled task.

I was inspired by this post:
http://fabrikar.com/forums/index.ph...rms-field-with-the-records-primary-key.46267/

I hope that the code is well written (at least it works) and that it will be useful to other users who want to do the same.

JP