One subform for common fields across nested data?

[chrispdx]

First: thanks to the volunteers and admins for all the outstanding assistance and resources you provide to this community.

BACKGROUND

This is a database for the management of a private family cabin that is owned and used by a dispersed second generation.

• Everything is organized into a three-layer hierarchy of locations: (1) Sections > (2) Areas > (3) Sub-Areas (defined as three tables with PK/FK relations established).
• Any location, at any of the three levels, can have two main types of data:
  (A) Orientation/Summary Information (a text field in each of the three Location tables), and
  (B) checklist items for procedures occurring at four main stages each year (defined in the "TYPEINFO" table):
  (1) Season Opening,
  (2) Mid-Season Departure,
  (3) Mid-Season Arrival, and
  (4) Season Closing.
• These check list items are all stored in a single (poorly named) "INFO" table with foreign key/s to the associated location and a field for TYPEINFO.

After much effort and learning (largely thanks to this forum), this all seems to be working well.

QUESTION Database: https://www.dropbox.com/transfer/AAAAAH ... 9RIciEdKMA

I've created a form (with several linked subforms) for reviewing and editing the Location, Orientation, and Checklist data ("Main Form 3"). This form works fine, but it is unwieldy to have three identical report sections for the exact same data points (Orientation field and four stages of checklist items) -- one for each of the three Location levels (Section>Area>Subarea). Not every Location has these data and so the user sees a lot of blank fields and has to scroll up and down the form to find the relevant info. I would prefer to keep this all on the same main form rather than having to open a new form window for Location details.

Can you help me show the Location "details" (i.e., the Orientation field and four Staged Checklists subforms) in the same place, regardless of which location level is the focus? So far I have been able to avoid using any macros, thankfully, but I presume this would involve three "Detail" buttons (one for each of the Location table-controls) to show the selected Location's Orientation field and the four Staged Checklist subforms) in the same spot, whether it's a Section, Area, or Subarea.

Would "power filtering" be a better approach rather than using the three Location subform table controls?

Thank you for your assistance.

[UnklDonald418]

As time allowed, I’ve been looking at your database and I believe what I uploaded does something similar to what you are asking. It looks like you already have most of the pieces to use power filtering, so I went that route.

I uploaded a file that contains 3 elements that you can copy into your JDBC (Split) database.
A table Filter3 is a modified version of your filter table.
A query, INFO_all_qry01 which is your query with a few indexes added that are needed to connect to the filter table.
A form document Main Form 4. For it to work properly requires two push buttons that need to be pushed after each change in the filter table selections. It would be possible to eliminate the buttons but that would require coded macros. The form document is for lookup only, you would still need your other form for data entry. It might be possible to combine them into a single form document but it might be a little crowded.

CabinMgmt01.odb

(14.33 KiB) Downloaded 281 times

[chrisb]

chrispdx said:
Can you help me show the Location "details" (i.e., the Orientation field and four Staged Checklists subforms) in the same place, regardless of which location level is the focus?

the answer may be to replace the four table controls with one table control which contains a flag that enables identification of the various stages of the season (opening, departure, arrival & closing).
even more page space can be reclaimed by replacing the three table controls (section, area, sub-area) with cascading list boxes.

i have run up a small demo db which utilises your table data but uses a different structure in order to provide you with a working example.
i added the table "tSeason" which contains the values (opening, departure, arrival & closing).
the form occupies one page & is fairly easy to read, input is simple.
two small macros are used which update the list boxes & reload the inner forms.

i wish you luck but can envisage a multitude of issues as your project progresses.

[chrispdx]

Thank you both very much for the time and effort you put into this. I regret the extended delay responding!

Responding to your feedback here. Current database and question are down below.

UnklDonald418 said:
A table Filter3 is a modified version of your filter table.
A query, INFO_all_qry01 which is your query with a few indexes added that are needed to connect to the filter table.

I now see why the filter table should just use the keyID fields for Section/Area/SubArea rather than the names, and why they also need to be included in the query.

chrisb said:
the answer may be to replace the four table controls with one table control which contains a flag that enables identification of the various stages of the season (opening, departure, arrival & closing).
even more page space can be reclaimed by replacing the three table controls (section, area, sub-area) with cascading list boxes.

Thanks for idea of using a single table control for the checklist items with a field indicating the stages (from my "TYPEINFO_tbl"). The macro-enabled list boxes are also very slick, thanks for that.

chrisb said:
i have run up a small demo db which utilises your table data but uses a different structure in order to provide you with a working example.
i added the table "tSeason" which contains the values (opening, departure, arrival & closing).

I hadn’t considered separating out the Summary and Checklist info into separate tables by location-level, as you have done. I see how that enables a simpler form design, though it's not clear to me whether there are other benefits to justify what seems a more complicated table structure. In any case, however, I already developed a report template (in TIBCO JasperSoft Studio) that is built around the existing table structure, and so I need to stay with the current table structure.

QUESTION 2 Database 2: https://www.dropbox.com/t/5sBJJdk54xOVnZGp

This database includes two forms reflecting the helpful suggestions and assistance provided:

• Main Form 5 - filter dropdowns: Uses the power filtering structure with list boxes to select a location.
• Main Form 6 - subform tables: Uses table controls with subforms to select a location.

Despite the improvements suggested before and implemented here, my question remains essentially the same: How can I show the Location "details" (i.e., Orientation field and Staged Checklists) in the same spot, regardless of which location level is the focus?

I found a macro that does precisely what I'm looking for: cycles through sets of controls making them visible or not based on a selection (in a group box, in this case). I'd like to apply this macro to my form so that it shows the relevant "Orientation & Summary Information" text control and "Staged Checklists" combo control based on the location level selected above (Section/Area/SubArea). Unfortunately, I am a very new to macro coding and cannot figure out how to adapt the code in the subroutines so they would apply to my form Here are links to the macro that does the trick:

• It's discussed on this forum page: https://ask.libreoffice.org/en/question ... -selected/
• Direct link to database with the macro: https://ask.libreoffice.org/upfiles/158 ... 676466.odb
• NOTE some of the words are in Polish, translated as: przelacz = Switching, oZdarz = oEvent, oPrzyc = oButton, and ukryj_wszystko = hide_all.

Again ... thank you VERY much for your help.

[chrisb]

hello chrispdx,
it's been almost three months since your initial post.
i downloaded your latest database & am pleased to see that you have taken our suggestions onboard.
you appear to be stuck at the setup stage having made little progress & your focus is still fixed on the input form.

lets look at the bigger picture:
imagine that someone is to visit the cabin next month & opening season tasks have to be carried out.
that person departs, departure tasks have to be carried out & then of course throughout the year we have numerous other arrivals & departures.
how do you propose to generate, present & then store that data? i see no mechanism to do that.

on the surface your project appears fairly basic but there are some real issues to be overcome here.
this is similar to a census, present a list of questions & receive a yes/no answer.
the difference here is that our census is perpetual with the need to constantly regenerate the task lists & store the users response.

having already developed a report template (in TIBCO JasperSoft Studio) is putting the cart before the horse.

OR am i missing something?
it may be that there is no intention to keep a digital log, that this project simply stores the text strings & user input is by pen & paper via report template.

[chrispdx]

Thank you, chrisb, for your clarifying response and questions. Upon reflection, I see that my initial description of this project, as a database for the “management” of a cabin, was rather aspirational.

Your last proposition is correct. This project began as an effort to (1) collect and organize the far-flung/aged institutional knowledge related to seasonally managing and maintaining our old family cabin, and (2) efficiently produce an updated series of printable checklists that could be completed by hand and stored in the cabin's old three-ring “Maintenance” binder. Initially there will one admin/user (me) collecting updates and distributing current checklist PDFs by email.

My focus had turned to designing this form at hand as a means to both (a) efficiently update the location and checklist data and (b) demonstrate to family members the database’s primary structure and functions in a user-friendly and hopefully intuitive way. Thus my desire to automatically hide/show the details based on the location-level selected.

I do, however, intend to add a Project-tracking component using the same location structure. And, one day, a more sophisticated account-based system with task-logging etc., such as you were reasonably envisioning.

Given these origins and my rather convoluted learning & development process, I wouldn’t be surprised at all to learn that a more experienced developer may have approached this in a fundamentally different way.

If you have feedback on how I’ve structured this database, or thoughts on a more-optimal approach that would better facilitate the addition of new functionality in the future, I would be truly grateful to continue learning and improving this. For what its worth, I have by necessity gained some experience manipulating the backend using SQL commands in hsqldb.jar.

Thank you again for your assistance.