Just a basic question.
As I noticed, the x relationship is used to show all the records in table 2 regardless of the values in the table 1.
What are the benefits of this relationship and when can we use it?
A classic example is to make a connection to a preferences table from anywhere in the solution and being able to pick up preferences stored there.
(Which is only one of a handful of ways of doing the "preferences pickup" thing).
Thank you for your help.
This is a perfect example.
About the preferences:
Is the method you have mentioned better than using script to fill in global fields (from values of the preferences fields)?
It might be the case if we have few tables (because we must relate the preferences table to all the other tables)?
The price you pay with globals is:
- more fields
- work in the onOpen script
- work to update the globals when the settings change in mid-session
The price you pay with an x-relationship to the pref table is
- more TOs on the graph
There is a 3rd method by using ExecuteSQL() to pick up the preferences. Works without global fields and without the extra TOs.
X-joins are also useful for special relationship-TOs used for things like counting total records/orders/whatever in a different table without having to change the found set in the current layout.
I had a test situation this week where an X-join allowed me to summarize data in across otherwise unrelated TOs via a new cartesian join in just a second of refresh time, while, for some reason, using the ExecuteSQL to display the same information took a delay many times as long to refresh the same results. (ExSQL is not always faster; it's worth comparison testing.)
Can you suggest any place to better learn ExecuteSQL()?
Thank you for your help
SQL is a whoooooooole different story with lots of things to learn.
ExecuteSQL only uses the SELECT statement and it is useful to grab information without taking care of context, however, it has limitations. I believe that the best approach is to use a combination of the three methods and use the advantages of one to get over the disadvantages of the others.
If you want to learn the basics of SQL, this is a good starting point:
I prefer to use the X relationship (the correct name is cartesian relationship) for preferences and for cases where I want to artificially create a context but actually there is not related data.
Also, this might be helpful:
Write FileMaker SQL with SQL Explorer - SeedCode
And this one.
Beverly Voth | FileMakerHacks
Thanks, Wim! Nehme, get the pdf and example file. The eSQL reference has many links to other resources as well, so it's a good starting tutorial.
Thank you all for your help
Stephen, I find your test situation intriguing. Could you provide some details about an example doing a summarize across an X-join?
The test was a very simple one, using the Count (table::pKey) to determine how many records existed in each of several tables for a library indexing system.
Separate x-join TOs returned the counts on multiple tables with only the slightest pause in screen updating to display the count on a secondary tab panel when it was clicked. Counting across related tables required additional calculation levels or special fields within the tables, and was much slower, as was the ExecuteSQL count function. So slow in fact that waiting for the clicked panel to redraw and show the sums was a tiresome wait. The special x-join TOs solved the relationship slowness.
Cartesian joins can also be used to force relationships to refresh. Otherwise you might need a Refresh Window [flush cache] script step which would be slower and would discard your session's cache (which is a huge deal for the solution I'm currently working on that has lots of images)
See this blog post:
Ditch those Flush Caches, Use Cartesian Join Instead!
Interesting use. Thank you
Retrieving data ...