9 Replies Latest reply on Feb 2, 2011 9:46 AM by JerryArntson

    Selecting records in one Portal to activate information in second and third Portal

    JerryArntson

      Title

      Selecting records in one Portal to activate information in second and third Portal

      Post

      In FileMaker Pro 11 and I have build a database to track commercial real estate buildings with tables for building info, owner, tenants, sale comps, and contacts made. One of my layouts allows me to see all of this information in one place. Essentially in four quadrants: Building Info, Tabbed Section, Tenant Portal, Contacts Made Portals. I have set up a tab panel which includes additional bldg info, owner info, tenant info and sales comps. The Tenant tab is a Portal linked to the Tenant Portal quadrant and the rest of the tabs contain fields from their related tables.  A  Button in the Tenant Portal quadrant allows me to see the specific detailed information related to that Tenant in the Tabbed Section or will give a blank record in the Tabbed section to enter a new Tenant for that building.  I have a "Junction Table" set up as a bridge between my Owners and Tenants Tables and the Contacts Made Table, which simple has just four fields: Junction#, Owner#, Tenant# and Contact#. 

      My questions are as follows:

      1)  Is there a way or a script that would allow me, when I select a Tenant in the Tenant Portal to not only see the detailed Tenant Information in the Tabbed Section but also see the last Contacts Made in the Contact Made Portal? 

      2)  Occasionally I have an Owner that owns more than one building and I would like to be able to search for and select an existing Owner by Last Name or Owner's Entity.  I tried using a Button with a drop down list linked to the Owners # that kind of worked, but when I selected an existing Owner it set up two records one with the Owner's information and one blank and didn't always link back to the Building Table.  I have a Junction Table bridging between the Building Table and the Owner Table similar to the one I described above.  Is there a way to search if an Owner does exist already and if so then select them to populate and link to that additional Building record, and if not, then proceed to set up a new Owner record?

      Thank you for any assistance.

      Filemaker_%282%29.jpg

        • 1. Re: Selecting records in one Portal to activate information in second and third Portal
          philmodjunk

          1)  Is there a way or a script that would allow me, when I select a Tenant in the Tenant Portal to not only see the detailed Tenant Information in the Tabbed Section but also see the last Contacts Made in the Contact Made Portal?

          The same basic method you used to "synch" your Tenants portal with the portal on the Tenant Info tab should also work to display the contact records for that tenant in the Tenant Contact Info. Can you describe how/why that isn't working for you?

          Are you using this tenant contact portal for data entry? If you are using portal filtering, there may be an issue with entering data for a new contact record that is then filtered out of the portal when you exit the portal. If that's the case, please post the filter expression you tried to use.

          2) is a question that deserves a thread of it's own, given its complexity. I don't have enough information to answer. What table is the above layout based on?  Building I presume?

          Since you appear to have this relationship: Building---<Building_Owner>----Owner

          You can place a portal to Building_Owner and add fields to it from owner to list the owners for the current building record. Assigning a new owner record can be down with a drop down to Building_Owner::Owner# with the value list set up to list owner#'s and names from the Owner table. That drop down would only allow you to add owners that already have a record in the owner's table and you'll know if such a record exists by whether or not they are listed in the drop down. I'd then add a separate button for adding a new owner record to owner and then using that new record's Owner# to create a record in Building_Owner with the correct numbers to link that new owner record to the current building record. That script could check for matching owner information before creating the record by performing a find for that information before creating a new record. If no records are found, create the record. If a record is found, display it and ask for confirmation since two owners could have the same name.

          • 2. Re: Selecting records in one Portal to activate information in second and third Portal
            JerryArntson

            Response to #1:  During construction I did convert to use this for data entry since everything was right there.  The filter expression I have is:  $$SelectedTenant = JunctContacts 2::Tenant#.  Instead of "JunctContacts" I had started with the Contacts Made table but there isn't a Tenant# established in that table so I moved to the junction table.  The problems: It seems to work for the first tenant but doesn't pull up any records when the second tenant is selected.  I entered two entries for the second tenant through this screen and when I reviewed the records two errors occured: 1) it set the contact up under the first tenants Tenant# and 2) it did not show those records under either tenant's list in the Portal.  I manually corrected the Tenant# in the JuncContacts table and they still didn't show up after selecting either Tenant record.

            Response to #2: The layout is based on the Building table.  Building table has relationships with Owner, Tenant and Sales Comp.  And then Contacts Made is related off from Owner and Tenant tables.  There are junction tables between Building and Owner; Building and Tenant; Owner and Contacts Made; Tenant and Contacts Made.  My buttons that were creating two records were based off from the Owners table not the JuncOwners table...I will try that course.

            FYI: Access was too difficult for me to learn last year and when I learned of FileMaker I was reinvigorated to try to get my database set up.  I have little experience and know basically only what I've read in the last month.  Some of these "options" are "dreams" and if they become too complicated or beyond my understanding I will accept what I have and mannually try work arounds.  I'm concerned that if I don't understand it, I won't be able to correct or adjust the database once it is established and working with live data.

            Thanks for your assistance. 

            • 3. Re: Selecting records in one Portal to activate information in second and third Portal
              philmodjunk

              The details are critical here. Are these your relationships? (Subsitute your join_table [junction] names for mine).

              Building---<Building_Owner>-----Owner---<OwnerContactsMade
                  |
                  |----<Building_Tenant>----Tenant----<TenantContactsMade

              You mention using a "junction" table for contacts made, but I'm not seeing a reason for that join table.

              • 4. Re: Selecting records in one Portal to activate information in second and third Portal
                JerryArntson

                Relationships correct except, I only have one Contact Made table and both Owner and Tenant tables connected to it.  In the second connection a second version of the tables were automatically made so they look as follows:

                Owner table; Tenant table; JunctContacts table; Contacts Made table

                Relationships:

                Owner-----JunctContacts-------Contacts Made

                Tenant----JunctContacts 2-----Contacts Made 2

                Honestly not sure why I created the junction table, all it contains is four fields (Junction#; Tenant#; Owner# and Contact#) It was a way for me to bridge each Contact back to an individual Owner or Tenant and not the Building.  Should I delete it?

                • 5. Re: Selecting records in one Portal to activate information in second and third Portal
                  philmodjunk

                  I believe this are the actual relationships you should have here:

                  Owner-----<Contacts Made

                  Tenant-----<Contacts Made 2  (----< means "one to many" )

                  Join tables allow you to match many records in one table to one or more records in another table. Multiple owners can own multiple buildings and any given building can have more than one owner, for example.

                  If I'm understanding you correctly, a contacts made record links either to one and only one owner or one and only one tenant. (But a given tenant or owner can have more than one contacts made record.) If so, you can simplify your design and link your contacts made records by an OwnerID for owner contacts and a TenantID for tenant contacts. Use two fields in contacts made, one for tenant IDs and one for owner IDs so that a contact made record won't match to a Tenant and an Owner that happen to have the same contact ID.

                  • 6. Re: Selecting records in one Portal to activate information in second and third Portal
                    JerryArntson

                    Thanks.  It sure seems to make a lot of sense as you explain it.  I will try this right away.....and again, THANK YOU!

                    Jerry.

                    • 7. Re: Selecting records in one Portal to activate information in second and third Portal
                      JerryArntson

                      Thanks again.

                      Everything seems to work great...except, (had to be an except):

                      When I enter new Contacts Made they all are set up with the first Tenant record listed in the Portal for that building.  If I go into the Contacts Made table and correct the Tenant# then everything shows up correctly.

                      I think I can simply add the field "Tenant#" to my Tabbed Tenant Portal and my Contacts Made Portal and manually enter the correct tenant's number shown in either Portal when I set up a new Contact record.  Should be an easy work around, but before I do that I want to make sure that I haven't done something incorrectly to cause this...something that would be just as easy to fix.

                      Any sugestions? 

                      • 8. Re: Selecting records in one Portal to activate information in second and third Portal
                        philmodjunk

                        Are you using a portal filter such as

                        $$TenantID = Contacts Made 2::TenantID    ?

                        If so, you can use a script performed by a script trigger to add the correct TenantID for you to your new record.

                        Set Field [Contacts Made 2::TenantID ; $$TenantID]
                        Set this script to run OnObjectEnter or OnObjectExit on all required fields. (A field that must receive data in order to log the contact.)

                        • 9. Re: Selecting records in one Portal to activate information in second and third Portal
                          JerryArntson

                          Thanks again for all of your help Phil.

                          I couldn't get the "Set Field" to work for me so I set up the work around, but I'll keep playing with it.

                          Thanks again!

                          Jerry.