Let's say you define the sameasCompany check box field to use a value list that inserts the text "Same as Company" when the box is clicked. Define a cSameAsCompanyKey field in your companies table that returns the constant value "Same as Company" (Make sure return type is text).
Now you can create a new relationship:
Contacts::CompanyID = SameAddCompany::CompanyID AND
Contacts::SameasCompany = SameAddCompany::cSameAsCompanyKey
Where SameAddCompany is a new table occurrence of your Companies table. (Select Companies, then click button with two plus signs while in Manage | Database | Relationships.)
Now you can define looked up value field options on your contact table's address, city, state fields to copy data from corresponding fields in SameAddCompany after you have first selected a company and clicked the check box.
Could you elaborate more on your first paragraph there? Is the value list pulled from the cSameAsCompanyKey field from the Companies table? Also, how do I return the text "Same as Company" to the cSameAsCompanyKey field via the checkbox? Thanks again
Define a value list with a single custom value: Same as Company. Use that formatting on Contacts::SameasCompany.
SameAddCompany::cSameasCompanyKey will not be set by any checkbox. It is simply a calculation field holding this quoted text as it's complete expression: "Same as company".
Because cSameCompanyKey always holds the same value in any record in the table, clicking the check box of the Contacts::SameasCompany field assigns a value that matches these two fields and creates a valid relationship so the looked up value field options set on the address, city, state fields will kick in and copy over the matching values.
Ok so I almost got it - I think I'm missing something though - in your first post - you say "define the sameasCompany check box field to use a value list that inserts the text "Same as Company" when the box is clicked" - where is the text "Same as Company" being inserted to? Because I think this is the place where it has to match up with the cSameasCompanyKey field - AND with a company defined the address and all can be put in.
Open manage | Value Lists....
Define a new value list with a single custom value: "Same as Company" (no quotes).
Place SameasCompany field on your layout.
Use the inspector to change it from an Edit box to a check box group.
Select your new value list as the list of values to use with this check box group.
You'll now get this on your layout: [ ] Same as Company
In browse mode, if you click the check box, the text Same as Company is inserted into this field. If you click it again, this text is cleared from this field.