Guidance needed creating multi-criteria search form with subform

[Keith-i]

I have some basic MS Access experience having cobbled together a database for our office many years ago, and am now trying to migrate it into nuBuilder. I've successfuly transferred all the data into mySQL and have just about got the hang of building a simple search form using the formbuilder 'wizard'.

Where I am now struggling (possibly as much due to lack of database knowledge as anything else) is trying to create a search form with subform that can show results based on a number of different search criteria limited to searching specific fields.

To give an example, we inspect houses so our database has an 'instructions' table with job number and date fields. This then has a many-to-one link to a table containing all of the houses, with further linked table containing road names and postcodes. What I am trying to do is create a form where a user can type in a house number and/or road name and/or postcode which will then show all of the instructions we have ever received for that property. What I need is a 'house number' search box that only searches the house number field, and a 'road name' search box that only searches the road name field etc etc. I need this search granularity as there are many occasions where house names are the same as road names, or the same road name exists in a few postcodes such that a global search of all the fields will bring up too many results with no way of narrowing it down. In Access I had some SQL that used an 'Add To Where' clause to add any search critereia into the SQL query. Is there a similar function in nuBuilder? And how to create the text boxes for typing the search criteria into?

Any pointers or examples would be really helpful. Ultimately once I get the hang of this I would want to be able to click on an entry in the subform results which would then show further related data about that property, for example its resale prices over the years which is contained in another related table.

[kev1n]

Maybe something like this?

viewtopic.php?p=27541

[Keith-i]

Thanks. That seems to be roughly along the right lines. I'll read into it properly and come back if I have any queries.

[Keith-i]

I'm gradually making some progress but would appreciate some further help. As you can see in the attached image, I have created a 'launch' form with my search boxes and then an iFrame containing my Property List form. Where I could now do with some help is how to write the custom code to filter the property list form based on search criteria in the filter boxes. I've looked at the demo you linked to but can't see how it works. Any chance of some help please?

Secondly, once I filter my list of properties I need to be able to click on a chosen entry and see related info. Do I create another iFrame within the Property List form or use a subform do you think?

[kev1n]

Did you try to follow this instruction and where exactly are you stuck?

[Keith-i]

Hi Kev1n

Many thanks for the link. For some reason I had overlooked the code example on it when I first looked at it the other day. I should be good to go now.
Cheers

[Keith-i]

I'm making some progress but just need a couple of things clarifying please.

In the following custom code for my Search button, I presume the first reference 'filter_adr_last_name' is the id of my filter object. What is the second reference? Can this be anything I want or is it pointing to something in particular?

Code: Select all

frame.nuSetProperty('filter_adr_last_name', adr_last_name.value);

Secondly, in the iFrame BB do these need to match the names above or should the latter one be the column name in my table that we are trying to filter?

Code: Select all

setFilter($filter, 'filter_adr_last_name', 'adr_last_name');

Thanks again in advance of any help
Keith

[kev1n]

adr_last_name is the Id of the object, filter_adr_last_name the Hash Cookie name.

Secondly, in the iFrame BB do these need to match the names above

Yes, they need to match.

[Keith-i]

Thanks for the help. Here's my Custom Code for my 'Search Button'

Code: Select all

// Example: Set HKs for Text fields
    frame.nuSetProperty('prop_name_fltr', prop_name_srch.value);
    frame.nuSetProperty('unit_no_fltr', unit_no_srch.value);
    frame.nuSetProperty('unit_name_fltr', unit_name_srch.value);
    frame.nuSetProperty('house_no_fltr', house_no_srch.value);
    frame.nuSetProperty('road_name_fltr', road_name-srch.value);
    frame.nuSetProperty('postcode_fltr', postcode_srch.value);

And here's the corresponding BB code on the iframe form. Does this look right to you as I'm not getting any results up. I've commented out the WHERE clause as I don't think I need it, but have added a LEFT JOIN to link the roads table to the property address table.

Code: Select all

setFilter($filter, 'prop_name_fltr', 'BuildingName');
setFilter($filter, 'unit_no_fltr', 'UnitNo');
setFilter($filter, 'unit_name_fltr', 'UnitName');
setFilter($filter, 'house_no_fltr', 'HouseNo');
setFilter($filter, 'road_name_fltr', 'RoadName');
setFilter($filter, 'postcode_fltr', 'Postcode');

function setFilter(&$filter, $filterName, $columnName) {
    $value = nuGetProperty($filterName);
    if ($value != '') $filter .= " AND $columnName LIKE '%$value%' ";
}
$create = "CREATE TABLE #TABLE_ID# ";

$select = "

SELECT 

    BuildingName,
    UnitNo,
    UnitName,
    HouseNo,
    RoadName,
    Postcode

FROM tblProperties LEFT JOIN tblRoads ON tblRoads.idRoads = tbpProperties.id_Roads

//WHERE (1 = 1)  -- your where clause, if there is any

" . $filter;

[kev1n]

if you add this line of code

Code: Select all

nuDebug($select);

after

Code: Select all

" . $filter;

the generated SQL is output to "nuDebug Results" allowing you to inspect it.