FIXED: DIY Advanced search screen

FIXED: DIY Advanced search screen

Postby DennisMe » Mon Jan 07, 2013 11:16 pm

Hi all,
First off, thanks for open sourcing nuBuilder, its a really useful tool!

I'm in the middle of building a sermon database and will need to display a list of sermons which match bible references. This is really the core part of its functionality.

Each sermon has a number of associated scripture references which take the form of (PK)'scripture_id, (FK)'scr_sermon_id', 'scr_book'(varcharNN), 'scr_chapter'(smallint), 'scr_versefrom'(smallint), 'scr_verseto'(smallint).

Theres a simple 1:n relation between sermon and scripture. I already have normal lists of sermons by date and place etc, with subforms and lookups etc working very well!

What I need is a form for the user to fill in book, chapter, verse from and verse to and then build an SQL statement that matches any range of scripture references containing the queried data.

The story goes like: user fills in book: "Genesis" chapter: "2" from v."5" to v. "5" (defaulted);
and this updates a subform containing all sermons like
title "ttttttt" date "dddd' book "genesis" verse from "1" to "17" (just a silly example, not real data)
title "ttttttt" date "dddd' book "genesis" verse from "5" to "10" ditto

other sermons are not shown.

I know how to match a sub form using the id (well, I don't really KNOW, I barely managed to get it working to be honest) but this "advanced search" form needs to match several fields.

Should I build a 'template' query table with just the required search fields and then use hash variables for each of these. Is that even possible? Are there automagically hash variables for every field? Or should I go about this in some other way?

Please help!
Last edited by DennisMe on Tue Jan 15, 2013 5:35 pm, edited 1 time in total.
DennisMe
 
Posts: 10
Joined: Thu Jan 03, 2013 6:51 pm

Re: need help: DIY Advanced search screen

Postby admin » Tue Jan 08, 2013 10:23 am

DennisMe,

Firstly I don't think you need 'scr_versefrom'(smallint) and 'scr_verseto'(smallint).

I would just have 'scr_verse'(varchar)

And type in "5-7".

By creating a inner join on the scripture table (on the sermon Form) and displaying all the fields you want to search, from both tables in the sermon Form's Browse, you will get your "advanced search".

Steven
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Re: need help: DIY Advanced search screen

Postby DennisMe » Tue Jan 08, 2013 8:01 pm

Ok, thanks,
I understand your approach.

There are still a couple of major issues with it, making it impossible to use.

1) using a varchar containing "1-5" how could that be made to also match a user's search for "3"? Or does the search filter handle ranges like this automatically?

2) as I understand it the search filter will match any field containing the search term (an implicit OR relation). This obviously works fine as long as all fields contain dissimilar data. If there is only one date column and one name column and one quantity any date in the (single) search field will match against the only date column, and any name will only match against a similar name, an integer will match the quantity and its all good...
However, as soon as you need to search columns containing similar data the OR relation can backfire by selecting way too much data.
see also: http://forums.nubuilder.com/viewtopic.php?f=4&t=8135

A partial but powerful solution that would seem to fit nicely with your philosophy of simplicity could be to enable a new type of search screen that automatically provides one search field above each column in a browse screen so the user can "filter by example". (Or you could perhaps use the first row of the grid for this). This creates an AND relation between columns, greatly improving the searches selectivity.

Unfortunately for me this still doesn't give me the ability to search for a value that falls within a certain range though, not unless the search fields could be provided to a custom SQL query for substitution into the WHERE clause. If you know of any way to do this with the current nuBuilder, I'm all ears!
DennisMe
 
Posts: 10
Joined: Thu Jan 03, 2013 6:51 pm

Re: need help: DIY Advanced search screen

Postby admin » Wed Jan 09, 2013 3:30 pm

DennisMe,

Here is an alternative..

Capture.PNG
Capture.PNG (82.22 KiB) Viewed 14520 times


You could put a c in front of chapter and a v in front of verse.

Either like I've done it or when you are creating the record.

Otherwise you can create a Report where you can filter and sort on anything you want.

Steven
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Re: need help: DIY Advanced search screen

Postby DennisMe » Wed Jan 09, 2013 5:31 pm

That might help, let me digest this further, and try some of this stuff out. I think I can fix this in combination with some of the PHP trickery I've found on the forum in the mean time.
I'll report back for the benefit of others when I crack this nut!
regards,
Dennis
DennisMe
 
Posts: 10
Joined: Thu Jan 03, 2013 6:51 pm

Re: need help: DIY Advanced search screen

Postby DennisMe » Thu Jan 10, 2013 6:54 am

:lol: Well, I finally figured it out and its really easy to do.

I'll just have a fake edit form (called directly from a button, thanks to "zazzium") with a sub form (browse form)
On the fake "edit" form I'll only have the fields I need to match as text boxes, these will be my advanced "search fields".

The sub form has custom SQL as usual but enriched with each of the hash variables and wildcards corresponding to the "search fields" so it only selects records where all the search fields match their respective columns, the standard search field remains as an optional extra to further refine the search if needed.

All the user needs to do is fill in the fields on the main form and hit search on the subform without entering any filters to display the exact matches (if any).

I tested this with one extra search field and it worked well.
DennisMe
 
Posts: 10
Joined: Thu Jan 03, 2013 6:51 pm

Re: need help: DIY Advanced search screen

Postby ruiascensao » Thu Jan 10, 2013 10:28 pm

Hi Dennis,

Could you please share your code as example?
I have the same problem.

Thank you.

BR
Rui
BR
Rui
ruiascensao
 
Posts: 177
Joined: Tue Nov 15, 2011 10:54 pm

Re: need help: DIY Advanced search screen

Postby DennisMe » Tue Jan 15, 2013 12:26 am

OK, here's what I did:

I made a new form (fmAdvancedSearch) and bound it to zzsys_variable, zzsys_variable_id as Primary key..
This form has nothing in the browse columns.
I then added the 'advanced search' fields: fldBook, fldChapter, fldVerseFrom and fldVerseTo to my new form.
(This is like a dummy form that we only need for its fields and the associated hash variables!)

I then created a new form called "find_advanced" on the table Sermon, with PK sermon_id.

This is what I put in the SQL:
SELECT s . * , t . * , h . *, l.*,sc.*
FROM sermon s
JOIN sermontype t ON s.ser_sermontype_id = t.sermontype_id
JOIN held h ON h.held_sermon_id = s.sermon_id
LEFT OUTER JOIN location l ON h.held_location_id = l.location_id
LEFT OUTER JOIN scripture sc ON s.sermon_id=sc.scr_sermon_id
WHERE sc.scr_book LIKE "#fldBook#"
AND sc.scr_chapter = 0#fldChapter#
AND sc.scr_versefrom <= 0#fldVerseFrom#
AND sc.scr_verseto >= 0#fldVerseTo#
ORDER BY h.held_date DESC

Then I added a new subform object to the original (fmAdvancedSearch) which brings this form (find_advanced) up.

Lastly I created a button object on the menu which brings up fmAdvancedSearch:
Important: This selects RecordId -1 in its "Button" tab.

Now the user can enter the relevant data in (fmAdvancedSearch) which is passed straight through to the SQL in my search form.

So basically my advanced search form is a dummy "container" form which wraps around the real search form and adds the ability to search on specific column values and use plain SQL for the grunt work. No code required!
DennisMe
 
Posts: 10
Joined: Thu Jan 03, 2013 6:51 pm

Re: need help: DIY Advanced search screen

Postby DennisMe » Tue Jan 15, 2013 12:30 am

Just one little detail worth explaining as it may not be self evident:

In the SQL there is:
AND sc.scr_chapter = 0#fldChapter#

The 0 before the hash variable is not an error, it is there to provide a sensible default value of "0" in case the variable is left empty!
Otherwise it would result in an SQL error! If the user does provide a value then the 0 just becomes an irrelevant prefixed 0, as in "01".

Dennis
DennisMe
 
Posts: 10
Joined: Thu Jan 03, 2013 6:51 pm

Re: FIXED: DIY Advanced search screen

Postby martbarr » Fri Jan 18, 2013 3:14 am

bound it to zzsys_variable, zzsys_variable_id


Sorry not getting this bit - fine with the rest and doing something similar here, but not understanding this ..........

thanks
Martin
martbarr
 
Posts: 60
Joined: Sat Oct 27, 2012 3:39 am

Next

Return to General