4 Replies Latest reply on Aug 21, 2012 9:36 AM by philmodjunk

    Locating specific address in relational table using value list

    bobg

      Title

      Locating specific address in relational table using value list

      Post

      I'm totally new to FMP (v12).  Done some programming with SQL, C#, so have some understanding of relational databases.  But am totally confused on how to use FMP to do look ups, etc.

      Have People table, with People_keyID and things like name, phone...

      Have Addresses table with People_keyID link, this table holds multiple addresses (home, work, beach cottage, boy's camp, etc.).  Addresses appear in a Tab window, I can currently display all addresses in multiple rows, but that's not the goal.

      Want to have a drop down list that displays all address names (home, work, etc.) for an individul "People" record.  When selected, have the selected address cause a different (Portal?) area of the tab screen show that specific address.

      I've tried just the two tables, no luck.  Tried adding a CrossLink table linking People to CrossLink::people_keyID then linking the CrossLink::Address_KeyID to the Address table.  I can make all the relationships work, but cannot get a populated drop down list to cause my selection to display that address in a portal.

      Does this even make sense to anyone?  I like FMP, don't want to do it the hard way with C# and SQL, but am having trouble finding examples or tutorials that address these real-world issues.

      Thanks,

      bob

        • 1. Re: Locating specific address in relational table using value list
          philmodjunk

          So you have this relationship:

          People::People_keyID = Addresses::People_keyID

          Addresses appear in a Tab window, I can currently display all addresses in multiple rows, but that's not the goal.

          What do you mean by a "tab window"?

          If you put a portal to Addresses on your People layout, you'll see all address records for that People record.

          You could set up a filtered portal where a filter expression uses the contents of your drop down to filter your portal down to just one selected record, but given the extra steps needed to handle screen refresh issues, let's handle the issue at the relationship level.

          In Manage | Database | relationships, make a new table occurrence of Addresses by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as SelectedAddress.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Add it to your relationships like this:

          People::People_keyID = SelectedAddress::People_keyID AND
          People::AddressType = SelectedAddress::AddressType

          People::AddressType would be the field you format with your drop down list.

          With this relationship, you can add fields from SelectedAddress to your layout to show the address selected from your drop down.

          • 2. Re: Locating specific address in relational table using value list
            philmodjunk

            sorry.

             

            Some glitch with my browse submitted my post before I finished typing it. I've edited the post to complete it, but this won't fix any notification appearing in your email.

            • 3. Re: Locating specific address in relational table using value list
              bobg

              Thanks for the quick response.  It worked great!!  I never would have thought of a second "view" of the same table with a second relationship that showed only selected items.  My background would have been to write reams of code... I think I like FMP.  BTW, the "tab window" is just a tab control so I can do other stuff in the same space besides just show addresses.

              • 4. Re: Locating specific address in relational table using value list
                philmodjunk

                We call those "views" table occurrences. They're a key tool to use in many different aspects of FileMaker data modeling. See this thread if you'd like to learn more: Tutorial: What are Table Occurrences?