Use the same field in Company for the two links to Action. Then use the same field in company to link to the same field in contact in the company to contact and the company_partner to contact_partner links. That will give you these relationships:
Action::CompanyID_fk = Company::CompanyID
Action::ParternID_fk = Company_Partner::CompanyID
Company::CompanyID = Contact::CompanyID_fk
Company_Partner::CompanyID = Contact_Partner::CompanyID_fk
This assumes only one contact for a given company and one contact for a given company partner. In some circumstances, it may make more sense to either link to contacts using ContactID (many contacts for one company) or even use a join table if the relationship is many contacts to many companies.
Many thanks! Simplified the relationships a lot. However, now I can't get the Full name to show properly beside the Customer Contact ID, and I can't seem to get the value list right for the Partner Contact ID (no matter what I do it selects the Customer Contact IDs).
Help much appreciated - I'm afraid I'm just an amateur at this...
Wish I could send another screenshot...
You can upload additional screen shots to a file sharing site and then post the link to that image here. That's a pain but best we can do here.
If you are trying to place the full name of the company contact next to the company ID field on the Action layout, you just add that field from contacts. For the partner, you'd add it from contact partner.
In both cases, your relationships suggest that it's possible to have more than one contact linked to the same company record. If this is the case, you'll get the full name of the first such contact--which might not be the contact you wanted here.
Again, many thanks. You have hit the nail on the head. I usually have several contacts for each Customer and Partner company, and need to be able to select and display the appropriate one, not just the first one! How to I do that?
Then I can also set up buttons to email them, Skype them, copy their contact information (which I paste into emails) etc...
Sounds like you need to link directly to a contact ID value instead of using company ID.
Will it work to link your tables like this?
Action::ContactID = Contact::ContactID
Company::CompanyID = Contact::CompanyID
Use the same set up for partners, but start with a different field for the contact ID in Action.
Nope, now the only field that works properly is the Company ID for both Customer and Partner.
I need to be able to select the company first, then choose the contacts for that company.
Fields are as follow:
Customer Company ID (from ACTION: Company ID which is an Indexed number - with a lookup table that (should) select COMPANY: Company ID and display COMPANY: Company_Full (but I get an error message that Company_Full can't be indexed)
Customer Company Name (from ACTION: Action_Company_Full which is a calculation = COMPANY: Company_Full
Customer Contact ID (from ACTION: Contact ID which is an Indexed number - with a lookup table that (should) select CONTACT: Contact ID and display CONTACT: Contact_Full and limits the choices to related IDs from COMPANY. (but I get an error message that Contact_Full can't be indexed)
Customer Contact Name (from ACTION: Action_Contact_Full which is a calcuation =CONTACT: Contact_Full
Partner fields are analogous.
Where am I going astray here???
"I need to be able to select the company first, then choose the contacts for that company."
Why? what problem does that solve for you?
You aren't selecting the company first if you use a calculation to supply the company name ( and this field isn't needed to display the company name either.)
Going back to my previous suggestion:
If you select or enter a customer ID in Action::customerID, you can add the name fields from customer and company to the action layout and they will appear when you enter/select this value.
The only reason I can see where you would need to select the company first is if you want to implement a conditional value list so that selecting a company narrows the list of contacts to just those contacts that are related to the selected company. That is possible here, but not something we've discussed and it requires an additional relationship.
Yes, that's exactly why I need to select the COMPANY first - I only want to be offered a choice of the CONTACTs related to that COMPANY, for both the customers and partners. i have hundreds of companies and thousands of contacts.
See my origina screenshot. Ed Grabover is one of two contacts at NaNaMed, and I would have two or three at XL Precision if I could get them to show.
When I just had customers in my ACTIONS layout everything worked fine, but adding the partners (which as you know take their COMPANY and CONTACT values from the same table, since there are companies that are both customers and partners/suppliers) has proved impossible for my limited knowledge (I have been playing with the fields, relationships, and value lists for a week).
This is my CRM system, and I input quite a few new actions (business inquiries) every day, so selecting the CONTACTS without the COMPANY would not be efficient. Also, doing it this way avoids errors (there may be two John Smiths, but only one working for XYZ Corp, and I don't want to call or email the wrong one.
By the way, I do not really need to display the ID numbers for the COMPANY or the CONTACT, so the selection might be possible with one field instead of two...
Please note that with the right details in place, what I am suggesting will work and we can still come up with the needed conditional value list also. But before diving into those details, I think you need to confirm one of my conclusions here.
With a given action record, the critical detail is that you are linking this action record to a specific contact record in the contacts table. Once you've selected a contact, all the related company info is then available and visible, but the crucial selection is that you are choosing a contact, not a company. Does that make sense?
If so, then your original set up won't work because you are only choosing a company. you are not selecting a specfic contact associated with that company which is why you kept seeing the wrong contact info unless the desired contact happened to be the first such contact related to the seleted company record.
Try out these relationships:
Action::CompanyID = CompanyContact::CompanyID--this is the relationship to use for your conditional value list. Set it up to list values from CompanyContact, include only related values starting from Action. The value list can list contactID as field 1 and a name field as field 2, just make sure the fields are selected from CompanyContact, an occurrence of the Contacts table.
Action::ContactID = Contacts::ContactID -- this is the relaitonship that gives you access to the selected contact record's data. You format action::contactID with the above described conditional value list so that you can select a company in the companyID field then select a contact in the contactID field from a list limited just to contacts that are linked to the selected company. You can add any field you want from contacts to your action layout and they will display the data from contact once you have selected a contact.
You can repeat this pattern of table occurrences (but use different ones) for your partner link to contacts and to company. You'll also need to define a new conditional value list as it must be based on a different contact unless both contacts are always from the same company.
Once you have all that working, you might be interested in a demo file that utilizes some enhanced value selection methods to make working with such large value lists easier to manage: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html
Sorry to be such a pain, but it is important to me to select the company first. Reason is that for a given action/project the contact person sometimes changes (the purchasing agent hands me over to the engineer, or whatever). Also, with thousands of contacts it is much easier to remember the company.
Any way to do this?
Please read my last post again. I spelled out exactly how to do this so that you select the company first, then the contact.
Every thing looks correct here in terms of the relationships used, though the calculation fields to return values from the related tables are not needed. You can, instead, add the fields from their respective table occurrences to your action layout. That doesn't explain why they aren't working for you, however.
In your example, I do not see a contact ID value selected in the Partner1_CtID_tk field. This field needs a different conditional value list than used with Customer_CtID_tk field. Without a value in that field the partner contact name calculation field cannot display a name.
Ok, just played with a demo file, and now I see what's missing.
For each contact, you need additional table occurrences of contacts so that you can link by company ID (for the conditional value lists) and then also a different table occurrence linked by contact ID (so you can access the contact's name)
This requires 4 occurrences of contacts and 2 of company.
Take a look at my demo file to see what I mean: http://www.4shared.com/file/rhrC1RSY/DoubleContactCondValueListDemo.html