Hello
for 2 days i am looking for how to run sql query as a result of complementary attributes
select field1 from table1 where rowid = value
what the valid syntax to run such query and get the result in extra field
thanks
many thanks for your kind reply, i will try
thanks
Necesito ayuda, no se como hacer para comentar en este foro, gracias!
HI
it return selection list not result, in invoice line i need to get value from llx_societe where rowid in llx_societe = fk_soc in llx_facture
i read many posts and tried many formulas but still cannot do it
also tried this
$db->query(‘select fk_soc from llx_facture where rowid=$objectoffield->id’)
no errors but no value
Hello 
for this result you can use $objectoffield->socid, if you read the file /compta/facture/class/facture.class.php, the ‘socid’ field is loaded with the ‘fk_soc’ value…
can you try this, for executing a sql query
(($resql = $db->query(sprintf('select fk_soc from llx_facture where rowid=%s', $objectoffield->id))) && ($obj = $db->fetch_object($resql))) ? $obj->fk_soc : ''
Good continuation
Bad string syntax to evaluate (found chars that are not chars for a simple clean eval string): (($resql = $db->query(sprintf(‘select fk_soc from llx_facture where rowid=%s’, $objectoffield->id))) && ($obj = $db->fetch_object($resql))) ? $obj->fk_soc : ‘’
but i used $objectoffield->fetch_thirdparty()
the result was 6 which is correct, now i need to get extra field value from llx_societe_extrafields field name “default_rate” for fk_object = 6 (i shouldnot enter number here and use variable instead which equal 6 for this thirdpaty
however i tried select from table and use societe_extrafields:default_rate:rowid::(fk_object:=:6) it works but i have to edit the selection and choose the only filtered result for fk_object 6 , i tried many way to change 6 with variable like socid but nothing work
rowid=%s not working
rowid=19 working
what alternative for %s
Hello
I made the first tests with version 17…
on version 20, you must use this
(($resql = $db->query(implode( ' ', array( 'select fk_soc from llx_facture where rowid=', $objectoffield->id) ))) && ($obj = $db->fetch_object($resql))) ? $obj->fk_soc : ''
Good continuation
1 Like
this work
i follow this for next step to get default rate from thirdparty extra fields
(($resql = $db->query(implode( ’ ', array( ‘select default_rate from llx_societe_extrafields where fk_object=6’, $objectoffield->default_rate) ))) && ($obj = $db->fetch_object($resql))) ? $obj->default_rate : ‘’
the question is what the variable to put for fk_object= (variable) instead of 6 which represent thirdparty id in the invoice
Hey
you can try
implode( ' ', array( (($resql = $db->query(implode( ' ', array( 'select default_rate from llx_societe_extrafields where fk_object=', $objectoffield->socid)))) && ($obj = $db->fetch_object($resql))) ? $obj->default_rate : '')
or
($objectoffield->fetch_thirdparty()) ? $objectoffield->thirdparty->array_options['options_default_rate'] : ''
1 Like
this one not working return empty value but the second one is working return the correct value
thanks 
That’s just a missing parenthesis at the end of the formula…
working
not working it return zero
the other working formula dont update llx_facture_extrafields whith the default_rate value it still null
implode( ' ', array( (($resql = $db->query(implode( ' ', array( 'select default_rate from llx_societe_extrafields where fk_object=', $objectoffield->socid)))) && ($obj = $db->fetch_object($resql))) ? $obj->default_rate : '') )
1 Like
this work find thanks but the llx_facture_extrafields default_rate field is null
i think this should be updated with the formula result or i miss something
Hello pcbleu
sorry to buzzer you, i have a question the database not updated with the calculated attributes without adding manual field like check box or something else and if not i found all extra fields with calculation formula null in the database, i suppose there is an update command could be added to the formula so is the formula is implode( ’ ', array( (($resql = $db->query(implode( ’ ', array( ‘select defaultrate from llx_societe_extrafields where fk_object=’, $objectoffield->socid)))) && ($obj = $db->fetch_object($resql))) ? $obj->defaultrate : ‘’) )
what the update command follow to it?
Beware with the quote characters
because in some posts, these characters may be changed…
This formula is fully ok in Dolibarr version 20.0.0
Good continuation
it the same as i see here is mine attached, this formula show the value on screen but dont update db without adding another manual extra field, i have to add any free text or integer extra field, once i edit its value the db updated with all fields except the integer fields always null and have to change from integer to float to be updated which is strange
