2 Replies Latest reply on Oct 21, 2009 8:39 AM by philmodjunk

    Import Excel Auto Import Customer Name to Transaction lines

    puzzledata

      Title

      Import Excel Auto Import Customer Name to Transaction lines

      Post

      The spreadsheet of data I am importing into my database, has the Customer information in one line, and all the transactions on the lines that follow.

      I need to have the CustomerNumber present in each transaction line, however there is nothing unique to the customer name or number in the transaction lines.

      there is a Customer Info line that preceeds all the transactions. I need to capture that CustomerNumber and include it with each transaction line that follows until a new CustomerInfo line appears to indicate the next customer. 

       

      CUSTOMER 1    ADDRESS   CITY STATE ZIP (this is an example of a CustomerInfo Line)

                                 1ST TRANSACTION DOLLAR AMOUNT (This is an example of TransactionLine)

                                 2ND TRANSACTION DOLLAR AMOUNT

       

      CUSTOMER 2   ADDRESS   CITY STATE ZIP

                                 1ST TRANSACTION       DOLLAR AMOUNT

                        2ND TRANSACTION      DOLLAR AMOUNT

                  3RD TRANSACTION      DOLLAR AMOUNT

       

      Please help as I am very stuck with this project until I get beyond this point.

        • 1. Re: Import Excel Auto Import Customer Name to Transaction lines
          puzzledata
            

          Please please help with this...

          Many spreadsheets that are exported data from databases use this type of format.

           

           

           

          The first column reveals the customer number. It has a code of 02 shown in columns 3 & 4 to show entry type which is customer name.

          The rows that begin with 03 in columns 3 & 4 are actual transactions this client had that day.

          I need to be able to place the customer number in each transaction line for my project to work.

           

          Please share ideas how to do this.

           

           

          • 2. Re: Import Excel Auto Import Customer Name to Transaction lines
            philmodjunk
              

            I'm not an excel expert, but I believe there's a way to do an auto-fill on the customer number column to fill in the missing data. I looks like this data should be imported into at least two filemaker tables: CustomerInfo and Transactions with the Customer number in column 1 serving as your primary key in CustomerInfo to link its records to matching transactions records. Is this what you have set up?

             

            To fill in the missing data in a table in filemaker, you can write a script.

             

            Import Records [Restore] //Use this step if you want to automate the import. Otherwise, do the import by hand and then leave this step out.

            Go to Record [First]

            Set Variable [$CustID; Value: YourTable::Field1]

            Loop

              If [IsEmpty(YourTable::Field1)]

                 Set Field [YourTable::field1; $CustID]

              Else

                 Set Variable [$CustID; Value: YourTable::Field1]

              End If

              Go to Record [Exit after last; Next]

            End Loop