3 Replies Latest reply on Jul 27, 2011 8:35 AM by philmodjunk

    How to use a portal to create records with two references to other tables

    kenfromboston

      Title

      How to use a portal to create records with two references to other tables

      Post

      Hi,

      I'm new to using portals, and I need some assistance in an interface to implement the following:

      I am building a database to keep track of photographs and the people who appear in them.  A simplistic version of the tables that I've set up are:

      "Documents" Table.  Fields are:

      ========================

      doc_id (key field)

      document object (holds a reference to the image)

      doc_description (description of the document)

      "People" Table.  Fields are:

      =====================

      person_id (key field)

      person_name (person's name)

      "PeopleInDocs" table.  Fields are:

      ==========================

      doc_id (reference to the document in which the person appears)

      person_id (reference to the person appearing in the document)

      location_description (text such as "front row, second from left")

      I'm currently able to set up the portal so I can create "PeopleInDocs" records for the current record, and specify the text value of the location_description field.  After creation, the doc_id field of the "PeopleInDocs" record is also updated correctly.  But I can't figure out how (or if it's possible) to enter the person's name in the portal field.  For maintenance reasons, I don't want to have the "PeopleInDocs" records contain the actual "People" data.  Can a portal row contain a dropdown list of the people in the "People" table, so I can select one?  Should there be a script that displays some sort of dialog or prompt for a person after the rest of the data for the portal record has been entered?

      Any advice is most welcome.

      Thanks,

      Ken

        • 1. Re: How to use a portal to create records with two references to other tables
          philmodjunk

          You appear to have this many to many relationship: (Let me know if I get this wrong)

          Documents---<PeopleInDocs>-------People

          Documents::doc_id = PeopleInDocs::doc_id
          People::person_id = PeopleInDocs::person_id

          So if you enable "Allow creation of records via this relationship" for PeopleInDocs in the Documents---<PeopleInDocs relationship, you can place a portal to PeopleInDocs on your Documents table and use it to link the Documents record to existing records in People.

          Put the PeopleInDocs::person_id field in this portal and format it to list the Person_ID's from People as field 1. Specify the name field from People as field 2. When you select a person from this list, you'll create a new record in PeopleInDocs that is automatically linked to Documents and the value you have selected will link it to a record in People. You can use a pop up menu with the "show only values from second field" option to display the names or if you prefer a drop down list, you can add the name field from People to this row to display the name.

          • 2. Re: How to use a portal to create records with two references to other tables
            kenfromboston

            Hi PhilModJunk,

            Thank you for the detailed information.  I've managed to get the portal working, and have enabled the addition and deletion of PeopleInDocs records from the portal window in a Documents record.  The only thing I'm still wondering about is if there's some way to have field 1, which currently displays People::person_id to display the associated People::person_name instead, while maintaining the underlying value of People_person_id.  Since the person_id is just a record sequence number used as a key, it's kind of distracting to a user to have it exposed in the user interface.  What I'd like to implement is a dropdown/popup that works like HTML dropdowns, where each entry has a displayed value and a potentially different stored value.  I believe that I investigated this back when I was using Filemaker 10, and this wasn't possible then, but I'm wondering if it can be done now.

            Thanks,

            Ken

            • 3. Re: How to use a portal to create records with two references to other tables
              philmodjunk

              There are ways to do what you asked. I held off on that because we needed to get the basics working first.

              The simplest method is to set a unique values validation on the name field in companies that's used as Field 2 to make sure that this field is always unique. Then you can select the "show only values from second field option" and the ID numbers in field 1 that are entered into the field when a person is selected are no longer shown in the list. If you make your format a pop up menu, the name in field 2 is what is displayed even when you exit the field.

              This is the simplest approach, but not always the best approach. After all, one of the reasons we use ID numbers is that Names aren't unique in the first place and here we've gone and added a validation that requires that very thing. (If you don't enforce unique names, a second entry with the same name but a different ID is excluded from the list if you show only field 2 or sort the values on that field.) On the other hand if you have a Company record that is for "Acme Inc.", Company ID = 5 and another for a different "Acme Inc.", Company ID = 532, there is no way to tell which "Acme Inc." to select just from your value list anyway.

              The workaround that deals with both issues is to come up with a "name field" that combines the name with other data in your table so that is both no longer unique and provides the user with enough information to correctly select the right record from the value list. This might be to create a text field that uses an auto-enter calculation to combine the company name and address or the name and a phone number (or all three). You can set your unique values validation rule on this field and use it as field 2 in your value list.

              Of course, duplicate names are more rare than two people with the same name (but still happen). So you may be able to get away with just the company name and a unique values rule.

              The other drawback to value lists that enter a number into a number field like this with text in field two is that auto-complete is no longer an option and this feature is very useful when value lists become very long. It's possible to use a scripts with a value list or with filtered portals to facilitate selecting values by name from longer lists (such as hundreds of companies).

              Since this is common need encountered here in the forum, I've created a demo file that demonstrates several scripted techniques that can make working with long value lists much easier:  http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html