cancel
Showing results for 
Search instead for 
Did you mean: 
Announcement | Alteryx Connect is now generally available! Find, understand, and trust all relevant information in your organization. Find out more.
rpaugh
Asteroid

How does one automate that which cannot be automated?

 

We are in an information age.  An age where someone can monitor the security of their home and unlock their care from a mobile device; start streaming a movie at home, move to an iPad, and finish on a plane; and even blend, cleanse, and predict data with little or no data science knowledge.  And yet many, if not all of us, at some point have tried to gain access to data within our own organizations only to be met with the solution of “we can email you an Excel file”.  Really?!?  I can put money into my Starbucks account via my phone, and have Starbucks scan and recognize the payment, deduct the cost from my account and give me my bonus stars for the transaction, but the only solution you have to give me data is to email it to me?  Now is a good time to tell you something important about myself – I’m lazy.  I loathe manual, tedious, repetitive tasks.  I often joke with my coworkers that I’m going to automate everything I do so I can sit and watch YouTube all day.  In fact, were I to successfully automate myself out of a job I would consider that the single greatest accomplishment of my career.  As you can probably imagine, I was not willing to manually download and process Excel files each week.  Fortunately, I come from a software development background and was able to write code to extract attachments from Outlook emails and baked that into an SSIS package that ran weekly. 

 

Outlook Input toolOutlook Input toolFast forward a couple of years to Inspire 2017.  I was having breakfast before the opening keynote and I heard an Alteryx employee talking about using Alteryx to blend data from a file she receives from another department.  I had a hunch and asked her how she gets those files.  I stifled a literal “LOL” moment when she said she receives them via email.  I told her I had some code that might help her get to a fully automated solution.  Later in the conference I attended a session on using the Alteryx API and SDK.  I paid particularly close attention to the SDK portion because at the time I had no idea creating custom tools was even an option.  I know this is going to sound lame and cliché, but at that moment I was truly inspired to jump into code and build my own custom input tool to pull attachments out of Outlook emails.  I know, I know, too corny.  Moving on.  As a fantastic conference was coming to a close I just happened to spot one of the engineers, @JPKa,  who hosted the session on using the API and SDK at the closing reception.  I told him my idea and he pointed me in the right direction to get started.  Two weeks of personal time later I had  a solid version 1  of the tool and I shared it with my friends at Alteryx.

 

 

 

Working with the SDK

 

Outlook Input Tool ConfigurationOutlook Input Tool ConfigurationEverything I needed to get started was already installed with Alteryx Designer. I used a combination of reading through and interpreting the sample and the getting started documentation. Honestly, my first impression was confusion.  The sample project involves using an XML file containing Alteryx tool XML config information as an input to set the tool XML config information.  When it came to interpreting the code to understand how it was linking the xml config information to the input data I was often confused as to whether the xml components in question were part of the Alteryx configuration or the input tool that was providing the configuration.  Simply put, it was setting configuration from configuration. If anyone from Alteryx Product Management is reading this,  a better sample might be to pull dummy customer data from a .csv file and send it to an output stream.

 

My biggest obstacle was interpreting the sample code - figuring out how to separate the xml config as input from the xml config that was reading the input.  It makes perfect sense now, but when I was trying to understand the custom tool architecture and input file interpretation simultaneously for the first time I had some trouble.

 

 

Once I had a working tool I went back and forth with the folks at Alteryx and there were some limitations there with getting them the install and sharing attachments. I discovered I could wrap the install into a yxzp file and include instructions and a help file. JP was very helpful with identifying some of the nuances of working with the SDK and offered some suggestions on improving the tool. I needed to incorporate the "UpdateOnly" parameter because the C# code was executing every time a new tool was added to the canvas. I didn't know that the Alteryx Engine had a special feature where it runs the workflow (with no actual records other than metadata) every time a new tool is added.  

 

He also suggested I add some date filtering capability to the tool, allowing people to avoid downloading their entire inbox if they select “Inbox”.  For this I added a configuration element for the user to input a query string and included a link to the documentation to build the query.

 

 

 

Using the Outlook Input Tool

 

You may be tempted to point this sucker to your Inbox right out of the gate, and I don't know what your Inbox looks like, but if it looks anything like some of our testers, I'd advise against that. Play with your drafts folder or deleted items first.

 

You may find copying the search syntax from Outlook into the Query String helpful:

 

copy the query string from outlookcopy the query string from outlook

 

As for me, this is how I've been using the tool. In this example I filter out a specific Excel file and send it through a Dynamic Input tool to see the results. I'll leave it up to you to discover new and interesting ways to consume the files that you no longer have to manually fish out of your inboxes.

 

OutlookInputToolSample.png

 

 

giphy-downsized (2).gifBut wait, there’s more!  Act now and as a bonus you get the added message stream allowing you to perform any number of analytics on your email messages.  And if that isn’t enough, join the two streams together on Message ID for maximum analytical potential!!

 

 

 

 

What's included in the package

 

You'll find an Alteryx *.yxzp file attached to this post. When you open it in Alteryx Designer, you will see installation instructions:

 

Outlook Tools Install instructions.png

 

and a help file

 

Outlook Tools Install help file.png

 

The workflow is a single RunCommand tool that will install the Outlook Input tool. You can find my source code on Git Hub.

 

I hope you find the Outlook Input tool useful. This has been a fun and exciting project and I can't wait to see what creative ways the community finds to use this tool. Use the comments below to let me know your experience working with the tool.

 

UPDATE 8/1/2017:

A new version is available on the gallery: https://gallery.alteryx.com/#!app/Outlook-Tools-Install/597b35c2f499c716ec34a782.

 

Changes:

  • Implemented paging to prevent timeouts through the Exchange Web Services when working with large inboxes and complex filters.  Note: you still may need to adjust throttling settings on your server: https://msdn.microsoft.com/en-us/library/office/jj945066(v=exchg.150).aspx.
  • Updated the installer to remove previously installed versions of the tools so it's not cluttering up your programs list.
Rick Paugh

Rick Paugh is a Sr. SFDC Business Analyst at Roche Diagnostics, responsible for delivering business solutions to enhance operation and performance and improve employee satisfaction, primarily through process automation and optimization. Rick specializes in solving business problems by leveraging technologies and processes readily available in the existing business environment to deliver maximum results with minimum cost. Solutions delivered have included a broad range of technologies including, but not limited to, C# web development (and javascript), various SQL services (SSAS, SSIS, SSRS), BI development (Tableau), Salesforce.com administration and development, and of course Alteryx (workflows, gallery apps, API, SDK).

Rick Paugh is a Sr. SFDC Business Analyst at Roche Diagnostics, responsible for delivering business solutions to enhance operation and performance and improve employee satisfaction, primarily through process automation and optimization. Rick specializes in solving business problems by leveraging technologies and processes readily available in the existing business environment to deliver maximum results with minimum cost. Solutions delivered have included a broad range of technologies including, but not limited to, C# web development (and javascript), various SQL services (SSAS, SSIS, SSRS), BI development (Tableau), Salesforce.com administration and development, and of course Alteryx (workflows, gallery apps, API, SDK).

Comments
Alteryx
Alteryx

@rpaugh  

 

Great work on this tool.  It does exactly as advertised, and a bunch of us here at Alteryx are already installing and using it for our own Outlook accounts.  

Can't wait to see what else you have to contribute in the future!  

 

 

Alteryx
Alteryx

@rpaugh

 

This is brilliant Rick, I just tested it, works great. awesome job!

Magnetar
Magnetar

Great job @rpaugh - nice to see other people using the SDK

 

Alteryx
Alteryx

 

 

If anyone from Alteryx Product Management is reading this,  a better sample might be to pull dummy customer data from a .csv file and send it to an output stream.

I read it! We're currently hard at work creating good (hopefully) samples and documentation for the upcoming Python and HTML SDKs, after which we certainly want to return our attention to our older C++ and C# SDKs to clean up their samples and documentation. So thank you for the feedback. And great post!

 

 

 

WBender
Atom

First off, game changer on the business side, I was elated to hear from our Alteryx SE that I should review this string when I was telling him about some issues we were trying to overcome.

 

I've been able to successfully load and validate a sample flow to our corporate Gallery, but when I try to execute/run I get an error with locating the 'Save Attachments to:' location.

 

Normally I would just |Options-Advanced Options-Workflow Dependencies-All UNC|, but the identified location for Attachments is not present as a dependency.  Nor can I update the browse to option output to a UNC.

 

Has anyone overcome this already or any ideas how to update the repository location while using the Gallery? 

rpaugh
Asteroid

@WBender, are you entering a non-UNC path and wanting it to automatically convert it to a UNC path?  Or are you entering a UNC path that isn't working on the gallery?

WBender
Atom

@rpaugh

 

Entering a non-UNC path and wanting a UNC path (reasoning below)

 

The 'Save Attachments to:' Field has a Browse only option, from there I am able to navigate to the Network Share Drive folder where I can select where I would like to store the files.  The location that appears after selecting is not a UNC path as it leverages an alias (i.e. S:\ rather than \\asb-123-xyz\).  I do not see an option to free form the UNC path or modify in any way.  This leaves me with a non-UNC path dependency in my Gallery, which then fails as expected.  The 'Save Attachments to:' location is also not seen as a Workflow dependency in Designer when I look to update to UNC in Advanced Options.

 

WBender
Atom

Entering a non-UNC path and wanting a UNC path (reasoning below)

The 'Save Attachments to:' Field has a Browse only option, from there I am able to navigate to the Network Share Drive folder where I can select where I would like to store the files. The location that appears after selecting is not a UNC path as it leverages an alias (i.e. S:\ rather than \\asb-123-xyz\). I do not see an option to free form the UNC path or modify in any way. This leaves me with a non-UNC path dependency in my Gallery, which then fails as expected. The 'Save Attachments to:' location is also not seen as a Workflow dependency in Designer when I look to update to UNC in Advanced Options.

rpaugh
Asteroid

@WBender, I updated the tool allowing you to manually edit the attachments path.  Tara will be posting the updated .yxzp file soon.  When you run the installer it needs to update existing DLLs so you'll be prompted to either close Alteryx or reboot later.  Obviously you can't close Alteryx because you're running a workflow, so elect to restart later.  Note: you will not actually have to restart your computer in order to use the new version of the tool.  Let me know if this meets your needs.

Alteryx
Alteryx

@rpaugh  

 

Pretty impressive turnaround time Rick!  

 

You may need to get some contributors on the Github repository for this tool as people find more edge cases requiring additional functionality.  

 

Thanks,

 

JP

rpaugh
Asteroid

Definitely a good idea, @JPKa.  For now, here's a link to the updated .yxzp file @WBenderhttps://drive.google.com/open?id=0B11L6XXkpz_YSU9heVN6NUwzblE

 

Let me know if you have any issues with it.

Community Content Manager
Community Content Manager

I updated the post with your latest fix @rpaugh

WBender
Atom

@rpaugh Update confirmed and tested.  I was able to successfully free form enter a UNC code for the location, post to gallery and run in gallery.

 

rpaugh
Asteroid

Thanks for the confirmation, @WBender!

JacobMoncla
Alteryx Certified Partner

This is awesome!

rpaugh
Asteroid

A new version is available on the gallery: https://gallery.alteryx.com/#!app/Outlook-Tools-Install/597b35c2f499c716ec34a782.

 

Changes:

  • Implemented paging to prevent timeouts through the Exchange Web Services when working with large inboxes and complex filters.  Note: you still may need to adjust throttling settings on your server: https://msdn.microsoft.com/en-us/library/office/jj945066(v=exchg.150).aspx.
  • Updated the installer to remove previously installed versions of the tools so it's not cluttering up your programs list.
Alteryx
Alteryx

This is so incredibly good, excellent work @rpaugh!  The original version didn't save attachments correctly, but upon updating to the latest and re-installing, I am now able to automate this.

 

Already have forwarded to several folks. 

rpaugh
Asteroid

Thanks for the update, @ChadM.  I'm glad the latest version worked for you.  

Atabarezz
Alteryx Partner

Awesome job, thanks.

Pretty useful for "mailbox analytics..."

TorbenL
Atom

Excellent  work @rpaugh - this tool will definitely come in handy!!!

 

I am trying to narrow down the search to a specific subfolder in my inbox... but without luck so far. I can't find any subfolders in the 'Folder to search' drop down, so I have tried to narrow the search in via the query string: folderpathSmiley SadInbox/Subfolder level 1/Subfolder level 2)

 

Am I handling it the wrong way? I hope I'm not missing something obvious :-)

 

Best,

Torben

rpaugh
Asteroid

@TorbenL,

 

The "Folders to search" drop down pulls the list of pre-defined folder names from Exchange, meaning it can not pull a list of custom sub folders.  And the way the tool is coded to allow for searching of all Outlook-based items (calendar, notes, etc.) it's difficult to simply tell it to traverse sub folders in its search.  However, I do have a couple of ideas and believe I can accomplish this.  Let me look into this and I'll keep you apprised of my progress.

TorbenL
Atom

Thank you for your quick reply @rpaugh. I really appreciate your effort :-)

 

Best,

Torben

Labels