How the search field work.

Questions related to using nuBuilder Forte.

Re: How the search field work.

Postby kev1n » Fri Aug 23, 2019 3:48 pm

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

Re: How the search field work.

Postby nub74 » Fri Aug 23, 2019 4:08 pm

Hi Kev1n,
in fact I also tried this solution following one of the examples posted in the forum
(viewtopic.php?f=20&t=9953&hilit=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.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: How the search field work.

Postby kev1n » Sat Aug 24, 2019 6:45 pm

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:

Code: Select all
SELECT * FROM #TABLE_ID#
kev1n
 
Posts: 244
Joined: Mon Oct 15, 2018 2:13 am

Re: How the search field work.

Postby nub74 » Thu Sep 05, 2019 1:03 am

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

Re: How the search field work.

Postby admin » Thu Sep 05, 2019 9:53 am

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

Re: How the search field work.

Postby kev1n » Thu Sep 05, 2019 12:18 pm

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

Re: How the search field work.

Postby nub74 » Thu Sep 05, 2019 6:08 pm

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

Re: How the search field work.

Postby nub74 » Thu Sep 05, 2019 6:14 pm

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

Re: How the search field work.

Postby kev1n » Fri Sep 06, 2019 2:10 am

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

Re: How the search field work.

Postby kev1n » Fri Sep 06, 2019 2:10 am

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

PreviousNext

Return to General