3 Replies Latest reply on Dec 7, 2009 9:32 AM by philmodjunk

    Design Problem



      Design Problem


      Need help with a design problem..  :smileyhappy:


      3 Tables: Accounts, Contacts, Work Orders


      Accounts contains names of Companies, addresses, etc.

      A "company type" field has choices (Real Estate, Escrow, Construction, Church, or Other.) via Value List


      Contacts contains people and contact info

      A "contact type" field has choices (Realtor, Escrow Officer, Manager, etc) via Value List


      Accounts & Contacts are linked by "AccountID"


      Work Orders table is my problem... I need to show this information:

      1. A Company & Contact (in this example a Real Estate company & Realtor with their Contact Info) 

      2. An Escrow Company (and Escrow Officer, Info, etc)


      Is it possible to show both types of info on the Work Orders layout with this design or does each company type or contact type need to be in their separate tables and then somehow pulled in? Is there another method not shown here?


      Whichever way works, can someone show me how to set it up?


      Please and thank you.




        • 1. Re: Design Problem

          With two ID number fields in Work Orders and two separate relationships that use differently named Table Occurrences to link to Contacts, you can do this.


          Define two ID fields: ContactID, EscrowID


          Two Relationships:


          Work Orders::ContactID = Contacts::AccountID


          Work Orders::EscrowID = Contacts 2::AccountID


          To create the second Table Occurrence (TO) box, click the Contacts TO in Manage | Database | Relationships and then click the button with two green plus signs. Both Contacts and Contacts 2 will refer to the same Data Source table: Contacts.


          Now use the field tool to select fields from Contacts for you first set of contact data and Select fields from Contacts 2 to display your Escrow company data. When you enter/select a value in Work Orders::ContactID, you'll see data appear in the first set of fields. WHen you select/enter a value in Work Orders::EscrowID, you'll see the Escrow Company fields display data.


          Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

          Table vs. Table Occurrence (Tutorial)

          • 2. Re: Design Problem

            Thanks for the response Phil... I do understand the differences between the Table and TO, maybe what I'm trying to illustrate is not the same as I have drawn out on paper. BTW... My design, and your suggestion are fairly similar.


            Let's expand on yours for a moment. With these relationships


            work order : realtor_id = contacts : account_id   (TO-1 from Contacts)   &

            work order : escrow_id = contacts2 : account_id   (TO-2 from Contacts)

            it appears the results would be the same. As far as the contacts table is concerned, realtors and escrows both have an account_id. (in other words, in the contacts table, the "account_id" is a foreign key, but in the accounts table, its the primary key. In this relationship, the realtor_id & escrow_id keys are foreign keys as well. Can 2 foreign keys work as match fields?


            Even if the relationship was set up like this, the results seem the same:

            work order : realtor_id = accounts : account_id   (TO-1 from Accounts)  &
            work order : escrow_id = accounts : account_id   (TO-2 from Accounts)

            The accounts table has a "company type" field (from value list), where you can choose realtor or escrow, etc, to define their type of business, and a relationship (accounts : pkey_account_id and contacts : fkey_account_ID will allow a user to show all the realtors in a real estate company via a portal or escrow, etc....


            How do you display this type of information while a user is creating a work order record?


            On a work order layout, the way I see it in my head would be something like a portal or small popup window (one for the realtors area displaying only those real estate companies with its respective agents and near the escrow section of the same layout, a similar object displaying only escrow companies and its respective escrow officers.


            lol, have I really confused the issue now?


            It's almost like choosing a zip code in a selection portal, once a selection was made, the city, state and zipcode fields would populate with the approriate data.


            Most grateful for any constructive comments on this or another method?














            • 3. Re: Design Problem

              The key here is that you have TWO separate relationships linking to different fields in your main table.


              Note that there is no "And" or "&" in my example. You have two TO boxes and different pairs of fields defining the relationship in each. That's so that when you enter/select a ID number in the first field, you can link to one related record and when you select/enter a value in the second field you can also link to a different record in the same table. You're layout should look something like this when you have this set up. (I'm using [] to represent field borders.)


              [ContactID] [Contacts::Name][Contacts::Address] ... etc.

              [EscrowID] [Contacts 2::Name][Contacts 2::Address] ... etc.


              Select/enter a value in ContactID and Contacts::Name, Contacts::Address etc. display data from a matching contacts record

              Select/enter a value in EscrowID and Contacts 2::Name, Contacts 2::Address etc. display data from a different contacts record.


              Make sense?