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
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'] : ''
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 : '') )
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?
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
