Discussions

Expand all | Collapse all

What kind of relationship do I need?

  • 1.  What kind of relationship do I need?

    Posted 03-13-2019 03:06
    Need some pointers on how to correctly setup my table relationships to be able to do the below.

    So I have three tables: Clients, Contacts, Work Orders.

    I am having difficulty getting the result I am looking for.

    Currently I have the following relationships established, and they are working correctly:

    • A Client has many Contacts
    • A Client has many Work Orders
    What I need to do is establish a correct relationship between the Contacts and the Work orders.

    There are 3 different contact types: Site Supervisor, Job Contact, & Home Owner.

    A single work order can have up to a max of 3 contacts, from the associated client, thus I was thinking a many contacts for one work order relationship...... but .......

    A single contact can be one of the 3 contacts for many work orders, so then it would be many work orders for a single contact ......  right ????????

    So then which is it?????

    Is it a Many to Many relationship??????

    Also, I would like to set up the Work order, so that there are 3 contact fields, one for each contact type, then when I select the Client from the client drop down from within the work order form, it only lists the contacts of that contact type, associated with the selected client.

    I now know how to do a conditional drop down for if I select the client name it only gives me contacts associated with that client ... but I need to add one other layer, the contact type.

    So, any thoughts/suggestions on:
    • How to correctly set up the Contacts & Work Orders table relationship?
    • Adding an additional layer of condition so that for each contact type drop down within the Work Order form, based upon the client selected, it will onl show me the associated contacts which have that contact type?
    Sorry, I know it is a lot .... I appreciate your patience and assistance!!

    -Andrew


  • 2.  RE: What kind of relationship do I need?

    Posted 03-13-2019 03:33
    Ok, so after messing around a bit, I believe that I need one contact to many work orders. Now to figure out the conditional layers .... any thoughts???


  • 3.  RE: What kind of relationship do I need?

    Posted 03-13-2019 05:08
    Ok, I think I got it. I utilized a one to many for Contact to Work Orders. I set up some conditional drop down and it works. The one thing is that I only tried it for one contact, meaning I only have one contact field in the form. If I do it this way, then there are going to be a total of 9 fields: contact Type (drop down menu), contact name (drop down menu), Contact phone (auto-populates).

    There has got to be a better way to do this, where the 1st field is always and only for contact types of: Site Supervisor; the second field is always and only for contact type of: job contact; and the third field is always and only for contact type of: home owner. Granted, each of those will have an associated telephone, but at least that would knock it down from 9 to 6 fields.

    Any thoughts?


  • 4.  RE: What kind of relationship do I need?

    Posted 03-13-2019 22:22
    Hi Andrew, 

    Taking just the example of the Work Order: You mention there will be 3 contacts.  Which seems like one work order has many contacts.  However, what I believe you have is one Site Supervisor contact.  That contact can be the Site Supervisor on many Work Orders.  So the relationship would be: Many Work Orders to One Contact.  

    I would suggest creating one relationship for each of the three contact types.  This should result in three drop down fields one your form (one for each contact type) with a list of contacts for you to select.  

    I hope I've understood your question.  Please let me know if you have any follow-up questions or need clarification. Thanks and good luck.

    Jake Rattner | Solutions Architect
    (847) 927-1427 | jrattner@quandarycg.com
    Quandary Knowledge Base


  • 5.  RE: What kind of relationship do I need?

    Posted 03-13-2019 22:49
    Hey there Jake! Thanks for the response. I think I see what you are saying, but let me clarify a bit more.

    I have a Client Table. It has a one to many relationship with my Contacts table. One client has many contacts.

    Among those contacts, there are three different types: Site Supervisor, Job Contact, and Home Owner.

    A Client can have many Site Supervisors, many Job Contacts, and many Home Owners.

    Also, I have a Work Orders table. It has a many to one relationship with the Client Table. One client has many work orders.

    I need to set up the Work Order table so that it has the following fields:
    • Site Supervisor
    • Job Contact
    • Home Owner
    Then from within the Work Order form, when I select the Client from the drop down, I need the Site Supervisor, Job Contact, and Home Owner drop down lists to only list the contacts (for that type) associated with the selected client.

    I can do a conditional drop down, no problem.

    The issue is setting up the relationship correctly between the Contacts and Work Orders tables and setting up the Work Orders table with the correct fields for each of the Contact types.

    I hope that clarifies things and helps.

    Any thoughts?

    Currently, I have all contacts, regardless of type, in a single Contacts table (as described above). But I am thinking that maybe I need to have a separate Contacts table for each type ... but then that gets cumbersome in my opinion ..... yuck ....

    -Andrew

    P.S. I just re-read your comment Jake, and yes, I need to create a relationship dependent on the contact type .... that way I can have 3 different relationships, one for each contact type ... but wouldn't that mean having to setup three different tables, one for each contact type?


  • 6.  RE: What kind of relationship do I need?

    Posted 03-15-2019 19:08
    Regarding the need to set up multiple tables in your P.S.: You don't need to setup multiple tables.  Instead just create multiple table-to-table relationships.  You can create more than one relationship between two tables.  

    Then, you will need to filter those connections to display only the Site Supervisors in the 'Site Supervisor' dropdown field and Job Contacts in the 'Job Contact' field.  

    In order to do filter each dropdown you will need to use a field in the contacts table to categorize whether a contact is a Supervisor, Job Contact or Home Owner.  Once you have those contacts categorized you should be able to use that field to filter your three drop-downs.

    Jake Rattner | Solutions Architect
    (847) 927-1427 | jrattner@quandarycg.com
    Quandary Knowledge Base