advanced search - browse table and filter

[Tinka]

I am trying to do an advanced search/filter in a form which collects data from many tables in a query.

I can't get the temporary #browseTable# to work, and tried the solutions in these posts:

http://forums.nubuilder.cloud/viewtopic.p ... rary+table

In nuDebug it looks like the temporary browse table is never created.

I want to do the same as in this post:
http://forums.nubuilder.cloud/viewtopic.p ... ble#p12289
Here, Max writes:

you can build a custom form (also with some code in Custom Code > Before Browse to create a temporary table), with all objects you want to use as filters and a button to call openBrowse() function;

Can you provide a step-by-step example? Is the #browseTable# discontinued in nubuilderPro?

Starting simple, I have this in BeforeBrowse:

Code: Select all

$sql = "CREATE TABLE '#browseTable#'";
$sql .= " select * from primer ";
nuDebug('run1-> '.$sql);

nuRunQuery($s);

And this on the form sql:

Code: Select all

select * from #browseTable#

Tinka

[Tinka]

Nobody has done a search screen? I am still struggling!

I tried to do the same as in this post http://forums.nubuilder.cloud/viewtopic.p ... wse+filter.

So I created a form with text fields for search parameters. I call the form with a button record -1 and removed all action buttons. It is based on the same table where I want to search.
The text objects should provide the hash variables for my sql query. I created a button which opens the same form as browse form. On both the sql of the form and the BeforeBrowse I have this (just one search criteria for a start).

Code: Select all

$sql="
Select p.*,chr.*, gp.* 
from primer p
Left join gene g ON g.gene_id = p.pri_gene_idFK
Left join genomic_position gp ON gp.genpos_id = p.pri_genpos_idFK
Left join chromnr chr ON chr.chromnr_id = gp.genpos_chrnr_idFK
WHERE p.prim_name = '#FindName#'";

nuDebug($sql)
nuRunquery($sql)

I can see in the debug, that the hash variable does not get replaced. Is this because the value gets lost when I reopen the same form?
I tried rewriting the sql in BeforeBrowse with

Code: Select all

  $this->form->sfo_sql="SELECT * FROM xxx" 

as described here http://forums.nubuilder.cloud/viewtopic.p ... wse#p10953 resulting in an error.


I also tried with a browse object on my form instead of reopening the same form, and after setting (just) one field, clicking on the integrated Search button (Nubuilder search filter) in the browse object, but it only accepts inputs from its associated search field.
This solution was suggested in another post (which I cant find again).

I also tried to build a temporary browse table with my filtered results, and discovered that you have to use #Table_id# instead of #BrowseTable#, this works and creates a temporary table.
I know I need to define a filter with the hash variables and then use it for a browse or temporary table, should I use OpenBrowse()?

Also, maybe, one could use the built-in search filter, like here:
http://forums.nubuilder.cloud/viewtopic.p ... rch#p12540

Can some one help? Max? Steven? Fike?

BR, Tinka

[massiws]

Tinka, if you are running nuBuilderPro you must look at this documentation.
If you find a solution in this forum that refer to nuBuilder 2, you have to "translate" it to nuBuilderPro: system tables and hash variables have different names (correct name is #TABLE_ID#, not #Table_id#) , etc.

Max

[Tinka]

Max,

Yes I am running NubuilderPro and try to recreate the "advanced DIY search screen" solution from http://forums.nubuilder.cloud/viewtopic.p ... wse+filter. I find it hard to figure out what has been changed since NuBuilder 2 and how to rethink the whole code (problems need to be solved in a different way).

I read the documentation a dozen times, watched all 18 videos and searched the forums - and I think all of it could be further improved. Like providing showcases with examples of the most common problems and their solutions, and the most wanted functionality for applications in general. Developers could share their good solutions and make them easier to find instead of they are drowning in multiple forums. Well, just a suggestion.

Can you tell why the search screen from http://forums.nubuilder.cloud/viewtopic.p ... wse+filter does not work this way in NuBuilderPro?
Also, I cannot find anything on the OpenBrowse() function and associated filters.

BR, Tinka

[admin]

Tinka,

Try using #TABLE_ID# instead of #browseTable#.

Steven

[Tinka]

Steven -
Yes, I have done this like I wrote at the end of my previous post.

I also tried to build a temporary browse table with my filtered results, and discovered that you have to use #Table_id# instead of #BrowseTable#, this works and creates a temporary table.
I know I need to define a filter with the hash variables and then use it for a browse or temporary table, should I use OpenBrowse()?

The form my Search button opens has this in the General-SQL:

Code: Select all

Select * FROM #TABLE_ID#

- it fails, the table can't be found.
So I replaced the SQL by the same as in Before Browse but without a WHERE clause:

Code: Select all

Select p.*,chr.*, gp.* 
from primer p
Left join gene g ON g.gene_id = p.pri_gene_idFK
Left join genomic_position gp ON gp.genpos_id = p.pri_genpos_idFK
Left join chromnr chr ON chr.chromnr_id = gp.genpos_chrnr_idFK

and get an unfiltered table, it seems that the Before Browse code is not executed.

In Before Browse I have this (selecting a particular record named "test5" until I find out how to do the filter by hash variables since inserting a hash variable here will not replace it):

Code: Select all

$sql_tbl="

Select p.*,chr.*, gp.* 
from primer p
Left join gene g ON g.gene_id = p.pri_gene_idFK
Left join genomic_position gp ON gp.genpos_id = p.pri_genpos_idFK
Left join chromnr chr ON chr.chromnr_id = gp.genpos_chrnr_idFK
WHERE p.prim_name = 'test5'";

$sql_cre = "CREATE TABLE #TABLE_ID# ".$sql_tbl
nuRunquery($sql_cre)

So I am still stuck with this search screen.

Can the function NuGetBrowseRecords be used somehow?

Maybe this post has part of the solution:
http://forums.nubuilder.cloud/viewtopic.p ... rch#p12540

Any (quick) help is greatly appreciated!

Tinka

[admin]

Tinka,

If you send a copy of your database to info@nubuilder.com I'll take a look at it.

Let me know where you are trying to create the browse and anything else that might help.

Steven

[Tinka]

Steven,

I sent a copy and explanations and hope you can help. Thank you for taking the time!

Anyone else is still welcome to share their ideas.

BR, Tinka

[admin]

Tinka,

I have now uploaded a fix to github to fix that problem. (a Browse Form can now access the Fields from the Edit Form that calls it, with Hash Variables.)

But yours still wont work until your field name matches.

Your Object's name is SrcPriName but in Before Browse you have '#ScrPriName#' (Scr not Src)

Steven

[Tinka]

Steven,

Phantastic! Just one line of code. Than you for pinpointing my type error.

But it seems that the BeforeBrowse code on the browse form - Custom code is not executed? The SQL on the browse form under General works, so I put my search filter (WHERE clause with hash variables) there.

Then the search screen model as in
http://forums.nubuilder.cloud/viewtopic.p ... wse+filter.
is working in NuBuilderPro as well.


BR, Tinka