Page 2 of 3
Re: How the search field work.
Posted: Fri Aug 23, 2019 8:18 am
by kev1n
I'm not sure if you can solve this by using brackets. An alternative way is to create a temporary table in Before Browse (PHP)
Re: How the search field work.
Posted: Fri Aug 23, 2019 8:38 am
by nub74
Hi Kev1n,
in fact I also tried this solution following one of the examples posted in the forum
(
https://forums.nubuilder.cloud/viewtopic. ... t=table_id)
but it does not create the temporary table for me (should it be visible in phpmyadmin?).
Looking at the debug I see that no errors are even generated. However, this seems to be the right way to proceed.
What is wrong with the example of the post mentioned above?
Thank you.
Re: How the search field work.
Posted: Sat Aug 24, 2019 11:15 am
by kev1n
Your PHP Before Browse code should look like this:
Code: Select all
$s = " CREATE TABLE #TABLE_ID#
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#')
)
";
nuRunQuery($s);
And the Browse SQL:
Re: How the search field work.
Posted: Wed Sep 04, 2019 5:33 pm
by nub74
Hi Kev1n,
the code above does not work for me as I get the "Unknown column 'task' in field list" error,
but there is no a field with this name in fact using the query as SQL source has always worked.
However the temporary table cannot be a solution for me as I don't have to limit myself to filtering data
but I also have to write new records in the table and in this way I would write them in the temporary table
instead of in my work table. What other solution can we find to filter data correctly?
I really need a solution because I only need this step to finish my project.
Thank you very much.
Re: How the search field work.
Posted: Thu Sep 05, 2019 2:23 am
by admin
nub74,
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')
I believe the double brackets in the middle of your GROUP BY clause is the problem.
Steven
Re: How the search field work.
Posted: Thu Sep 05, 2019 4:48 am
by kev1n
nub74 wrote:
However the temporary table cannot be a solution for me as I don't have to limit myself to filtering data
but I also have to write new records in the table and in this way I would write them in the temporary table
.
The temporary table is just used for the Browse View, not the Edit Screen.
Re: How the search field work.
Posted: Thu Sep 05, 2019 10:38 am
by nub74
Hi kev1n,
somehow I managed to make the temporary table work.
Now I would understand how nuBuilder works.
You wrote:
The temporary table is used only for the Browse view, not for the Edit screen.
I added some records to do some tests but apparently they reside only in the temporary table
because they don't exist in the original table from which I created the temporary one.
Is it correct? Should not the new records and their edits appear in the original table
based on what you wrote?
Thank you.
Re: How the search field work.
Posted: Thu Sep 05, 2019 10:44 am
by nub74
And one last question:
is it possible to use the search field to search for multiple data or I need to write custom code for it?
Thank you.
Re: How the search field work.
Posted: Thu Sep 05, 2019 6:40 pm
by kev1n
nub74 wrote:
I added some records to do some tests but apparently they reside only in the temporary table
because they don't exist in the original table from which I created the temporary one.
Is it correct? Should not the new records and their edits appear in the original table
based on what you wrote?
Thank you.
Yes, newly created records should be saved in the original table (the one you entered in the settings, Table Name field). The other temp. table is only used for Browse, has no impact on how/where the records are saved.
Re: How the search field work.
Posted: Thu Sep 05, 2019 6:40 pm
by kev1n
nub74 wrote:And one last question:
is it possible to use the search field to search for multiple data or I need to write custom code for it?
Thank you.
It's better to create a new topic for that.