I am attempting to use a custom query in Workflow using an ODBC. If I enter the values manually into the criteria the query works fine. However, when I try to use a token for any of the values. I get this error:
I think maybe you are formatting the month first and then trying to use the month token calculator to subtract. So essentially its trying to take a month away from 12 instead of a month away from 12/19/2019 and then formatting to 12.
Try removing the formatting from the custom date calculation. Then add that formatting to to the token you pass into the query instead.
So
for the calculation and the formatting in the query.
Can you show us a screenshot of your custom query?
Query:
Date Token Calculator:
Basically, I want the token to enter the previous month as the value in the custom query. (EX: January would be 1, December would be 12)
What are you entering in the Token Values dialog when you testing the query activity?
Hoping I understand your question:
I'm using the Date Token Calculator in order to create the token as seen in my screenshot above and entering that token as the value in the query criteria.
The Date Token Calculator is set to retrieve the current date, subtract one month and then enter just the month number by itself as the token.
So it takes this Custom Date: %(Date#"%M"#) and then subtracts one month.
I meant, your initial post shows a dialog that you're getting when you test the query in the activity. Correct?
To test that query, you'll need to supply the month. When prompted for a value, are you typing in the month or a full date?
Are you saying you are getting this error when you click the Test Query button and type in the values? Or that works but running the actual workflow throws the error. Add a track tokens activity to your workflow and and run it again. Is the "Excise Month" value what you expected in the token menu?
I get the error message both when testing the query and also when I run the workflow.
When I test it I enter the token which is supposed to just be a single or double digit number (January = 1, November =11). If I just type "11" for example it works. However, when I use the token it does not.
Chris, I will try tracking the tokens and yes the Excise Month is the name of the token i'm trying to use.
So after adding the track tokens activity I see that the token is returning a value of 0. So there must be something wrong with the token string. How do I make a token that will take the current date, subtract a month, and then return just the month digit?
I think maybe you are formatting the month first and then trying to use the month token calculator to subtract. So essentially its trying to take a month away from 12 instead of a month away from 12/19/2019 and then formatting to 12.
Try removing the formatting from the custom date calculation. Then add that formatting to to the token you pass into the query instead.
So
for the calculation and the formatting in the query.
That is the correct behavior when testing. You are expected to type the actual value the query will use. There is no resolving tokens from other activities when you test the query.
As far the runtime goes, Chris is correct. Date Token Calculator needs a full date to work with. You either need to subtract the month first and then format the resulting date to get just the month. Or you need to use Token Calculator and subtract 1 from the date formatted to just the month.
Thank you both for your help!