I’ve recently started working on a project where the folder structure uses years and months. It looks a bit like this:

This structure makes a lot of sense for that team for this project, but it’s a nightmare for my Alteryx workflows – every time I want to save the output, I need to update the output tool.

…or do I? One of my favourite things to do in Alteryx is update entire file paths using calculations. It’s a really flexible trick that you can apply to a lot of different scenarios. In this case, I know that the folder structure will always be the year, then a subfolder for the month where the month is the first three letters (apart from June, where they write it out in full). I can use a formula tool with some date and string calculations to save this in the correct folder for me automatically. I can do it in just one formula tool and a regular output tool, like this:

Firstly, I’m going to stick a date stamp on the front of my file name in yymmdd format because that’s what I always do with my files. And my handwritten notes. And my runs on Strava. Old habits die hard. I’m doing it with this formula:

Replace(Right(ToString(DateTimeToday()), 8), "-", "")

…which is a nested way of saying “give me the date right now (2021-02-08), convert it to a string (“2021-02-08”), then only take the 8 characters on the right, thereby trimming off the “20-” from this century because I only started dating stuff like this in maybe 2015-16 and I’m under no illusions that I’ll be alive in 2115 to face my own century bug issue and I’d rather have those two characters back (“21-02-08”), then get rid of the hyphens (“210208”).

The next two calculations in the formula tool give me the year and month name as a string. The year is easy and doesn’t need extra explanation (just make sure you put the data type as a string):

DateTimeYear(DateTimeToday())

The month is a little harder, and uses one of my favourite functions: DateTimeFormat(). Here we go:

IF DateTimeMonth(DateTimeToday()) = 6 THEN "June"
ELSE DateTimeFormat(DateTimeToday(), '%b')
ENDIF

This basically says “if today is in the sixth month of the year, then give me ‘June’ because for some reason that’s the only month that doesn’t follow the same pattern in this network drive’s naming conventions, otherwise give me the first three letters of the month name where the first letter is a capital”. You can do that with the ‘%b’ DateTimeFormat option – this is not an intuitive label for it and if you’re like me you will read this once, forget it, and just copy over this formula tool from your workflow of useful tricks whenever you need it.

The final step in the formula tool is to collate it all together into one long file path:

"\\big ol' network drive\that team\that project\" +
[Year] + "\" +
[Month] + "\" +
[DateStamp] + " file name.csv"

You don’t need the DateStamp, Year, or Month fields anymore. I just deselect them with a select tool.

Then, in the output tool, you’ll want to use the options at the bottom of the tool. Take the file/table name from field, and set it to “change entire file path”. Make sure the field where you created the full file path is selected in the bottom option, and you’ll probably want to untick the “keep field in output” option because you almost definitely don’t need it in your data itself:

And that’s about it! I’ve hit run on my workflow, and it’s gone in the right place:

It’s a simple trick and probably doesn’t need an entire blog post, but it’s a trick I find really useful.