Page 2 of 2

Re: FIXED: DIY Advanced search screen

PostPosted: Fri Jan 18, 2013 6:33 am
by DennisMe
Hi Martin,
when you make the new form the values for table and primary key should be zzsys_variable and zzsys_variable_id respectively.

To be completely honest the name of the table doesn't matter (as long as it exists!), it could just as well have been any other table. We are not really going to do anything to the table in question anyway we just need to trick nuBuilder into displaying a blank form for us with some blank fields. Although the button with the "-1" for a recordId simulates adding a new record, nothing is ever really saved there.

Re: need help: DIY Advanced search screen

PostPosted: Tue Jun 10, 2014 8:11 pm
by Tinka
I know this is an old post, but maybe someone can answer.

DennisMe wrote: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!

Thank you for your solution - I try to rebuild your advanced search form, but I can't understand what you mean by :

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

I have made the two forms, but as soon as I input the hash variables from the first dummy form for the WHERE clause in the browse sql I don't get any records selected. The "result" form is a browse form that opens in a new window but not a subform on the search form that provides the search filter? How can I solve this? I tried both subform and browse object.
I was thinking if the hash values "carry over" when I click the button to open the browse from with the filter, or if I need cookies or similiar.


Re: FIXED: DIY Advanced search screen

PostPosted: Wed Jun 25, 2014 11:57 am
by admin

Which version of nuBuilder are you using?


Re: FIXED: DIY Advanced search screen

PostPosted: Thu Jun 26, 2014 7:27 pm
by Tinka

Re: FIXED: DIY Advanced search screen

PostPosted: Fri Jun 27, 2014 10:19 am
by admin