SQL newest records query

If the information needed to decide what date range to use isn't available on the SQL server, you'll need to get it from FileMaker. You can do this by creating your SQL query string using FileMaker string calculations. Using a script or calculated field, you create the SQL query string by piecing together chunks.
For example, suppose you have a FileMaker field called "FM-LastExportDate", and you want to use a query like this:

  select * from orders where orders.date_added > some-date-from-filemaker
  or orders.date_modified > another-date-from-filemaker

To submit this string with the date value filled in, use the option to get the SQL query from a calculation, and use string concatenation to build the actual text of the query string.

  SQLQueryString = "SELECT * FROM ORDERS WHERE Date_Added = " & FMDateAdded & " OR Orders.Date_Modified = " & FMDateModified

Now the SQLQueryString field contains the query needed to return the newest results, based on values in FileMaker.

This is more awkward to describe than it is to actually do.

In your example, where do I use the field, "FM-LastExportDate"?

That would be a value from the FileMaker database, either a calculation, a field, or a script variable, which contains the variable portion of the query string. I just made up an arbitrary field name, you'd probably be using a field that already exists in your database.
 
A couple of things to be aware of...

Quotation marks (double quotes) are used by FileMaker to delimit the beginning and end of a text string. If you need to include double quotes in your query string, you'll have to add those in by using the backslash character in front of the quotation mark, like this:
  \"
This tells FileMaker that you want that quotation mark to be included inside the string rather than indicating the end of the string. Single quotes are ok, and don't need any special treatment.

Also, you'll need to make sure that the date format used is one that's acceptable to the SQL server, so you might need to change the formatting of the date field when putting it into the query string.

I submitted this string....
"SELECT * FROM ORDERS WHERE Date_Added = " & Local_System_Settings::gCurrentTime & " OR Orders.Date_Modified = " & Local_System_Settings::gCurrentTime

It returned the following error...
ODBC Error: [Actual][SQL Server] Incorrect syntax near the keyword 'OR'.


What happened? Also, will this return results that meet both criteria that I want (new records and updated ones)?

The string I provided is not a valid SQL query string on its own. It's intended to be evaluated by FileMaker as a calculation, and then the results of that calculation should be submitted as the query string.

Also, my example was typed off the top of my head, and probably doesn't meet the syntax requirements of your SQL server's ODBC driver. It could be that you need the quotation marks around the field identifiers, as you posted originally, for example.

Also, I used Date_Added for the first field, but Orders.Date_Modified for the second. You'll want to make them consistent, one way or the other.  
 
It may help you to divide the process into steps.

First, manually create a query string that produces the results you want. (This might be the hardest part)

Next, create a calculated field which evaluates to produce the same query string you created manually.
Tweak the calculation until it returns a result that looks exactly the same as the query string you know works.
Then, use the results of that calculation as the query string for your SQL query.

>Also, will this return results that meet both criteria that I want (new records and updated ones)?
That depends on exactly how your tables are defined and how you're using those fields.
Here's what that query will do:
It will create a result set that includes any SQL records where the Date_Created field is LATER than the date specified in that FileMaker field, or the Date Modified field is LATER than that same Filemaker date.
If you could have multiple entries on one day, you would also need to take the TIME into account.  

The formula I provided can be used as a reference, but if you want an exact example of how to create your specific query from within FM, you'll need to post the exact full text of your query, and the exact names of your tables and fields from both the SQL and FileMaker databases.
 
 

I tried a few things, including your suggestions but was unable to get any further. Below is the information that might help you help me construct a bettery query string. Perhaps I should have given some of this information to you sooner. Thanks for your patience.

You should also know that the ODBC driver I have only downloads, and cannot upload. This "one-way communication" may have an affect on how (or whether or not it's acutally possible) the query is constructed. All of my examples are specific to my situation and refer to actual fields I'm using.

With my driver, I can construct a query in one of two ways to get information. Below are examples of the first way I can do it. This first example shows a query from one table and one field from that table. The second example shows all fields from one table. The second way I can construct the query is through calculated text.

Ex 1:
SELECT "Orders"."Location"
FROM "Orders"

Ex 2:
SELECT *
FROM "Orders"


I would like to compare the local time (established in Filemaker, see below) with the remote records to decide what records it needs to download. It would need to download any record that is new, or that has been updated. The two fields on the SQL database that use a timestamp for the records are:
"Orders"."Date_Added"  and  "Orders"."Date_Modified" (it returns a timestamp like this: 2/28/2006 1:46 PM)

The field I'm using in Filemaker to establish the current time is: Local_System_Settings::gCurrentTime (it's format for a timestamp is: 3/4/2006 10:57:13 PM)

It is possible for us to have multiple entries in one day (we're querying the server every 5 minutes).

OK, let's start out with a simpler scenario and then work up to the more elaborate query, so we can figure out where it's going wrong.

- Create a new FileMaker script, and insert an Import Records script step.
- Check the box at the bottom that says perform without dialog.
- Specify Data Source, and choose ODBC as the source.
- Select the ODBC DSN for your SQL server, and any user/password required
- Don't use the "query builder" function. Instead, select "Calculated SQL Text" as the source of the query.
- In the calculation box, enter this text:

   " SELECT \"orders\".\"Location\", \"orders\".\"Date_modified\" FROM \"orders\" "

Notice that there's a double-quote at the beginning and end of the whole string,
and notice that everywhere a double quotes appears in your original query, we're adding a backslash in front.
The backslash tells FileMaker to treat that quotation mark as just part of the string.
Specify what field you want the orders.location data to be imported into.

Now, try running the script and see if it works. If it doesn't there's a mistake somewhere, so go back and double-check things.
Once this runs successfully, then we can move on to making the query more complicated.


One other point:
If your SQL fields are actually timestamp fields, then you'll probably need to use ODBC timestamp format when specifying the criteria. This may involve some string manipulation to get the actual value looking as it should.
Basically, ODBC timestamp format looks like this:
   {ts '2005-02-15 09:30:00'}

Here is a simple example file I wrote that shows how the script step is set up.
Since I don't have access to your SQL server, I created an Access MDB file, which I included in the archive, and set up an ODBC connection to it

http://www.milleniumhandandshrimp.com/upload/orders%20test.zip

The SQL query worked. I'm moving on to work on the timestamp issue you pointed out. Where do we go from here with the SQL string?

I tried the string and got an error message stating that there was an error with the Syntax on Line 1.

I just had a random thought...

What if I just did a regular import and based on matching/updating...and importing remaining records? If you think this is a good idea and would work, how would you recommend I go about it?

>I tried the string and got an error message stating that there was an error
>with the Syntax on Line 1.
At which point did you get this error? What is the exact text of the error message? It should show you the actual text of the query that caused the error, which could be due to differences in your ODBC driver and mine.
.

>What if I just did a regular import and based on matching/updating...
That would work if you want to change existing records, but it's not going to give you a history of past records. Also, won't you still need a SQL query to initiate the import?

>I tried the string and got an error message stating that there was an error
>with the Syntax on Line 1.
At which point did you get this error? What is the exact text of the error message? It should show you the actual text of the query that caused the error, which could be due to differences in your ODBC driver and mine.
>>>I doesn't actually show me the error. This a screenshot of the error it gives me: http://www.passportcamps.org/uploads/errormessage.gif .  I tried doing a query that the "where" was based on the SQL side (comparing date_added and date_modified) and I was able to get a record. So, I'm guessing that there's just a problem in the way that we're asking it to compare the SQL with the Filemaker side. I tried this....

SELECT "Orders"."Location", "Orders"."Date_Modified"
FROM "Orders"
WHERE "Orders"."Date_Added" < "Orders"."Date_Modified"

...and it worked fine. This however is not what I want it to do, but it does show that the SQL side is working fine.


>What if I just did a regular import and based on matching/updating...
That would work if you want to change existing records, but it's not going to give you a history of past records. Also, won't you still need a SQL query to initiate the import?
>>>I would still use an SQL query to get the tables I needed, but on the field mapping section it gives the option of updating records (and adding new). I wouldn't mind "changing" the local records with remote data, as long as the record matching criteria (=) indicated that they were esentially the same record. The difference in dates would indicate that it was a different record, right?

> So, I'm guessing that there's just a problem in the way that we're asking it to compare the SQL with the
>Filemaker side.
I'm sure it's just some sort of typo or similar error. To troubleshoot this, just create a calculated field based on this FileMaker calculation...

" SELECT \"orders\".\"Location\", \"orders\".\"Date_modified\" FROM \"orders\"  
  WHERE \"orders\".\"Date_modified\" > " & TimeStampForQuery

Show this calculated field on a layout, and it will show the actual text of the SQL query as it is being submitted to the ODBC driver. Copy and paste it here, and we can figure out where the error is.

>I wouldn't mind "changing" the local records with remote data, as long as the record matching criteria (=)
>indicated that they were esentially the same record. The difference in dates would indicate that it was a
>different record, right?
Only if you used that date as one of your matching criteria.

But if you do this based on a modification date, then ANY change to the record will automatically mean a new record in FileMaker, so effectively, it would be no different than if you just imported new records each time.

Ok, I figured out the problem. I contacted the SQL driver company and they said my log file looked like this....

SELECT "orders"."Location", "orders"."Date_modified" FROM "orders" † † WHERE "orders"."Date_modified" > {ts '2006-03-01 00:00:00'}

As you can see, right before the WHERE clause there is two † † signs. It turns out this was caused by the carriage returns in SQL calculated text.

I'm getting the results I need now. Thanks for your help, and thanks for sticking in there on this one!