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

Discuss the database features
Post Reply
Thegame23
Posts: 16
Joined: Sun Mar 16, 2014 2:58 pm

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

Post by Thegame23 »

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
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

Post by MTP »

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
Thegame23
Posts: 16
Joined: Sun Mar 16, 2014 2:58 pm

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

Post by Thegame23 »

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 349 times
OpenOffice 4.0.1 HSQL Database Engine with MacOS 10.9.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Thegame23
Posts: 16
Joined: Sun Mar 16, 2014 2:58 pm

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

Post by Thegame23 »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

Source Type: SQL
Source:

Code: Select all

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

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

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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

Post by DACM »

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 1216 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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 611 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Thegame23
Posts: 16
Joined: Sun Mar 16, 2014 2:58 pm

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

Post by Thegame23 »

@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
Post Reply