Question about your in "${input:identifiant[]}" tutorial

Hello and best wishes for a happy and prosperous new year!

i have set up my query (that contains left joins and a WHERE clause). The query i have set works pretty fine before using the "${input:identifiant[]}" example that you have provided in the forum.

Details...

Field with alias "customer-order-number" has to be reloaded according to the selection of field with alias "reported-customer".

So we have 2 fields:
1. reported-customer (selectsql - Make reloadable: No)
2. customer-order-number (selectsql - Make reloadable: Yes - Hide field, if list is empty: No - Preselect solitary option: No - Reload on change of: "reported-customer" field)

We query: a.order_number AS label and a.order_id AS value

The WHERE part of my sql query for "customer-order-number" is... ( WHERE order_parent_id !='0' AND e.id='${user:id}'; ) and it works ok!

Following your example...
select f_label as label,f_value as value from #__adherent where f_label in ${input:identifiant[]}

i change the WHERE part of the query to... ( WHERE a.order_number in ${input:reported-customer[]} AND order_parent_id !='0' AND e.id='${user:id}'; ) but it is not working anymore

Can you please advise?

Thank you in advance!

In order to make more clear what the issue is we are explaining again...

Here is my sql query... (trimed)

SELECT a.fields, b.fields, c.fields, d.fields, e.fields, f.fields
FROM #__bla1 a
left join #__bla2 f on f.ord_id = a.ord_id
left join #__bla3 b on b.ven_id = a.ord_ven_id
left join #__bla4 c on c.us_id = a.ord_us_id
left join #__bla5 d on d.id = c.us_cm_id
left join #__bla6 e on e.nam = b.ven_nam
WHERE ord_par_id !='0' AND e.id='${user:id}' AND f.ord_pro_nam; = 'some_value';

So far it works like a charm when used in the "SQL-Select-Statement *" field of visforms.!!!

If i change the last line to...

WHERE ord_par_id !='0' AND e.id='${user:id}' AND f.ord_pro_nam; in ${input:solpro[]};

("solpro" is the alias of the field that triggers that the options list is reloaded), NOTHING IS RELOADED.

Also tested with...

WHERE ord_par_id !='0' AND e.id='${user:id}' AND f.ord_pro_nam; in ${input:repcus[]};

("repcus" is the alias of the field that needs to be reloaded when another field triggers the reload), NOTHING IS RELOADED.


Can you please advise what we are missing?

Hi,

I'm sorry, for the late response, but as you might have notice, we were closed til today.

Using the input object in the select sql statement works fine.
I have set it up hundreds of times on different websites.
Nevertheless it is sometimes really difficult to get the sql statement right and sadly it is very difficult to debug.
It might well be possible, that you have different errors in the different test cases you describe.
Therefore I will only concentrate on your real use case (reported-customer).

WHERE a.order_number in ${input:reported-customer[]}

This would mean, that only records are selected which have in the database field a.order_number a value, that matches the value of the option which is selected in the form field with alias "reported-customer".

Example:
Your form field "reported-customer" has 3 options with the values 1, 2 and 3.
The user selects the Option with value 3.
According to your SQL Statement only record sets, with a value of 3 in the database field a.order_number would be selected.
Are you sure, that the selectable option values of the "reported-customer" field contain values, which are available in the a.order_number database field?

Regards,
Aicha