9 Replies Latest reply on Dec 15, 2012 10:58 AM by philmodjunk

    Need to be smarter with data import

    rickaltman

      Title

      Need to be smarter with data import

      Post

           I think I have a fundamental misunderstanding of the finer points of importing data. I am constantly frustrated when I try to automate imports of data that cross tables. For instance, if Customer A purchases Widget B and a Wufoo form provides a worksheet with the essential information, I don't know what to do.

             
      •           Customers and Widgets are stored in separate tables
                   
      •      
      •           Even when I succeeded in writing an elaborate script that found Cust A and stored Widg B in a related record, I was confounded if Cust A already had purchased from me (in which case Widg B replaced whatever the most recent product is that was purchased).

           I have had to resort to writing a script with an external macro program that performs an elaborate copy and paste to and from each field in the Wufoo worksheet and in FM.

           As you can see, I just don't get it. What am I missing?? I'm using FMPA12. Many thanks...

            

            

           Rick A.
           Pleasanton CA

        • 1. Re: Need to be smarter with data import
          schamblee

               You have to import directly from the table that contains the field.  You would go the the customer layout and import customer infomation then you would go to the invoice layout and import invoice data. 

          http://www.filemaker.com/help/html/import_export.16.9.html

          • 2. Re: Need to be smarter with data import
            rickaltman

                 I appreciate, and am discouraged by, your confirming my fear that this cannot be automated. I remain stumped by two things:

                 1. It seems as if even the second part, importing invoice data, can't be automated because even the act of creating a new child record carries uncertainty. If it is the customer's first order, it works fine; but if that customer has already before, the most recent record gets overwritten with the incoming data.

                 2. How does anyone place a FileMaker database online where everything happens automatically??

                  

                  

                 Rick A.

            • 3. Re: Need to be smarter with data import
              philmodjunk

                   I think you should examine the invoices starter solution that comes with your copy of FileMaker. It, for example, uses relationships to look up data from a products table each time you fill out a sales invoice.

                   1) I suggest describing what you are doing and how in a fair amount of detail. This should all be preventable with proper design.

              • 4. Re: Need to be smarter with data import
                rickaltman

                     Thank you, Phil. I suspect it to be the case that my design is flawed. I write books, service clients, and host conferences and each of those activities generates contacts in my customer database. At the top of the food chain is my Patrons table, a fictitious example of which I have uploaded here. The portal on the right of that screen image is to my Details table where each area of activity is tracked. In this case, Mr. Theesen has purchased books, attended seminars, and has traveled to our annual conference.

                     Were he to order a copy of my newest book, that would come to me via an Internet-based form (Wufoo) and its information would be in worksheet form. I know how to match records on the way in so that a new Patron record would not be created, but I do not understand how to automatically have a new child record in Details created as part of the import process.

                     I can provide more detail still, but let's start there. I appreciate the help.

                      

                     Rick A.

                • 5. Re: Need to be smarter with data import
                  philmodjunk

                       The data would need to be imported in a separate import into the related details table, not into the patrons table. The default option for importing records is to create new records for each imported row of data so this should not overwrite existing data unless you specifically select that import option.

                       The challenge will be in how to correctly link the newly imported details record to the correct patron record. Hopefully, you have something in the file from which you will import this data that works better than a name to uniquely identify each patron. Names are not unique and are very vulnerable to data entry errors so they don't work well for matching records in a relationship.

                  • 6. Re: Need to be smarter with data import
                    rickaltman

                         The tables are linked by unique ID numbers, not just by name, so we're good there. For existing customers, that would be cake to import with those IDs. But what to do for new customers who *would* need all of the patron info there? I can't seem to get around the appearance that this has to take place in two steps and therefore cannot be automated.

                    • 7. Re: Need to be smarter with data import
                      schamblee

                           I didn't say it couldn't be automated.   As I stated and Phil Stated you have to peform more than one step to import your data. Depending on what you want to import.  You would need to check if the customer is in your Patron database first, if they are not a current customer then import the customer information, or alert the user if Patron data is keyed.  Then you would go to the purchase order table and import the purchases. As Phil as stated the trick is linking the purchase order back to the patron table. 

                      • 8. Re: Need to be smarter with data import
                        rickaltman

                             Thanks, guys -- I appreciate the input.

                             Currently, I have a macro that performs an elaborate copy-and-paste procedure from the form into FM. While it's a one by one routine, it eliminates all manual entry. Perhaps its stock has gone up when I consider the alternative...

                        • 9. Re: Need to be smarter with data import
                          philmodjunk

                               Why use a macro when you can use a script? Why copy and paste when you your script can pull all of this data in with two import records steps?

                               Hint: If you specify Unique Values and Validate Always on the ID field in your Patrons table, records for repeat customers will be automatically excluded from the import. Another option is to do an update matching records type of import with the "add if no match" option enabled so that new records are automatically added.