# Run sql query as the result of complementary attribute **URL:** \ **Category:** Using my Dolibarr\ **Tags:** Extrafields\ **Created:** [November 25, 2024, 12:52am UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629 "2024-11-25T00:52:12Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 25, 2024, 12:52am UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/1 "2024-11-25T00:52:12Z")
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 ---
**Author:** ![pcbleu](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/pcbleu/32/3120_2.png) [@pcbleu](https://www.dolibarr.org/forum/u/pcbleu)\ **Post date:** [November 25, 2024, 4:58pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/2 "2024-11-25T16:58:35Z")
Hello You can make a try with this example 🙂 ![image](https://www.dolibarr.org/forum/uploads/dolibarrorg/original/2X/a/aa70029ded8ee9a09d4391606f73916a90130873.png) You can also build extrafunctions … ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 25, 2024, 7:09pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/3 "2024-11-25T19:09:32Z")
many thanks for your kind reply, i will try thanks ---
**Author:** ![pepex\_dolibarr](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/pepex_dolibarr/32/7998_2.png) [@pepex\_dolibarr](https://www.dolibarr.org/forum/u/pepex_dolibarr)\ **Post date:** [November 25, 2024, 11:03pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/4 "2024-11-25T23:03:43Z")
Necesito ayuda, no se como hacer para comentar en este foro, gracias! ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 27, 2024, 9:18pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/5 "2024-11-27T21:18:57Z")
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 ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 27, 2024, 9:20pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/6 "2024-11-27T21:20:24Z")
also tried this $db-\>query(‘select fk\_soc from llx\_facture where rowid=$objectoffield-\>id’) no errors but no value ---
**Author:** ![pcbleu](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/pcbleu/32/3120_2.png) [@pcbleu](https://www.dolibarr.org/forum/u/pcbleu)\ **Post date:** [November 27, 2024, 11:45pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/7 "2024-11-27T23:45:54Z")
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 ```auto (($resql = $db->query(sprintf('select fk_soc from llx_facture where rowid=%s', $objectoffield->id))) && ($obj = $db->fetch_object($resql))) ? $obj->fk_soc : '' ``` ![image](https://www.dolibarr.org/forum/uploads/dolibarrorg/original/2X/0/0dcaf059ab9b6404feff46c36a1ecd70aa6418f3.png) Good continuation ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 28, 2024, 12:06am UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/8 "2024-11-28T00:06:56Z")
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 ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 28, 2024, 12:24am UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/9 "2024-11-28T00:24:43Z")
> [@Ghonz](#): > > (($resql = $db-\>query(sprintf(‘select fk\_soc from llx\_facture where rowid=19’, $objectoffield-\>id))) && ($obj = $db-\>fetch\_object($resql))) ? $obj-\>fk\_soc : ‘’ rowid=%s not working rowid=19 working what alternative for %s ---
**Author:** ![pcbleu](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/pcbleu/32/3120_2.png) [@pcbleu](https://www.dolibarr.org/forum/u/pcbleu)\ **Post date:** [November 28, 2024, 12:29am UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/10 "2024-11-28T00:29:10Z")
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 ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 28, 2024, 12:50am UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/11 "2024-11-28T00:50:06Z")
> [@pcbleu](#): > > (($resql = $db-\>query(implode( ’ ', array( ‘select fk\_soc from llx\_facture where rowid=’, $objectoffield-\>id) ))) && ($obj = $db-\>fetch\_object($resql))) ? $obj-\>fk\_soc : ‘’ 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 ---
**Author:** ![pcbleu](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/pcbleu/32/3120_2.png) [@pcbleu](https://www.dolibarr.org/forum/u/pcbleu)\ **Post date:** [November 28, 2024, 8:07am UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/12 "2024-11-28T08:07:24Z")
Hey 🙂 you can try ```auto 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 ```auto ($objectoffield->fetch_thirdparty()) ? $objectoffield->thirdparty->array_options['options_default_rate'] : '' ``` ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 28, 2024, 11:27am UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/13 "2024-11-28T11:27:37Z")
> [@pcbleu](#): > > `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 one not working return empty value but the second one is working return the correct value thanks 😍 ---
**Author:** ![pcbleu](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/pcbleu/32/3120_2.png) [@pcbleu](https://www.dolibarr.org/forum/u/pcbleu)\ **Post date:** [November 28, 2024, 1:21pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/14 "2024-11-28T13:21:14Z")
That’s just a missing parenthesis at the end of the formula… > [@pcbleu](#): > > 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 : ‘’) ) ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 28, 2024, 1:34pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/15 "2024-11-28T13:34:11Z")
> [@pcbleu](#): > > ```auto > ($objectoffield->fetch_thirdparty()) ? $objectoffield->thirdparty->array_options['options_default_rate'] : '' > > ``` working > [@pcbleu](#): > > 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 : ‘’) ) not working it return zero the other working formula dont update llx\_facture\_extrafields whith the default\_rate value it still null ---
**Author:** ![pcbleu](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/pcbleu/32/3120_2.png) [@pcbleu](https://www.dolibarr.org/forum/u/pcbleu)\ **Post date:** [November 28, 2024, 1:38pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/16 "2024-11-28T13:38:07Z")
```auto 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 : '') ) ``` ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 28, 2024, 1:51pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/17 "2024-11-28T13:51:10Z")
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 ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 28, 2024, 10:55pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/18 "2024-11-28T22:55:38Z")
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? ---
**Author:** ![pcbleu](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/pcbleu/32/3120_2.png) [@pcbleu](https://www.dolibarr.org/forum/u/pcbleu)\ **Post date:** [November 28, 2024, 11:32pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/19 "2024-11-28T23:32:53Z")
Beware with the quote characters because in some posts, these characters may be changed… ![image](https://www.dolibarr.org/forum/uploads/dolibarrorg/original/2X/6/673ccc488d4ec0225c396da9526636968324dd9f.png) This formula is fully ok in Dolibarr version 20.0.0 Good continuation ---
**Author:** ![Ghonz](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/g/9dc877/32.png) [@Ghonz](https://www.dolibarr.org/forum/u/Ghonz)\ **Post date:** [November 28, 2024, 11:57pm UTC](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629/20 "2024-11-28T23:57:16Z")
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 ![Screenshot 2024-11-29 at 1.51.44 AM](https://www.dolibarr.org/forum/uploads/dolibarrorg/original/2X/1/1bbd728d69b2981ca563c380ad408454f5c09277.png) [Next page](https://www.dolibarr.org/forum/t/run-sql-query-as-the-result-of-complementary-attribute/29629.md?page=2)