3 Replies Latest reply on Sep 18, 2014 9:01 AM by philmodjunk

    Importing Invoices into the FMP13 starter solution

    violinmike

      Title

      Importing Invoices into the FMP13 starter solution

      Post

      I've had a dig around the archives to find an answer to this one, but no joy.

      I'm trying to import invoice data from an excel spreadsheet into FMP.

      The spreadsheet setup is basically as follows:

      Columns: invoice Number, Date, Customer name, Total, then each Item being sold has a column of its own.

      The price of each item in a given record (row) is located in the relevant column for the item and the total is calculated automatically. It's not a very sophisticated setup. For example, there's no indication of how many of each item were sold, or what discount was given, just a figure in the cell and a total.

      I've managed to import the invoice numbers and dates, but getting customer and item info seems more difficult (as they are fetched from a different table within FileMaker?)

      What's my best bet for importing these into the new FMP solution? We're talking over 1500 invoices some with multiple items, so I'm keen not to have to do it manually.

      Any help greatly appreciated as ever.

        • 1. Re: Importing Invoices into the FMP13 starter solution
          philmodjunk

          Given that: " there's no indication of how many of each item were sold"

          Your data doesn't match the design of the starter solution as the item cost figures are calculated from specified quantities. Thus, you will be unable to import this data without either modifying the design of the starter solution or the data in your spreadsheet.

          The data in your spreadsheet would need to be parsed into several different tables as the data you describe resides in several related tables:

          Customers
          Invoices
          Invoice Data

          Not only do you have to get the data from each row of your spreadsheet into records of each of these tables--which at minimum is a separate import for each such table, you'll need to filter out duplicate customer entries and link the records in relationship by the starter solution's match fields.

          And the data you would need to import into Invoice Data will be a challenge. Each record in Invoice Data represents one type of item listed on a particular invoice, but you describe this data as being located in columns so you'd need to import this data into a table whose structure matches your spreadsheet and then use a script to parse the data into a series of related records in Invoice Details. You may or may not have the skills needed at this time to construct such a system in order to import the data into your database.

           

          • 2. Re: Importing Invoices into the FMP13 starter solution
            violinmike

            Thanks for your suggestions. I'm hesitant to make more than very minor changes to the invoices starter solution, though I'd be quite comfortable modifying the spreadsheet as necessary to make it importable.

            I currently have 1686 invoices listed in Filemaker, with only invoice number and date appearing in the vast majority (the ones I haven't yet filled in manually). The only things I need to add are items sold and customers. NB All the customer data is already in the customers table and product data in the products table.

            You're probably right that the best solution would be beyond my abilities at the moment, but it would already be a major step forward to be able to automatically match the customers up to the invoice data I've already imported. In order to do that, which fields would I need in the spreadsheet? Should I then import that into invoices or invoice data?

            Please forgive my ignorance!

            VM

            • 3. Re: Importing Invoices into the FMP13 starter solution
              philmodjunk

              The problem here is that your match field primary keys (such as the customer ID) are values generated in FileMaker rather than in yoru imported data. I don't think you can import this data without making changes to the starter solution. (Anytime you fear a change may mess up your file, make a back up copy first and then you can toss the current file and switch back to your previous copy if your fears turn out to be justified.)

              You'll need some kind of value in your spread sheet of data that uniquely identifies each customer. Names may not work for this as you may have more than one customer with the same name. A Driver's license, and email, concatenating name and billling address into a single vlaue, are all possible values that you might use to temporarily match a record in customers to a record in Invoices so that you can copy over the needed match field value.

              It would be tedious to the extreme, but since you already have your data in Customers, you could click through each record and manually select the correct customer.

              Line items will be  real problem to import from your file due to the fact that data arranged into multiple columns in the spreadsheet row has to become individual records in the Invoice Data table. The typical way to do that is to import into a different table and then use a looping script to copy the data into new records in Invoice Data while also assigning the needed primary key value to it's foreign key value so that these items appear in the portal for invoice data in your starter solution.