1 2 Previous Next 21 Replies Latest reply on Jun 15, 2011 3:25 PM by philmodjunk

    Restrictive Importing

    ScottNikiel

      Title

      Restrictive Importing

      Post

      Hi, I'm working with a membership database. We often receive excel files with 4-5,000 records that we would like to import, however we would like to our database to take presedence. Is there an easy way to import records, but only records that have a new id number?

      Similarly, is there a way to lock a record to protect it from being changed by an import script?

      Thanks.

        • 1. Re: Restrictive Importing
          philmodjunk

          Specify the Unique Values, Validate option validation on your ID number field. Then, during import, only rows of data in your excel file that are for new ID numbers will be imported. Since all of these rows will create new records in your table, the existing records will remain unchanged.

          • 2. Re: Restrictive Importing
            ScottNikiel

            Thanks, though I did forget to mention that I would like to keep any of the duplicate records from being imported at all.

            • 3. Re: Restrictive Importing
              philmodjunk

              Yes and this method does exactly that.

              Make a copy of your file and run a few tests on it to see for yourself. If you can't get it to work feel free to report back with a description of how it failed.

              • 4. Re: Restrictive Importing
                ScottNikiel

                Perfect, thank you.

                Also, as a manual override, I would like to create a button that will emulate the scenario where the id numbers match (without having the id number in the database), by toggling a button (essentially an import lock/unlock button) that is set to unlock by default.

                Thanks.

                • 5. Re: Restrictive Importing
                  philmodjunk

                  Interesting idea, but I'm not coming up with any easy way to do that. You may have to throw out my original suggestion and take a completely different approach.

                  What I'm currently thinking of is a method that imports into a related, duplicate "import only" table. Then you can either import all records with an update import that matches values on your ID number field for the new option you are describing. For the original option, you'd find and delete all records from this table where the ID number matches an existing record in you main table, then you import...

                  • 6. Re: Restrictive Importing
                    ScottNikiel

                    This project has been on hiatus for a while... I've started working on some script that will ask the user to import their file into an empty copy of the original table. From there I figure it will take one record from the file and compare its assigned ID number to the same field in all records in the new table to eliminate any issues with ordering, etc. from there it will delete any matching records in the new table and move on to the next record in the original table.

                    I'm having troubles understanding how to compare one field in one record in one table to a field in all records in a different table. Do you have any ideas of how to start?

                    • 7. Re: Restrictive Importing
                      philmodjunk

                      Define a relationship between the two tables based on your ID numbers. If a record of the same ID number is already present in the main file, IsEmpty ( MainTable::ID ) will be false (and in an If step, you can just use If [MainTable::ID] to test for the presence of a related record with the same ID number.)

                      Looking back over your previous posts, I find that I have a new question. Can you describe in more detail how you want your "lock out" to work? Maybe it's the passage of time, but I'm thinking the import table may not be needed.

                      • 8. Re: Restrictive Importing
                        ScottNikiel

                        Your idea sounds effective, but I'm not sure what kind of relationship to create or how to do that.

                        Basically, we have a large FM database and we regularly (at least quarterly) recieve .xls lists with a mix of new and old records. The lock would simply protect records that don't currently have ID numbers, but do have the most recent address, email, etc. It doesn't really matter how it all functions, but it made sense to incorporate all of these into one step/script including a radio button field that can be switched from off to on if necessary.

                        • 9. Re: Restrictive Importing
                          philmodjunk

                          Define your temporary import table. We'll call it "Import". Let's say your current table is called "Main". And your ID field is called "ID" in both tables.

                          Open Manage | Database | Relationships and drag from ID in Main to ID in Import and you have your relationship.

                          So you have Records in "Main" that don't currently have an ID number or are these in the spreadsheet? If so, do not use this ID number field as your primary key in relationships within your database, use a different auto-entered serial number field so that all records have a unique value in that field, Then use this ID number field only for matching records during the import purposes (Or in the above relationship for matching from the import table).

                          A quick test reveals that any records in the source file that do not have a value in the ID column will not import with the import options we've discussed here. Does that work for you?

                          • 10. Re: Restrictive Importing
                            ScottNikiel

                            Got it.

                            There are a number of records in "Main" that don't have an ID number, but anything that we get in a spreadsheet will (it boils down to the fact that we are getting the spreadsheet from another organization and they have an internal ID number which doesn't apply to all of our records, nor can we rule out the idea that someone we currently have in our lists won't be on theirs at some point with an ID number). So it doesn't matter if a record without an ID number won't import into the "Main" table.

                            • 11. Re: Restrictive Importing
                              philmodjunk

                              What I'm trying to get a clear picture of is what records you want to "lock", and under what circumstances as that's the issue that may require a temporary import table in order to selectively prevent updates when the ID numbers match.

                              • 12. Re: Restrictive Importing
                                ScottNikiel

                                A "locked" record would be a record (with or without an ID number) in the main table that we know is up-to-date and we do not want to be updated regardless of what information may be in potentially imported record.

                                • 13. Re: Restrictive Importing
                                  philmodjunk

                                  Just thought of a way to do this without the temp table:

                                  In Manage | Security set up record level access control, using your lock field to prohibit modification of any record where a specified value is entered into your lock field. See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set this up.

                                  With that in place, you can show all records and do your matching fields import to update your records as previously described, but now record level access will keep "locked" records from being updated by the import. Just be careful not to try this when you have the database open with a full access password or the lock will be ignored. I'd set this up with a script that checks to be sure that the file is open with the correct, limited access privilege set and then does the specified import for you.

                                   

                                  • 14. Re: Restrictive Importing
                                    ScottNikiel

                                    Will this allow the restricted access users to view the locked files (this is something we need)?

                                    1 2 Previous Next