SQL and Filemaker- Getting information about Deleted Records from SQL Database

Posted on 2006-05-10
Last Modified: 2008-01-09
I'm using an SQL database to record information about conference attendees. My Filemaker database utilizes the ODBC driver/plug-in from Actual Technologies to communicate with the SQL database. My Filemaker database checks for records that are modified or added (by utlizing the maximum timestamp) and then downloads/updates those records. The only problem I have is, I don't know how to check for records that have been deleted from the SQL database. My Filemaker reporting needs to be accurate, which means it would need to exclude records that have been deleted from the SQL database, but how can my Filemaker database know which records those are?
Question by:wesbrowning
    LVL 28

    Expert Comment

    you can check if every fm record exists in the sql base (with a simple select return error), but thats gonna ages if the base is big.
    you could disable deletion on sql side, ask users to mark records to be deleted with a specific field a check box or something, instead of deleting, and then have filemaker to query these, delete its local copy and the sql record at the same time.
    I know nothing smarter so far.

    Author Comment

    how would i do the "select return error"? The option of marking the records on the SQL side is not an option for us right now, as the programmer feels it will take too much time on their end to do it that way.
    LVL 28

    Expert Comment

    return error: that depends on your plug-in which I do not know.
     using the regular fm SQL commands, you can use the get (lastODBCerror ) to find out what's wrong, and then decide the record is not found.

    sql side, too much work to implement one field and remove the delete button from forms??

    otherwise, you could delete all records everytime you use it and re-import, but I guess yr base is too big to do that....
    the alternative is to have the SQL server to delete obsolete records in the fm base using the fm odbc driver, but there again, yr dev people are not gonna like it.

    Author Comment

    I'm using the ODBC driver from Actual Technologies, which I would assume uses standard SQL commands. How could I tell it to find the records that are in my database? I assume you're telling me that if it does a find (or matching of some kind), it will return an error message that contains information about all the records that it couldn't find (which I would extract info on which records to delete on my end). What command would I send it to make this happen?
    LVL 28

    Accepted Solution

    Sorry, I got confused (starts like that you know!), I thought you were using a SQL plug-in, its just another ODBC driver.
    So you can issue a select command in a "run SQL" using the record key field, and see what it returns, you would ignore a good answer, and delete local records for which you got a "nothing found". The odbc error number mirror the standard SQL errors, I don't have the list, but you can easily work it out by experience with a know missing record. See fm doc at " lastODBC error".
    I may have it in a partial file @ home I did a long time ago, but I am not there at the moment.
    You could also try to import the record in a temp table, and if there is none, deduce it is missing on SQL base side.
    The big drawback of this method is that you need to run a loop record per record, and that's gonna be lengthy...

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
    Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now