DatabaseJoin With Filter - Based on DropDown Value

[tiagovareta]

Hi,

I am developing an application for control / register boxes.
In this case to register (input / output), on the client, I have:
1 - Customer Choice Field (databasejoin)
2 - Field of Choice of the Type of Record I enter / exit (dropdown)
3 - Box Selection Field (databasejoin)


So far everything works right!

But now I have a situation!
When I choose the "2 b" option (the client returned the box), the "3" box field should only show the boxes that the client can return, meaning that it has registration as delivered on the client.

How can I apply this filter, whenever the field "2" has a value of 1 (if it has a value of 2, should it show all the boxes)?

 

[troester]

In your databasejoin (3) add a "Data where" MySql condition using {your-element2} (or {your-element2_raw}) placeholder and enable "Ajax update"

 

[tiagovareta]

In your databasejoin (3) add a "Data where" MySql condition using {your-element2} (or {your-element2_raw}) placeholder and enable "Ajax update"

Hi Troester,

Many thanks for the reply! But I still can not figure it out.

Further detailing the operation:
- I have a table where I register the boxes;
- I have a table where I select the client and register the movements of the boxes;

In this dropdown, I put the code:
WHERE {thistable}.`id`
IN ( SELECT iwrjz_avg_clientes_registo_11_repeat.caixa_rclt FROM iwrjz_avg_clientes_registo_11_repeat
WHERE iwrjz_avg_clientes_registo_11_repeat.id_cliente = '{id_cliente_rclt_raw}')

The dropdown does not return anything, but if you change the line:
WHERE iwrjz_avg_clientes_registo_11_repeat.id_cliente = '10')

The dropdown, returns the boxes in which the client had registration (inputs / outputs), but I just want to return the boxes that he may have in stock. Basically applying this query:
"SELECT sum(quantidade_mais_menos_rclt) FROM iwrjz_avg_clientes_registo_11_repeat WHERE caixa_rclt = {thistable}.`id`"

Summarizing everything:
1º - The Filter will only be applied, when we choose the option "2 B" of Field 2;
2º - When choosing the option "2 B", you will have to calculate the registers (entries and exits) of boxes, and only present the boxes that have a value greater than 0;


- How do we get the filter to be applied only by choosing the "2 B" option in Field 2?
- The fact that the ( WHERE iwrjz_avg_clientes_registo_11_repeat.id_cliente = '{id_cliente_rclt_raw}') ), does not work, can it be because the field "id_cliente_rclt" is in a different group than the other fields?
- How to construct the filter with these two expressions:
WHERE {thistable}.`id`
IN ( SELECT iwrjz_avg_clientes_registo_11_repeat.caixa_rclt FROM iwrjz_avg_clientes_registo_11_repeat
WHERE iwrjz_avg_clientes_registo_11_repeat.id_cliente = '{id_cliente_rclt_raw}')
+
"SELECT sum(quantidade_mais_menos_rclt) FROM iwrjz_avg_clientes_registo_11_repeat WHERE caixa_rclt = {thistable}.`id`"

Thanks for your help and availability!

 

[troester]

'{id_cliente_rclt_raw}'
element placeholders have 3 underscores between table and element name, so I assume here your table name is missing.
'{your-table___id_cliente_rclt_raw}'

 

[tiagovareta]

'{id_cliente_rclt_raw}'
element placeholders have 3 underscores between table and element name, so I assume here your table name is missing.
'{your-table___id_cliente_rclt_raw}'

Hi Troester! With the name of the table, I can already fill the dropdown, after selecting the client

Now this filter is applied to any line you add. But I wanted it to be applied only by selecting the "2 B" option of Field 2;
If I selected the "2 A" option, I wanted the dropbox to be populated with all the boxes.
That is the content (dropdown list) of Dropdown 3, will depend on the option chosen in field 2.

Is it possible to do this?

 

[cheesegrits]

If I'm reading your requirements right, that you need a different WHERE clause depending on the value of element 2, then you'd need to use something like an IF() statement ...

WHERE id IN (IF('{yourtable___element2}' = '1', SELECT ...., SELECT ...)

... so you have two SELECT statement, one which gets executed if element2 = 1, and another for any other value.

-- hugh

 

[tiagovareta]

If I'm reading your requirements right, that you need a different WHERE clause depending on the value of element 2, then you'd need to use something like an IF() statement ...

WHERE id IN (IF('{yourtable___element2}' = '1', SELECT ...., SELECT ...)

... so you have two SELECT statement, one which gets executed if element2 = 1, and another for any other value.

-- hugh

Hi Cheesegrits!

Thank you very much for the response and availability! I think I can do what I want now