6 Replies Latest reply on Sep 19, 2011 9:00 AM by philmodjunk

    Compare a Serial# field between two files and omit records that have a matching serial

    GabeDiaz

      Title

      Compare a Serial# field between two files and omit records that have a matching serial

      Post

      Hello! Thank you all so much with all the help you have provided me so far. I very much appreciate it!

      I have another problem. Please feel free to point me to a guide or other thread if this has already been answered.

      I have two files we will call "File Database.fp7" and "File.fp7".

      File Database sits on a MacMini and acts as a database. File is loaded onto an iPad and data is collected.

      I can not use remotely hosted file as I do not have a reliable wireless siganl. What I want to do is after data is collected using the iPad. I want to drop File.fp7 in the folder with File Database.fp7 and run a script that will compare the serial numbers of all the records in both databases and return the results of records in File.fp7 that do not have an identical Serial# in File Database.fp7. Once I can accomplish this, then I can import the remaining unique records.

      I know I can use a method of setting a field that calculates Get ( ApplicationVersion ) and then replaces on import but I do not not want to do this as it requires me to load the file back onto the iPad, which is a step I would like to avoid.

      Please let me know if anything is unclear or you have a question and thank you all I very much appreciate the help.

        • 1. Re: Compare a Serial# field between two files and omit records that have a matching serial
          philmodjunk

          First the basics: If you specify Unique values, validate always for this serial number field, you can import records with Import Records into this table and the existing records will prevent the import of any records with matching serial number fields.

          You can either upload the file from the iPad and then run a script on the MacMini file to import recors from the uploaded file or you can add external references to the tables in the MacMini file in File and run the import from the iPad once you have returned to a location where you have a reliable wireless location--something that you'll need to do anyway inorder to upload the file back to the MacMini.

          • 2. Re: Compare a Serial# field between two files and omit records that have a matching serial
            GabeDiaz

            Sorry I am not sure what you mean by the basics. I set the Serial field to require Unique value in both files and when I told the Database file to import records from the Mobile file it just imported all of the records still.

            • 3. Re: Compare a Serial# field between two files and omit records that have a matching serial
              GabeDiaz

              I believe I figured out a way to accomplish what I need. I am gonna do a little more work tonight and then I'll post my solution if all goes well. =]

              • 4. Re: Compare a Serial# field between two files and omit records that have a matching serial
                Sorbsbuster

                Is the Serial Number set to automatically issue the next serial number?  Your setup will only work as long as the Database file is used only to store data collected by the iPad, and that you only collect data on that one iPad.  In other words you can't have simultaneous data collection in two places, or you will have two records with the same serial number (which will not import) when in fact they would each be unique records.

                You could consider marking those records that have been exported to the File Database as 'Exported' and use a Find script step to omit them with your next import.

                 

                • 5. Re: Compare a Serial# field between two files and omit records that have a matching serial
                  GabeDiaz

                  Hello guys,

                  I just wanted to share how I accomplished this task. I am not going to go into the specifics of my script but this is the gist of it:

                  Just a disclaimer, I don't claim this is the best way to do this. It's just what I figured out.

                  In this example we will call my files "Form-Database.fp7" and "Form-Mobile.fp7"

                  I created five scripts:

                  Form-Database.fp7 Scripts:

                  Sync Control: This script runs the dialogs to confirm the user wants to import, and confirm that the records were imported. It also tells all of the other scripts to run in the correct order and the command to do the import from "Form-Mobile.fp7" is also in here.
                  Isolate Last Record: This script enters browse mode, go to the layout, shows all records, unsorts all records, goes to the last record, omits the record, and then shows only omitted.
                  Show All Records, Unsort, Go to Last: This script runs when everything is done to return "Form-Database.fp7" to a state that shows all records and puts in on the last record.

                  Form-Mobile.fp7 Scripts

                  Get LastSerial and Isolate: This script enters browse mode, goes to the layout, unsorts the records, goes to the last record, omits the record, and then shows only omitted records, it then imports the Serial field from the isolated record in "Form-Database.fp7" to a field that isn't on the layout called "LastSerial", it then sets a variable "TheLastSerial" based on the "LastSerial" field, it then does a search for records where Serial>$TheLastSerial. This returns only the records in "Form-Mobile.fp7" that have a serial # greater than the serial # of the last record in "Form-Database.fp7". Thus, we now have all of the new records isolated.
                  Show All Records, Unsort, Go to Last, Close: This script is run in "Form-Mobile.fp7" after everything is done to return it to a state that Shows All Records, Unsorts Records, goes to the Last Record, and then closes the file.

                  Here is a screenshot of how all of the scripts are handled by the Sync Control script. I blurred out the filenames for privacy/security reasons.

                  I hope this helps somebody else!

                  • 6. Re: Compare a Serial# field between two files and omit records that have a matching serial
                    philmodjunk

                    Works, but is way more complicated than it needs to be.

                    If you set these two options on your target table: Unique Values, Validate Always, you can import all the records from your source table into the target table and the records with matching serial ID's will automatically be kept from importing. From your posts, it looks like you neglected to select the "validate always" option.