Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

Odd Table Occurrences

Discussion created by cmj on Nov 26, 2017
Latest reply on Nov 27, 2017 by cmj

Like • Show 1 Like1
Comment • 11

I stumbled upon something I found a bit odd. I have 4-child tables of which one has a grandchild table (Years).

All the child tables all refer to the grandchild table for calculations. Initially I made multiple table occurrences of the grandchild table (Years) and linked them to each of the individual child tables. By mistake, I referred to the original grandchild table in one of the other child table's instead of the occurrence, for a calculation. The calculation worked fine. I then deleted all the grandchild table occurrences and all the child tables calculations still worked fine - just relating back to the one grandchild table (Years). I'm surprised this works. Any explanation for this? Is it because they all link back to the parent table? Note that the relationship between the child table and grandchild do not allow the creation of deletion of any records.

Outcomes

11 Replies

philmodjunk Nov 26, 2017 2:57 PM
I can't tell from the screen shot whether the Child TO's are all based on the same data source table or different data source (base) tables.

If they are all the same, your calculations work because the calculation field probably specifies Income as the "context" for the calculation.

If the are not the same as I suspect is the case, then it works because the relationships, even though they trace through Members to get to Income all correctly match to the right record in Years, this may be a dangerous conclusion to make as it may be that the current sample data works for you but additional values in your tables might lead to different results that are not correct.
Like • Show 1 Like1
Actions
cmj @ philmodjunk on Nov 26, 2017 8:28 PM
When I had the TO's, they were all based on the table Years.
If they are all the same, your calculations work because the calculation field probably specifies Income as the "context" for the calculation.
No they did not specify Income at all.
...even though they trace through Members to get to Income all correctly match to the right record in Years...

More likely the case. The results of the fields in Years are used as column headings in numerous layouts. There is no other data stored in the table Years. The fields calculate this year and subsequent years Year(Get(CurrentDate)+1)...+2, +3....
This keeps years dynamic as our years move forward.
Should I make TO's of Years for all of the Tables below Income or do you feel that given the role of Years, I can avoid the TO's.
Thank You
Like • Show 1 Like1
Actions
philmodjunk Nov 27, 2017 6:26 AM
“When I had the TO's, they were all based on the table Years.”

This is not what I meant. I was referring to the child tables shown in your screen shot, not the ones that you first removed.
Like • Show 1 Like1
Actions
philmodjunk Nov 27, 2017 6:51 AM
In any case, if you are only using the Years table to show column headers and it's the same data in every context, what is the purpose of the YearsID fields?

Sounds like data that could be loaded (at startup) into global fields from Years and then no relationship at all is needed.

The current setup could fail if there should ever be no matching Income records when you need this data for one of the other tables.
Like • Show 1 Like1
Actions
cmj Nov 27, 2017 8:31 AM
Thanks Phil. Yes, the Years table's only purpose is to show column headers on various layouts (in the Header section). After making one generic layout with the column headers, I copy the layout to create new layouts, populating the Body of the layouts with fields that are contained within the table the layout is serving.
As I mentioned before, no records are created or deleted via the Years table relationship. Your suggestion of an unlinked table and using global fields makes sense but I have to understand more about global fields before I make the change. As long as the Global field is "alive" throughout the db and it can be used in conjunction with any layout, it will work. Note that the Years table has 60 fields. Will this have a detrimental affect on the database if they are all Global fields?
Thanks
Like • Show 0 Likes0
Actions
philmodjunk Nov 27, 2017 8:55 AM
Is there only one record in the years table?

A global field may be placed on any layout in your file. However, a global field cannot be permanently modified by a client of a hosted database. Any editing of the data in the field is lost when the file closes. Thus an "onFirstWindowOpen" script is often used to "load" global fields with data from corresponding non global fields in a utility layout. That way, you can edit the non-global fields in order to make a change to the data that will "stick" when the file closes.

Linking the table to Members via a Cartesian join may also be an option to consider.
Like • Show 0 Likes0
Actions
cmj Nov 27, 2017 9:32 AM
Yes - just one record in Years. And it will always remain that way, as there's no access to it from an interface and no related calculations that will change the record in Years.
Cartesian join?????
Like • Show 0 Likes0
Actions
philmodjunk Nov 27, 2017 9:48 AM
You could look it up.

A Cartesian join links any record in table 1 to all records in table 2. In FileMaker, you can double click a relationship line in your relationship graph to get a dialog box where you can change the default relationship operator (=) to a different operator such as the X that will produce a Cartesian Join.
1 person found this helpful
Like • Show 0 Likes0
Actions
cmj Nov 27, 2017 11:08 AM
I did as you suggested using a Cartesian Join. Everything still works fine. I don't really understand how this works though?
Like • Show 0 Likes0
Actions
philmodjunk @ cmj on Nov 27, 2017 11:40 AM
From what I can see here, accessing the data in years from the context of any of the table occurrence boxes visible in your graph does not make sense unless there's a member record. So a single link to members should make this available to all the different contexts where you may need this information. If you linked the TO to any one of the Child tables shown here, you might not be able to access the data from the context of the other tables should there not be a related record linking member to Years. Plus this type of "tunneling" as it's called can slow down solution performance as well as complicate your design.

If you don't understand how a Cartesian Join functions, it's unique to the other options. As I stated earlier, any record in one table will automatically match to all records in the table on the other side of the relationship. If you had multiple records in Years and put a portal to Years on a Members layout, the portal would show all records in years.

In fact, you could now go into Manage database, delete the YearsID fields from Years and Members and the Cartesian relationship would still exist and still function. I don't recommend that, but it would still work if you did.
Like • Show 0 Likes0
Actions
cmj Nov 27, 2017 12:04 PM
Thank you for your explanation. The original table layout and joins did not sit right with me as the relationship flow appeared to be operating backwards through Members. I'm pleased that it's corrected to avoid potential future problems.
The more I learn in FM, results in the more I see that I need to learn in FM.
Like • Show 0 Likes0
Actions

Odd Table Occurrences

Outcomes

Related Content

Recommended Content