4 Replies Latest reply on Jan 7, 2011 1:09 PM by DanielClark

    Adding records in another table's field via separate table

    DanielClark

      Title

      Adding records in another table's field via separate table

      Post

      I have two tables in a database I'm building - A table of Companies (with fields including: CompanyName, CompanyAddress, etc) and a table of Products (with fields including: ProductName, ProductCompany, etc). I set up the ProductCompany field to be a drop-down field with a value list pulling data from the CompanyName field in the Companies table.

      This all works great, but I want to be able to enter in a Company's name into my ProductCompany drop-down list field, and if that name doesn't exist already - create a new record in the Companies table with the name I entered in the CompanyName field.

      How can I do this?

      Thanks

        • 1. Re: Adding records in another table's field via separate table
          philmodjunk

          It would be a good idea to add a Company ID field that's an auto-entered serial number in Companies and then replace ProductCompany with a matching number field that you link in a relationship like this:

          Companies::CompanyID = Products::CompanyID

          There are several reasons for this:

          1) Company names aren't really unique in every case
          2) Companies change their names
          3) If you enter a company name incorrectly and don't discover the error immediately, attempting to correct the error can "break" the connection to any related records in Products as they still have the original, incorrect name.

          The ID field avoids those issues. The ID field can be formatted with a drop down list that shows the ID number in column 1 and the company name in column 2 so that you can still select companies by name.

          Given the ID field, You'd need a "new company" button that takes you to the Companies layout for you to create the new record.

          There are ways to have a text field that's used as a search field (and lists company names in an auto-complete enabled drop down) and a script can then be performed via script trigger on the field that finds the record by name and then enters the matching ID number back into your original products record. This script could include code that creates a new record in Products and enters the company name you typed in if no matching company name record was found.

          • 2. Re: Adding records in another table's field via separate table
            DanielClark

            Hm, ok, well If I have a list of 100 companies (as I do or more), how can I assign a serial number to all of them without having to manually type it, because if I create a auto serial number field, it only starts doing that when I create new records, not when I already have them in there

            • 3. Re: Adding records in another table's field via separate table
              philmodjunk

              For existing records, after adding the serial number field option for new records, you can go to a layout (or make one) where you have the ID field visible. Put the cursor in the field and use Replace Field Contents with the serial number option to both add serial numbers and update the next serial value setting so that the next new record will get the next value in the series.

              • 4. Re: Adding records in another table's field via separate table
                DanielClark

                Ah nice, thanks again PhilModJunk!