I am working in FP12. I have built a contacts database that has the following table occurrences: Company, Contacts (people), Membership and Advertising, each has their own ID number (auto serial). In Contacts, I have several different possible contact types such as Main, Membership Billing, Ad Insertion, Ad Agency and Ad Billing. Each Contact could be one or more of these contact types. Each contact type has its own yes/no radio button.
I need to be able to create the appropriate fields and relationships to get the proper contact information (Company ID & Name, Contact Name, and company or contact address) connected to each ad record. Each one could potentially be a different Company/Contact.
I have the correct Company ID and Company Name coming in to each of the three areas of Ad (Insertion) Company (this is the direct relationship), Ad Agency and Ad Billing. I can also get the Ad Insertion Contact (again, direct relationship) to come in just fine. Where I’m running into trouble is getting the Ad Agency Contact and Ad Billing Contact to auto enter into their respective areas.
I have a Company Data, a Contact Data and an Advertising Data Table. The Ad Insertion Data that is the direct relationship, and working, goes like this:
Relationship: Company Data|Company ID = Advertising Data|Company ID and then Advertising Data|Compnum Ad Insertion Contact (from Advertising Data, =Company ID & " " & "Yes”) = Ad Insertion Contact Data|Compnum Ad Insertion Contact? (from Contact Data, = Company ID & " " & Ad Insertion Contact?) (Ad Insertion Contact? is a Yes/No radio button on the Contact Data page).
So, for Ad Billing (and Ad Agency), I have set up the same type of relationship: Company Data|Ad Billing Compnum (which is a drop down list of the company IDs) = Ad Billing Compnum|Company ID. I have tried what feels like a million different ways to connect Ad Billing Data and Ad Billing Contact Data….but to no avail…I cannot get the correct person to show up.
I am sure I am probably leaving something out….but am hoping this is a good place to start to get a conversation going, at least. I appreciate any help I can get!
I have attached a PDF of each of the three main layouts, hopefully this will help visualize what Im trying to do, even though there is no data or filenames....