This is a public Forum  publicRSS

Forum post

    Jim Silverman
    Multiple Portals on a Single Layout
    Forum post posted March 7, 2011 by Jim Silverman, last edited February 9, 2012 
    1755 Views, 10 Comments
    Title:
    Multiple Portals on a Single Layout
    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

     

    • PhilModJunk

      That's a lot for one post! Wink

      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:

      1. Enable "Allow Creation of Records Via this Relationship" for the child table in the relationship linking it to the parent record. Then place a portal to the child table on a layout based on the parent record. When you enter data in the bottom blank portal row, a new record in the child table is created and the Primary Key value from the parent record is automatically entered into the child records foreign key field.
      2. On a layout based on the child table, format the Foreign Key field with a value list of the Parent Table's Primary Key values. Usually a 2nd colum in this value list is used to display a text field of some sort form the same parent record to help the user determine which record to select.

       

      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.

    • Jim Silverman

      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

    • PhilModJunk

      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.

    • PhilModJunk

      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.

    • Jim Silverman
      1. Correct on relationships and primary/foreign key associations.
      2. I added the button to the Person portal and the filter on the Purchases portal, and things are working somewhat better.  However, there is still some very strange behavior coordinating and navigating between the portals.  I'm going to have to spend some time experimenting to see if I can figure out what's going on; I'm clearly on step 1 of the learning curve!
      3. I understand how value lists work, and I know that I can set up an assisted data entry mechanism; I never expected the user to enter key values.  However, since the navigation path would always go from family to person, the user would reasonably ask, "Why would I have to identify the family when I've already done so before navigating to the person layout?"  In any case, the solution you suggested to provide a link between the two tables should work nicely.

      Thanks again for your assistance.  I'm sure I'll be posting other questions as I slog through the learning process.

      Jim Silverman

    • Jim Silverman

      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

       

    • PhilModJunk

      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

       

    • Jim Silverman

      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

    • PhilModJunk

      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/

    • mary barrett-sparrow