14 Replies Latest reply on Apr 13, 2012 2:01 PM by Tucker

    2 Portals

    Tucker

      Title

      2 Portals

      Post

       I am using FM11 and am new to FM. I am building a contact management db and have run into a brick wall.

      For this example I have 2 tables, Customers and Contacts. An edit customer form layout contains all of the customer fields for editing. The bottom portion of the layout contains a portal with all contacts belonging to the specific customer currently on the edit layout.

      The contact portal is on a tab control and allows editing of contacts as well as adding new contacts. The first tab is labeled Contacts. The tab control also has a second tab labeled Contact Notes. I'm trying to accomplish the following:

      Once a specific contact record is in focus (in the Contacts tab), I want to be able to click on the Contact Notes tab and see notes that related to the specific contact. Problem is that I cannot create this relationship between the tabs since Contacts are a child records of the main parent layout - which is Customer. Is this possible to accomplish? Please keep in mind that I am very new to this!

        • 1. Re: 2 Portals
          philmodjunk

          You need three tables for this:

          Customers---<Contacts-----<Notes   (---< means one to many)

          Customers::__pk_CustomerID = Contacts::_fk_CustomerID
          Contacts::__pk_ContactID = Notes::_fk_ContactID

          This is your basic set of relationships, and allows you to put a portal to notes on your contacts layout, but to get the portal on your tab in customers, you'll need to make a new occurrence of notes and link it directly to customers. Then a trigger controlled script can update a field in Customers to link to different sets of records in Notes to produce a portal of notes "synched" to your contacts portal.

          In Manage | Database | relationships, make a new table occurrence of Notes 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 to be ContactNotes.

          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:

          Customers---<ContactNotes

          Customers::gSelectedContactID = Notes::_fk_ContactID

          Use the OnObjectEnter trigger for the contacts portal to perform this script:

          Set Field [Customers::gSelectedContactID ; Contacts::__pk_ContactID]

          • 2. Re: 2 Portals
            Tucker

             Thanks Phil. I believe I did as you suggested:

            Created a Notes table and related back to Contacts.

            Created an occurance of the Notes table and named it ContactNotes

            Created a global field in Customers table and related it to the ContactNotes table: Customers::gSelectedContactID = Notes::_fk_ContactID

            Dropped a Notes text field (from the table instance ContactNotes) into the tab control (2nd tab called Contact Notes)

            Tied the script to the 2nd tab on the tab control: Set Field [Customers::gSelectedContactID ; Contacts::__pk_ContactID]

            Result: I can type in the Notes text field on the second tab but when I attempt to leave the tab I get an error "This field cannot be modified until "_gSelectedContactID" is given a value."

            I must have missed something here?

             

            • 3. Re: 2 Portals
              philmodjunk

              I assumed you'd use a portal to Notes on the second tab. The purpose of a separate table is so you can record multiple notes about the same contact and a portal would be part of the set up you'll need for that. A simpler structure is possible if you choose to limit your system to one note for every contact.

              For your existing records, does __pk_ContactID have a value? This field will not recieve a value automatically except for each new record you create in Contacts. If you have records in contacts that existed before you created this field, you'll need to give them values. An easy way to update them is to use the Replace Field Contents tool to give all your existing records serial numbers in this field.

              • 4. Re: 2 Portals
                Tucker

                 I should also let you know that I wish to create new notes for the contact in the 2nd tab (Contact Notes) of the tab control as this is the only place where contact notes can be added or viewed. There is no main layout to edit contacts. Contacts are edited via the portal in the Customer Edit layout.

                To recap: The main layout is Customer Edit.

                Corresponding contacts (can be one or more) related to the customer are located in a portal in the Customer Edit layout. The portal is located in a tab control with (2) tabs - Contacts on the first tab and Contact Notes on the 2nd.

                When a specific contact is selected in the portal contained in the Contacts tab (1st tab), I want to open the Contact Notes tab (2nd tab) and create a new note and or review existing notes.

                Existing tables are Customers, Contacts, ContactNotes. A new table occurance of the table ContactNotes now exists and a global text field called gSelectedContactID resides in the Customer table.

                Just want to ensure that I am representing the situation clearly. Thanks in advance for assistance on this. 

                • 5. Re: 2 Portals
                  Tucker

                   I will change the text field to a portal on the 2nd tab.

                  ...For your existing records, does __pk_ContactID have a value? Yes - all of the contacts have a pkContactID.

                  • 6. Re: 2 Portals
                    philmodjunk

                    No problem there as long as you enable the "allow creation of records via this relationship" option for ContactNotes in this relationship.

                    What I presented first in my initial post (Customers---<Contacts-----<Notes   (---< means one to many) ), showed the "conceptual relationships" typical of your setup. It's your choice whether or not to create and use a different layout based on contacts for working with notes. The relationship linking Contacts to Notes will not be needed then, but you might want to keep it in place just in case you need it in the future. (One other thing you can do with such a relationship is set up a layout based on Notes for printing them out with data from Contacts and Customers included on the layout.)

                    • 7. Re: 2 Portals
                      Tucker

                       I have gone with your suggested concept relationships. Added the portal to the second tab for Contact Notes and check "allow creation of records via this relationship". Still same result after typing in the notes :  "This field cannot be modified until "_gSelectedContactID" is given a value."

                      • 8. Re: 2 Portals
                        philmodjunk

                        That indicates that the field is not getting a value from the pk field.

                        Let's try a different trigger. Set up the portal with the OnObjectExit trigger and see if that makes a difference here. There may be cases where you create a new record in contacts and then there will be no value to put in the global field at the time you enter the portal, but it will be there by the time you exit it.

                        Also, while debugging your design here, put the field on your notes tab so that you can see whether or not it is getting a value.

                        • 9. Re: 2 Portals
                          Tucker

                           No luck. Same result. As you said the field is not getting the pk. 

                          • 10. Re: 2 Portals
                            philmodjunk

                            I ran a quick test. OnObjectExit should be capturing the ID number--at least it worked for me.

                            Let's check a few details. Compare what you did to what I did and see if you see any differences.

                            Enter layout mode

                            Right click the lower part of the portal to be sure that I am selecting the portal and not a field in the portal

                            Choose script triggers

                            Select OnObjectExit

                            Specify the script that sets the field with the contact ID from the contact portal's contact table occurrence.

                            When I click into any field in the portal and then click outside the portal, I see the ID number appear in the field.

                            • 11. Re: 2 Portals
                              Tucker

                               Previously I had the script trigger right on the 2nd tab where the notes portal is located. I removed it and placed it right in the portal. Now when I type a new note and then click outside of the portal nothing remains. I also have a ContactID field in the portal and it remains blank. May my 2nd. ContactNotes table occurance relationship is wrong?

                              • 12. Re: 2 Portals
                                Tucker

                                Well...have to sign off for the night. I will come back to fight it in the morning. Thanks! 

                                • 13. Re: 2 Portals
                                  philmodjunk

                                  This trigger needs to be on the Contacts portal rather than the Notes portal.

                                  • 14. Re: 2 Portals
                                    Tucker

                                     Thanks Phil. I got it working after specifying on the 2nd tab - the notes field from the Notes table occurance vs the original ContactNotes table.

                                    There is some strange behaviour going on with some records but I will work on that to see were the problem is. I reallyappreciate your help on this!