• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Sequential Number

startpoint

Active Member
Hi,

How to generate a sequential number for new entry in the list?

I tried the following method but not working in my form.
http://fabrikar.com/forums/index.php?threads/create-an-auto-increment-numeric-field.38311/

I tried this method too.
http://fabrikar.com/forums/index.php?threads/how-to-make-an-automatic-increase-with-prefix.38659/
but my number not generated after form saved, but generated after editing a row.

From this thread I get a random number when I edited a row and this number not connected with the sequential number of the newly created row.
http://fabrikar.com/forums/index.php?threads/autoincrement-field-in-form.23315/

I want to get row id in element field after form submitted.

I read that function mysqli_insert_id may do the work, but do not know how to implement it in Fabrik.

Please someone to share their experiences in a similar situation.

Regards
 
Sorry I was just reading and may be able to help but not sure exactly what you want the data to do.

From initial reading it sounds like you want to create a new ID type number

I use something similar that gets reset every year for my test and tag system. This is setup to produce a tag if of CLIENTCODE-YEAR-NUMBER OF ITEMS TESTED FOR THAT CLIENT THIS YEAR.

This will then produce STA14-0001 for a client with the Client Code of STA. The 0001 is the auto increment and it will increase by 1 every time you add a record.

Is this the kind of thing you are looking to create?
 
Such an variant is described in one of mentioned above links, but does not work. When creating a new record of sale is not generated number, because there is still no ID. After opening and re-entry, then generate the correct number, which includes this ID.
Needs to be generate a sequential number for the invoice. Specifically, this number should not be able to be duplicated in any way and the application will be used by multiple users simultaneously.

I hope someone has a solution or an idea.
 
You need to use calc element and mysql query to make it work. Let's assume your table name is sample.

PHP:
$occupation1 = '{sample___occupation_raw}';
$occupation2 = JFactory::getDBO();$occupation2->setQuery("SELECT member_id FROM sample WHERE occupation = '$occupation1' ORDER BY id DESC LIMIT 1");$occupation2 = $occupation2->loadResult()+1;
 
$member_id = JFactory::getDBO();$member_id->setQuery("SELECT member_id FROM sample WHERE id = '{sample___id}'");$member_id = $member_id->loadResult();
 
$title1 = JFactory::getDBO();$title1->setQuery("SELECT occupation FROM sample ORDER BY id DESC LIMIT 1");$title1 = $title1->loadResult();
$title2 = JFactory::getDBO();$title2->setQuery("SELECT occupation FROM sample WHERE occupation = '$occupation1'");$title2 = $title2->loadResult();
 
$isnew = $this->getFormModel()->isNewRecord();
 
IF ($isnew && $title2 == '') { return '1'; }
IF ($isnew && $occupation1 == $title1) { return 'Your Prefix Style' . '-' . $occupation2; }
ELSE { return $member_id; }
 
Hi,
Thank you myfatebiz for your suggestion.
Based on that this is my code:
PHP:
$invoice_number = JFactory::getDBO();
$invoice_number->setQuery("SELECT invoice_number FROM oerp_stores WHERE id = '1'");
$invoice_number = sprintf("%010d", (int)$invoice_number->loadResult());
 
$invoice_number_seq= JFactory::getDBO();
$invoice_number_seq->setQuery("SELECT invoice_number FROM oerp_invoices ORDER BY id DESC LIMIT 1");
$invoice_number_seq= sprintf("%010d", (int)$invoice_number_seq->loadResult()+1);
 
$invoice_number2 = JFactory::getDBO();
$invoice_number2->setQuery("SELECT invoice_number FROM oerp_invoices WHERE id = '{oerp_invoices___id}'");
$invoice_number2 = $invoice_number2->loadResult();
 
$isnew = $this->getFormModel()->isNewRecord();
IF ($isnew && $invoice_number2 == '')
{ return $invoice_number; }
ELSEIF ($isnew && $invoice_number_seq !== $invoice_number)
{ return $invoice_number; }
ELSEIF ($isnew && $invoice_number_seq == $invoice_number)
{ return $invoice_number_seq; }
ELSE
{ return $invoice_number2;}

The problem is that IF worked, but ELSEIF not working correctly.
I think the problem is with the variable $isnew, but not sure for that.
Can you help me how to change my code to working properly?
 
Can you give me an example output on the screen or listing so that would be easy me to think how to write a code.
 
This is my solution.

1. Create a list Numeration with elements field for the required sequential numbers (Sales Sequential Number, Orders Sequential Number, Invoices Sequential Number).
2. Create list Users with fields User (element plug-in user) and Group Of Numbers (element databasejoin with list Numeration to get group id).
3. Create list Sales with element calc Sales Number with this code:
PHP:
//group of numbers for logged Joomla User
$grn = JFactory::getDBO();
$grn->setQuery("SELECT numeration FROM table_users WHERE user = '{$my->id}'");
$grn = $grn->loadResult();
 
//sales seguential number from list Numeration
$sn = JFactory::getDBO();
$sn->setQuery("SELECT sales_sequential_number FROM table_numeration WHERE id = '$grn'");
$sn = $sn->loadResult();
 
//sales number of this sale
$sn2b = JFactory::getDBO();
$sn2b->setQuery("SELECT sales_number FROM table_sales WHERE id = '{table_sales___id}'");
$sn2b = $sn2b->loadResult();
 
//new record variable
$isnew = $this->getFormModel()->isNewRecord();
 
IF ($isnew)
{ return $sn; }
ELSE
{ return $sn2b; }
!!! Important: Set Only Calc on Save.
4. Create field Group Of Numbers with element calc with this code:
PHP:
$nowgrn = JFactory::getDBO();
$nowgrn->setQuery("SELECT numeration FROM oerp_users WHERE user = '{$my->id}'");
$nowgrn = $nowgrn->loadResult();
 
//sales number of this record
$grn = JFactory::getDBO();
$grn->setQuery("SELECT group_numbers FROM oerp_sales WHERE id = '{oerp_sales___id}'");
$grn = $grn->loadResult();
 
$isnew = $this->getFormModel()->isNewRecord();
IF ($isnew)
{ return $nowgrn; }
ELSE
{ return $grn; }
Create some other fields.
5. Add Autofill plugin to Form Sales (get Sales Sequential Number from List Numeration and autofill element field Sales Number from list Sales, when add new record).
List -> Numeration
Lookup field -> Group
Field to observe -> Group Of Numbers
Map data -> {"table_numeration___sales_sequential_number":"table_sales___sales_number"}
Options: Autofill on load ->New Forms Only
6. Add PHP plugin to Form Sales (update Sales Sequential Number field in list Numeration when you create a new record).
PHP:
$grn = JFactory::getDBO();
$grn->setQuery("SELECT numeration FROM oerp_users WHERE user = '{$my->id}'");
$grn = $grn->loadResult();
 
$ssqn = JFactory::getDBO();
$ssqn -> setQuery("SELECT sales_number FROM oerp_sales WHERE group_numbers = '{$grn}' ORDER BY id DESC LIMIT 1");
$ssqn = $ssqn->loadResult()+1;
 
$ssqnu = JFactory::getDBO();
$ssqnu -> setQuery("UPDATE oerp_numeration SET sales_sequential_number = '$ssqn' WHERE id = '$grn'");
$ssqnu->query();
* This code must be used when add new sale (PHP on NEW) and Process script -> End of form submission (onAfterProcess)
7. Some validations:
7.1. List Users->field Group Of Numbers
- Action: not empty
7.2. List Numerations->field Sales Sequential Number
- Action: php
php code
PHP:
$value = '{table_numeration___sales_sequential_number}';
 
$db =JFactory::getDBO();
$query=("SELECT sales_number FROM table_sales where sales_number = $db->quote($value)");
$db->setQuery($query);
$response = $db->loadResult();
 
if ($response == $value) {
return false;
} else {
return true;
}
Match or Replace: Match
* check for an existing number in the list Sales
- Action: php
php code
PHP:
$value = '{table_numeration___sales_sequential_number}';
 
$db =JFactory::getDBO();
$query=("SELECT sales_sequential_number FROM table_numeration where sales_sequential_number = $db->quote($value)");
$db->setQuery($query);
$response = $db->loadResult();
 
if ($response == $value) {
return false;
} else {
return true;
}
Match or Replace: Match
* check for an existing number in the list Numeration
 
Hi,
I read that function mysqli_insert_id may do the work, but do not know how to implement it in Fabrik.

Please someone to share their experiences in a similar situation.

Regards
Sorry that I missed this thread until now - and I'm still not really certain just what you want to do -
BUT to get the last id of a newly inserted row in a table in Joomla 3.x, try something like...
$new_id = $db->insertid();
immediately after $db->execute();

I needed an answer to that question just the other day. Don't ask me why the JDatabase class decided to eliminate the underscore from the insert_id of the php mysqli_insert_id() function - but they did.:rolleyes:
 
Hi, Bauer
The idea that you provide me, may I use it otherwise, but this seems no longer relevant.
?fter long reflection on how to accomplish what I want and testing, I came to the decision that I have described above. I do not know if the code is the correct one, if something can be optimized for security, if necessary, but this way works.
Any feedback is very important.

What it does is the following:
In the list Numeration are assigned a group of numbers for a given user.
For each group are assigned sequential numbers for the types of documents that are needed.
In a new document according to the user who is logged in, take the sequential number for this type of document. After recording the document updated list Numeration as set new sequential number for this type of document.
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top