How the search field work.

Questions related to using nuBuilder Forte.

How the search field work.

Postby nub74 » Wed Aug 21, 2019 5:36 pm

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.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: How the search field work.

Postby kev1n » Wed Aug 21, 2019 7:07 pm

nub74 wrote: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)
kev1n
 
Posts: 244
Joined: Mon Oct 15, 2018 2:13 am

Re: How the search field work.

Postby nub74 » Wed Aug 21, 2019 10:09 pm

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.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: How the search field work.

Postby kev1n » Thu Aug 22, 2019 1:25 am

Can you show us the SQL as shown in the dev console?
kev1n
 
Posts: 244
Joined: Mon Oct 15, 2018 2:13 am

Re: How the search field work.

Postby nub74 » Thu Aug 22, 2019 1:46 am

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.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: How the search field work.

Postby admin » Thu Aug 22, 2019 7:53 am

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
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am

Re: How the search field work.

Postby nub74 » Thu Aug 22, 2019 3:46 pm

Hi Steven,
I have already tried with the brackets but the result is the same.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: How the search field work.

Postby nub74 » Thu Aug 22, 2019 4:08 pm

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.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: How the search field work.

Postby kev1n » Thu Aug 22, 2019 8:50 pm

Can you also post the SQL you entered in the form's browse sql?
kev1n
 
Posts: 244
Joined: Mon Oct 15, 2018 2:13 am

Re: How the search field work.

Postby nub74 » Fri Aug 23, 2019 12:06 am

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#')
)
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Next

Return to General