Input/Output excel using a dynamic Sharepoint address.

Hi All,

I've encountered a problem when two people are trying to use the same workflow but at different occasions.

I am currently using a designer alteryx license and have designed a workflow that requires to run automatically. I have asked my colleague who has a scheduler license to set the workflow on a schedule.

The workflow is inputting and outputting data from a Sharepoint location which we individually require access to in order to run the workflow. The issue we're having is the Sharepoint address contains each one of our credentials.

For example:

The address I require to run the workflow is - C:\Users\Tom\Corporation\Analytics\workflow.csv

The address my colleague uses is - C:\Users\Jonny\Corporation\Analytics\workflow.csv

If Jonny's file path was used to run the workflow last, and I make any changes and require to also run the workflow, I must change all the input/output's to use my Sharepoint address as my alteryx login can't access the Jonny's Sharepoint address.

A work around I'm using is to have two sets of each input/output and placing them within separate containers. The first container is using my sharepoint file path and the second container is using Jonny's.

If I want to run the workflow I disable Jonny's container and vise versa.

This is not a great solution so would anyone have any idea how we can get around this? Ideally I want a dynamic file path which we can both access.

Thanks,

Tom

Workflow

Input

Output

Scheduler

Sharepoint

Accepted answers

Tom216

I found a workaround to this issue which is to use a macro to load the users name which can then be put into the file path.

I saved the macro (attached) into the bin folder where Alteryx is installed on my machine.

Next I created a workflow which uses the macro to pull my windows name as a field value.

Load from SharePoint Workflow.JPG

From there, I referenced the field (username) from the macro in the file path of the data which I want to load. This is within the formula icon.

All that's left is to use a dynamic input and use the formula field "Path" to load the data.

If each user has the macro installed on their machine, each time they run a workflow requesting access to SharePoint, it will connect using the username credentials acquired via the macro.

Thanks,

Tom

Dynamic_Data_Load.7z

All comments

BenMoss

Check out workflow dependencies and look at 'relative paths'. This essential takes the current workflow location, whoevers machine it is, and routes to the input files given the direction to the folder rather than specific names.

https://help.alteryx.com/11.7/ModuleDependencies.htm

Ben

Tom216

Unfortunatly I don't think this works for my problem. With my credentials the relative path becomes '...\...\...\analytics\workflow.csv'

When making the other file path relative it becomes '...\...\Jonny\Corporation\Analytics\workflow.csv' and this I can't execute this without error as it keeps Jonnys credentials in the path. It does add mine though to the path which is what I want so it turns out to look like:

c:\Users\tom\corp\Corporation\Analytics\workflow.csv\Jonny\Corporation\Analytics\workflow.csv

Hope that makes sense.

David-Carnes

Forgive me for asking a basic question. Why is the .csv not in a SharePoint library? If you both get Contribute permissions you should be able to use the UNC path to the file.

Best,

David

Tom216

Hi David,

It is in a SharePoint library and I have tried with the UNC path (https://corp.sharepoint.com/sites/etc......) but get an error from it.

The error looks like this:

Cannot access the folder C:\Users\Tom\Corporation\Analytics\https://corp.sharepoint.com/sites/etc.....

I don't know why it does this as the when using the UNC path, there is nothing but that path in the output data address field.

For some reason it adds the UNC onto the end of the local sharepoint drive(C:\Users\Tom\Corporation\Analytics\) even though the this file path is no where to be found in the workflow.

I hope this answers your question.

Thanks,

Tom

Tom216

I found a workaround to this issue which is to use a macro to load the users name which can then be put into the file path.

I saved the macro (attached) into the bin folder where Alteryx is installed on my machine.

Next I created a workflow which uses the macro to pull my windows name as a field value.

Load from SharePoint Workflow.JPG

From there, I referenced the field (username) from the macro in the file path of the data which I want to load. This is within the formula icon.

All that's left is to use a dynamic input and use the formula field "Path" to load the data.

If each user has the macro installed on their machine, each time they run a workflow requesting access to SharePoint, it will connect using the username credentials acquired via the macro.

Thanks,

Tom

Dynamic_Data_Load.7z

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703