[Solved] Search from a form on several fields

[gstanley]

Hi,

I haven't been able to find anywhere that has already solved this problem, but as I don't know the official words for what I'm trying to do, I may have missed it...

I have set up a simple database, and I would like to be able to use a form to search it and create a report that shows ONLY records that match the search criteria (ideally with 1 button...). I'm using OO.o 3.4.1 in WindowsXP.

Details:

Table fields:
Problem (drop down list)
Age_Kindergarten (yes/no checkbox)
Age_Primary (yes/no checkbox)
Age_Junior (yes/no checkbox)
Recommendation (text)

I have successfully created a form that uses a button to run a search based on user input from the problem field using the following script:

Code: Select all

Sub Search1 
dim oFilter as object 
dim oFormCtl as object 
oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm") 
oFilter = oFormCtl.getByName("Combo Box 1")
if oFilter.Text <> "" then 
oFormCtl.Filter = "Problem LIKE " + "'%"+oFilter.Text+"%'" 
oFormCtl.ApplyFilter = True 
else 
oFormCtl.ApplyFilter = False 
end if 
oFormCtl.Reload 
End Sub 

I'm not an experienced coder (not by a long shot!), and I've tried a number of ways to get it to search based on Kindergarten, Primary, and Junior, but have been unable to do so. I want users to be able to select a problem from a drop down menu AND select one Age_ category. The catch is that each recommendation may be associated with several different Age_ categories, but I still want my search to return all records for which at least that one Age_ category was checked off. (eg., On a form, user selects Problem = "reading" and checks off Age_Primary = yes. The other Age_ categories would be left blank by the user. Then the user pushes a button, and I want it to give me all records that have those criteria, even if they were also checked off for Age_Kindergarten or Age_Junior.)

Is this possible to do? If so, can someone point me in the right direction?

Thanks!
Gillian

[RPG]

Hello

The tutorial of Arineckaig contains information how you can build such a form without macros.

I have the idea it is good to build two form one for data input and one for showing information. In the last form you can also do some updating.

Romke

[gstanley]

Thank you! I'll look through that and see what I can do with it. I am very much in favour of avoiding macros...
Gillian

[gstanley]

Hi,

This has been very helpful. I have spent 4 days now playing with the information and database you've sent, and I have succeeded in accomplishing much of what I was hoping. I am still running into 3 stumbling blocks, and I was hoping to get a bit of help. I'm using the Power Filtering technique, as it best matches what I am looking for. I've uploaded my database in case that helps make it clearer. The form that I have been trying to Power Filter on is called "Search."

HighlyRecommended.odb

(24.43 KiB) Downloaded 419 times

1. This is the most problematic issue - I can't get my form to display the data in the "recommendation" field of the subform. And currently, when it does do a search, it informs me that it is showing me record 1 of 1, even though I know there are more records that match the entered search criteria.

2. On the "Search" form, I would like the search fields (Problem, and the 6 checkboxes for age) to appear as blank each time, rather than as whatever was last entered.

3. In the table that is part of the subform (on the Search form), I would like it to list the actual problem name (eg., reading) rather than the id number (eg., 1).

Thank you so much for your help!
Gillian

[Arineckaig]

Many apologies for the delay in my reply.

1. This is the most problematic issue - I can't get my form to display the data in the "recommendation" field of the sub-form. And currently, when it does do a search, it informs me that it is showing me record 1 of 1, even though I know there are more records that match the entered search criteria.

I suspect the source of several problems may be the various boolean (check box) fields in the “Data” and “Filter” tables. Such fields in the embedded database can be tri-state: they can hold 1 or 0 but also a NULL. For your purposes I suggest this tri-state is better avoided and NULL values should not be permitted for any of the boolean fields. The search criteria on those fields then becomes the simple values of 1 for checked and 0 for unchecked.

[Please note that this differs from the 'Power filter' example, which uses text fields. The search criteria for text fields are either the content of the field or the presence of a NULL.]

2. On the "Search" form, I would like the search fields (Problem, and the 6 checkboxes for age) to appear as blank each time, rather than as whatever was last entered.

You will appreciate that all these search form controls are contained in the “Filter” main data form whose source is the SQL

Code: Select all

SELECT "Filter".* FROM "Filter" AS "Filter" WHERE "ID" = 0

Thus, those form controls will tend to be filled with whatever values have been previously saved in the “Filter” table. I suspect it would require a macro (possibly triggered as the form document opens) to clear all those values. I would, however, avoid resort to any macro until you have all the other elements of the “Search” form document fully operational.

3. In the table that is part of the subform (on the Search form), I would like it to list the actual problem name (eg., reading) rather than the id number (eg., 1).

In Base this facility is readily supplied by use of a List box. For example in place of the Text box use a List box bound to the same “Data Field” property with SQL as “Type of list contents” property and

Code: Select all

SELECT "Problem", "ID_Prob" FROM "Problem"

as the “List Content” property.

Please come back to let us know how you get on or if these explanataions merely confuse further.

[gstanley]

Thank you so much for your reply! I think it is very helpful, but it also leads to new issues and thoughts.

I took out all the (or = NULL) pieces and converted the Problem column in the subform table into a listbox (although I cannot change the properties for it, since it's part of a table control), and now my form just won't search at all. It continues not to show anything in the recommendations column of the subform table, and only briefly flashes the number associated with the problem before then displaying a blank field in the Problem listbox. So I've done something wrong there.

I wonder if one way to solve this is with a one-to-many or many-to-many relationship. I originally wanted to have all of the age categories in one field, but ended up separating them into 6 fields because I could not figure out how to make it do what I wanted. I would like to be able to enter new data (recommendations) that are associated with one problem and many different age groups (multiple check boxes). The user who searches for recommendations will only select one problem and one age group. So for the searching user, that would be a simple listbox, but for the data entry user, it would be a multi-select list box. I understand that many-to-many relationships are better than/equivalent to (?) multi-select boxes and could be used by both the data entry user and the search user? I will have to do some more research on this... Do you think it would possibly solve my problem?

I would like to continue to work without macros for as long as possible, so I will just ignore the fact that there are always default values in my Filter form. Thanks for letting me know!

Cheers,
Gillian

[Arineckaig]

A quick reply on one point:

converted the Problem column in the subform table into a listbox (although I cannot change the properties for it, since it's part of a table control)

To access the properties of a column in a grid/table form control:
open the form document in edit mode;
right click the head of the column;
select "column" from the bottom of the list that drops down;
the Properties dialog for that column should now open.

On the broader issue if you are just beginning with Base, I would dare to suggest you start with a simpler database design. Please permit me some time to give more thought to your task, and I will come back to you with either some suggestions or more likely with a number of questions.

[gstanley]

Perfect - that fixes the listbox. Thanks!

I am just beginning with Base, and am trying to keep things as simple as possible, but since I've been asked to accomplish a specific task by work, I can't simplify it beyond what it needs to do. I will try it without the age categories, and see if I can get it to search, and then see if there's some way to add the ages back in.

Any suggestions/questions are welcome!
Thanks again for your help,
Gillian

[gstanley]

Okay, it works perfectly if I take out the age categories and just search on the one field.

Is there any way to search on age categories IF each record can be associated with more than one age category? (Eg., in entering data, I want to be able to select any/all of the 6 categories, and then in searching, I want to be able to search by each age category individually.) Is that possible?

Thanks,
Gillian

[Arineckaig]

An example is probably better than 1000 words (especially if they are my words), so I have attached a skeleton demo file that hopefully implements your original intent. Please forgive the extra fields in the sub-form: these are surplus but added to show that the multiple selections produce the required result. Please note also the sub-form in the DEMO file is set to be read only - simply to avoid inadvertant changes to the source table when testing the form document.

You will see I have set all check box fields and form controls to exclude tri-state. You will also see in the "Data" table I have replaced the "Problem" text field with the foreign key "ProblemID" integer field to ensure a better link with the primary key field in the "Problem" table.

[gstanley]

That's brilliant! Thank you so much! I will explore it and do my best to learn and replicate. I'll post back on my success.
Gillian

[gstanley]

So your example works perfectly, while mine is lacking somewhat in the working department... I believe that it has something to do with the definition of Problem or ProblemID... Or something... I tried to recreate the search form from scratch, and it wasn't linked to any databases (I had to do that manually). Can you suggest what I may be doing wrong? The forms just don't do anything (and now that I've added in the ProblemID field and taken out the Problem field, even the data entry form has stopped working).

I've re-uploaded my file in case that would help you.

Thanks again! I really do appreciate your guidance!
Gillian

HighlyRecommended.odb

(39.57 KiB) Downloaded 351 times

[Arineckaig]

There are a couple of areas that you may wish to consider.

First, for the “Results” sub-form of the “Search” form document of my DEMO file I used as the “Content” property the following SQL:

Code: Select all

SELECT * FROM "Data" 
WHERE ( "ProblemID" = :z) 
AND ( ( "Kindergarten" = :k AND :k = 1 ) 
OR ( "Primary" = :p AND :p = 1 ) 
OR ( "Junior" = :j AND :j = 1 ) 
OR ("Intermediate" = :i AND :i = 1 ) 
OR ( "Secondary" = :s AND :s = 1 ) 
OR ( "Adult" = :a AND :a = 1 ) )

When I copy and paste this SQL to replace the equivalent property in the sub-form of your “Search2” form document, it appears to work. The SQL comprises a fairly complex boolean statement and the locations of the several parenthesis pairs is critical. For some reason that I have not investigated, your existing SQL for that property may appear to be the equivalent but does not work.
[Later edit: After checking further I find the error in your SQL is due to the addition of a single quote around each numeral 1: the effect is to make the criterion the text 1 as opposed to numeral 1. Removing all these single quotes permits the SQL to be effective, for example:

Code: Select all

SELECT * FROM "Data" 
WHERE ( "ProblemID" = :z AND "Kindergarten" = :k AND :k = 1 
OR "ProblemID" = :z AND "Primary" = :p AND :p = 1 
OR "ProblemID" = :z AND "Junior" = :j AND :j = 1 
OR "ProblemID" = :z AND "Intermediate" = :i AND :i = 1 
OR "ProblemID" = :z AND "Secondary" = :s AND :s = 1 
OR "ProblemID" = :z AND "Adult" = :a AND :a = 1 )

This SQL code is the direct equivalent and is probably the more efficient.]

Secondly, I would again warn against permitting NULLS in boolean or check-box fields. You will see in my DEMO file all boolean fields in the “Data” and “Filter” tables have been set to prevent NULLs - the “Entry required“ property is set to 'Yes'. Such precautions may not be strictly necessary, but if you are going to make use of check-box controls or boolean data fields be aware of the havoc that inadvertent NULLs can cause.

[gstanley]

Thank you!!!!! I really appreciate you taking the time to help me understand this, so that I can learn and do it again! I'll make the changes this afternoon, and will report back.

Have a great day!
Gillian

[gstanley]

It worked perfectly! Who knew that quote marks could be so pesky? Thank you again for all of your help.
Gillian

[RPG]

Hello Arineckaig

When I did see the start of this thread I did not knew a solution. When I did saw your answer It seems me a little long but when I did search better it makes clear that I did have a wrong understanding of how the forms are working in this case. I did search for an other solution and this learned to me a better understanding of the forms and also of the methode you are using. I think with the use of a nested statement the query is a little faster and also not so complicated
My thank is special for the use of the parameters in the query. For other reading this kind of queries can only be used in subforms.

Romke

Code: Select all

select * from
	(SELECT * FROM "Data" WHERE "ProblemID" = :z)
where ("Kindergarten" = :k AND :k = true 
		OR
	 "Primary" = :p AND :p = true
	 	OR 
	 "Junior" = :j AND :j = true
	 	OR 
	 "Intermediate" = :i AND :i = true
	 	OR 
	 "Secondary" = :s AND :s = true
	 	OR 
	 "Adult" = :a AND :a = true )