How the search field work.

[nub74]

Hi to all,
I need to understand how the search field works
because in some forms it filters the data while in others it simply highlights them without filtering.
I have a form with 30 pages and it would be better if I filtered the data I am looking for
instead of highlighting them to avoid fooling all the pages.
Thank you.

[kev1n]

because in some forms it filters the data while in others it simply highlights them without filtering.
.

Enter this command in the developer console, after you have performed the search:

nuCurrentProperties().browse_sql

Then you'll see the sql query and check if the syntax is correct (e.g. by executing the statement in phpMyAdmin)

[nub74]

Hello Kev1n,
Thanks for your quick response. I performed the check as you suggested,
and in fact the combination of the conditions set by me and those added by the search code
does not allow to filter the data correctly. If I delete a simple condition 'OR' the filter is applied correctly.
The problem is that I need that condition so that the form returns the right values;
how can this be solved?
Thank you.

[kev1n]

Can you show us the SQL as shown in the dev console?

[nub74]

Here is the sql:

SELECT id_riga,commessa,sus_name,task,note,ore,data
FROM registro_task LEFT JOIN tasks ON registro_task.id_task = tasks.id_task LEFT JOIN zzzzsys_user ON registro_task.id_operatore = zzzzsys_user.zzzzsys_user_id LEFT JOIN commesse ON registro_task.id_commessa = commesse.commessa LEFT JOIN livelli_view ON registro_task.id_operatore = livelli_view.id_operatore
WHERE tasks.id_reparto = (SELECT reparti.id_reparto FROM reparti LEFT JOIN operatori ON reparti.id_reparto = operatori.id_reparto WHERE operatori.id_nub = '5d3578bad8ecfe0') AND (livelli_view.livello < (SELECT livelli_view.livello FROM livelli_view LEFT JOIN registro_task ON livelli_view.id_operatore = registro_task.id_operatore WHERE ((registro_task.id_operatore = '5d3578bad8ecfe0')) AND ( (CONVERT(commessa USING utf8) LIKE "%1185%" OR CONVERT(sus_name USING utf8) LIKE "%1185%" OR CONVERT(task USING utf8) LIKE "%1185%" OR CONVERT(note USING utf8) LIKE "%1185%" OR CONVERT(ore USING utf8) LIKE "%1185%" OR CONVERT(data USING utf8) LIKE "%1185%") )
GROUP BY livelli_view.livello )) OR (registro_task.id_operatore='5d3578bad8ecfe0')

By deleting "OR (registro_task.id_operatore='5d3578bad8ecfe0')" the filter work fine.

[admin]

nub74,

If you have a WHERE clause in your SQL for a Form

you need to make sure it is surrounded by brackets because when filtering,

nuBuilder adds extra SQL.


Steven

[nub74]

Hi Steven,
I have already tried with the brackets but the result is the same.

[nub74]

Allow me to amend the last post:
the result changes but that OR condition adds more records to the search results anyway
but I need that condition.

[kev1n]

Can you also post the SQL you entered in the form's browse sql?

[nub74]

Hi Kev1n,
here is my sql:

SELECT
registro_task.*,
tasks.codice,
zzzzsys_user.sus_name,
commesse.commessa

FROM
registro_task
LEFT JOIN tasks ON registro_task.id_task = tasks.id_task
LEFT JOIN zzzzsys_user ON registro_task.id_operatore = zzzzsys_user.zzzzsys_user_id
LEFT JOIN commesse ON registro_task.id_commessa = commesse.commessa
LEFT JOIN livelli_view ON registro_task.id_operatore = livelli_view.id_operatore

WHERE
(
tasks.id_reparto = (SELECT reparti.id_reparto
FROM reparti
LEFT JOIN operatori ON reparti.id_reparto = operatori.id_reparto
WHERE
(operatori.id_nub = '#USER_ID#'))
AND
(livelli_view.livello < (SELECT livelli_view.livello
FROM livelli_view
LEFT JOIN registro_task ON livelli_view.id_operatore = registro_task.id_operatore
WHERE
((registro_task.id_operatore = '#USER_ID#')) GROUP BY livelli_view.livello ))
OR
(registro_task.id_operatore='#USER_ID#')
)