2 Replies Latest reply on Jun 13, 2011 2:01 PM by EricSommeling

    import data from excel for different companies using drop down list

    EricSommeling

      Title

      import data from excel for different companies using drop down list

      Post

      In my database I have one table with 5 company names and the second table with the details for the people they have contracted, the fields in this table are: Company name, Name, Address, Tel, Amount. Each company has provided an Excel file with the details of the people they have contracted (> 5000 records each), Fields in the Excel sheet: Name, Address, Tel, Amount. Now I want to be able to select the company name from a drop down list and upload (using a script) the Excel data in the second table. I am able to upload the data but without setting the company name I selected from the drop down list. Does anyone has an idea how to do this? any help would be appreciated! Eric

        • 1. Re: import data from excel for different companies using drop down list
          philmodjunk

          Using a company ID number to link your two tables would be better than a company name. Just like people names, company names are neither unique in every case and they are subject to changing their names--both situations could complicate how you manage your database and using a company ID number to link your two tables can eliminate that issue.

          Define a global text field in your database: gCompany

          Format it with a two column value list of company names and company IDs. With CompanyID as "field 1" and company name as "field 2". Selecting a company from this value list will enter the ID number in to the global field. If format your field this field as a pop up list, you can set up the value list so that the company name is displayed even though you have actually entered it's number.

          Once you have selected a company. You can use Replace Field contents in a script to enter the contents of this global field into the matching CompanyID field of your import table for every record in your found set. And just after importing, the newly imported records will make up this found set for you so you can import the records, select a company and then use Replace Field Contents to add the needed value to CompanyID. This can be done by hand or in a script and a script trigger set on the global field can perform a script to use Replace Field contens so that all you need do is select a company from the value list.

          • 2. Re: import data from excel for different companies using drop down list
            EricSommeling

            Hi PhilModJunk,

            Thanks for your answer!

            Will try as you have suggested.

            Eric