3 Replies Latest reply on Jan 24, 2014 2:59 PM by keywords

    Auto-filling fields based on mutiple fields


      I have 2 tables :


      SSN_NAMES Table (names with social security number)

      TRANSACTIONS Table (where tranactions for customers are stored.


      These table are joined by SERIAL fields


      The transactions table has 6 name fields in it - name_1 thru Name_6.  The transactions table also has 6 soical security number fields - SNN_1 thru SNN_6.

      What I would like the user to be able to do is when they are in the TRANSACTIONS table, and they enter a social security number in SNN_1, pull over that person's name into thre NAME_1 field. When the user goes to the SNN_2 field and enters a valid sociel security number, pull over the person's name ( associated with this social security number) for NAME_2, and so on.


      I have tried to do this with a value list in the names field and also tried with a lookup function for these fields, but when I use a look up function in the name fields, and I type a social, it puts the same name in all name fields. 


      How do I get it to pull the matching name for each social entered:


      in TRANSACTION Table - ssn field, type 123456789 and in the TRANSACTION Table - name field, autofill with name os person matching the entered social.



        • 1. Re: Auto-filling fields based on mutiple fields

          Why does the Transaction table have 6 names and social security fields, instead of a single one? This defies the purpose and advantages of using a related table in the first place (as does the idea of duplicating non-transactional data).


          If a transaction is more like an event in which several people can participate, create a child table to Transactions (say, TransactionsPeople) and use it to create one record for each customer/participant of a transaction.

          • 2. Re: Auto-filling fields based on mutiple fields

            I was going to do it the way I describe because each transaction could have up to six people for one transaction.  So you would see:


            all people names with their respective socials listed for $1000.00 coming from account number 1234, account number 5678 and account number 9876.


            You mention a child table.  I'm assuming that would hold a transaction for each person and then I would display them alll together on the transaction layout?

            • 3. Re: Auto-filling fields based on mutiple fields

              The child table suggested is basically a join table to creat connections between a specific Transaction record and whatever People are associated with that transaction. You can then display and/or create the connections in the manner you describe in a portal (which might be set to DISPLAY six lines at a time, but can CONTAIN as many or as few as are needed).


              If you must proceed the way you originally propose you will need to create a relationship to the people table from each of your six SSN fields, so that each field points to a different person. Three issues arise with this approach compared to that suggested by erolst:


              1.     You have to create and manage more relationships

              2.     Any Transaction record which has less than six people associated with it has empty fields

              3.     Worse still, you schema cannot handle a Transaction record which has seven or more associated people