Scribe AX Adapter Source Query Error – Wrong SQL Query with Scribe TablePageing

Hello again folks!

This time I bring you yet another interesting twist in my daily adventures in system integrations…

So let me set the stage.

My client has the Scribe AX 2009 Adapter and we need to do some fairly simple integration from AX to Dynamics CRM 2011. Pretty simple right? Well yes, and it is something I’ve done a number of times, however, this time because of forces beyond my control, I find myself needing to use the AX Adapter as the source of one of my integrations. Again no biggie right?

I also need to add some filtering to my source to limit the number of records returned to this DTS so I hit the old configure source button, select my Adapter object from AX in my custom query window, and hit the filter tab to add a filter. Here I need to use the familiar “If CreatedDateTime >= LastRunDateTime” filter so that I only get any new records since the last time this DTS Query was executed.

So here is what my config screens look like

Here we add our filter field and select our Scribe variable

Here we add our filter field and select our Scribe variable

Here is what our filter looks like in the Query window - Still good right?

Here is what our filter looks like in the Query window – Still good right?

Next we simply hit OK because that always worked in the past for other types of queries and data sources…

Woah! What have we here?

Woah! What have we here?

And behold! My day is about to be wrought with despair and frustration!

Woe is me…So you’ll notice in our error message that I highlighted where it is complaining about the date, so naturally this really didn’t mean much to me, so I began sifting through logs and traces and gobs of search results.

Naturally, when you’ve been doing this as long as I have, you get pretty good at piecing together the right solution from tiny bits and pieces of “similar” pains others may have had.

Now right before I was ready to toss in the towel and contact my support friends at Scribe, I stumbled upon a similar issue someone had with dates and SQL formatting and such.

You see, one thing I had not mentioned yet was that this client instance of AX was in New Zealand, and therefore the AX Created Date was formatted dd/mm/yyyy, like 20/08/2013. Well you say OK so that is why systems are flexible enough to handle those things right? Well for the most part yes, but turns out that our friend SQL doesn’t want any part of it and so SQL wants to be doing the query filtering with a date format of mm/dd/yyyy.

So I hard-coded a date into my query and formatted it mm/dd/yyy and joila! It did not complain and the filter worked properly!

So, now what to do? I pondered again long and hard, reaching deep into my bag of Scribe tricks, and once again Scribe user variables would prove to be my salvation!

Turns out that I have no control over the LastRunDateTime system variable, so I would have to abandon that and look to another way of filtering. I turned to the Today() function. I figured if I could filter my query on records created >= today’s date minus a day or 2, that would be pretty good. note: The client does not need this to be real-time and actually these integrations run on a timed schedule every couple of hours so not a big deal if my query returns the same results as I am doing Update/Inserts. Not the greatest bullet proof design, but it does work! 🙂

So I created a user variable called “Today” and set the formula to Today()-2. That gets me anything created within the last 2 days. Perfect, but now I still have the format issue because Today is returned as dd/mm/yyy.

Here is my Today user variable

Here is my Today user variable

NOTE, see the data type? It must be set to Date, the default is String! I repeat, make sure you change the data type of your variable to a date!

OK, we’re getting there…

So now we need to handle some formatting of our date so we can use it in our query and keep SQL happy.

For this I create yet another Variable. (Yes you could probably just reformat the one above, but I like to see before and after!)

So here is how we will format our Today variable, I create a new variable called “ReformatToday” and use this function:

RIGHT(“00″&MONTH(today),2)&”/”&RIGHT(“00″&DAY(today),2)&”/”&YEAR(today)

Here is our ReformatToday Variable

Here is our ReformatToday Variable

NOTE: Here you must leave your Data Type set to String!

So does it work? Lets fire up the test window and have a look!

Testing out our variables and reformatted Date

Testing out our variables and reformatted Date

Awesome! We have flip-flopped our dates! Now we just need to rebuild our query using our new ReformatToday variable as our filter criteria. before and after! It’s visual validation 🙂

The final filter that saves the day!

The final filter that saves the day!

Now when we click on OK, we have a happy filter and life is good again!

I sure hope that some of you facing this same issue can find this post and save some time and frustration!

Happy Integrating!

Don