This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
You typically dynamically load from a database when you have a list of years that you were sending via a list to your dynamic input SQL.
Based on the thread what I would suggest is a slightly different approach. Instead of using a dynamic input directly modify the sql in an Input tool as follows. Note that my example uses SQL Lite and is attached for you to review.
1) Configure the input with your SQL statement that is functioning, in my example it is as follows:
The years i want to replace are highlighted red.
2. Set up a text input for the date (you could also use a numeric input if you want:
3) BEST PRACTICE not required but recommended... set up an error message to force the user to enter a date, you could be more forceful if you wanted and have it be between a particular years etc:
4. Set your action as "Update Value", select your SQL statement in the tree, Check the box to replace a specific string and delete all the text in that replace string text box, enter the year that appears in your SQL statement that you want to replace.
Note that this can lead to problems if i had "2016' elsewhere in my SQL statement and I did not wish to replace it.
If you go to the Interface Designer (View > Interface Designer) there is an option to debug your app that is very helpful.
It sounds like your selection is wrong can you put a screen shot of your action?
So you will notice that my dates were YYYY-MM-DD but I am only replacing the year and happen to be replacing it twice.
You should be selecting the line with your connection and SQL query and only have the value you want to replace in the text box below (do not replace any of the quotes because they are not really quotes they are ticks)....
If you select the SQL statement and use formula then you will have put in the entire SQL statement with the formula, in my example that would look like this:
// Connection string my example is a sqllite file
//SQL Statement with #1 from Text Box
"SELECT * FROM orders where orders.date >= '" +
[#1] + "-04-01' and orders.date <= '" +
AlternativelY i could also do it this way:
However that is the exact same behavior as use select value and just changing the specific string I selected in the below example
How I know what is being replaced is when I open the View > Interface Designer and click on the Magic Wand option in the Interfacer Designer and enter in some text then click OPEN DEBUG the workflow below the log shows what was replaced in the tool and the Debug workflow should be able to run. (If it doesnt run then my action is not working correctly.)
You've created some great workflows that others need to run. Learn how to turn your workflow into an analytic app by constructing a graphical user interface (GUI) so users can easily set the required parameters.
You'll learn how to:
Choose from the range of interface tools to construct the best GUI
Publish your app to the Alteryx Gallery for consumption by others