[Solved] Using Cascading List Boxes

Discuss the database features

[Solved] Using Cascading List Boxes

Postby dreamquartz » Mon Mar 31, 2014 7:53 pm

Hi All,

I am trying to implement the principle of Cascading_List_Boxes (https://forum.openoffice.org/en/forum/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
Attachments
Company_Cascading_Address.odb
(5.79 KiB) Downloaded 106 times
Last edited by dreamquartz on Mon Jun 30, 2014 2:15 am, edited 2 times in total.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Using Cascading List Boxes

Postby Villeroy » Mon Mar 31, 2014 9:46 pm

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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26638
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Cascading List Boxes

Postby dreamquartz » Tue Apr 01, 2014 5:21 am

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   Expand viewCollapse view
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
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Using Cascading List Boxes

Postby Villeroy » Tue Apr 01, 2014 5:33 pm

cascading_listbox.odb
(106.79 KiB) Downloaded 83 times
cascading_listbox_relations.png
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26638
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Cascading List Boxes

Postby dreamquartz » Fri Apr 04, 2014 9:43 am

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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
Attachments
Company_Cascading_Address_1.odb
(28.69 KiB) Downloaded 103 times
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Using Cascading List Boxes

Postby DACM » Fri Apr 04, 2014 6:58 pm

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:

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.
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
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Using Cascading List Boxes

Postby dreamquartz » Fri Apr 04, 2014 10:29 pm

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:

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.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Using Cascading List Boxes

Postby DACM » Sun Apr 06, 2014 4:55 am

Your Addresses are generated through a fairly elaborate process :shock: ...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:
Attachments
Company_Cascading_Address_2.odb
Cascading List Boxes in 1:n relationship
(21.23 KiB) Downloaded 272 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
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Using Cascading List Boxes

Postby dreamquartz » Tue Apr 08, 2014 6:21 am

DACM wrote:Your Addresses are generated through a fairly elaborate process :shock: ...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?
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Using Cascading List Boxes

Postby DACM » Thu Apr 10, 2014 7:38 am

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.
Last edited by DACM on Sun Apr 13, 2014 11:29 pm, edited 1 time in total.
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
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Using Cascading List Boxes

Postby dreamquartz » Fri Apr 11, 2014 1:35 am

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 https://forum.openoffice.org/en/forum/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
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Using Cascading List Boxes

Postby DACM » Sat Apr 12, 2014 12:18 am

dreamquartz wrote:I was following the COALSCE as a result of https://forum.openoffice.org/en/forum/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.

Speaking of avoiding NULLs, it's interesting that Sliderule didn't employ COALESCE in this instance:
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
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Using Cascading List Boxes

Postby dreamquartz » Tue Jun 24, 2014 7:57 am

DACM wrote:Your Addresses are generated through a fairly elaborate process :shock: ...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 https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=70624

Changed to [SOLVED]
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am


Return to Base

Who is online

Users browsing this forum: systemdocean and 1 guest