What kind of relationship do I need?

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???

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?

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

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?

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