FileMaker certainly enables you to work with "various fields across several tables". The difference is that in other systems, you can construct SQL expressions "on the fly" that link your tables as needed where with FileMaker, you have to construct the same links in advance in the Relationship Graph. Given that you can use multiple table occurrences for the same data source table to establish as many different relationships as you need.
Thus, portals can contain fields from more than one table, provided you've established the needed links in the Manage | Database | Relationships.
And you can also place fields from several tables on the same layout by the same rules.
I am in the same boat as you Raymond. Besides this forum and the excellent help you get from it, I reccommend a copy of "The Missing Manual" series for your version of Filemaker. Very easy to understand, and covers the gamit of FM functionality. My copy is a month old and well used.
So here is hopefully a more clear explanation of what I am attempting to do. Still researching this but any further direction would be helpful.
Let me know if this is possible
Table 1 : Transaction (real estate transactions)
This will hold data such as contract terms associated with the transaction.
Table 2: Customers (these are basically sellers & buyers and their contact and personal details) This needs to be separate because there could be various numbers of sellers and/or buyers.
Table 3: Principals (basically this will hold the transaction_id, the customer_id). The idea hear is I could add multiple buyers and sellers id to this table and associate them all with the same transaction id to be pulled later.
The problem here is I am unsure how to pass the data from "PRINCIPALS" over to the transaction interface based off the common transaction_id that is saved in this table.
I am use to other languages where you would use a query like "Search * tablename WHERE Principal Type="Seller" and _kp_transaction_id = "tra00001" based off the same setup. Than I would dynamically loop them and manipulate the layout with the found results.
I would like to pull the date over to this interface and manipulate it. I have searched various resources but have not been able to find anything like this.
What I can do is pull data into an interface based off of one foreign key id (related tables). This only gives me the option of doing this once so if there are multiple sellers and buyers with the same transaction_id this doesn't work.
I also am able to pull in the portal if I set up my relationships differently but than I lose control over the ability to manipulate the layout
Any help or assistance. Thanks again.
It sounds (to me) as if the structure is fine. The Principals table is basically a "join" table, between the Customers and the Transaction (with an ID from each those two tables, and possibly a unique auto-enter ID of its own, though not required at this point). Each person can have one of two "roles" in Principals, either "buyer" or "seller."
If you create what I think of as "dummy text calculations", that is a calculation field with the word "buyer" as its contents, and another with "seller" (both can be unstored, but it hardly matters). Then if you include each of those calculation fields in relationships, to two more "table occurrences" (the large boxes on the Relationship Graph, TO for short) of the Principals base table, then you can target only "buyers for this transaction" and "sellers for this transaction" separately.
In FileMaker 11 you could actually do this with only a filtered portal, not requiring the two calculation fields or extra relationships, if you only want this for display. But a real relationship can do more, like navigation and calculations (more on this later). If you have 11, try it with just the portal filter first and see if it does all you want. In that case the filter would be in the calculation you define for the portal (an option in the portal dialog). This is actually more like SQL, than the more "structural" older FileMaker method in the preceeding paragraph.
You're already targeting Principals via the Transaction ID, so you'd just need to add a filter for:
Principals::Role = "buyer" // or "seller" on another portal.
You can put those "buyer" and "seller" portals separately on the layout, wherever you want. Except you cannot put them where they might cross a page break. They can "slide up", which is a bit of science, a bit of black art (sometimes), to get text and other objects to slide up (the way you want, intellegently hopefully) on the printed page.
There are also methods to "gather" the related records with several fields into a large text result calculation. If you have FileMaker 11 Advanced you can use a Custom Function for this (they can do calculations "recursively"); there's one named GetRows (also requires GetNthRow). From: http://www.briandunning.com/filemaker-custom-functions/list.php
Using that with added tabs (typed into a text editor and pasted) within the calculation, you can then use tab stops within the calculated text, to appear much like a "table" of text (no lines, but otherwise looks OK). It can also more safely cross page breaks. Though, I must warn you, FileMaker 11 has serious problems right now with page breaks; it often cuts the last line in half (sliced horizontally). I've seen this both in Mac 10.6.4, and in Windows 7 (same machine). I imagine the next update will fix it, as it's well known, though who knows when).
But there is it. FileMaker can do much of the same stuff, though it's a bit trickier to gather data from all over. But I have used that GetRow () function in several situations to do reliable printing over page breaks |except in 11 | And just regular portals do fine on a form.