[Solved] Create a Form with the List Box and Subcategories

Discuss the database features

[Solved] Create a Form with the List Box and Subcategories

Postby Thegame23 » Fri May 02, 2014 10:58 pm

Ciao a tutti,
I need to filter the results between subcategories.

Filter List Box Results:
es. I select Animals in the first List Box >> in the second I can choose, Dogs, Cats... and If I choose Dogs in the second List Box >> in the Third I can choose Alaskan, Bulldog etc...
Look at the attached file...

Can someone help me to create this in base?
Attachments
database-sub-category-openoffice-base.jpg
Last edited by Thegame23 on Sun May 04, 2014 6:29 pm, edited 1 time in total.
OpenOffice 4.0.1 HSQL Database Engine with MacOS 10.9.2
Thegame23
 
Posts: 16
Joined: Sun Mar 16, 2014 2:58 pm

Re: [Help] Create a Form with the List Box and Subcategories

Postby MTP » Sun May 04, 2014 3:32 am

I think this is what you are looking for: [Example #1] Filter/Search with Forms (leveraging SubForms)
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: [Help] Create a Form with the List Box and Subcategories

Postby Thegame23 » Sun May 04, 2014 3:00 pm

Thanks MTP,
Please take a look at my TestDB.odb file to get an Idea... can someone help me? :?
Attachments
TestDB Form Screenshot.png
TestDB.odb
(24.89 KiB) Downloaded 100 times
OpenOffice 4.0.1 HSQL Database Engine with MacOS 10.9.2
Thegame23
 
Posts: 16
Joined: Sun Mar 16, 2014 2:58 pm

Re: [Help] Create a Form with the List Box and Subcategories

Postby Villeroy » Sun May 04, 2014 3:26 pm

I know of 4 methods:
1) Cascading list boxes with macros (for programmers)
2) "Power filtering" as hyperlinked by MTP (for power fiddlers). Possibly enhanced by a one-line refresh macro.
3) Using a form and subform with records in a grid control instead of list boxes (when there are dozends but not thousands of categories). Works well with ABC registers where you pick items by first letter.
4) Concatenating abbreviated categories and sub-categories in one list box so you type "SW 23456" into the focussed list box in order to get item "23456" from main category "SW" (very simple and efficient).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26974
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Help] Create a Form with the List Box and Subcategories

Postby Thegame23 » Sun May 04, 2014 3:44 pm

Hi Villeroy,
thanks again for the help! :)

I prefer the method 4: Concatenating abbreviated categories and sub-categories in one list box so you type "SW 23456" into the focussed list box in order to get item "23456" from main category "SW" (very simple and efficient).

Have you any example for method 4?
OpenOffice 4.0.1 HSQL Database Engine with MacOS 10.9.2
Thegame23
 
Posts: 16
Joined: Sun Mar 16, 2014 2:58 pm

Re: [Help] Create a Form with the List Box and Subcategories

Postby Villeroy » Sun May 04, 2014 4:09 pm

Source Type: SQL
Source:
Code: Select all   Expand viewCollapse view
SELECT "C"."Name"||' '||"SC"."Name" AS "Visible", "SC"."ID"
FROM "Categories" AS "C", "Subcategories" AS "SC"
WHERE "C"."ID" = "SC"."ParentID"
ORDER BY "Visible"

Bound field: 1 (which is the second one with "SC"."ID")
The first field labeled "Visible" is the category name concatenated with a space and the subcategory name.

It can not apply to your example db because you don't have a list of countries and a list of cities.
Tools>SQL...
Code: Select all   Expand viewCollapse view
CREATE TABLE "Cities"(
"N" VARCHAR(32) NOT NULL,
"CID" INT NOT NULL,
"ID" INT GENERATED BY DEFAULT AS IDENTITY,
FOREIGN KEY ("CID") REFERENCES "Country"("country_id"));
DROP TABLE "ItalyCity";
DROP TABLE "GermanyCity";
DROP TABLE "FranceCity";

View>RefreshTables

And with this structure in place:
Linked field: the CityID in your form
Source Type: SQL
Source:
Code: Select all   Expand viewCollapse view
SELECT "C"."Name"||' '||"SC"."N AS "Visible", "SC"."ID"
FROM "Country" AS "C", "Cities" AS "SC"
WHERE "C"."ID" = "SC"."CID"
ORDER BY "Visible"

Bound field: 1 (which is the second one with "SC"."ID")
The first field labeled "Visible" is the category name concatenated with a space and the subcategory name.
Last edited by Villeroy on Sun May 04, 2014 4:38 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26974
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Help] Create a Form with the List Box and Subcategories

Postby DACM » Sun May 04, 2014 4:36 pm

Here's a (#2) power filtering example without macros. But it's not as elegant or as easy as (#4) List Content concatenation.
Attachments
TestDB 2.odb
cascading List Box filtering
(26.54 KiB) Downloaded 672 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: [Help] Create a Form with the List Box and Subcategories

Postby Villeroy » Sun May 04, 2014 5:47 pm

Yet another extension of the same. Added a field of short country names to the country table, a form to map cities to countries, relations (I love relations) and another subform with a grid and a single list box.
[event]1<--n[event_city]n-->1[city] is a many-to-many relation so we should allow for many cities per event.
Attachments
TestDB 3.odb
(36.22 KiB) Downloaded 301 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26974
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Help] Create a Form with the List Box and Subcategories

Postby Thegame23 » Sun May 04, 2014 6:28 pm

@DACM
Wow, really well done! Thank you very much! :)

@Villeroy
I don't know how to thank you! You are a great person! Thank you very much! :)

@DACM - @Villeroy
Now I've infinite options to complete my database!! XD Thanks guys! :)
OpenOffice 4.0.1 HSQL Database Engine with MacOS 10.9.2
Thegame23
 
Posts: 16
Joined: Sun Mar 16, 2014 2:58 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests