Cascading filter of list box not working

[mtukut]

I can not get the refresh button to work.

I've attached the simple database. There are 3 tables and one form. I've input some test data into the tables.

The form had a listbox that allows the user to select the customer.
Then I've added a pushbutton to refresh the form so that the second listbox, projects will display the projects only for the customer selected in the first listbox.

Listbox A: User Selects customer from table customers
Listbox B: User Selects projects from table projects that are specific to customer selected in Listbox A
Pushbutton: Action: Refresh form

Table: Customers:
ClientID PK
Firstname
Lastname

Table: Projects:
ProjectID PK
ClientID
ProjectName

Table: Tasks:
TaskID PK
ProjectID
Description

The first listbox does pull the info from the Customer table.

Nothing happens in the second listbox even after refresh.


SQL for second listbox:

Code: Select all

SELECT "ProjectName", "ProjectName", "ProjectID" AS "Bound" FROM "Projects" WHERE "ClientID" = ( SELECT "ClientID" FROM "Customers" WHERE "ClientID" = '0' ) AND "ClientID" >  0 ORDER BY "ProjectName" ASC

Again the simple test db is attached.

I got the sql code from here:
http://user.services.openoffice.org/en/ ... 00&t=42845
It's just not working for me.


What am I doing wrong?

[rudolfo]

You haven't read the Example on Filtering/Searching with Forms closely enough.

Lets take a closer look at using a List Box to set the filter-criteria for a SubForm.

This is about MainForm and SubForm, but your example uses two (Main) forms on the same level.
A Master-Detail Form design would look like the following:

Code: Select all

Forms
 - MainForm
   : fmtClientId
   : fmtFirstName
   : fmtLastName
   - SubForm
     : fmtProjectId
     : fmtClientId
     : fmtProjectName

Note that the SubForm (or detail Form) is on the same level as the controls of the main form. This is a requirement otherwise you can't link the detail form to the master field. Your Projects Form should have two more items on the Data Tab of the Form properties: "Link master fields" and "Link slave fields" between "Sort" and "Allow additions". These two fields are typical for Subforms.

[mtukut]

Yes I saw that and redid the forms but still did not get those field. I will try from scratch now.

[mtukut]

Ok I redid the form from scratch and still can't get it to work.

subform1:
customer listbox
refresh button
subform2:
projects listbox

The refresh button does not do anything. It should populate subform2.

I have attached the updated database with the Activity form.

I made this form in design view since it appears you can only create one subform with the wizard and the intent is to have another form on this one. But I can't get the second form to work. There must be something else missing in this example that I am not seeing.

[mtukut]

I must've tried a hundred ways and still can't make it work. I've stripped everything out and am now just trying to show data in a second list box based on the first listbox using the same table. Still can't get it to work.

Table: Customers
ClientID
FirstName
LastName


Form:
MainForm
Listbox: Display lastname (works fine)
Button: action: Refresh form
Subform
Listbox: Display firstname (does not work)

Not only does the refresh button not display the first name in the list box, when I change the last name and click refresh, it keeps asking me if I want to save the form data.

Attached is the stripped down database. I could've written the whole thing in mysql/php in half the time it is taking just to connect these listboxes. Are there some good tutorials somewhere? This should be much easier than it is.

[mtukut]

Happy to say I successfully got the same table version working. Still can't get the multi table version working. I've been thinking everything is accomplished through wizards and dialog boxes but it seems that's not really the case. I've been thinking that the forms are linked through the properties master/slave but that appears not to be the case. I am guessing I need to do join statements in the sql but it is not clear to me how to do this in base. It's also not clear to me what the SQL statement needs to be in the subform.

I have attached my updated db.

The first test form works great within the same table.

The second test form is not displaying the data from a different table.

I would think that I would just select all from both tables where field from table1 equals field from table2. But that's not working.

Any help appreciated.

[Villeroy]

I've been thinking everything is accomplished through wizards and dialog boxes but it seems that's not really the case.

All of the wizards are useless and the graphical design tools do not support as many features as they should. Being familiar with SQL you get the best results with some SQL editor.
Most importantly: The embedded HSQLDB is rather useless too since it will destroy your data sooner or later. As you notice in this forum, the embedded database is pretty convenient for demos and tutorials. That's it.

1) Create your (MySQL, HSQL or whatever) database with the mature tools of your choice but not with Base.
2) Connect a "Base document" to that database. A "Base document" is a mere configuration file in the first place.
3) Get the 2 relevant toolbars and draw your forms without using any of the stupid wizards. Based on a valid relational database structure, the graphical form design is a fairly straight forward process. You may decide to attach input forms to stand-alone office documents which gives more advantages than draw-backs compared to the forms attached to embedded Writer documents. Yes, forms work exactly the same way on tabbed Calc sheets.
4) Calc templates can provide dynamic reports, charts and pivot tables within seconds (just in case you find the Writer table reports too simplistic and the report builder extension too complicated).

The database connectivity is an extremely useful part of this office suite but the toolset called "Base" which has been built around the connectivity looks promising while being effectively unusable. Luckily, we can work with databases without Base starting with some database and a simple configuration file called "Base document". You may disregard all the Base rubbish and get a working database setup anyway. Notice that this office suite is entirely about ODF documents. It was never intended to provide a development environment for databases.

[Villeroy]

For the cascading filter boxes there are 2 alternatives:
1) A single list box with a concatenation of "Category,Subcategory" entries. You type the main category and a comma followed by the subcategory. This works very well with short main category names.
2) A main form with a vertical table control where you pick the main category record rather than the name. A linked subform will display the subcategories related to the selected record. This works very well with a few dozends of main categories (scroll and click).

[DACM]

Hi mtukut,

You're probably referring more specifically to this example:
[Example #2] Filter/Search with Forms (leveraging SubForms)

If the intention is to select a Customer and then click a button to filter the Projects list box by the selected customer, then we need to make some adjustments.

Observations:

• MainForm is based on a filter-record (ID=0) offering two Text [VarChar] fields as available in the Customers table.
  Customer list box is setup to display and save the customer's last name to the LastName field in the MainForm filter-record.
  SubForm1 is based on the same filter-record as the MainForm and linked by LastName.
  Projects list box is setup to display and save the customer's first name to the FirstName field in the SubForm's filter-record.

Adjustments:

• Modified the Customer list box > List content > Bound field to save CustomerID rather than LastName.
  Modified the Customer list box > List content > Display field to reflect LastName, FirstName using SQL concatentation.
  Modified the Projects list box > List content > Bound field to save ProjectID rather than FirstName.
  Eliminated the Projects list box > List content > WHERE "CustomerID" > 0 clause as it was unnecessary.
  Added a 'Save' button to save the Project list box selection to the filter record (as desired...?).

Please note that SubForms are not the primary filtering mechanism with this macro-free approach to cascading List Boxes. In this case, SubForms serve only in a secondary role as 'refresh' button hosts, because placing these 'refresh' buttons on their respective SubForm avoids the annoying popup box (asking to "save your changes") while also ensuring subsequent List Box refresh. For emphasis, I didn't even bother to link the SubForms to their parent data-Forms in the Examples. The primary filtering mechanism is the SQL command used to populate the List content of each successive List Box. These SQL commands include nested SQL queries with WHERE clauses used to read the Filter-table/record.

In most cases, the nested-SQL for successive List Boxes remains relatively simple. It's a function of the data (relationships) used to derive the List Box content. When deriving the List Box content from one-to-many relationships exclusively (as in your case), the nested logic goes something like this:

• List Box 1:
  • selects Criteria1
    contains no nested SQL
  List Box 2 (cascaded):
  • selects Criteria2
    WHERE "Criteria1" = ( SELECT "Criteria1" FROM "Filter" WHERE "filterID" = 0 )
  List Box 3 (cascaded):
  • selects Criteria3
    WHERE "Criteria2" = ( SELECT "Criteria2" FROM "Filter" WHERE "filterID" = 0 )
  List Box 4 (cascaded):
  • selects Criteria4
    WHERE "Criteria3" = ( SELECT "Criteria3" FROM "Filter" WHERE "filterID" = 0 )
  ...and so on.

With some designs (involving many-to-many relationships), these WHERE clauses may need to be repeated collectively in each successive List Box's SQL command using AND logic as follows:

• List Box 1:
  • selects Criteria1
    contains no nested SQL
  List Box 2 (cascaded):
  • selects Criteria2
    WHERE "Criteria1" = ( SELECT "Criteria1" FROM "Filter" WHERE "filterID" = 0 )
  List Box 3 (cascaded):
  • selects Criteria3
    WHERE "Criteria1" = ( SELECT "Criteria1" FROM "Filter" WHERE "filterID" = 0 )
    AND "Criteria2" = ( SELECT "Criteria2" FROM "Filter" WHERE "filterID" = 0 )
  List Box 4 (cascaded):
  • selects Criteria4
    WHERE "Criteria1" = ( SELECT "Criteria1" FROM "Filter" WHERE "filterID" = 0 )
    AND "Criteria2" = ( SELECT "Criteria2" FROM "Filter" WHERE "filterID" = 0 )
    AND "Criteria3" = ( SELECT "Criteria3" FROM "Filter" WHERE "filterID" = 0 )
  ...and so on.

NOTE: A dedicated Filter-table might be more appropriate in this case than setting aside an embedded Filter-record, because you're working with a relational database design (as opposed to a flat-file database). This can affect things like data-type matching between fields when trying to adapt the existing filter-record fields for relational tasks -- although we can employ SQL CAST {field AS data-type} to ensure proper handling. And in this case, we run out of fields in the Customers table for filter-record purposes rather quickly, limiting us to 2 cascaded List Boxes. For that reason, the second example Form (Example2) utilizes a separate Filter-table with the necessary INTEGER fields for 3 cascading List Boxes. Of course you can add fields (or even records) as necessary to a dedicated Filter-table to accommodate all kinds of filtering needs. But there may be reasons to choose a filter-record over a filter-table such as generating a write-through query (read/write record-set) involving multiple tables. I did employ your filter-record for the first form (Example1) while saving INTEGER keys (CustomerID and ProjectID) to VARCHAR fields (FirstName and LastName). Just food for thought.

[mtukut]

I've been thinking everything is accomplished through wizards and dialog boxes but it seems that's not really the case.

All of the wizards are useless and the graphical design tools do not support as many features as they should. Being familiar with SQL you get the best results with some SQL editor.
Most importantly: The embedded HSQLDB is rather useless too since it will destroy your data sooner or later. As you notice in this forum, the embedded database is pretty convenient for demos and tutorials. That's it.

1) Create your (MySQL, HSQL or whatever) database with the mature tools of your choice but not with Base.
2) Connect a "Base document" to that database. A "Base document" is a mere configuration file in the first place.
3) Get the 2 relevant toolbars and draw your forms without using any of the stupid wizards. Based on a valid relational database structure, the graphical form design is a fairly straight forward process. You may decide to attach input forms to stand-alone office documents which gives more advantages than draw-backs compared to the forms attached to embedded Writer documents. Yes, forms work exactly the same way on tabbed Calc sheets.
4) Calc templates can provide dynamic reports, charts and pivot tables within seconds (just in case you find the Writer table reports too simplistic and the report builder extension too complicated).

The database connectivity is an extremely useful part of this office suite but the toolset called "Base" which has been built around the connectivity looks promising while being effectively unusable. Luckily, we can work with databases without Base starting with some database and a simple configuration file called "Base document". You may disregard all the Base rubbish and get a working database setup anyway. Notice that this office suite is entirely about ODF documents. It was never intended to provide a development environment for databases.

I am starting to see this. But I am confused what I'm supposed to use. If I use MS Access do I have the same issues? I thought I could use Base (or MS Access) as a standalone relational database on a windows machine. I did not want the user to have to install php/mysql and deal with that since I am not local to him.

[mtukut]

Hi mtukut,

You're probably referring more specifically to this example:
[Example #2] Filter/Search with Forms (leveraging SubForms)

If the intention is to select a Customer and then click a button to filter the Projects list box by the selected customer, then we need to make some adjustments.

Thank you, I will review this now. But I am concerned with Vilroy's comment that I need to connect Base to some other database. Not sure what I would connect it to on a Windows 7 machine.

[mtukut]

Hi mtukut,

NOTE: A dedicated Filter-table might be more appropriate in this case than setting aside an embedded Filter-record, because you're working with a relational database design (as opposed to a flat-file database).

So how does this dedicated filter table get created?

If I create the following forms:
1) Form to add a customer
2) Form to add a project
3) Form to add a task

Does the user have to create the dedicated filter table or is there a way to automatically update that table when I create a task?

Of course I would much rather create one "add" form where the user fills out all the initial data for the customer, the first project and the first task all on one screen, but I don't see how to do that since I need to store the customer id into the project table and the project id into to the task table. Or are you saying don't do that, instead store the connections in a filter table? If so, how do I tell Base to do that?

[DACM]

...I need to store the customer id into the project table and the project id into to the task table. Or are you saying don't do that, instead store the connections in a filter table? If so, how do I tell Base to do that?

Did you examine the example file posted above?

• Cascading_List_Boxes.odb

Cascading List Box requirements (without resorting to macros):

• A filter-table/record is necessary.
  Only the last List Box in the series can be used for data entry.
  The preceding List Boxes in a series are only used to set filter/search criteria.

You can always setup multiple series of List Boxes on a single Form, to accommodate both data entry and filtering requirements across multiple tables. Otherwise, we can filter cascading SubForms without macros as well, so there may be other acceptable designs based on form-Controls that simply derive their display-content from the data-Form itself (result-set)...such as a Text Box or Table Control (grid). For instance, [Example #2] link demonstrates the use of Text Boxes as successive filtering mechanisms. Villeroy outlined a few alternatives as well, which are nicely-documented in [Example] Arineckaig's Form filtering examples.

The alternatives to List Boxes can get ugly and/or non-intuitive compared to everyday Forms. And when that's unacceptable it's time to employ macros in this List Box filtering role, or purchase MS Access (and deploy your creation freely), or turn to the tools developed for rich internet applications such as Morfik. Apparently, MS Access 2010 has morphed into one of those 'rich internet application' development tools within the context of the Microsoft ecosystem (SharePoint services, SQL Server, etc.). Base is light-years behind that level of front-end function.
...