[Solved] Using Cascading List Boxes
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[Solved] Using Cascading List Boxes
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
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
- Attachments
-
- Company_Cascading_Address.odb
- (5.79 KiB) Downloaded 326 times
Last edited by dreamquartz on Mon Jun 30, 2014 2:15 am, edited 2 times in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Using Cascading List Boxes
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.
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"
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
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Using Cascading List Boxes
Hi Villeroy,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: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.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"
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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Using Cascading List Boxes
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
Code: Select all
SELECT "N", "ID" FROM "MainItems" ORDER BY "N" ASC
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)
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Using Cascading List Boxes
Hi Villeroy,Villeroy wrote: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:Code: Select all
select * from "Filter" where "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.Code: Select all
SELECT "N", "ID" FROM "MainItems" ORDER BY "N" ASC
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.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)
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.
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 306 times
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Using Cascading List Boxes
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.
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
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Using Cascading List Boxes
Hi DACM,DACM wrote: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.
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.
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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Using Cascading List Boxes
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:
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 522 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
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Using Cascading List Boxes
Thank you so much.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:
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 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Using Cascading List Boxes
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.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.
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.
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.dreamquartz wrote:PS: was the Address remark a compliment or is it more to be read as a hint?
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
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Using Cascading List Boxes
Hi DACM,DACM wrote: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).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.
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.
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.dreamquartz wrote:PS: was the Address remark a compliment or is it more to be read as a hint?
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.
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
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Using Cascading List Boxes
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.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.
Code: Select all
COALESCE( ', ' || "TypeOfService", '' ) AS "Service"
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
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Using Cascading List Boxes
Hi DACM,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:
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]
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.