Hi,
I am trying to create an analytic app in which the user can enter dates, choose from a drop down, and check box and then have this update a SQL query and produce an excel file.
I am not having any luck and the error messages I am getting are not providing me with enough information on what to troubleshoot next. I appreciate any help. Please see attached screenshots below.
Workflow
Running as Analytic App
Errors
1st tool configuration
Solved! Go to Solution.
There could be quite a few things happening here, but let's look at the immediate.
You are replacing -9999 and 7777 in your query, but those are numeric values and you are placing dates in their stead, which act as strings. Your query has no single quotes around the queried value, but in SQL, you will need those.
So it needs to look like: cast(Field as date) = '2018-01-01'
It currently looks like: cast(Field as date) = 2018-01-01 <-- This will fail
Change these and report back your findings
Thanks for responding Treyson!
I simplified the workflow and kept only the date inputs.
I also input some dates and asked for my update tool to replace the 2018-01-01 and 2018-01-31 so that the quotations still remain.
I chose the dates 2018-06-11 and 2018-06-15 in the interface
However, the final output file gave data from 1/1/2018 to 1/31/2018 and not the dates selected. This makes me feel that the replace a string is not happening.
I went back to using 9999 and 8888 in the query and kept the quotations.
However, this time the excel file produced no rows of data. This makes sense if the query ran as it original was as there are no dates 9999 and 8888 and thus no rows returned. It appears the main issue is with the Action Tool not actually updating and replacing any strings.
I am looking at your setup and it seems that you have selected the action tool to adjust where "@fileformat - value = "23"". There is no 9999 in that so it doesn't have anything to replace. Check to make sure the action tool is looking at the correct thing to do the work on. it should be the option 'File - option: (your query)'
Can you please show me with screenshots where I can adjust the Action Tool configuration? The only two places I have options are at the top drop down and the bottom replace a string field box. I am not able to click on the area where it says @FileFormat - value="23" to adjust it.
I sent you my phone number in the private messages box, call me and I will help clarify things a little. You are right there.
I have a very similar issue and nothing I try works.
I have a sql query where i have to use a particular date in several places of the query.
I would like to have this change with input. In other words (see attached picture), I want to replace 2018-05-31 in all places it appears in the query with the user input.
This seems so simple and common, but I find no answer anywhere and all the variations I've tried fail.
Any and all help appreciated.
Thanks, Joe
Thank you Treyson! Would have never guessed that. They should give it a pickbox or something...
that sounds like a good "idea". I can't find anything about it over there, so we will put one together.
You can go comment and star this:
If you would like.