Calculation formula based on extra field from another table


I created an extra fiels on invoices (called “program”) to allow user to select a service (so « rowid » of this service is stored in array_options[‹ options_program ›]). Do do that, I create a list from a table and I put following value :

Now, I would like to create a second extra field where I would like to retrieve automaticaly the “description” on the service selected in my extrafield “program”. What code do I need to put in dolibarr field dedicated to the calculation of this new extrafield?

By searching on the internet and on the forum, I tested following 3 ways:

$SEL$ description FROM llx_product WHERE rowid=$objectoffield->array_options['options_program']

mysqli_fetch_array($resql = $db->query(sprintf('SELECT description FROM llx_product WHERE rowid = "' %s $objectoffield->array_options['options_program'] %s "', chr(46), chr(46))))["description"];

fetch_array($db->query("SELECT description FROM llx_product WHERE rowid = ?", $objectoffield->array_options['options_program']))['description']

Unfortunatly, noone seems understood by Dolibarr.

Thank you by advace for you help :grin:

Best regards,

Hello :slight_smile:

Try this :

(($myobj = new Product($db)) && ($myobj->fetchNoCompute($objectoffield->array_options[‘options_program’]))) > 0 ? $myobj->description: ’ ’

Good continuation

Hello @pcbleu

Thank you for your help

(($myobj = new Product($db)) && ($myobj->fetchNoCompute($objectoffield->array_options[‘options_program’]))) > 0 ? $myobj->description: ’’

is well working.

So now, I would like to do a little bit more complex: I want to retrieve list of countries from the extra field “country” of the service (can be multiple values, comma separated) linked to the invoice (still via the extrafield “program”). So I did the same:

(($myobj = new Product($db)) && ($myobj->fetchNoCompute($objectoffield->array_options[‘options_program’]))) > 0 ? $myobj->array_options[‘options_country’]: ’’

Unfortunatly, this formula is bringing list of countries IDs (instead of labels) coma separated.
So I tried the double look-up to retrieve labels from c_country table:

(($country = new c_country($db)) && ($myobj->fetchNoCompute((($myobj = new Product($db)) && ($myobj->fetchNoCompute($objectoffield->array_options[‘options_program’]))) > 0 ? $myobj->array_options[‘options_country’]: ’’))) > 0 ? $country->label: ’’

But this is not working :slightly_frowning_face:

Do you understand what is my mistake?

Thanks a lot by advance,

Hello :slight_smile:

About countries, there is a Ccountry class in core/class/ccountry.class.php - but it is not possible to use this class in extrafields because this class is not loaded…

To do what you want, you must build and execute a query on your llx_c_country table, where “v3wn_” is the prefix you use…

With this following formula, you can get the result you expect…
($myval=((($myobj = new Product($db)) && ($myobj->fetchNoCompute($objectoffield->array_options[‘options_program’]))) > 0 ? $myobj->array_options[‘options_country’]: ‘0’)) &&
($sql=dol_concatdesc(‘SELECT label FROM v3wn_c_country WHERE rowid =’, $myval)) &&
($resql = $db->query($sql)) &&
($obj = $db->fetch_object($resql)) > 0 ? $obj->label : ‘’

Good continuation…


Thank you @pcbleu but this is not working :frowning:

((($myobj = new Product($db)) && ($myobj->fetchNoCompute($objectoffield->array_options[‘options_program’]))) > 0 ? $myobj->array_options[‘options_country’]: ‘0’)

is well retrivening the list of country IDs

($sql=dol_concatdesc(‘SELECT label FROM llx_c_country WHERE rowid =’, ‘161’)) &&
($resql = $db->query($sql)) &&
($obj = $db->fetch_object($resql)) > 0 ? $obj->label : ‘’

is well retriving a country name if I put an ID manualy (161 in my example)

But in:

dol_concatdesc(‘SELECT label FROM llx_c_country WHERE rowid =’, $myval)

variable $myval seems to not work

Besides, if there are several values selected in the country field, the SELECT is not working. I changed it to use a “IN” instead of “=” exemple:

SELECT label FROM llx_c_country WHERE rowid IN (161, 163)

But, even with a “IN” in the SQL, following PHP

($resql = $db->query($sql)) && ($obj = $db->fetch_object($resql)) > 0 ? $obj->label : ‘’

returns only the label of the first country not the 2 comma separeted (maybe a implode() need to be used?)

Thanks and best regards

Hello :slight_smile:

Is this formula ok for an unique country id ?

How do you build your multi id extrafield named “country” for products ?

Hi @pcbleu,

Sure, here is the setup of my extrafield “Country” on services:

Actually no:
If I select only one value :

((($myobj = new Product($db)) && ($myobj->fetchNoCompute($objectoffield->array_options[‘options_program’]))) > 0 ? $myobj->array_options[‘options_country’]: ‘0’)

retrieve well the country ID (in the example I gave, if I select “Namibia” on the associated service, the field is well dispalying the corresponding id: 161)
The next part of the formula you provided is also well working when I replace manualy the variable “$myval” with the ID:

($sql=dol_concatdesc(‘SELECT label FROM llx _c_country WHERE rowid =’, 161 )) &&
($resql = $db->query($sql)) &&
($obj = $db->fetch_object($resql)) > 0 ? $obj->label : ‘’

This is well displaying “Namibia”.

But when I tried the combinasion of both, I can see the variable $myval is not working:

($myval=((($myobj = new Product($db)) && ($myobj->fetchNoCompute($objectoffield->array_options[‘options_program’]))) > 0 ? $myobj->array_options[‘options_country’]: ‘0’)) && ($sql=dol_concatdesc(‘SELECT label FROM llx _c_country WHERE rowid =’, $myval )) &&
($resql = $db->query($sql)) &&
($obj = $db->fetch_object($resql)) > 0 ? $obj->label : ‘’

(nothing is displaid)

Thank you by advance for your help! :grin:

Hello :slight_smile:

I didn’t know checkboxes from table for extrafield definition

What you want is possible, but not easy to obtain with calculated fields
a solution would be to create a new plugin to achieve this

Good continuation

I have a similar issue with this topic. I hope you can help if possible.
I want to create a new field recording the remaining unpaid balance for each customer. If I use it in the “societe” module, I can use the formula “$object->getOutstandingBills(‘customer’, 0)[‘opened’]”. But I want to print it on the invoices sent to customers, so I think I need to create an extra field similar to this in the “facture” module, or somehow print the “unpaid” extra field from the “societe” module on the invoice using the ODT form. I don’t know how to call the calculation formula from another module. Thank you very much.


You need to create a new computed field on the invoice module, and put this formula :

(($reloadedobj = new Societe($db)) && ($reloadedobj->fetchNoCompute($objectoffield->rowid)) && ( $ret=$reloadedobj->getOutStandingBills())
) ? $ret['opened']: 'Not found'

Thanks for your help, but for me it return 0.


Thank you for your replies (sorry for the delay I had heathcare issues).

It would be great! I never created a pluggin for now, I tryed to read the wiki page of the module builder but didn’t understand how it can be used to add button on existing module (like invoicing). Do you know if there is a tutorial for newbees somewhere?

Thank you!