[Solved] Search from a form on several fields

Creating and using forms

[Solved] Search from a form on several fields

Postby gstanley » Wed Sep 26, 2012 7:12 pm

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   Expand viewCollapse view
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
Last edited by gstanley on Thu Oct 04, 2012 10:47 pm, edited 1 time in total.
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: Search from a form on several fields

Postby RPG » Thu Sep 27, 2012 2:04 am

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
LibreOffice 6.1.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2170
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Search from a form on several fields

Postby gstanley » Thu Sep 27, 2012 4:15 pm

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
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: Search from a form on several fields

Postby gstanley » Mon Oct 01, 2012 8:40 pm

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 156 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
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: Search from a form on several fields

Postby Arineckaig » Tue Oct 02, 2012 1:26 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Search from a form on several fields

Postby gstanley » Tue Oct 02, 2012 4:38 pm

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 :x = 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
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: Search from a form on several fields

Postby Arineckaig » Tue Oct 02, 2012 5:34 pm

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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Search from a form on several fields

Postby gstanley » Tue Oct 02, 2012 5:57 pm

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
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: Search from a form on several fields

Postby gstanley » Tue Oct 02, 2012 10:41 pm

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
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: Search from a form on several fields

Postby Arineckaig » Wed Oct 03, 2012 10:17 am

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.
Attachments
DEMO-HighlyRecommended.odb
DEMO to show use of multiple check-box filtering
(15.34 KiB) Downloaded 172 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Search from a form on several fields

Postby gstanley » Wed Oct 03, 2012 3:26 pm

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
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: Search from a form on several fields

Postby gstanley » Wed Oct 03, 2012 7:41 pm

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 127 times
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: Search from a form on several fields

Postby Arineckaig » Thu Oct 04, 2012 12:06 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Search from a form on several fields

Postby gstanley » Thu Oct 04, 2012 3:01 pm

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
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: [Solved] Search from a form on several fields

Postby gstanley » Thu Oct 04, 2012 10:48 pm

It worked perfectly! Who knew that quote marks could be so pesky? Thank you again for all of your help.
Gillian
OpenOffice version 3.4.1
Installed on Windows XP
gstanley
 
Posts: 13
Joined: Mon Sep 24, 2012 6:56 pm

Re: [Solved] Search from a form on several fields

Postby RPG » Sun Oct 07, 2012 6:10 pm

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   Expand viewCollapse view
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 )
LibreOffice 6.1.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2170
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands


Return to Forms

Who is online

Users browsing this forum: No registered users and 0 guests