11 Replies Latest reply on Dec 5, 2013 1:05 PM by philmodjunk

    Data Base question

    fmpro_novice

      Title

      Data Base question

      Post

           I have a Data Base with a handful of fields. When a new record is created, a serial number is generated. I need to be able to update the records in the Data Base from an outside source (Excel spreadsheet) using a .tab Import. Can someone tell me where to begin to look for info or a tutorial on how I need to go about this?

           Thank you.

        • 1. Re: Data Base question
          philmodjunk

               Why use a tab import when you can import from the Excel SpreadSheet?

               There are several parts to this process:

               Setting up the import (manual or scripted?)

               Making sure that the new data get's an auto-entered serial number

               What parts of this can you do? Can you manually import the data but want to script it? Are you having trouble with the serial number field? Or ???

          • 2. Re: Data Base question
            fmpro_novice

                 I was coming back on to edit my post, when I saw yours. Yes. The best option for me is Import from the Excel Spreadsheet.  This application is in a Runtime and I would like to be able to update the existing data in my data base and create new records automatically if the Excel contains records my data base does not.

                 I'd like the user to have the option to match up the fields, but I don't want them to be able to see my tables, etc. The reason is because not every user that is importing data, will be importing from the same type of Excel spreadsheet. The import could be based on the Key_Id field I have set up in my data base. I have it set now to be modifiable by the user. Right now, they just need to make sure the Key_Id field in their Excel file is the same as what is in my data base.

            • 3. Re: Data Base question
              philmodjunk

                   They will have to see your tables in order to match up fields. That can be problematic since it allows your users to select import options that could disastrously scramble the data in your field. You'll need to carefully document/train your users in how to work with the import mapping dialog.

                   Where exactly are you having a problem in setting this up?

              • 4. Re: Data Base question
                fmpro_novice

                     Mainly in not wanting users to see the tables. I think I may be able to work around this, though. I am going to try some things and then report back here as to successes, failures, etc.

                     Thank you!

                • 5. Re: Data Base question
                  fmpro_novice

                       For my project:

                       Excel spreadsheets could be created from a variety of different sources. But, my DataBase has 2 fields that will always be common to the outside sources.

                       One of those fields is Key_ID and the other is weight.

                       The weight is what needs to be updated. So anytime someone wants to update my main DataBase - my DataBase needs to examine the Import data from the Excel spreadsheet, and update the weight when the Key_ID numbers are the same.

                       Event though there are several fields in the DataBase, the only thing that will ever update is the weight.

                       I see what you are saying about the disaster that could happen if they map the wrong fields. But, all they need to do is map the Key_ID and the weight, right?

                  • 6. Re: Data Base question
                    philmodjunk

                         Yep, but the door remains open that they might map the wrong column to the wrong field once you open that field mapping dialog box. And there are options in the dialog where you can create new records with your import, plus two different options for update type imports that will overwrite data in existing records....

                         It'd be really nice if a future version offered a different dialog or a way to customize a scripted dialog for field mapping that only allowed the user to select columns for field mapping and that allowed the developer to block options that are dangerous.

                         But that's not an option at the moment.

                         But you might:

                           
                    1.           Have users import into a secondary table. When the imported data is then visually inspected and/or possibly validated via some scripts, a script then does a second import to pull the data into your 'working' table. The second import won't need any user interaction to import the data beyond clicking a button to perform the script.
                    2.      
                    3.           Save a copy of the file just before importing. If the import produces a disaster, you can swap out the current file for the saved copy.
                    4.      
                    5.           Try to find a way to get users to import from an excel file that always has these two columns as the first two columns with the ID field as column 1. (Perhaps a Visual Basic macro in the Excel file?) Then you can script an import where the user does not have to map fields to columns.
                    • 7. Re: Data Base question
                      fmpro_novice

                           I was thinking along those same lines. I'm going to get after it and see what I can produce.

                           Thanks.

                      • 8. Re: Data Base question
                        fmpro_novice

                             I renamed the fields in the Excel page to be exactly the same name as the fields in my Data Base. But when I click Import Matching Records, I get an error that says

                             "At least one pair of matching fields must be specified for the "Update Matching Records" option.

                             I actually have 3 fields that are matching. Why would I get this error?

                        • 9. Re: Data Base question
                          philmodjunk

                               FileMaker needs to know which field or column in the source table/field is to be used for matching records during the import. To do that you click the line between the mapped pair of fields/columns to turn it into an arrow. Without at least one such arrow, FileMaker has no way to match records.

                               This, by the way, has nothing to do with matching fields/columns by name. That's a selection in a drop down that automatically maps fields in the source table to fields in the target table if the field/column names are exactly the same.

                          • 10. Re: Data Base question
                            fmpro_novice

                                 I had all three fields set to arrows. When I changed the Key_ID to an equal sign, then the field I wanted to update to the arrow, it would perfectly. I think the people I am working with will be able to handle this. When I created the Runtime, that helped me with the issues. The user cannot see my table info or database fields. So I am very happy with the solution you helped me reach.

                                 Thanks again!

                            • 11. Re: Data Base question
                              philmodjunk

                                   Sorry, the = sign is what is needed to identify a field as a match field, not an arrow. My mistake.