6 Replies Latest reply on Jan 1, 2015 4:00 PM by GrahamKeogh

    Importing Records

    GrahamKeogh

      Title

      Importing Records

      Post

      Hi all

      I have to import a clients and jobs .csv from an old non FM database into a new Filemaker file which has a clients table and a jobs table. Each client has a unique projectID which was assigned in the original database.

      There is over 2600 jobs records, so wondering what is the best way to relink them so the clients table will provide the client name etc to the jobs. I tried lookup fields but not working, so maybe not as simple as that, so would welcome any advice!

        • 1. Re: Importing Records
          philmodjunk

          So you have two csv files one for Jobs and one for clients?

          And a column of data in both files is the original Project ID?

          Then, at least to temporarily link your data, you should be able to simply define a relationship between your two Filemaker tables that links records by this projectID field and import your data into the two tables. You can then figure out whether you can use this field going forward (perhaps it's a simple serial number field and you just need to update the auto-enter serial number settings on this field to assign a value to the next new record larger than any existing record's ProjectID) or to define a new ID for this purpose where you use the original relationship to update new match fields via Replace Field Contents before discarding the old pair of match fields for the new as your basic relationship between the two tables.

          • 2. Re: Importing Records
            GrahamKeogh

            Perfect, that worked, thanks a million!

            I also have a quotation .csv that I need to import into a Filemaker Quotation table, which has a portal that links to a Line Items table. What would be the best way to import this so they populate the line items table as full quotes and not lots of seperate quotes? DO I import first into the line items table and work from there? Thanks again for you help.

            • 3. Re: Importing Records
              philmodjunk

              Hard to say when I don't know what the data looks like. Sometimes you can import from the same file twice--once into quotations and once into line items, specifying a different combination of field to column mapping for each.

              But only if the data is "there" in the csv file to make that workable.

              • 4. Re: Importing Records
                GrahamKeogh

                Here is the data headers if that helps, let me know if you need any more info. Just not sure how to combine each seperate quotation item all back into one quote as would have originally been the case?

                • 5. Re: Importing Records
                  philmodjunk

                  Combining the items all back into one quote requires creating one record in quotations linked to the correct set of line item records in line items. It looks like you have the needed details for that though the purpose of some fields in this example are a mystery to me. If I am correct, id identifies the quotation so if there were 5 line items for Quote #580, there would be 5 rows of data all with 580 in the id column. If that is correct, then you have the minimum necessary to do the double import that I outlined previously.

                  Import into Quotations.

                  Make sure that you have field to which you can map to the id column in the above example. In field options specify "validate always" and "unique values". With those options specified, duplicate rows with the same value in id will be omitted during the import and you'll get one record for each unique value in id.

                  Now import your data from this file into quotations. Clearly, you'd map the id column to your Quotation id field. Any other columns that are specific to the quote rather than the line item should also be mapped to fields in your Quotation table.

                  Import into Line Items

                  Now import into line items. Don't set any field options in this table to omit duplicate values. Map the id column to your foreign key match field in line items and all other columns except any columns where you imported the data into quotations as data that is not specific to any one line item record.

                  Done

                  Caulkins Consulting, Home of Adventures In FileMaking

                  • 6. Re: Importing Records
                    GrahamKeogh

                    Brilliant, that worked like a dream, huge thanks!