Table Occurrence, one table can have 1 or more occurrences in one solutions.
TO tells FM where to get the data.
Thanks! Now I know how to search for it so I can learn more about it.
I will try my hand at this.
A table is the "raw" or basic technical definition of a table.
Say a very basic Contacts table, with First Name, Last Name, Address, City, State, Zip, Contact ID.
A table occurrence is an instance of this table on the relationship graph.
A layout must be based on a table occurrence. It can display the fields from this table occurrence.
Through the relationship graph, you can relate different tables to each other.
Imagine a second TO based on Contacts. We define this table occurrence to have a connection to the first occurrence.
We set it up where State = State. We name this table occurrence Same_state_contacts.
Now, we can create a portal on our original contacts layout; and easily see all the other people who live in the same state.
This representation on the graph is a way of allowing data connections to be set up; and a way of allowing layouts to look at the data. Both table occurrences are based on the same set of records. Nothing has been duplicated.
You have the answer (Table Occurrence). Here is where it is referenced:
In FileMaker 14 help:
(follow the links to Related topics, too!)
On this forum:
I think of a TO as the way FM does joins.
In a SQL situation, you'd do a WHERE clause to do joins. While not exactly the same thing, a FM TO is similar since it gives you a way to look at data in different ways. And, unlike SQL, the FM TO is persistent in the database.
For example, if you have a primary key in "CUSTOMERS" and a foreign key back to CUSTOMERS in "ORDERS" you could drag a line in the Relationships tab from the customer PK to the orders FK. Now the two tables are related in a similar way to a SQL (sort of like, .... WHERE customers.pk = orders.customer_fk,).
FileMaker will decide what the cardinality of relationship is and you can't manually override the cardinality. So, if FileMaker looks at your customer and orders tables, it will properly decide that it's 1:M.
If you look at the Relationships tab, and click on the line, you'll see an "=" sign.
You could now, for example, create a portal where you have a customer record at the top and a list (in the portal) of all related orders. You could enter new records in the portal, or probably better, in the ORDERS layout. (Check out the GTTR function in FileMaker for easy switching back and forth).
But, now say you want to look (or count) at ALL customer records independently of the relationship you created above. Can this be done? Sure. Easy.
Create a new Table Occurrence (TO) between CUSTOMERS AND ORDERS by clicking on ORDERS table, then clicking the ++ at the lower left of the Relationships tab.
Connect the line between CUSTOMERS and ORDERS like you did before, but now go into the line connecting them and change the "=" to an "x". Now you have a new relationship where you can do COUNTS and such independently of the relationship from the first TO above.
FileMaker calls this "x" relationship a "Cartesian Product", which has always concerned me since a "Cartesian Product" is generally (in SQL, no where clause) has all records of table1 * table2. So, if you table1 had 1,000 records and table2 had 1,000 records, a Cartesian product in SQL (SELECT * from CUSTOMERS, ORDERS -> no where clause: a Cartesian product) would have 1,000,000 records! However, that's probably not how FileMaker implements the "x" relationship.
There are other relational operators (>, <, etc.) you can use to set up relationships as well as being able to create M:M relationships and probably just about any relationship you could ever need. To me, the TO implementation in FM is one of it's best (along with the excellent UI) features.
(Note: SQL in FM can also get counts and such independently of the relationship)
So, in summary, creating multiple TOs just give you new ways to "join" your tables to view whatever you need to. And, yes your Relationships tab can get very full of TOs, but FileMaker has a very nice ability to shrink these TO down to just the title if needed.
Hope this helps.
P.S. There are lots of YouTube videos on TOs and the FM Database in general as well as all the experts here. I also got lots of good practical examples from the FileMaker training course which has included excellent videos. Lynda.com has great FileMaker courses too, but they're not free after the eval period.
NOT JOINS, more like alias'!
A T.O. can be on the RG (relationship graph) and _not_ have any relationships. In fact a table/TO must be on the RG to be used with ExecuteSQL() function or to be displayed on a layout. And likely a problem if not on the RG with calculations, script, value lists, custom functions...
If you mouseover (hover) on the TO, you will see all kinds of information about the "base table" for the TO. Just "naming" a TO the same as the TABLE, it's still an alias linked to the base.
You are correct on the Cartesian relationship. ALL records are related. It is the same as no WHERE clause in the SQL query.
An alias is just shorthand for the tables (as in SELECT CUSTOMER.PK FROM CUSTOMERS cus, ....), yet the connection between the two tables defines a relationship between them. That's more of a join (as in a WHERE clause) to me. Maybe you can clarify this?
Yes, you're right I didn't describe every possible way to use TOs as I've never need to use the case you described.
That's good additional information about the mouse over. I wasn't trying to do a complete tutorial (your links you included would do that).
YES! the 'relationship' (graphical connector between two tables/TOs) is similar to the SQL WHERE clause (or the ON part of JOIN). The TO is the graphical representation of the TABLE, while not a 'true' SQL alias, it's more like the function of an alias, than the function of the JOIN.
Not to muddy the waters, but this is a sample of the DDR XML output (values removed) for a simple relationship:
<LeftTable cascadeCreate="" cascadeDelete="" name="">
<RightTable cascadeCreate="" cascadeDelete="" name="">
<Field table="" id="" name=""/>
<Field table="" id="" name=""/>
And the RelationshipGraph/TableList in the DDR (before the above information), has something like this (for each TO in the graph, but it's labeled as Table here):
<Table id="" color="" baseTableId="" baseTable="" name=""/> (note the baseTable and name!)
Ahh, yes, now I see what you're saying.
Right, I was focusing on the connector when mentioning the join and not the TO itself.
You're right, and I totally agree, the TO itself is more like an alias in SQL.
You rock Bev!
Correction to my posting above (thanks again to Bev for noticing this).
Instead of saying: "I think of a TO as the way FM does joins.", I should have said "I think of a TO to being similar to an alias in SQL".
So, wherever I said "join" above, I meant the action when you drag a connection between a table and a TO, a table to itself, or whatever "join".
Sorry for any confusion.
P.S. Some time period had expired and I was no longer able to edit my original post so I just added a correction here.
It might be worth noting an historical oddity here...
"TO" was coined and used back in 2007, I think, by a number of FileMaker developers and wasn't initially used by FileMaker Inc.
If I recall correctly, FileMaker just used the word table to mean both the base table and the uses of that table in the graph, but people were looking for a descriptor that distinguished between the two.
and the confusion continues, as Table is used in many places (the DDR), meaning the object on the RG (relationship graph).
I'd prefer Table Object, but no one asked me...