Forum post
As a newcomer to FileMaker, I understand tables and their relationships. I have three tables: Family, Person, and Purchases, each related to the next via key fields. I have a layout based on the Family table, and a portal on that layout based on the Person table. This portal seems to work properly vis-a-vis its parent table in the layout. However, I also want to put another portal, based on the Purchases table, on this same layout. I'd like the behavior of this layout to be as follows: When I select a record in the Person portal, I'd like only the related records from the Purchases table to appear in its portal. That said, I'm not sure how to tell the Purchases portal which record is the "current" record in the Person portal.
I also want to be able to create and delete records in the Purchases table via the Purchases portal.
How do I set up these portals? In addition, how do I declare the foreign key fields in the child tables? As auto-enter with a calculated value from its parent's primary key field? (It doesn't seem as though I should have to do this, having declared the explicit relationship between these tables based on these primary/foreign key fields.) Is there anything else I need to do when setting up the data model and the relationships, or the portals that present these relationships to the user?
Thanks in advance,
Jim Silverman
Answer
That's a lot for one post!
The first problem is the classic "detail view" problem that is sometimes done with a second portal like you describe. The simplest approach depends on which version of FileMaker you are using. FileMaker 11 added a portal filter feature that can make this approach simpler to set up. So let me know what you are using here. When implemented, you'll have either a button in the portal row or all the fields in the portal row can be set up as a single button. Clicking the button selects the portal row and also performs a script that will interact with your detail portal so that it shows the detail records for the selected portal row.
In much the same method, you can add a button in the portal row that uses Delete Portal Row to delete a row of the portal. The mouse click on the delete button selects the portal row and then performs the delete. The confirmation dialog that appears can be cryptic so I came up with a scripted approach you can use with the delete button. If you download this file: http://www.4shared.com/file/8orL8apk/FMP_Bugs.html, it has a delete portal row script you can import into your file that can work with any portal on any layout in any file without any modification. The trick is to pass it the correct list of parameters. You can enter layout mode and click a D button in any portal in this file to see the parameters that are passed. The file also illustrates conditional formatting and tool tips used with these delete buttons to help make them more user friendly.
how do I declare the foreign key fields in the child tables?
These fields are simple data fields--usually a number field. No auto-enter calculation can be used as there isn't any parent record associated with this new child record until you assign a value in this field anyway. The two most typical ways to link a new child record to an existing parent record are:
There are other ways to automate assigning a value to the child's foreign key field, but these two methods are the simplest, most basic ones to set up.
Be the first to like this
|
Sign in to "Like"
Thanks much for the reply, and sorry to be so long-winded. I'll try to keep it brief (and to a single point)!
First, I'm using FM Pro 11, so the portal filter capability is present. I look forward to your description of how to set up the coordination and navigation between the first-level detail portal (i.e., Person) and the second-level detail portal (i.e., Purchases).
As for the foreign key fields, I did set them up as number fields without auto-enter. However, they didn't behave as I had expected. In particular, I had thought (hoped?) that when I had an active parent record (e.g., Family), and navigated to a layout based on the child record (e.g., Person), that when I created a new record in the child layout the defined relationship would automatically fill in the foreign key field's value. That did not happen. Even when I used GTTR to navigate to the child layout, the Create New Record operation did not provide any foreign key value. I'm sure I can script the record creation process to include a step that will initialize this field's value, but I wanted to confirm with an expert that this is, in fact, necessary.
FWIW, I'm dealing with a very unsophisticated user community, and cannot expect them to select values, even from a value list. Therefore, this step in the process must be automatic.
Thanks again for your thoughts. Jim Silverman
Be the first to like this
|
Sign in to "Like"
I think you have these relationships:
Family----<Person----<Purchases ( ---< "means one to many" )
Family::_kp_FamilyID = Person::_kf_FamilyID
Person::_kp_PersonID = Purchases::_kf_PersonID
On a Family layout, you can place a portal to Person and a second portal to Purchases.
On the Purchases, portal define this portal filter expression: $$PersonID = Purchases::_kf_PersonID
You can then either select all the fields in the Person portal and use button setup... to turn the entire row into a button or you can add a button to the portal row (adding a separate button allows you to edit the fields in the portal row) to run this script:
Set Variable [$$PersonID ; Value: People::_kp_PeopleID]
Refresh Window [Flush cached join results]
You can also define a conditional format with this expression: $$PersonID = People::_kp_PeopleID to assign a contrasting fill color to each of the fields in the People Portal. Then they will "highlight" to show which portal row is currently selected for the purchases portal.
Be the first to like this
|
Sign in to "Like"
I'm sure I can script the record creation process to include a step that will initialize this field's value, but I wanted to confirm with an expert that this is, in fact, necessary.
In FileMaker there is no built in link between the current record of one layout and the current record of another. Keep in mind that you could have 5 or 6 layouts that all refer to family and they might each have a different current record. (This also wouldn't work with Access forms either.)
What you can do is place a "new person" button on the Family layout. When you click it, its script can use Set variable to store the _kf_FamilyId value in a variable, switch layouts, create a new record, and then use set field to enter the value in the variable into the People::_kf_FamilyID field. A new person button on the person layout, can switch to the Family layout, capture the ID, then switch back to create a new record and enter the value.
FWIW, I'm dealing with a very unsophisticated user community, and cannot expect them to select values, even from a value list. Therefore, this step in the process must be automatic.
I think it's possible you are underestimating your users here. They won't be selecting ID numbers, they'd just click on the field, see a list of names and select the name of the family they need this record to be a part of. There are also ways to set up search boxes where the user types in a partial name and gets a list of all matching records. Selecting from the list then enters the appropriate Primary Key into the child record's foreign key field.
Be the first to like this
|
Sign in to "Like"
Thanks again for your assistance. I'm sure I'll be posting other questions as I slog through the learning process.
Jim Silverman
Be the first to like this
|
Sign in to "Like"
Well, I've made a great deal of progress in the past couple of days. Per your advice, Phil, I've successfully developed the Family/Person Parent/Child layout navigation procedure, and can now add, delete, and edit the Person records via the Person layout.
However, I'm still stuck on the coordination of the contents of the two portals on the Family layout. Again, per your suggestion I am able to successfully coordinate the rows displayed in the Purchases portal using a portal filter. However, when I attempt to add a new row to the Purchases portal, the foreign key assigned to the new row is always that of the same row in the Person portal, regardless of which row is active in this portal. In my example, the active row in the Person portal is that for Person ID 57; this is confirmed by the fact that the title in the Purchases portal refers to the proper Person's first name, and the first two rows of the Purchases portal were properly loaded using the portal filter (i.e., the Person ID). However, when I add a new row to the Purchases portal, the Person ID - which is derived from the Person/Purchases relationship - is 32, the primary key for the other row of the Person portal.
(I tried to attach a JPEG image of my layout, but couldn't get it to work.)
Am I trying to do something that is not possible with FM, or am I just going about it the wrong way?
TIA,
Jim Silverman
Be the first to like this
|
Sign in to "Like"
You are assuming that FileMaker somehow "knows" that the currently selected Person row in the portal is to be the parent for the new record in the Purchases portal. FileMaker knows nothing of the kind and thus you don't get the results you want. With the relationships you have here, I imagine that new Purchases records created in the portal are getting the Person ID of the first related Person record.
You'll need to add either an auto-enter calculation or a triggered script to enter the ID number from currently selected Person into the new Purchases record. Since this number is captured in the global variable, your auto-enter calculation or script can simply refer to that variable to enter the desired value into the new record.
There is an alternate approach that eliminates the extra "fiddle" to get the right value in new Purchase records, but you end up with a more complicated relationship graph so I don't know if it's really an improvement or just different.
If you had these relationships:
SelectedPurchases>-----Family----<Person----<Purchases ( ---< "means one to many" )
Family::_kp_FamilyID = Person::_kf_FamilyID
Family::gSelectedPersonID = Purchases::_kf_PersonID
Where SelectedPurchases is a new table occurrence of Purchases and gselectedPersonID is a field with global storage specified.
Your Purchases Portal would then be pointed at SelectedPurchases instead of Purchase (If you make this change, each field in the portal has to be respecified to come from SelectedPurchases instead of Purchases.) The script that is performed when you select a portal row would assign the PersonID number to this global field instead of the global variable and you would remove the portal filter on this portal. (And refresh window is no longer needed.)
Now, new records created in the portal to SelectedPurchase will auto enter the contents of gSelectedPersonID and this will remove the need for special handling each time a new record is created in the portal.
Definition: "Table Occurrence" is the name of a "box" in Manage | Database | Relationship. Any given Table defined on the tables tab can have any number of Table Occurrences on the Relationships tab that refer to it. You can duplicate an existing table occurrence by selecting it and then clicking the button with two green plus signs.
For more on table occurrences see here:
http://forums.filemaker.com/posts/3d8b364a6c
Be the first to like this
|
Sign in to "Like"
Thanks, Phil.
I believe you are correct in your assumption that FM is simply providing the key to the first record in the portal. Sigh.
I had actually thought about adding a second occurrence of the Purchases table, with a direct relationship to the Person table, but (as you pointed out), it would add unnecessary hair to what is actually a pretty straightforward relationship graph.
Per your suggestions, I'll fiddle around with the auto-enter approach and see if I can make a go of it.
Thanks again,
Jim Silverman
Be the first to like this
|
Sign in to "Like"
FileMaker often requires extra table occurrences in order to get the interface and scripts to work correctly. One technique to help get order out of the potential chaos of a "spider webbed" relationship graph is called Anchor Buouy. If you are unfamiliar, it's worth a read:
http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/
Be the first to like this
|
Sign in to "Like"
Be the first to like this
|
Sign in to "Like"