Apache OpenOffice Community Forum

Hi All,

I am trying to implement the principle of Cascading_List_Boxes (viewtopic.php?p=233508#p233508, but have no luck so far.
I am trying to populate a 3rd table, based on cascading selection of the 1st table.
The tblProject Table must hold a CompanyName, the specific Project related address, the person who runs the project, date and time.
The Company is already entered into the database, with potentially multiple addresses, like Billing, Mailing, Training addresses.

I would to be able to select an address, belonging to a selected Company.
I would like to see only those Company addresses, belonging to the selected Company and no other addresses to select.

I would like to use this same principle with i.e. Country and their State/Province selection. Select a Country, and only then show the State(s)/Province(s) from the selected Country.

I attached a sample database.

I am running the embedded HSQLBD.

Hope that someone can point me in the right direction.

Dream

Filtering box bound to some integer "INT_Field" in SELECT * FROM "FilterTable" WHERE "ID"=13 (13 being an existing row number)
List Content: SELECT "Text","ID" FROM "MainItems" ORDER BY "Text"
Bound to: "INT_Field" in "FilterTable"'s row #13

Filtered box bound to some foreign key of sub-items in a data table.
List Content is the sub-item names and their primary keys filtered by the main item IDs: Both list boxes work like any other list box with a visible first field and a second bound field. The first stores its value in a "INT_Field" @ row 13, the second refers to the stored value picking the records that belong to a SubItem's MainItem according to the stored main item ID in row 13.

Villeroy wrote:Filtering box bound to some integer "INT_Field" in SELECT * FROM "FilterTable" WHERE "ID"=13 (13 being an existing row number)
List Content: SELECT "Text","ID" FROM "MainItems" ORDER BY "Text"
Bound to: "INT_Field" in "FilterTable"'s row #13

Filtered box bound to some foreign key of sub-items in a data table.
List Content is the sub-item names and their primary keys filtered by the main item IDs:

Code: Select all

SELECT "S"."Text","S"."ID" 
FROM "SubItems" AS "S","FilterTable" AS "F" 
WHERE "F"."ID" = 13
AND "F"."INT_Field"="S"."MID"
ORDER BY "Text"

Both list boxes work like any other list box with a visible first field and a second bound field. The first stores its value in a "INT_Field" @ row 13, the second refers to the stored value picking the records that belong to a SubItem's MainItem according to the stored main item ID in row 13.

Hi Villeroy,

Thanks for the response. I will look into it, but saw that I used the wrong example database.
I will modify the attachment and see if your input works.

Dream

Each Table1 record has one SubItem. Each SubItem has one MainItem.
The form enters SubItems into Table1. The selectable subitems are filtered by a preselected MainItem.
Like so often we store a filter criterion in a distinct row of a dummy table (Filter.INT @ ROW#1) in order to filter some record set by this particular criterion value. But this time the filtered record set is the content of a list box rather than a subform.

Filter form specifies the row where the criterion can be found: Filtering box bound to column INT in that row is filled with all MainItems so you enter one MainItem.ID into Filter.INT@ROW#1: Data form is simply bound to Table1 showing 2 versions of SID. One column shows the raw SID number in a text box. The second column shows the same field represented by a filtered list box.
Filtered list box on grid to where we want to enter one SID form a filtered set of SIDs: showing a descriptive concatenated string (forgot to add a field of SubItem names). All SubItems are selectable if the criterion is Null.
The SID column shows the SID as the list box as a raw number without any filtered list box.
I added a small subform to inform you about the MID of an invisible SubItem.

Villeroy wrote:

The attachment cascading_listbox.odb is no longer available

The attachment cascading_listbox_relations.png is no longer available

Each Table1 record has one SubItem. Each SubItem has one MainItem.
The form enters SubItems into Table1. The selectable subitems are filtered by a preselected MainItem.
Like so often we store a filter criterion in a distinct row of a dummy table (Filter.INT @ ROW#1) in order to filter some record set by this particular criterion value. But this time the filtered record set is the content of a list box rather than a subform.

Filter form specifies the row where the criterion can be found:

Code: Select all

select * from "Filter" where "ROW"=1

Filtering box bound to column INT in that row is filled with all MainItems so you enter one MainItem.ID into Filter.INT@ROW#1:

Code: Select all

SELECT "N", "ID" FROM "MainItems" ORDER BY "N" ASC

Data form is simply bound to Table1 showing 2 versions of SID. One column shows the raw SID number in a text box. The second column shows the same field represented by a filtered list box.
Filtered list box on grid to where we want to enter one SID form a filtered set of SIDs:

Code: Select all

SELECT 'Subitem ' || "S"."MID"||' -- '||"S"."ID" AS "Name", "S"."ID" 
FROM "SubItems" AS "S", "Filter"AS "F" 
WHERE "F"."ROW"=1 AND ("F"."INT"="S"."MID" OR "F"."INT" IS NULL)

showing a descriptive concatenated string (forgot to add a field of SubItem names). All SubItems are selectable if the criterion is Null.
The SID column shows the SID as the list box as a raw number without any filtered list box.
I added a small subform to inform you about the MID of an invisible SubItem.

Hi Villeroy,

Thank you so much.

That took a while to understand, but I think I am almost there where I would like to be.
It turned out that the "Refresh form" and "Save record" buttons I need, were in the wrong SubForm.

I am able to select a Company, and link an Address. I am also able to select a Service, but with the last step I am lost a little.
I want the data that is placed in the tblFilter, to be transferred/saved into the tblProject, so that I can select an other Project that can be added to the tblProject.

In the tblProject the information of the ProjectDate, ProjectNumber, FKServiceID, FKCompanyID, and FKAddressID needs to be stored.
The simple thought was to place an other Form on top of all others, but that is where I am lost.
The Form Filter works, based on your info, but Filter_Attempt is a mess.

Can you or anyone else point me in the right direction?

Dream

dreamquartz wrote:I want the data that is placed in the tblFilter, to be transferred/saved into the tblProject, so that I can select an other Project that can be added to the tblProject.

In the tblProject the information of the ProjectDate, ProjectNumber, FKServiceID, FKCompanyID, and FKAddressID needs to be stored.

Dream,

If you truly need to save the tblFilter entries to the tbleProject, then you're going to run into some limits with a macro-free design, just as the tutorial mentions. In general, a macro-free series of filtered List Boxes (cascading List Boxes) is limited to saving the LAST List Box selection to the target table (tblProject). All intermediate selections must be saved to the Filter table because this allows filtering subsequent List Boxes using SQL based on a known record (WHERE ID=0). However, you can pass the intermediate selections from the Filter table (tblFilter) to NEW records only in the target table (tblProject) using SubForm links. SImply include the progressive Filter table selections as link-fields in the SubForm's Master and Slave links. This is quite limiting because it only applies at the moment of NEW record creation, such that any subsequent modifications to that record will not reflect the current Filter table values. So I would typically resort to macros to filter cascading List Boxes when intermediate values must be saved to the target table (tblProject).

See:

• Cascading List Box macro code
  [Tutorial] Cascading Listboxes with macros

However, I would suggest that you do not need to save FKCompanyID to tblProject because FKAddressID is in one-to-many (1:n) relationship with FKCompanyID. So you can always derive FKCompanyID from a given FKAddressID using a Query or View as necessary.

DACM wrote:

dreamquartz wrote:I want the data that is placed in the tblFilter, to be transferred/saved into the tblProject, so that I can select an other Project that can be added to the tblProject.

In the tblProject the information of the ProjectDate, ProjectNumber, FKServiceID, FKCompanyID, and FKAddressID needs to be stored.

Dream,

If you truly need to save the tblFilter entries to the tbleProject, then you're going to run into some limits with a macro-free design, just as the tutorial mentions. In general, a macro-free series of filtered List Boxes (cascading List Boxes) is limited to saving the LAST List Box selection to the target table (tblProject). All intermediate selections must be saved to the Filter table because this allows filtering subsequent List Boxes using SQL based on a known record (WHERE ID=0). However, you can pass the intermediate selections from the Filter table (tblFilter) to NEW records only in the target table (tblProject) using SubForm links. SImply include the progressive Filter table selections as link-fields in the SubForm's Master and Slave links. This is quite limiting because it only applies at the moment of NEW record creation, such that any subsequent modifications to that record will not reflect the current Filter table values. So I would typically resort to macros to filter cascading List Boxes when intermediate values must be saved to the target table (tblProject).

See:

• Cascading List Box macro code
  [Tutorial] Cascading Listboxes with macros

However, I would suggest that you do not need to save FKCompanyID to tblProject because FKAddressID is in one-to-many (1:n) relationship with FKCompanyID. So you can always derive FKCompanyID from a given FKAddressID using a Query or View as necessary.

Hi DACM,

Thanks for your response.
Maybe it helps to explain what I am trying to accomplish.
We provide services to different companies who might have different locations.
I wanted to use the Cascading List Box principle to select a specific address for these services.
We have to go to that specific location to provide these services.
We want to keep record of what services has been provided, by whom, when, and contact information.

I do not know if this approach is the right one, or if it can accomplished differently.

Dream.

Your Addresses are generated through a fairly elaborate process ...interesting

Again, with macro-free cascading List Boxes, you'll only be able to save the LAST List Box selection in the series to the target table (tblProject). But when the series involves 1:n relationships, you can derive (SQL), or even pass (subform links), the selections otherwise saved only to the Filter table, within some limits (new records, etc.).

Here's a demo of these concepts, including elimination of Company (FKCompanyID) from the target table (tblProject) since it can be derived from the 1:n relationship with the company's Address:

DACM wrote:Your Addresses are generated through a fairly elaborate process ...interesting

Again, with macro-free cascading List Boxes, you'll only be able to save the LAST List Box selection in the series to the target table (tblProject). But when the series involves 1:n relationships, you can derive (SQL), or even pass (subform links), the selections otherwise saved only to the Filter table, within some limits (new records, etc.).

Here's a demo of these concepts, including elimination of Company (FKCompanyID) from the target table (tblProject) since it can be derived from the 1:n relationship with the company's Address:

Thank you so much.

By the looks of it, it is what I was looking for.
I wiil have to study it in detail.

It appears that this solution is quite complicated.
Is that my approach, or the limitations of the embedded SQL, or both?

If this can be simplified, I am all ears.

Dream

PS: was the Address remark a compliment or is it more to be read as a hint?

dreamquartz wrote:It appears that this solution is quite complicated.
Is that my approach, or the limitations of the embedded SQL, or both?

If this can be simplified, I am all ears.

Well, the complexity is driven by your preference for macro-free cascading List Boxes (primarily), and your need to permanently store/derive Filter table criteria in support of your Address generator (secondarily). The database engine (whether embedded or external/split) doesn't affect the complexity of this Form design. In general, Base is designed to generate practical forms with relative ease utilizing Table Controls and linked SubForms. These forms are workable but the average end-user is more accustomed to clean-looking forms utilizing List Boxes and the like (even filtered cascading List Boxes). This requires more complex development as you're forced to leverage SQL, table structures (Filter table technique), Form structures (via Form navigator), strategic Push Buttons, and SubForm-features to get everything working properly. This added complexity is really no different in MS Access.

Otherwise, macros could have been used to simplify this particular design to a degree. But unlike VBA (MS Access), macros in Base are quite difficult to learn and employ efficiently (although this forum provides example code, demos, and tutorials, while Access2Base provides some additional relief).

In other words, database front-end development is not an easy task because it involves a working knowledge of SQL, database design concepts (appropriate normalization), in addition to Base features, techniques and quirks.

dreamquartz wrote:PS: was the Address remark a compliment or is it more to be read as a hint?

It was a relatively neutral comment. But the design is impressive -- assuming you actually need to breakdown and store Address-data in that manner. In any case, I like the way you think because it will serve you well when you need such data granularity. Most folks tend towards flat-file designs or data-redundancy because they fear or are unaware of SQL's ability to formulate the necessary pseudo-table queries or views. Or they don't understand indexing with respect to table-joins and overall database performance. Your Address generator defies these stereotypes to an opposite extreme. That's why I found your approach so interesting.

But in terms of complexity, your Address generation technique does complicate the overall design somewhat. But that's okay. I did have to rewrite the associated queries because COALESCE is insufficient to determine NULLS when CONCATENATION is employed with explicit strings in that manner. Also NULL testing is sufficient for numerals, but strings also require testing for EMPTY strings or strings with only spaces. I think the final query accounts for these situations appropriately.

DACM wrote:

dreamquartz wrote:It appears that this solution is quite complicated.
Is that my approach, or the limitations of the embedded SQL, or both?

If this can be simplified, I am all ears.

Well, the complexity is driven by your preference for macro-free cascading List Boxes (primarily), and your need to permanently store/derive Filter table criteria in support of your Address generator (secondarily). The database engine (whether embedded or external/split) doesn't affect the complexity of this Form design. In general, Base is designed to generate ugly forms with relative ease utilizing Table Controls and linked SubForms. These may be practical for database administrators in a pinch, but the average end-user prefers a more intuitive, attractive Form design utilizing List Boxes (even filtered cascading List Boxes). This requires more complex development as you're forced to leverage SQL, table structures (Filter table technique), Form structures (via Form navigator), strategic Push Buttons, and SubForm features to get everything working properly. Otherwise, macros could have been used to simplify this particular design to a degree, but macros in Base are just as difficult to learn and complex to employ (although this forum provides example code, demos, and tutorials, while Access2Base provides some additional relief).

In other words, database front-end development is not an easy task, because it involves a working knowledge of SQL, database design concepts (appropriate normalization), in addition to Base features, techniques and quirks.

dreamquartz wrote:PS: was the Address remark a compliment or is it more to be read as a hint?

It was a relatively neutral comment. But the design is impressive assuming you actually need to breakdown and store Address-data in that manner. In any case, I like the way you think because it will serve you well when you need such data granularity. Most folks tend towards flat-file designs or data-redundancy because they fear or are unaware of SQL's ability to formulate the necessary pseudo-table queries or views. Or they don't understand indexing with respect to table-joins and overall database performance. Your Address generator defies these stereotypes to the opposite extreme. That's why I found your approach so interesting.

But in terms of complexity, your Address generation technique does complicate the overall design somewhat. But that's okay. I did have to rewrite the associated queries because COALESCE is insufficient to determine NULLS when CONCATENATION is employed with explicit strings in that manner. Also NULL testing is sufficient for numerals, but strings also require testing for EMPTY strings or strings with only spaces. I think the final query accounts for these situations appropriately.

Hi DACM,

Thanks for the explanation.
I thought I was doing things so complicated, because of the lack of knowledge I still have. I am learning, but the tips and tricks you and other members like you are truly appreciated by me and others. So again, thank you.

I was following the COALSCE as a result of viewtopic.php?f=13&t=68566#p305885, based on the remark placed by F3K Total about Fred Tousi.

I will follow your advice, concerning my approach and will continue with the design.
I do think that there should be an easier approach.

Dream

dreamquartz wrote:I was following the COALSCE as a result of viewtopic.php?f=13&t=68566#p305885, based on the remark placed by F3K Total about Fred Tousi.

Well those are my mentors, so I may have to test my theory again. It may be that an explicit string CONCATENATED with a NULL resolves to NULL such that COALESCE works as advertised in this case as well. When I switched to CASE WHEN (from COALESCE), I was also dealing with the possibility of both NULLs and SPACES in strings, which is not as simple as NULL testing with COALESCE.

• Code: Select all

  COALESCE( ', ' || "TypeOfService", '' ) AS "Service"

Speaking of avoiding NULLs, it's interesting that Sliderule didn't employ COALESCE in this instance:

• viewtopic.php?p=304775#p304775

DACM wrote:Your Addresses are generated through a fairly elaborate process ...interesting

Again, with macro-free cascading List Boxes, you'll only be able to save the LAST List Box selection in the series to the target table (tblProject). But when the series involves 1:n relationships, you can derive (SQL), or even pass (subform links), the selections otherwise saved only to the Filter table, within some limits (new records, etc.).

Here's a demo of these concepts, including elimination of Company (FKCompanyID) from the target table (tblProject) since it can be derived from the 1:n relationship with the company's Address:

Hi DACM,

Just a quick questiom.
Where/how to set a colour change of a push button?
In your example you used a Save button, that changes colour.

Dream

Update:
Question answered at viewtopic.php?f=13&t=70624

Changed to [SOLVED]