3 Replies Latest reply on Mar 20, 2015 5:58 AM by philmodjunk

    New User !

    MegP

      Title

      New User !

      Post

      Total new user here... Sorry in advance if my questions are very basic!

      I would like to create a database that incorporates all customer details, with the various jobs completed for each customer. Currently, I have two Excel documents- one with customer details, and one with job details. 

      Is it possible for me to import both of these files into Filemaker Pro or would one need to be created within the database? I understand in order for both of the docs to correlate, a relationship would need to be formed- potentially a Customer ID No.

      Any help would be appreciated as I have tried a couple of different ways but I just end up confused!

      Thanks!

        • 1. Re: New User !
          philmodjunk

          You appear to have two different things that you need to achieve:

          1) Importing the data from your two Excel files into two different tables in a FileMaker File.

          2) Linking the data in the two tables such that a customer record in one table can be used to access just the list of job records associated with that customer in the other.

          1) is easy. 2) may be easy or require a lot of work on your part. Is their a customer number of some sort in both Excel files that can be used to match things up? If not, is there a unique customer name in both files.

          1) is fairly straight forward as you can drag and drop one of your two excel files onto the FileMaker icon to get a FileMaker file produced from the Excel file. You can then open this file and use Import Records | File from the File menu to find and import the records from the second file. Use the "New table" option from the target table to import the data into a separate file.

          • 2. Re: New User !
            MegP

            Thanks for you advice! 

            Just to re-cap I have one excel doc with customer details, and one excel doc with job details

            In regards to 2) there is a unique customer name (i have gone through and edited all names) but i have also inserted a column for Customer ID No. in the Excel doc for Customer details. So all are numbered. 

            Am I better off inserting a column in the job excel doc and writing the Customer ID No against the customers listed in there to make linking the relationship easier once imported into Filemaker?

            Hope that makes sense,

            Thanks

            • 3. Re: New User !
              philmodjunk

              As long as you have a field in both files that uniquely identifies the customer, it won't matter for purposes of importing and initially linking your records whether it is a name or a number.

              What you would do first is use Manage | Database | Relationships to link your two tables by that field found in both tables. Then you would define a new  field in Customers to auto-enter a serial number, a matching field in customer details. You'd use Replace field contents to first update this new field in Customers using the Serial Number option that is part of this tool. You'd then use replace field contents to update the new field in customer details by using the calculation option to copy over the value of the new field from Customers. This then enables you to change the relationship linking the two tables to use the new fields.

              Here's why you'd do this. Now that you have imported your data, you'll want any new customer records to automatically get an ID that is unique and you'll want to use that ID to link to any new customer details records that you create. Any ID imported with from Excel that uniquely identifies each customer can't readily be used for that, but it can be used as a temporary way to link records until you can replace it with this better option that sets you up for adding new records to the system.