3 Replies Latest reply on Jan 19, 2013 10:48 AM by philmodjunk

    Importing data to related fields from an external file

    StephanKrasner

      Title

      Importing data to related fields from an external file

      Post

           I just created a new database to hold Order and Customer records. The file I am pulling data from has all of the customer and order data intermingled. The file is a plain jane tab deliniated .txt file. The file is filled with thousands of orders and contacts, bith of which should stay connected in the database like the orders that have been input by hand.

           The problem I am having is that I don't know how to import all the record fields at the same time. When I am in the customer layout I can't populate the related fields on the page that references the orders table.

           My question is how can I import data to all the fields at the same time?

        • 1. Re: Importing data to related fields from an external file
          philmodjunk

               You can't import it all at the same time. You import twice from the same file, designating a different target table for each import. (For manual imports, you select a layout based on the target table in order to import records into that table.). Since the customer data is likely repeated over and over again, you'll want to set up a validation rule that filters out the duplicated values during import to give you one record for each customer.

               But this assumes you have a column in your tab file that uniquely identifies each customer and a name is not likely to work perfectly for that purpose. Do you have some kind of customer ID column in this data?

          • 2. Re: Importing data to related fields from an external file
            StephanKrasner

                 I can definitely import twice (once for each table). But the bproblem with that is connecting the two data sets afterwards. Is there a way I can make an identifier to make the records (Customers & Orders) recognize each other and link up after import?

                 To answer your question, each order in the table I'm trying to import has it's own unique identifying number. The customers do not have identifying numbers, but every single customer on the list has a unique email address. Most of the orders are one time orders. Only a handful are repeat orders.

            • 3. Re: Importing data to related fields from an external file
              philmodjunk

                   Is there a way I can make an identifier to make the records (Customers & Orders) recognize each other and link up after import?

                   The Order number column can be used for that purpose, but what you really need is to match up orders to customers by Customer or you get duplicate records in the Customers table for that "handful of repeat orders".

                   Best bet is to import the email address field into text fields in both tables. Set the unique values, validate always field options on the customers table, to eliminate duplicate records in that table. Then tempoararily link your two tables by email address, but use an auto-entered serial number field in Customers to link to a number field once you have the data imported.

                   A Replace Field Contents operation can copy the serial number value from customers into the match field in Orders after you have imported the data.

                   You database, BTW, can use separate table occurrences to link orders and customers by email address in one relationship and by CustomerID serial number in a second. That way you can use the first relationship to set up the second.