Nested Tables Relation Lookups or Script Solution Needed
I have three related Tables that are related by unique IDs
(Invoice ID, Company ID, Contact ID)
Currently I have it set up so that a new invoice is related to a company via the company ID and related information is populated on the invoice end (Fax No, Main Phone, Billing Adress). This link also populates an invoice history in a portal on the company layout for account summaries and such.
Each company has a list of contacts that I can look up in drop down lists from the invoice layer if I "include only related values starting from: Invoices" on the custom drop down list options.
What my question is is this:
Is there a way that I can pick the Contact from the drop down list on the Invoice layer and have it auto populate the cell phone, title, email, and alternate phone onto the invoice without having to type in the contact's ID. In the example database solutions for FMP11, there is an invoice option that allows you to pick a client from a list and it will populate all info without entering a customer ID, but there isn't a companies table in between like there is on my database. We need invoices related by companies because there are simply too many people we deal with at a given company through different departments so we need to know the company 1st and secondly the production manager.
Is there a script that I can use to input a contact id even though the invoice as a record is already be related to the company id?
The script for the Starter Solution is:
"Clear Customer ID"
If[PatternCount (Get ( ScriptParameter) ; Bill)]
Set Field [Invoices::Bill To Customer ID; ""]
Else If [PatternCount (Get (ScriptParameter) ; "Ship")]
Set Field [Invoices::Ship To Customer ID; ""]
Commit Records/Requests [Skip data entry validation; No dialog]