Integrating date picker with indb filter

[danloz]

Hi,

Oddball issue:  

I've got a workflow that works flawlessly with a indb filter with either a static value (ie, dt_create = '2022-07-01') or with a calculated value.  

I'm trying to put a UI over one of the workflows to allow a  run time user to select a specific date they want to run the workflow for.  

Therefore, I've:  

1)  Inserted the date picker

2)  Inserted the action tool

3)  Configured the action tool to replace specific string 2022-07-01, where as the previous contents of the filter tool would have been "dt_create" = '2022-07-01' AND "some_field" = 'some_value'

But, when I run this as is return no values, unexpected.  My immediate suspicion it had to do with the quotes in the filter tool, so I even tried instead replace specific string '2022-07-01' and that returns some errors like "

• Error SQLPrepare: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;266 inconsistent datatype: INT type is not comparable with DATE type.: line 1 col 103 (at pos 102) (Tool Id: 17)"

I'm about 99% certain whatever I'm doing wrong here is pretty obvious but I'm not sure what I should try next.  Does anybody have a guess?  

[apathetichell]

can you show us the config of your action tool - what you are describing should work. I tend to edit dates either inside the SQL query in In-DB or in a text field which is part of a dynamic In-DB tool - but your filter situation should be fine.

[apathetichell]

also - can you confirm that you are correctly configured to show results in your interface designer?

[apathetichell]

o.k. - correction - this doesn't seem to work. This would work I believe if you edited a date in the sql in-db connect

off to test.

Date to Action tool to modify the SQL query in a Connect-In-DB does work. user "'" in the string to replace.

[danloz]

Sure.  

Just fwiw, here's the offending bit of workflow.  Both actions do the same thing on separate queries against the same DB but different data sets.  

Here's the action tool:  

And  here's the filter:  

[danloz]

Oh my!  I found something that Alteryx can't do, I guess.  

Just curious:  if I can't do that with the indb connection, do you think I can rebake the front part of this workflow to use the input tool with ODBC to achieve the same thing - dynamic filter from the date picker?

[apathetichell · Accepted solution]

There are two different ways which definitely did work:

1) you have your where statement in your sql query in your connect in -db. Your action updates your Connect In-DB directly. That works.

2) You update outside of in-db and pass your query into your db tool via dynamic connect in db. This is actually my preferred way to update queries/dynamically change queries. As a reminder to use dynamic connect in -db you must concatenate your query so that you are passing in 1 row - a connection tool and a query are the two columns passed in.

I'm also not 100% sure your filter way doesn't work in a batch macro. I haven't tested it - but I could see it being fine.

[gabrielvilella]

Hi @danloz, you can also use the debug option to try to understand if those action tools are behaving the way you expect them to. Go to the Interface Designer, on the left menu select Test View, select the date and anything else needed the hit the Open Debug button. It is going to open a new workflow with all the substitutions made by the action tools so then you can run it and test it.

[apathetichell]

@gabrielvilellaPlay around this - I just did for a bit and I think debugging the steps noted above don't do much - they produce the correct output in debug - but crash on execution.

I think this is probably relates to how  Alteryx determines when to update values vs how it renders SQL in the In-DB interface. It's basically trying to update a value after it's already created the SQL which is creating an error. A filter tool In-DB isn't like a normal filter tool.

Again - using Dynamic In-Db or updating the Query itself in a Connect In-DB tool will work.

[danloz]

Oh cool!  I was looking for something like that and I didn't see an obvious way to spit that output back at me.  That will be super useful in the future.  Thanks a ton.