3 Replies Latest reply on Sep 25, 2014 11:36 AM by philmodjunk

    Conditional based on if there is a matching serial number for a record.

    JustinKaiser

      Title

      Conditional based on if there is a matching serial number for a record.

      Post

      I’d like to create a script that compares the serial number of records in an external data source to the serial number of records in my FileMaker database and then deletes the record in the external data source if no matching record is found in the FileMaker db. In this case I'm creating a script that syncs between these two data sources and this portion of the script would delete any "orphan" records that were deleted in my Filemaker database but still exist in the other data source.

      What would be the best way to see if there is a record with a matching serial number in my Filemaker database?

      To be more specific, I’m using a plugin (Address Book Manipulator http://www.addressbookmanipulator.com/) to update records in my Apple Contacts application.

      In essence my script would look something like what’s shown below. I'm just not familiar enough with the Filemaker functions to know how to best implement this.

      Go to first address book contact (in the mac Contacts application)
      Loop
         Get the value for the Apple Contact’s serial number
            Search for records in my FileMaker database with a matching serial number.
            If [no record with a matching serial number is found]
               Delete the record in the Contacts application
            End if
         Go to next Record in the address book application / Exit after Last
      End Loop

      As always, thank you for any help. :)

        • 1. Re: Conditional based on if there is a matching serial number for a record.
          philmodjunk

          Note that when using import records to move records from one table to another, there's a "matching" option that can be used to update existing records when an imported record has the same serial number value but which also creates new records when there is no match. I find myself wondering if such an import might eliminate the need for the above script.

          Another way to compare records by serial number field between two tables (Doesn't matter if tables are in the same file or different files), is to link them in a relationship by serial number field. You can then enter find mode, specify an asterisk in the serial number field from the related table, make the request an "omit" find request and then perform the find. Your found set will be all records in the layout's table that do not have a matching record in the other table.

          To link a table from another file to a table in the current file, you can add a table occurrence to Manage | Database | Relationships that uses an external data source reference to refer to records in the table from the other file.

          • 2. Re: Conditional based on if there is a matching serial number for a record.
            JustinKaiser

            PhilModJunk thanks for the response. Unfortunately I can't really do this via a table occurrence because I'm dealing with data from a separate application that can't be accessed via a relationship table (or at least that I'm aware of). Also, importing the data into Filemaker from the Contacts application also wouldn't really work, because I'm working on a one-way push of the data from Filemaker to the Apple Contacts application (importing into filemaker would be the opposite direction). Again, all I'm trying to do is find any orphan records in the Contacts Application that don't have corresponding records in Filemaker and delete them. Or another way of putting it, if I delete a record in FileMaker it will then be deleted in the Contacts Application during the next sync via script.

            Here's what I've come up with for a script so far. The problem that I'm having is that the whole script stops when a matching record isn't found. Is there a way of doing something similar that wouldn't halt the script when no records are found?

            # Get the Unique ID of the Record in the Contacts Application and Set it to a Global Field
            Set Field [ Contacts::gCurrentReccord; PCAB_OpenFirstRecord ]
            # Exit Loop if there's an error with the Contacts applicaiton or there are no more records in the contacts application
            Exit Loop If [ LeftWords(Contacts::gCurrentReccord; 1) = "!!ERROR" or LeftWords(Contacts::gCurrentReccord ; 1) = "END" ] 
               # Set a Variable with the Contact Application Record's unique ID so that I can use it later in the find script step
               Set Variable [ $contactID;     Value:Contacts::gCurrentReccord ]
               Go to Layout [ “All Fields” (Contacts) ]
               Show All Records
               Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts::Address Book ID: “= $contactID” ] [ Restore ]
               If [ Get ( FoundCount ) = 0 ]
                  # Deletes the record in the address book application if there are no records found in the FileMaker db with the corresponding ID
                  Set Field [ Contacts::gResult; PCAB_Delete(Contacts::gCurrentReccord ) ]
               End If
               # Iterates to the next record in the Contacts application
               Set Field [ Contacts::gCurrentReccord; PCAB_OpenNextRecord ] 
            End Loop 

            Again, Many Thanks

             

            • 3. Re: Conditional based on if there is a matching serial number for a record.
              philmodjunk

              Set Error Capture [on]

              will keep a "records not found" error result from halting your script.