That's a bit vague.
1) can you give an example of what you mean by "keep subject matter together"?
2) Can you give an example of what you mean by "bread crumbs"? do you want a "back" button that returns you to the topic record for which the current record is linked as a sub topic. That can be fairly easily managed by storing the previous record's primary key in a global variable. The variable can even be a "stack type" list of such ID's such going back "pops" an ID off the stack and moving to a sub topic from the current record "pushes" a new ID onto the stack.
And just so you know, though you've described a "tree" structure, the basic relationships described here also support a "web" structure to the topics and subtopics. Not only could a topic be linked to many sub topics, a sub topic can link to many topics. That may not be a road you want to travel, but I wanted you to keep that idea in mind as you explore your design options for the database.
For instance if it can be handled with ExecuteSQL than should I not consider a Table Occurrence or vice versa.
Try not to limit your options at this point. New users generally find using table occurrences in Manage | Database | Relationships much easier to understand and work with. The SQL in ExectuteSQL can be very cryptic and very frustrating to work with as this new feature doesn't have a lot of built in support for the developer as they work with their SQL queries. On the other hand, thoughtful use of ExecuteSQL can greatly simplify the relationship map. But then again, this can be at the cost of "hiding" key relationship details inside a calculation field instead of documented in Manage | Database | Relationships.
Yes as you put it I mean a way to show a trail to my users that they are “here” and it is 1, 2, 3, 4 (hope it is not much more) levels deep with the names of the levels visible to them. I guess you mean “web” as website like navigation where link can go any direction, indeed I want to know about that.
Much depends on what you need to show on your layout at any given time. If you use form view to only access one record at a time, this is easy. If you need to show one "top level topic" (Information) with all linked subtopics for two levels (topics and sub topics) or at least a specified number of levels "down", then this could also be done, but changes what you track as your "bread crumbs". And clicking any one item listed below the "top" one could "drill down" by making it the new top level item.
Basic scripted methods for using a single global variable as a stack so that you have a line of "bread crumbs" to follow:
Push: (used to lay down a "breadcrumb")
Set Variable [$Stack ; value: List ( $Stack ; Topics::__pkTopicID ) ]
Pop: (used to pick up a breadcrumb while returning along your original path.)
GetValue ( $Stack ; ValueCount ( $Stack ) ) --> use this with set field or set variable to capture the most recently added item on the list
Set Variable [$Stack ; Value: RightValues ( $Stack ; ValueCount ( Stack ) - 1 ) ] ---> deletes the most recently added item from the list
I guess you mean “web” as website
No that is not what I meant. I am talking bout how the different records in the Topics table can be linked to each other. We've discussed this as a "tree" structure where you have a "root" topic (information) linked to possibly many "branch" topics (Topics) that can then link to "leaf" topics (subtopics). This assumes that for any given record in Topics it can have one and only one parent record that links to it, but that it can link to many different child records.
But in point of fact, the underlying relationships do not need to be limited in that way.(Though you and your users may find it easier to limit them in that fashion.) In actuality, the same topic record could have more than one "parent" record, thus creating a "spider web" of connections instead of a "tree".
A “spider web” is an interesting concept to explore. I am interested in learning about this at some point. At the request of my users I am going to keep this database a tree. I understand Push and Pop because I am very familiar with arrays. I instantly learned something with the example that you just gave. That is I will rely on scripts to do some of the work once I create my logic. I don’t think I have thought of scripts enough up till now. They have been something I made good use out of creating related records and doing other useful things like parsing text. Would I use Script Triggers on a layout to run a script that would Push and Pop something like a breadcrumb? I totally get using the script to set up the $$global variable to hold what the context needs. Now I just have to put it in action. Is it correct to assume:
- Similar topics will start with a Layout base on displaying them
- A Table Occurrence can help in narrowing content down in to a $$Stack (temporary storage of all Topics)
- The way to bring it all together is with Scripts
Are Table Occurrences something you want to use sparingly are too many be something that can be detrimental to you database?
You can end up with a tangled "web" of TO's and relationships that can be very difficult and time consuming to analyze and modify when the need arises. Execute SQL is one new tool that can "prune" away a lot of special use TO's intended for facilitating a specific calculation or layout feature and thus produce a simpler relationship graph to work with. The down side, as mentioned earlier, is that SQL is cryptic and hides relationships from view--you have to track down a specific calculation expression that could be in a field or a script step and analyze it to figure out what is going on.
Another tool for making your relationships graph easier to work with is to use the Anchor Buoy method of organizing your TO's into groups specific to a given layout or group of very similar layouts. This replaces a large tangled mess of TO's and relationships with smaller organized "tree structured" groups of TO's.
OnRecordLoad might be useful to Push the current Topic ID onto the stack. Popping an ID and going to it, would likely be done via clicking a button to run the appropriate script. On the other hand, you may find that you have to use navigation buttons anyway in order to correctly traverse the branches of these trees and thus the same script that does so can also push values instead of putting it in a script trigger controlled script.
Here's what I am imagining:
TO's in green all have the same data source table, Topics. TO's in Blue all have the same join table as a data source table.
When you go to "view" the data for a given topic, a script would use a layout based on Information to find that Record. It would then use Go To Related Records to pull up a list of all related topics with data from Information listed in the header and portals to SubTopics listing any related sub topics. A button in the body of the layout can then take you "down" a level, making that topic entry "information" and a button in the portal row can take you down two levels, making that subtopic the new information level. And the scripts performed by those buttons can also push a bread crumb.
PS. ExecuteSQL could be used here in a number of ways, but the above is much less cryptic to describe and explain.
This is extremely helpful!
I had set up what you previously demonstated. I used ExecuteSQL to do things like display the Subtopics in the Portal under its Topics. This which you describe sounds a lot better. The reading about Anchor Buoy was good. I had heard it mentioned in some training I watched. As for what you stated about a possible setup, I have the:
Topics::__pkTopicID = SubTopicList::_fkParentTopicID
Topics::|SubTopics::__pkTopicID = SubTopoicList:_fkSubTopicID
Setup and working well, is what you refer to here the same Topics and Join table
Or are you saying I would want a Table for Topics?
Will each level (Topics) of the Table Occurrences have a parameter that helps it determine something helpful in the delegating of what is a Topic at which level? Or will that be all handled by the Scripts?
If you double check my previous post, you should read where I indicated that the table occurrences with the same color have the same data source table. My example uses 5 table occurrences but only two data source tables. So all Information, topic and subtopic data are entered in the same table.
So yes, they are the "same tables" in the sense that in the same relationships map, Topics, Topics|SubTopics and Information would both be colored green and refer to the same name when you hover the mouse over the upper left corner. SubTOpicLIst and Information_topic would likewise be colored blue and refer to the same data source table. (apologies for the confusion, I was trying to help you connect the relationships and occurrences back to your original description of the issue.)
Will each level (Topics) of the Table Occurrences have a parameter that helps it determine something helpful in the delegating of what is a Topic at which level
You could add a number field to the record that records its "level". 1 = information, 2 = topic, 3 = subtopic, etc.
Now brace yourself. I've been rethinking this a bit and there's a design option that just can't be ignored here. If you want to lock this basic structure down into only a tree structure, no "webs" permitted, you could simplify the relationships down to three TO's and only one date source table:
Information::__pkTopicID = Topics::_fkParentTopicID
Topics::__pkTopicID = SubTopics::_fkParentTopicID
The key difference, besides being MUCH simpler, is that with this structure, a given record can only link to a single Parent Topic. The original design allows for the possibility that a given topic could be linked to two records at the "information" level.
That setup is exactly where I was at before you gave me this great knowledge. “Once you know”! No I don’t want go back because it is simpler, and I want the option of having “webs” I am dealing with users that need a lot of guidance at the moment, so not here or now, but these are all concepts I need to learn to be proficient with FileMaker. Even though more complex, thank you for letting me know the possibilities. I am loving this! I am almost there. One of the best things about FileMaker is you can have a working database and have another copy that is a work in progress.
That setup is exactly where I was at before you gave me this great knowledge.
As I understood it, not quite. I understood your original approach to be using 3 different tables. My last suggestion was to use one table and three table occurrences---which allows for more than 3 "levels" of branching as is not possible with 3 separate tables.
I get it. I will go with the one that has the possibility for webs if needed. As I begin to make it all work I am noticing the power of it. I would like to explore the other method you speak of also.
I have made some progress on my Breadcrumb system. I have a dropdown/popup (I like popup much better) that shows Topics or Subtopics at the same level of record being viewed, and when I click it navigates to the correct record. I am now exploring ways to make the Breadcrumbs that come before it be buttons that take you to their level. When I am at the parent level for a Topic I have only one entry in the popup and it displays all the time without having to click it. When you click however it show “no values defined”. Is there a way to not see this or to make it not dropdown when there is nothing to see?
Is there a way to not see this or to make it not dropdown when there is nothing to see?
You might be able to hide it inside a tab control where one tab panel has the drop down and the other does not. A script performed by OnRecordLoad might be able to use Go to Object to hide or reveal the control.
With all the help received here I have the breadcrumb system working well, I also have the dropdown that lets me browse Topic at the same level. Any suggestions on how I could determine what "level in" a Topic is. If I could generate a 2 if it is Topic of Information and 3 if it is a Subtopic of Topic… and so on?