2 Replies Latest reply on Jan 10, 2013 12:18 PM by StevenDeNeal

    Associating Multiple Records

    StevenDeNeal

      Title

      Associating Multiple Records

      Post

           I am creating a quick search database for all of our invoices by exporting my sales journals from my accounting software. The interesting thing my accounting software does when it exports as .CSV is it makes every invoice line item its own record. So to give an example:

           Record   Cust_ID    Invoice    Description

                1           ABC          123          Part# 321

                 2          ABC          123             Labor

                 3          ABC          123               Tax

           So, when I query a particular invoice or customer ID, I come up with with a dozen or so records which are really just one invoice. Is there a way to have FM associate all records with the same invoice number as a single record?

        • 1. Re: Associating Multiple Records
          philmodjunk

               The interesting thing my accounting software does when it exports as .CSV is it makes every invoice line item its own record.

               Every Line Item SHOULD be a a separate record. The number of line items on an invoice can vary so trying to accommodate varying numbers of lineitems as fields within a single record runs into all complications.

               You could import this data twice, once into an invoices table and once into a line items table with the Invoice field as the match field in a relationship linking the two tables. By specifying "Unique values", "validate always" for the Invoice field in your Invoices table, the duplicated data will be filtered out, importing just data from just the first row for each invoice. (This table would have fields for Invoice, customer ID and any other fields that need to record data once for the entire invoice).

               But if this is just for "quick search" you may not need to do that. You can set up a list view layout with Cust ID and Invoice in the header and the Descriptoion and other line items field located in a narrow row in the body and this will allow you to find and view the group of records that comprise one invoice.

          • 2. Re: Associating Multiple Records
            StevenDeNeal

                 Ok, so for simplicities sake, I just need to sort it so that the invoice data is more or less together. I am a FM rookie so additional importing and setting relationships doesn't appeal to me. I will just keep it simple. Thanks!