10 Replies Latest reply on Nov 7, 2012 10:03 AM by philmodjunk

    Advice needed:  From one flat file TO relational

    PaulBlakeman

      Title

      Advice needed:  From one flat file TO relational

      Post

           Hi everyone - I'm a newbie to Filemaker Pro!

            smiley

           I've downloaded the Filemaker Pro12 - 30 day trial software. (running on OSX 10.8.2)

           In the process of evaluating I'm also trying to provide a solution.  If I can get this working then FM get another sale!

           I am trying to migrate an exitising database (APPLICATION: "My Mail List" running on OS9!!!!) to work on FM Pro12.

           Here's what I can do.

           export old database to a CSV type file, which I opened in Excel and then import into Filemaker.

           I could not figure out how to "move" data into other tables (which I have created)

           It also appears that you can only ever import into 1 table, so I'm slightly confused how you can import from a flat, single file into multiple tables.

            

           The database is essentaily a subscription management system.

           So we have customers who can have subscriptions to 1 or more different magazines/books.

           To simplify I have really stripped down my data just to figure this out...

           I have attached my database design file - 3 tables with relationships are what I need to populate.

           The Customer table has 8300+ names, the customer_ID is an auto-increment. (populated when imported)

           The Products table has currently one item (BBR110) with a Product_ID value = 1

           The Subscription table is empty (this is the one I'm struggling to populate)

            

           So in Excel I took all my customer names that were mapped to product BBR110 and gave a boolean value = 1 and figured if I imported into Filemaker I could somehow get the data relational.

           Well that's kind of where I get stuck!

           I imported this data (Initials, Surname, BBR110) into another table (called Table) and was trying to figure out if a script was the way to go???

           Any pointers please?

            

            

            

      Screen_Shot_2012-11-05_at_23.19.55.png

        • 1. Re: Advice needed:  From one flat file TO relational
          philmodjunk

               To import data into multiple tables, you just use one import records operation for each table in Filemaker into which you want to import data. If you are selecting Import Records from the file menu to do this, first select a layout based on the table into which you intend to import data.

               This assumes that your data to be imported already contains values for Customer_ID and Product_ID that uniquely identify each customer and each product.

          • 2. Re: Advice needed:  From one flat file TO relational
            schamblee

                 First of all Filemaker can import csv files, so you don't have to import to excel.  Filemaker can import into multiple tables.  When you click import in filemaker a import dialog box will appear  at the top left is a drop down where you select the source file and then on the top right there is another drop down where you select the target table. You can use a script to import but I don't see that it will make anything easier.  Even in a script you would have to select the source and target files.   Goto your customer layout to import customers, then goto your subscription layout to import subscriptions and so on. 

                  

            • 3. Re: Advice needed:  From one flat file TO relational
              PaulBlakeman

                    

                   Thanks for the feedback here - it's very much appreciated.

                   The first option is slightly confusing to me as I have 8000+ customers with about 100 different product subscriptions.  Not too sure how create the correct CSV file to do this (although I do understand the concept of having to provide ID numbers).  My confusion is how to deal with CustomerA having Subscription A,B,C whilst Custmer B has Subscription A,C and so on.  Don't understand why I have to the relational sort?

                   As for second option I cannot choose multiple tables.

                    

                   Any more pointers?

              • 4. Re: Advice needed:  From one flat file TO relational
                philmodjunk

                     It depends on how your data is structured. If it is structured like this:

                     Customer A Subscription A
                     Customer A Subscription B
                     Customer A Subscription D
                     Customer B Subscription B
                     Customer B Subscription C

                     Then you would import once into a customer table using a unique value  validation rule to filter out duplicate instances of the customer info. Then you would import once into your subscriptions table.

                     But if you have each customer listed once with each subscription in a different column, then your import process, while still quite possible will be more complex--you might need to import into a temporary table and use a script to move the subscription data into the subscriptions table.

                • 5. Re: Advice needed:  From one flat file TO relational
                  PaulBlakeman

                       PhilModJunk - thank you for your reply.

                       Unfortunately the CSV file I have is laid out as described in your second description.

                       My initial post tried to describe the same situation.  I had the data in a table and needed advice/help as to how to script a process that would populate my relational tables.  I am aware I obvioulsy need to do some more reading.

                       Does anybody know if this resource covers scripting in depth?

                  http://store.filemaker.com/GB/ENG/RTL/product/view/group/TSM/

                       If not, where is the best online resource for scripting?

                  • 6. Re: Advice needed:  From one flat file TO relational
                    philmodjunk

                         If you want, I can tell you how to construct a script such that you import your data into a temp file and then a script with two nested loops moves the data from your columns of data into separate related records that correctly link to your customer table.

                    • 7. Re: Advice needed:  From one flat file TO relational
                      PaulBlakeman

                           That would be very kind of you and very helpful.

                      smiley

                      • 8. Re: Advice needed:  From one flat file TO relational
                        philmodjunk

                             On further review of this thread you mentioned a unique identifier field. Does your CSV file contain a column where the data in it uniquely identifies each row of data in the file? If so, we can skip the need for this table and script.

                        • 9. Re: Advice needed:  From one flat file TO relational
                          PaulBlakeman

                               No.  But I can easily add one in Excel and import from there;  yes?

                                

                          • 10. Re: Advice needed:  From one flat file TO relational
                            philmodjunk

                                 Then what you can do is make repeated imports into the Subscriptions table, specifying the ID column each time, but then mapping just one of the subscription columns in each of the imports. This will build your needed related data in the subscriptions table.

                                 Add another import of the customer data (and ID field) into the customer table and you'll have all your data imported and linked.

                                 You can set up a script to do this so that yo perform one script and a series of Import Records script steps pulls all the data into your two tables.

                                 After Import you can update the ID number field in customers to be an auto-entered serial number field. Just be careful to specify a next serial value that is larger than the largest imported ID number.