2 Replies Latest reply on Jul 31, 2014 4:04 PM by jjfcpa

    Need help with relationshsips


      My apologies if this is a dumb question.


      I have a table called "Tax Returns" and here are four items that need to be included in this table:


      Resident State


      Tax Return Type

      Tax Return Address


      Each return can only have ONE tax return type and ONE tax return address BUT, the tax return address is dependent on the tax return type.


      Each return type can be mailed to many different addresses depending on the RESIDENT STATE and if there is a refund or balance due on the return.


      These items can be selected from a value list: Resident State and Tax Return Type


      This item is just a logical field: Refund


      How do I then limit the value list of Tax Return Addresses based on the above 3 items?


      Any help greatly appreciated.

        • 1. Re: Need help with relationshsips

          jjfcpa wrote:

          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 …)


          Anyway …


          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)

          • returnType

          • residentState


          • 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 …

          • 2. Re: Need help with relationshsips

            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.