I’m building a basic expense tracking application and would like it to include a list of 30+ general accounts (categories) in one List Box (which I know how to do). And a second List Box that changes its list of more granular (sub-category) options based on the selection made using the general accounts List Box.
I believe the second (subordinate) List Box options could be defined in a second table with multiple fields (columns), with each field representing a different option of the general accounts listed in the first List Box. I also believe through research this could be done with an appropriate SQL statement, but don’t have a clue how to construct it. So my questions are:
a) Is this the right approach to the design of the subordinate list box?
b) What would the SQL statement format look like?
c) How would I need to construct the control(s) of the List Boxes to accommodate the above?
Thanks, Erik
No. Each sub-category is unique and is associated with only one category.
Villeroy,
The fourth example is what I was looking for. Let me digest and get back with questions.
Thanks, Erik
Valeroy,
After closer review, where are the controls for the list boxes?
Also I don’t understand the invisible buttons between the list boxes?
Thanks, Erik
https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=88831 is linked on the form. Unfortunately, the site is down right now.
- Open the form for editing and its form navigator (menu:Form>Form Navigator…)
- “Filter Continent” is linked to
select * from "Filter" where "FID"=0and a hidden control “AutoRefresh” storing the string../FilterCountry/ListBoxCountry. The form’s record action event is linked to an embedded Python macro. - “Filter Country” is linked to
select * from "Filter" where "FID"=1and a hidden control “AutoRefresh” storing the string../New_Voyage/listCityID. The form’s record action event is linked to the same embedded Python macro. - The white listbox and the white grid are linked to the “Voyages” table. Listbox writes the CityID.
When we store a value the continent or country filter, the record action event triggers the macro. The macro reads from “AutoRefresh” the hierarchical name of the form control(s) to be refreshed and refreshes the specified elements. Multiple list boxes, combo boxes or forms can be separated by semicolons and will be refreshed in the order of appearance.
P.S. You can remove the invisible buttons. They are not necessary. When you move the focus from the continent listbox to the country listbox, you save the chosen continent anyway because the listboxes belong to different forms. Same with country listbox and city listbox.
P.P.S. If you keep the invisible buttons, set property “Default button” to Yes (which I forgot to do). Then you can change the listbox value, hit enter (which saves the record), and the next listbox will be refreshed by the macro before getting the focus.
Villeroy,
I appreciate the detail of your recommendations. But as a newbee, I still have questions.
a) What is triggering the hidden buttons to execute? When I view the controls for the continent pull-down, I only get the General tab and there is no indication there is a macro at play.
b) How can I view the Python code?
Thanks, Erik
a) It’s the Enter key, if “Default button”=Yes.
b) Extract it from the zip archive which constitutes the Base document or download the installer from https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=88831 or install the APSO extension.
The code is trivial. This is a StarBasic version:
REM ***** BASIC *****
Function getElement(obj, s)
a = split(s,"/")
sform = obj.getName()
for each x in a
if x = "." then
elseif x = ".." then
obj = obj.getParent()
else
on error goto errName
obj = obj.getByName(x)
endif
next
getElement = obj
exit function
errName:
msgbox "The form "& sForm &" has no child "& x &" in "& s, 16, "getElement"
End Function
Sub refreshElements(obj, tags)
for each s in tags
e = getElement(obj, s)
if isObject(e) then
if hasUnoInterfaces(e, "com.sun.star.form.XLoadable") then
e.reload()
elseif hasUnoInterfaces(e, "com.sun.star.util.XRefreshable")then
e.refresh()
else
msgbox s &" is not a refreshable object", 16, "refreshElements"
endif
endif
next
end sub
sub form_Action(e)
frm = e.Source
hiddenControl = frm.getByName("AutoRefresh")
tags = split(hiddenControl.HiddenValue, ";")
refreshElements(frm, tags)
End SubSo, my macro free solution 2 would be appropriate. 30+ categories can easily be shown in a table grid where you click one and the subform does the rest.
Villeroy,
I have had time to study your four examples you provided for cascading drop-downs. Considering the 3rd example, what is the purpose of the filter table?
Thanks, Erik
The purpose of a form with subform(s) is to filter the subform(s) and determine the default values for new records as shown in the last member of the cascade where the finally chosen city is transfered as a destination into a new record of the voyages table. The city listbox exposes the city name to the user, whereas the ID of the chosen city is written into the foreign key field of table “Voyages”.
All this works with stored values only. Any data appearing in a (sub-)form or listbox is stored in the database.
The second sample works according to this “mechanism” selecting records by means of subforms and form controls writing a single value into the current record. IMHO, this is good enough for 30+ categories to choose from.
The 3rd sample (free of macro code) stores the continent ID in a distinct record of the filter table:
SELECT * FROM "Filter" WHERE "FID" = 0.
The continent listbox stores it’s continent ID in the “INT” field of that record 0.
The content of the continent listbox is simply: SELECT "N", "ID" FROM "Continents" ORDER BY "N" ASC
The country is stored in another filter record:
SELECT * FROM "Filter" WHERE "FID" = 1
The country listbox stores it’s country ID in the “INT” field of that record 1.
The content of the country listbox isSELECT "Name", "ID" FROM "Countries" WHERE "ContID" = (SELECT "INT" FROM "Filter"WHERE "FID"=0) ORDER BY "Name" ASC
-
The continent form with filter record 0 is NOT parent of the country form with record 1. Both forms are main forms independent from each other, simply storing one integer value in a distinct table cell (like a spreadsheet).
-
The blue button belongs to the country form with FID=1. It takes away the focus from the continent form, which stores the continent to filter record 0, before refreshing it’s own form (Action=Refresh) with filter record 1. The listbox is refreshed with its form and contains the countries according to the stored continent-ID in filter record 0.
-
The green button belongs to form "New Voyage. It takes away the focus from the country form, which stores the country to filter record 1, before refreshing it’s own form (Action=Refresh).
-
Form “New Voiyage” is bound to one single record too. It is the new record of the voyages table (form property “Add data only” = YES). “New Voyage” starts with a new record when it’s green button takes the focus and the button’s refresh action reloads the entire form including the city listbox:
SELECT "Name", "ID" FROM "Cities"WHERE "CountryID" = ( SELECT "INT" FROM "Filter" WHERE "FID" = 1 ) ORDER BY "Name" ASC -
The user is supposed to add some additional voyage data and save the record before adding the next new voyage. The “Save” button saves the current voyage and jumps to the new record (Action = New Record).
The macro driven form No. 4 is just a refinement of this one. It works in the exact same way. It does even refer to the same continent-ID and country-ID in the filter table.
In order to make the 4th sample independent, store the 2 IDs to column “INT2” of the filter form or add another pair of filter records with ID 2 and 3.
Storing the continent triggers a macro to refresh the country listbox according to the continent form’s AutoRefresh info.
Storing the country triggers the same macro to refresh the city listbox according to the country form’s AutoRefresh info.
If you keep my misconfigured, hidden buttons, the enter key triggers these buttons (default button=YES) to store the form. Contrary to the refresh buttons of the 3rd sample, these hidden buttons belong to the same form as the preceding listbox, and they do save the form. The macro free buttons belong to the form of the succeeding form. They store the preceding form by taking away the focus before refreshing their own one (action=refresh).
Vileroy,
It has been some time since you provided me with four examples of cascading list boxes. I am still struggling with this. I’m trying to adapt your fourth example (Voyages_cascading_AutoRefresh) to my application. You refer to a filter table in the SQL of the Country field of this example (SELECT “Name”, “ID” FROM “Countries” WHERE “ContID” = (SELECT “INT” FROM "Filter"WHERE “FID”=0) ORDER BY “Name” ASC).
• Do I need the filter table when I only am cascading once - from a parent drop-down to child drop-down?
• Also, can the 2nd drop-down be a combo box, so I can enter additional options when needed?
• Lastly, I’d like to automate the population of the 2nd drop-down after making the selection in the 1st drop down, like you show. Can I do this without using Python code which I do not understand - without macro code?
Thanks
Yes. The value needs to be stored somewhere in a database table.
Yes, of course, but a combo box always stores the literal text rather than a reference. If you store additional info about a continent, a country or a city (population, area, arbitrary details), you need a reference to the full record rather than the name. This is what a list box does. It stores a unique number that identifies a continent/country/city.
There is a simple solution to this problem:
- Add another main form, directly below “Forms” in the form navigator.
- Set “New data only” to Yes and add a pair of save/cancel buttons. There you can enter records of new items.
- Point the form’s record action event to the exact same macro you already have.
- Add a hidden field named “AutoRefresh”, pointing to the listboxes you need to refresh.
When you choose “Africa” from the continent list box, and you notice that an African country is missing, you can enter the name of the new country and its continent (these are 2 attributes that can’t be set by one combo box), the macro refreshes the list box (or all country-boxes on the same form) and you find the new country when you go back to the place where you missed the country and it will be there.
You don’t have to understand my Python code. You install it, assign the form action event and configure it by means of a hidden field named “AutoRefresh” where you point the macro to many elements that will be refreshed in the order of appearance. It is generic code to refresh anything anywhere.
Well, I did it again:
countries_cities.odb (69.6 KB)
I tweaked the 4th sample,
- added 4 forms to add new continents, countries, cities and persons.
- added a detail form to edit the details of a selected person (voyage participant).
- added unique indices to the tables of continents, countries, cities and persons. You can’t enter duplicates anymore.
- added AutoRefresh to all the new forms without editing any macro code.
That form has become rather complex but user friendly. The same thing can be implemented without user code, analog to the third sample form. But without macros, you would need several refresh buttons to refresh forms and list boxes manually.
Villeroy,
Regarding your statement that I must have a filter table for my one-level cascade, will the filter table just have one row and 2 columns, maybe with the same value in both columns like 0 and 0 (and refer tot them accordingly), like in your example first row of two. It just seems unnecessary to require a filter table at all in this situation.? Thanks
skyandrews,
The example you provided I understand, but looks like tables not list boxes. I would like to be able to select an option from both list boxes. The first one determines the list shown in the second one.
Erik
I have written basic macros in Basic, but frequently need help with object oriented programming in general. I’m old school, used to more conventional FoxPro style DB programming. So yes, with some guidance.
MacroListboxes.odb (15.8 KB)
This should be close to what you require. Tweak it to your liking. Instructions are included in the filterTable macro as ‘comments’ (anything following a single quote ’ or the text REM on a single line of basic code).
Skyandrews,
I will check it out. Thanks, Erik
Sky,
Thanks for the PrinterParts example. I finally found the macro code and am studying the code and your remarks. At a high-level what is the purpose of the FilterTable macro?
Erik