Change File Path Based on User Input

[jsamstad]

Hello!

Background: I have a flow that transforms + unions different vendor invoices (.xlsx) each month so that we can analyze the data and create journal entries. Each vendor is separated into its own container that begins with the directory tool that is pointed at the vendor name folder within the current period folder.

My Question: how can I roll the filepath/folder forward each month instead of manually updating each directory tool for each vendor container? I assume using a dropdown that would list the fiscal year + fiscal period would be my best bet, but haven't found a way to do it yet.

P01 folder > Vendor A

                     Vendor B

                     Vendor C

P02 folder > Vendor A

                     Vendor B

                     Vendor C

Thanks in advance!

[jzkyburz]

@jsamstad see if this works for you:

Add a text input with the filepath pointed to an existing directory and file:

Add Drop Down(s) to allow user to select year/period (I added 2 separate drop downs, but you could handle in 1 if you have the values set up in a way that match how your directory is structured):

Set the action type to 'Update Value' and check the box to 'Replace a specific string' at the bottom, and input the value you have in the text input above as that is what you want to replace:

Set a Dynamic Input tool to read in the updated filepath and Action set to 'Change Entire File Path'

[jzkyburz]

@jsamstad example attached here

[jsamstad]

On the right track. The input would flow to each Directory tool because the idea is to ingest all of the relevant .xlsx files in each vendor folder and union them. There has to be separate containers as each invoice/workbook has a different layout for parsing/transforming.

I see that the Drop Down + Update Value can only be connected to one Directory tool, not several, which is needed in my case. Thanks for helping and apologies if I'm not conveying the issue correctly. 

C:\Reporting\FY23\P01\Vendor A

C:\Reporting\FY23\P01\Vendor B

C:\Reporting\FY23\P01\Vendor C

C:\Reporting\FY23\P02\Vendor A

C:\Reporting\FY23\P02\Vendor B

C:\Reporting\FY23\P02\Vendor C

[jzkyburz]

@jsamstad do you have additional data or can share the workflow so that I can understand this better? There may be a way to indirectly update multiple directory tools, but tough to make a suggestion based on the info I have now. Hoping we can find you a solution!

[AndrewSu]

@jsamstad , you can recreate the file path in a formula tool and use the datetimenow() function along with other transformation to update specific parts of the string within an action tool (which would be configured to "Update value with formula"). 

Action tools do NOT need to be attached to any interface tools for them to function properly.  Given that you want to dynamically update the parameters at run-time, I don't believe you'd need any interface tools. 

For example, if your file structure is "C:\Users\Desktop\{PERIOD}\{VENDOR}"

The formula could look something like this. 

if datetimeformat(datetimenow(),"%B") in ("January", "February", "March") then "C:\Users\Desktop\"+"P01"+"\Vendor A"

elseif datetimeformat(datetimenow(),"%B") in ("July", "August", "September")
then "C:\Users\Desktop\"+"P03"+"\Vendor A" else Null() endif

You would create an "if" clause for each grouping of months that fit into a designated period and then feed this formula into the Action tool that is connected to each of the directories and update the directory value.  see screenshot below. 

I hope this helps!  Please mark this reply as a solution if so.

Happy to clarify anything above as well!

P.S.  Just realized you gave the file structure in your recent reply.  But you'd do the same process/idea with the "FY23" element in the file path that needs to be dynamic. 

[jsamstad]

Very cool. Didn't know about that. It seems that Action tool does not flow to the Directory tool as the path that is previously provided overrides the formula.

[AndrewSu]

@jsamstad ,  could you share with me the formula you put into the action tool and the settings of your action tool?

[AndrewSu · Accepted solution]

@jzkyburz , this is a great understanding of the interface tools, but I think @jsamstad wants to even eliminate these from his process.  Check out my post above to see how we can address that. 

[jsamstad]

I've found that if you point it at the root folder "C:\Reporting" then use conditional logic to filter the directories for each container, it should get you to the correct end result. Thank you both for your input! 😀