2 Replies Latest reply on Oct 11, 2013 6:32 AM by philmodjunk

    Assigning multiple values to one record.



      Assigning multiple values to one record.


           Good Morning,

           Basically the company I work for has asked me to incorporate another section to their database structure.

           We have another load of clients joining us (around 750) and they want to assign each of these 750 new clients 10 unique voucher codes. The unique codes must be unique, not only to each record to each other code.

           I have set up an external way of generating these codes at mass. I just need to know the best way to set things up so that I can attach ten unique codes to each record of a client. I was considering 10 extra fields but I think that would be very bad in the long run.

           Thanks in advance.

        • 1. Re: Assigning multiple values to one record.

               You could create a new table "VoucherCodes" with three fields:

               - ID - A number field set to auto enter a Serial Number
               - ClientIdFk - A number field that will contain the client's Unique Id number
               - VoucherCode - A number (or Text) field that will contain your voucher code.

               Every record will contain a value and so you will have 10 records with the same clientId but always a unique Voucher Code.

               This does assume you have a Clients table with an Id field that contains a Unique Id per client (Ideally also a number field set to auto enter a Serial Number.

               Then you relate the Client::ID field to the VoucherCodes::ClientIdFk field and you can put a portal on your Client layout to display all voucher codes related to this client.

               In order to get the voucher codes into the VoucherCodes table you could do a simple import from a file.

               And to relate the clients to 10 voucher codes you could write a script looping trough the clients and then assigning 10 codes to each one.

          • 2. Re: Assigning multiple values to one record.

                 And please note that in the future, you might be able to use this field to auto-enter a unique voucher code each time you create a new record in this table instead of generating those codes externally.