User-Friendly Date Selection

[Coriel-11]

Hi everyone,

I'm trying to make life easier for less-technical colleagues  who need to run workflows.

Typically our workflows will revolve around a certain date. The report will be pulled on an ad-hoc basis and the person will be after all the records since last time it ran (which was similarly ad-hoc.

What I'm trying to do is have an easy way for them to be able to enter that date without having to dive into the code in the Connect IN-DB tool, or find it in a filter. I want a simple way to be able to enter it into a box so it becomes a constant which other parts of the workflow can then draw on.

I've looked at the date-interface tool, but that seems to be for something else .I've also looked at defining constants by right-clicking on the workflow, but can't get them to work as dates (in say and in-database filter).

I can't be the only one after something like this, but can't work out how to do it. Please could some of you give me some ideas?

Thanks,

Matt

[JagdeeshN]

Hi @Coriel-11 ,

You can do this by either letting the user select a date using the 'Date Interface' tool or enter it into a textbox. The values in these elements can then be used within the workflow.

Please find attached a sample solution.

Please do let me know if this helps.

Best,

Jagdeesh Narayanan

[Coriel-11]

Hi @jagdeeshn,

Thank you very much for your reply.

As I mentioned above I've done a bit with Data interface tools and thought that was probably the best solution, BUT what I can't really work out is how to:

a- use them in a in-database tool, (i.e. only records after a certain date, as specified by the user via the interface) particularly how to do this if the interface date format isn't the same as that of my database 

b- use that date elsewhere in the process.

Many thanks,

Matt 

[Coriel-11]

Hi @jagdeeshn,

I've amended my clarification, but I'm not sure I'm allowed to tag people yet.

Thanks,

Matt

[JagdeeshN]

@Coriel-11 

Using the same example I attached in the earlier response, you can get the user inputted date within the Alteryx Data Stream. This can now be used with the workflow.

As for using the dynamic input tool, you can construct the query in a formula tool by making use of the inputted date. Post this you can replace the whole query with this string in the dynamic input tool.

Do let me know if this helps.

Best,

Jagdeesh

[Coriel-11]

Thanks @jagdeeshn,

So my SQl in the query/Connect In-DB is:

SELECT 
 RECORDS.FIRST_NAME "First Name",
 RECORDS.LAST_NAME "Surname",
 RECORDS.DATE_ADDED "Constituent Date Added",

FROM 
DBO.RECORDS AS RECORDS

WHERE 
RECORDS.DATE_ADDED >=  CAST('12/31/2005' AS DATETIME)

 But I suspect the problem is the CAST bit (Records.DATE_ADDED otherwise comes out in 2020-06-17 00:00:00 format).

For an "update value (default)" action, underneath the "Replace a specifc string" tick box I have

12/31/2005

But it doesn't seem to work. Tried it with single and double quotes around it in all permutations I can think of, Tried ditching the CAST bit and trying to replace the whole 2020-06-17 00:00:00 string. Tried hooking the interface date/action tools up to a Filter In-DB tool. Still no success. Any further ideas?

Thanks,

Matt

[echuong1 · Accepted solution]

Try this - I used a standard input, but the same should work in an in-DB input as well.

SELECT FIRST_NAME as "First Name",
LAST_NAME as "Surname",
DATE_ADDED as "Constituent Date Added"
FROM "Demo Datasets"."dbo"."Records"
where DATE_ADDED >= '2020-03-10'

[Coriel-11]

Thanks @echuong1 That's fixed it. I think partly I've been using Alteryx in a slightly odd way and so it's taken me a while to figure out how these tools work.

So is what's happening here that with the "Where" part of the SQL you have forced onto my database the way Alteryx formats the dates? (rather than the other way around i.e. trying to make Alteryx fit my database).

With much appreciation,

Matt