You should be able pick the contact name from a filtered portal or a conditional value list filtered by companyID, when you select the contact have it enter the contactID into the Invoices::contactID field (you may need to add that field to the Invoices table and set up the relationship) to link invoice to contact.
First, you aren't limited to the current relationship, which appears to be:
Invoices>-----Companies------<Contacts ( ----< means one to many )
You can create a new occurrence of Contacts by selecting it and clicking the duplicate button (two green plus signs).
You can then link a ContactID field in Invoices to the ContactID field in contacts to produce this relationship:
Invoices::ContactID = InvoiceContacts::ContactID
Where InvoiceContacts is a new occurrence of Contacts.
Then you can set up a conditional value list like you already describe, but specify contactID in Field1 and the Contact Name in Field 2. This way you select a contact by name but the ContactID is what is entered into the Invoices::contactID field. You would then use this new relationship to display or look up data from the contacts table.
Ok, so I added the contactID to the invoices table and made the 2nd occurence of "Contacts" and related them via contactID. I then put a value list on Invoices::ContactID specifying Contacts2::Contact_ID and "Contacts 2:: Full_Name" Including only related values starting from Invoices and showing only values from the second field. (I don't want anyone getting confused by id #s).
Then I allowed entry of other values.
Now it will not populate contacts from the company. I either get every company when I check "Include all values" or nothing when I check "Include only related values starting from: Invoices"
Also, when I pick a name from the list, it doesn't populate any data into other fields do I just use standard lookups for these now?
I actually got the list to work from the 1st occurence of the contacts table. Can I still do it this way. I hit the arrow, it pulls up everyone at that company and I pick the number and this will lookup all the relevant fields.
Is there a way that I don't have to have the id field visible though? Where I can pick the name and have the id be entered into a field not on the layout?
You don't want to use the Contacts 2 table for the value list as it will only show contacts related to that invoice, which would be none. For the value list use the relationship where contacts are related to company.
You should be able to set up the lookups for phone, fax, email, etc to work from the contacts 2 to invoice table then. Or you could just make those fields Contacts 2::phone, etc
Cool. That makes sense.
Also, if I want to associate multiple contacts to the invoice how do I go about that, do I still need a linking table then?
Ex. The first person is the production manager, but another is the billing contact.
I think that would take another ContactID field in the iinvoice table (maybe PMcontactID and BillingcontactID). It would require another TO of contacts for the second relationship as well. I would rename those as BillingContact and ProductionContact (or whatever makes sense to you) rather than having Contacts 2 and Contacts 3.
Is there a way to make this more fluid. Ideally I'd like to pick someone from the list, have their name stay in that box and populate the other fields almost simultaneously, like in the starter solution for Invoices that comes in FMP9
Say you have set up the production manager to be based on ProductionContacts table. If you set the fields for his phone, fax, email on the invoice layout to be ProductionContact::phone, ProductionContact::fax, ProductionContact::email they should populate as soon as the Invoices::PMcontactID is selected from your drop down.
Can those still be editable from the invoice side though without compromising the data in the contacts table?
They would be editable unless you prohibit field entry. The edits would modify the original contact record. If you don't want that possibility, you can define matching fields in Invoices and use the looked up value field option to copy the data from the contact table via the relationships Mark has had you set up here. Since the data is now copied, you can edit them without modifying the original contact record in the contacts table.
Ok thanks! This should work!
For my billing contact, I have a calculation of the following or similar on the other fields I want to copy over:
If ( not IsEmpty (Same_As_Billing_Contact) ; Prod_Mgr_Cell ; Billing_Cell )
If the check box is checked, then it copies the production managers information into the billing fields.
Is there a way that I can have both the method for the lookup described above and this checkbox calculation to make it easier when the billing contact isn't the same as the production manager (which is about 50% of the time)?
Two options you can play with to see which works best for you:
1) You can use looked up value fields to copy over the three fields referenced in this calculation. Then define this same calculation field in your invoices table. With this option, you can click the check box on the invoices layout to make a change specific to that one invoice.
2) you can use a looked up value field to copy the value of this calculation field into a text field. In this case, you only need one field instead of 4, but now cannot change the value of that check box and have the change only affect the current Invoice.
You could have that check box calculation run as a script (triggered from whatever event would work for you) replace the looked up value if the box is checked. If the box is not checked it will leave the looked up value.