[Solved] Create a Form with the List Box and Subcategories
[Solved] Create a Form with the List Box and Subcategories
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?
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?
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
Re: [Help] Create a Form with the List Box and Subcategories
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
Re: [Help] Create a Form with the List Box and Subcategories
Thanks MTP,
Please take a look at my TestDB.odb file to get an Idea... can someone help me?
Please take a look at my TestDB.odb file to get an Idea... can someone help me?
- Attachments
-
- TestDB.odb
- (24.89 KiB) Downloaded 349 times
OpenOffice 4.0.1 HSQL Database Engine with MacOS 10.9.2
Re: [Help] Create a Form with the List Box and Subcategories
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).
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Help] Create a Form with the List Box and Subcategories
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?
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
Re: [Help] Create a Form with the List Box and Subcategories
Source Type: SQL
Source:
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...
View>RefreshTables
And with this structure in place:
Linked field: the CityID in your form
Source Type: SQL
Source:
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.
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"
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";
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"
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Help] Create a Form with the List Box and Subcategories
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
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Re: [Help] Create a Form with the List Box and Subcategories
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.
[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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Help] Create a Form with the List Box and Subcategories
@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!
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