My apologies if this is a dumb question.
Not at all. Relationships in general and conditional value lists in particular have stumped the greatest of minds (and the smallest, too – so you're in good company, either way …)
The set-up: (fancy formatting, like Steve always does …)
You should have at least one additional table – ReturnAddresses, with fields for:
• returnAddressID (primaryKey, auto-enter serial ID; we won't use it here, but it should exist)
• address fields – the usual: an optional name, street1, street2, city, zip, state, etc.
• cAddressComplete – a calculation field, type text, for a complete address block, built from the assorted address fields; that one would go on any print layouts
• another calculation field (type text) – a one-line version of the complete address that makes the entry identifiable (for a human operator); probably the same as cAddressComplete, but as a line, instead of a block. Needed for the value list.
Create a relationship between TaxReturns and ReturnAddresses as
TaxReturns::returnType = ReturnAddresses::returnType
TaxReturns::residentState = ReturnAddresses::residentState
The value list: (more fanciness!)
Create a value list with fields from ReturnAddresses:
1. field: the address block
2. field: the address line
Check the options to …
• show values from the second field only
• show only related values, starting from TaxReturns
Format your TaxReturnAddress field in TaxReturns with that value list. Selecting an address line (field 2) will actually put in the address block (field 1).
Some notes: (OK, we got it …)
Normally this would work with the primary ID as the first field, but ( ! ) since you want to store the address as it was at some point in time (and not have it change after a modification), simply copy over the entire address, instead of using a reference.
Note that if you have many identical addresses that are in use for different combinations of state and type, you could stick a join table between those two tables.
The Address table then would only the hold the “pure” addresses (and you have a single place where to manage them), and the join table would denote the assigments of the same Addresses record to different state/type combos.
Caveat: the ususal tradeoff between an optimized structure and a more complicated set up procedure …
Thank you for your very thorough reply. Hopefully, after I spend just a little time analyzing I will be apple to appreciate your experience with this kind of situation.
I was thinking I'd have to wait days for a reply, so the promptness of your reply is very much appreciated.