AppGini Community Forums

Posted: **2023-01-09 07:39**

Hi guys,
I have a very frustrating problem. I have this query in a lookup field in the 'cases' table:

SELECT `cases`.`id`, IF(CHAR_LENGTH(`cases`.`title`) || CHAR_LENGTH(`cases`.`id`), CONCAT_WS('', `cases`.`title`, ' - ', `cases`.`id`), '') FROM `cases` LEFT JOIN `problems` as problems1 ON `problems1`.`id`=`cases`.`problem` LEFT JOIN `cases` as cases1 ON `cases1`.`id`=`cases`.`preceeding_case` LEFT JOIN `case_types` as case_types1 ON `case_types1`.`id`=`cases`.`case_type` LEFT JOIN `case_subtypes` as case_subtypes1 ON `case_subtypes1`.`id`=`case_types1`.`case_subtype` LEFT JOIN `organizations` as organizations1 ON `organizations1`.`id`=`cases`.`organization` LEFT JOIN `currencies` as currencies1 ON `currencies1`.`id`=`cases`.`currency` WHERE `cases`.`id` <>  '%ID%' ORDER BY `cases`.`id` DESC

The idea here is to populate all records from 'cases' table without the actual record that we are in right now. The problem is that this record still shows up. If I replace %ID% with an actual id like 394 for example it hides it just like it has to be. In the same table I have another calculated field with this code:

Code: Select all

SELECT DATEDIFF(`cases`.`end_date`, `cases`.`date`) FROM `cases` WHERE `id` = '%ID%';

And this one calculation is working perfectly fine.
I can't understand why %ID% is not working in the lookup field.

Posted: **2023-01-09 13:43**

I have this query in a lookup field in the 'cases' table

As far as I know "Advanced Query" for lookup fields does not support variables. I doubt that the string %ID% is automatically resolved, as I have never seen such a feature in any AppGini documentation I know.

Check ajax_combo.php:
If you have an Advanced Query, there should be a value in custom_query item of $lookup array. In ajax_combo.php there is a code-block starting just below the comment // define the combo and return the code. There are a few operations on the custom query for sorting etc. but I cannot see any %PLACEHOLDER% replacement.

---

And this one calculation is working perfectly fine.

Please do not mix up the SQL function for Calculated Fields with the Advanced Query SQL function for Lookup Fields: Placeholders are supported for Calculated Fields but not (yet?) for Lookups.

Calculated Field

: AppGini_t3HX7qI4x9.png (4.65 KiB) Viewed 1903 times

(Various variables here)

Lookup Field / Advanced Query

: AppGini_zyQ9pf9Q8D.png (12.04 KiB) Viewed 1903 times

(No variables here)

Posted: **2023-01-10 07:09**

thanks a lot jsetzer,
It is a shame that it is not supported for the lookup fields.
I guess ill need to to it in JS

AppGini Community Forums

Lookup field %ID% not working

Lookup field %ID% not working

Re: Lookup field %ID% not working

Re: Lookup field %ID% not working