Searching on multiple fields

[icoso]

Using the normal search form, how can I search on multiple fields? For example, My database has the Last name and first name in separate fields. If I search by "Smith" I may end up with 10 + pages of records because the database system searches in each field specified on that search form for the occurance of "smith". However if I want to limit that search by specifying the first name too, how can I do that? Or is there a way to "search" within the results that are listed?

Do I need to create a search form of some type that hs the fields on it that I want to the user to be able to enter search criteria on, ie:

Last name:
First name:
Address:
City:
phone:
etc...

Then based on the data they entered, perform an SQL function that would search the database based on a "Like" for each of the fields they enter data on?

Is there a function that lets me perform a search that will list the records like whats built-in to the databse system ?

[kev1n]

The most optimal is probably an iFrame and separate search fields in a "Launch" form something like this:

search.png

[icoso]

No, I don't think that will work for them. They basically want to be able to open the SAME form or one that is exactly like that they use to enter a new user in to it. (they are stuck on what they currently do in an old database) then they want to be able to enter data on that form, such as the last name, first name, the address street name, maybe search by the billing date field. Then click Search and have it list the results like it does now with the standard search form. Then when they click on on of the records it opens it in the Edit form.

The reason they want to do this:

If they enter "Smith John" in the current search field, Its going to find EVERY record that has Smith in the last name field, or any other field that contains the word "Smith" or "John". It appears that if you put more than one criteria in the search field, it does an AND so it only returns records it if finds both search terms. The problem is that each search term gets applied to every field that is on the search screen. In this case they have a last name, a first name, and a preparer name. So the Preparer name is Smith, John and that person might prepare 1000+ records per year. That person is also a customer or there are other customers who's last name and/or first name are "John" and since "Smith, John" was the preparer on their forms, they get pulled up in the search list. since those terms match the last name field, the preparer field, and/or the first name field. This then retrieves too many records for them to have to page through to find the ONE that they need to edit.

Right now on their old database, they pull up a screen that looks exactly like their data entry screen with ALL the fields on it. IF they enter smith in the last name and john in the first name, IT ONLY searches in those fields for the occurence of whatever the searched for, not all the fields. SO in this case its ONLY going to list is those records that have Smith in the last field, and John in the first name field. IT doesn't look at the preparer field, because they didn't enter anything in it.

SO the question is, Is there a way to run a query against the database (Using the built-in functions the nuBuilder system has) that when the records are retrieved, they will be listed and selectable to open a form like they do now?
My idea is that I create a Search form, that looks like the edit form, let he user enter any data then click a search button. The only thing I don't know how to do is how do I list all those records it retrieves, so that I can click on one of them to then open that record in an Edit form?

[kev1n]

No, I don't think that will work for them.

Why will this not work? If you have a Launch form with an embedded browse form, you can place search fields on that Launch form.
Then on a button click, refresh that Browse form and run some PHP code in BB to construct your SQL.

[kev1n]

In additional, you could hide the search field and buttons of the Browse form and add a search button on the launch form. There you can also set the Hash Cookies, which then can be retireved in BB PHP.

Code: Select all

let cw = $("#iframe_object_id")[0].contentWindow;
// set Hash Cookies
cw.nuSetProperty('first_name', $('#first_name').val()); 
cw.nuSetProperty('last_name', $('#last_name').val());
// refresh Browse
cw.nuGetBreadcrumb(0); 

[icoso]

Because Honestly, I have no idea how to do that in this database system or even how to get started with it. The Launch form with all the search fields on it would need to look EXACTLY like the Browse/Edit Form that I use to edit/add the data.

This is my current search form:

TaxFormSearchScreen.png

This is my current edit/browse form:

TaxFormEditScreen.png

I guess these are both the same form. The search screen is just what the user sees first when opening that form. Then they enter some criteria and hit search, get a listing and then click one of the records to open the edit/ browse form.

SO how can I duplicate my existing edit/browse form, make it a search from, and the use an Iframe to include what: another browse/edit form? How do I even put an IFrame on a nuBuilder form. I can do it in an HTML page that I design myself, but I have no clue how to add one in nuBuilder. Can you give me an idea on how to take my existing Form above and turn it into what you're describing?

For example,
1. How do I create a launch form and place the existing TaxCustomer form in an IFRame on that launch form? How do I put a search field (LastName) on the launch form then click a button on that form that will then update the listing that the Tax Customer form is displaying int he IFrame?

[icoso]

I'd Prefer not to do this using an IFRame. The TaxCustomers From takes up the entire hieght of my screen as-is so it would be a paint to try to get tyhat into an IFRame along with the search form. IT will look really bad. Is there a way to open a launch form, with some fields on it, LAst Name, First Name, etc... thtat will then have a button on it to run the search, that will then pass the results from the SQL search to the TaxCsutomers form and have those records automatically be listed there?

For example here is my current launch form for this whole project. The stuff on the rigth side allows them to enter a date range and pull whatever reports they want to pull. The left side currently just has a button that calls the TaxCustomers Form.

Can I put a few fields on a screen like this, Lastname, Firstname, address, etc. with a search button on it that when clicked will run an SQL query against the database, then open the TaxCustomers form with the results from that SQL Query? That's kind of how the reports work now, right? It's just that Im passing the data to the TaxCustomers form instead of a report. How do I do that?

LaunchForm.png

[kev1n]

The idea:

Set Hash Cookies (HK) when the search button is clicked with nuSetProperty() for each search field on the Launch screen. Use true as 3rd parameter to set global HKs. Then open the search form with nuForm().
Retrieve the HKs in BB php of your search form and construct the sql. Use a temporary table.

[icoso]

If I use a temporary table, how do I save a record that is then pulled up to edit in the TaxCustomersForm, back to the main database?

[kev1n]

Doesn't the Browse form show records from the TaxCustomersForm table and hence the PK is the same?