7 Replies Latest reply on Jun 25, 2010 3:11 PM by philmodjunk

    portals or merge field?

    HarvJNep2n

      Title

      portals or merge field?

      Post

      Mac OS10.6

      FileMaker Pro 11

      Not using web

      Novice-Intermediate user

       

      The nature of my business dictates that I set up more than a regular "contacts" database. Dealing with the military, a single unit (say, the 55th Fighter Squadron) may change contacts as often as every few months as officers are promoted or transferred. We usually deal with the same units, but the contact person is always changing. 

       

      I have set up a table called "Organizations" and one called "Contacts."

      The organization addresses, phone numbers, order information, and order history, etc. will not change. I would like to be able to click in the "contact" field, and select the name of the contact (most recently modified at the top). That way I could see which contacts were previously linked to this organization, and the fields would auto-fill with the most recent/current contact. 

       

      The organization name is the field that links the two tables. I have tried setting up a portal, but I'm having trouble getting the information to display correctly and to update when it's modified. I would like to be able to enter new contacts on the fly through the portal.

       

      Also, the information I'd like displayed through the portal includes the Rank, Name-first, Name_last, phone1, and email1. However, I want the name displayed together on one line, and the phone and email addresses separate. As I have the portal set now, I can change the names with a scroll bar, but if I move the phone1 and email1 fields out of the portal, they will not update as I select the contact name. 

       

      So....

      1. Is the portal the best way to go about this solution

      2. If not, what is the better way?

      3. If so, how can I "select" a line of the portal so that all current dealings, invoices, etc. show the name of the contact currently at that unit?

      4. How can I update the email address and phone number when a new name is selected or input?

       

      Thanks!

        • 1. Re: portals or merge field?
          philmodjunk

          Name based relationships can be trouble. Serial number ID fields avoid a number of problems.

           

          You can specify a sort order on your relationship. You can place a date field in your contacts record and update it with the current date whenever you link it to a specific organization record. If you specify a sort order for the relationship that sorts the records in descending order by this date field, then a portal to contacts will list the most requently linked contact record at the top and fields from contacts placed directly on your layout, such as the phone, email fields will be from this, most recently added record. That handles the issue of adding/selecting a new contact and having the phone and email fields automatically update.

           

          That's the basic outline here. There are a number of possible variations and you might want to use a join table if it's important to see a list of all past contacts even though they've since transferred to a different unit and you need to also see them as the contact for that new unit without losing the "history" of being linked to their previous unit.

          • 2. Re: portals or merge field?
            HarvJNep2n

             Thanks for your help and response!

             

            I currently have a Modification timestamp  field set to auto-enter each time the Contact record is modified. Will that work for the date field you suggested? I have specified a sort order based on that timestamp field. 

             

            Is there a good link you know about which details making portals visually look and act the way you want them to? 

             

            I have a serialized Organization ID, but how do I set up a relationship between Contacts and Organizations using that ID? Won't that limit each contact to only one Organization? Each contact will only be part of one organization at a time, but they will likely change.

             

            What would you suggest as the key field between these two tables?

             

            -Bryan 

            • 3. Re: portals or merge field?
              philmodjunk

              "I have a serialized Organization ID, but how do I set up a relationship between Contacts and Organizations using that ID? Won't that limit each contact to only one Organization? Each contact will only be part of one organization at a time, but they will likely change.

               

              What would you suggest as the key field between these two tables?"

               

              Your organization ID is exactly the right field to use to link one organization to many contact records.

               

              OrganizationTable:: OrganizationID = Contacts:: OrganizationID

               

              You can put a matching OrganizationID number in the Contacts:: OrganizationID field as you need and they will all show in your portal. I think the sticking point for you is how to add/remove links to contacts.

               

              The simplest is to use a button and scripts to find a contact record on a contact layout and have Contacts:: OrganizationID formatted as a drop down or pop up listing all organizations with a two column value list. Column 1 is the ID number and Column 2 is the organization name and you can draw both values from their respective fields in the Organization table. If you set a validation rule to enforce unique values on OrganizationTable:: OrganizationName, you can even hide the ID number column and just display the organization names in the drop down. This is particularly useful if you use a pop-up menu as you will see the name displayed, but the ID number is what's actually stored in the field.

              • 4. Re: portals or merge field?
                HarvJNep2n

                OK. Thanks.

                 

                Am I correct in assuming that in addition to the OrganizationID I would also need a unique ContactID if I were going to create a many-to-many relationship such that one contact could be a member of multiple organizations, and one organization could be associated with multiple contacts?

                 

                I have the following problem now:

                 

                On the Organizations layout, I have referenced fields from the Contacts layout, including Rank, FirstName, LastName, Phone1, and Email1. The Ranks field is a drop-down list based on a conditional value list related to a table called Ranks which has all the Navy ranks, the Air Force ranks, etc. so that data entry personnel cannot inadvertently tag an Airman as a Seaman, for example. These are based on the key field "branches."

                 

                I have created a button called "New Contact" that opens a window, goes to the Contacts layout, enters the matching organization ID, thereby populating the Organization and Branch fields. I choose the rank from the drop-down list, enter the name of the officer, and click an OK button scripted to close the window and return to the Organizations layout. When I'm back on the organizations layout, the selected rank is NOT visible. I can click that field an select the rank, but the name information shows up, so why doesn't the rank that I just entered in the previous window?

                 

                 

                • 5. Re: portals or merge field?
                  philmodjunk

                  Is this field inside the portal or on the layout?

                   

                  Does it refer to a field in organization or contacts?

                   

                  It should refer to a field in contacts and if inside the portal, you' should see it as part of the same contact record as the rest of the fields shown.

                   

                  If you place this field directly on your layout, it will refer to the "first" related record in contacts. What record is "first" depends on whether you have specified a sort order for the relationship in Manage | Database | Relationships. If you have not specified a sort order there, it will show the first created record which may or may not be the same record as the one you just edited if you have more than one contact record with the same OrganizationID. That's why I suggested sorting the relationship by a date field. If you place this field inside a portal to contacts, you can specify a sort order in portal setup... If you place it directly on the organization layout, you'll need to specify a sort order in Manage | Database | Relationships.

                   

                  For a many to many relationship, you'd need a contactID (You should have this anyway even if you don't set up a many to many relationship.) you also would need a Join table with matching Organization and Contact ID fields. To link a contact to an organization, you'd create a record in this table and enter the correct Organization and Contact ID fields.

                   

                  Here's a many to many demo file you can download and play with if it helps you better understand how it works: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                  • 6. Re: portals or merge field?
                    HarvJNep2n

                    The Rank field is inside the portal. It refers to a field in Contacts. In the Contacts table, the field is populated. In the Portal on the Organizations table, however, the field is empty until I click into it and choose a value. 

                     

                    I guess I don't understand how fields in portals relate to the same fields on their home tables. Can I have the field in the portal Lookup the the field in the Contacts table, or would that be like telling the field to Lookup itself? I've tried relookups. I don't understand how I can see the first and last name but not see the rank. all those values are present on the contact layout, just the rank is not visible through the portal on the Organizations layout. 

                     

                    Thanks for the Many-to-many link. 

                     

                    I did sort by the timestamp-mod field. Is there a problem with that? Should the timestamp field actually be INCLUDED in the portal in order to be used for sorting?

                     

                    I really appreciate your help. 

                    • 7. Re: portals or merge field?
                      philmodjunk

                      Make sure this is a field of type text and not number.