I want to pass a custom value to the SQL input query during runtime

[annamalaimoorthy]

I have a workflow which has an input SQL query as below

SELECT * From TABLE
Where TABLE.COLUMN1 = '2020-12-31'

I want the user to be able to input the date when the workflow is triggered to run on the server. 

Is there a way we can do this ? 

I tried creating User constants in the workflow tab, and looks like i cannot reuse them inside a query, those constants are accessible only in formula tool. 

[yogi01061]

have you tried creating the SQL script using formula tool and passing the same to dynamic input? 

[annamalaimoorthy]

Hi @yogi01061 

Yes, but in this case the dynamic inputs cannot be modified to contain user inputs provided at runtime. 

I am looking for a solution where, when the user is running the workflow on the server , it prompts for a question and the date entered as an answer is being picked up in the input SQL query. 

Can you please help me with a sample workflow, should your suggestion cater my requirement. Thanks a ton in advance. 

[yogi01061]

Ah Got it, you can achieve your requirement by building an application. You need to have interface tool to get input from user, and make use of dynamic indatabase tool execute the SQL. In the Input text tool add your table name and provide your indatabase connection name.

Hope this helps!

[annamalaimoorthy]

@yogi01061  - Thanks for this.

Pardon my lack of knowledge in this area. When I open the workflow and select today's date, it returns the below error. 

LockInDynamicInput (7): Error finding connection "<Your Indatabase Connection Name>"

Can you please help me here on how to open and see your workflow to reverse engineer. Thanks

[yogi01061 · Accepted solution]

please find below steps,

1) inorder to use indatabase tools, you need to create a connection first (refer to Indatabase and manageindb screenshot)

2) once the connection is created take the connection name and replace the same in text input tool 

similarly place the correct table name in the Field1 sql query  (refer to text input screenshot)

once you have done above, you should be able to execute the workflow as a app by clicking on the magic wand.    (refer to executescreenshot)

hope this helps