community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More

Email subscription tool based on table updates

Hi,

 

We have a data quality table in a database like this:

 

DatasetStatus
Dataset A

Green

Dataset BGreen
Dataset CGreen
Dataset DRed
Dataset EAmber
Dataset F

Green

Dataset GGreen
Dataset HGreen

 

The status column is always red for all datasets at the beginning of the day, the table is updated throughout the day. Typically moving to amber and then green, although occasionally they go back from green to amber. 

 

I want to create a workflow that allows users to subscribe to changes within this table. They should be able to subscribe to a subset of datasets and decide if they want to get an email for all changes or just when everything is green. 

 

For example John might be interested in datasets A,B,C,D. He may only want to receive an email confirming once all four datasets have been signed off. He also wants to know if any of the datasets revert back to red or amber.

Peter might also be interested in A,B,C,D but he wants to get an email every time any of the statuses change. This is captured in the two tables below:

 

PersonDataset
JohnA
JohnB
JohnC
JohnD
PeterA
PeterB
PeterC
PeterD

 

PersonPreferenceEmail
JohnAll Greenjohn@company.com
PeterAny changepeter@company.com

 

Is there anyway to create an Alteryx job which I can run on the server (every 15 minutes or so), which will then generate emails to the relevant people . E.g. John will receive one email per day assuming that  A,B,C,D data sets go green whilst Peter will receive multiple emails showing whenever a status changes.

 

Both will receive an email if A,B,C,D were green and go back to red/amber.

 

I'm fairly good with Alteryx, but really not sure where to start with this one.

 

Your help is greatly appreciated. 

 

Asteroid

Hi Daniel,

 

This shouldnt be to bad to create. The way i would do this is to create a table which duplicates your dataset/status. Name this Last update. I would then do a join on that table for Dataset/status, any non matched joins will be those that have updated. From this join it up with your subcriber list, create a formula to concatenate the Data set/status. So you should have a table which looks like 

DatasetStatusConcEmail
Dataset A

Green

Dataset A - Green

email@gmail.com

Dataset BGreenDataset B - Greenemail1@gmail.com
Dataset CGreenDataset C - Greenemail2@gmail.com

 

From here use a table tool and group by email and you can email it from there. You then want to refresh your duplicate table at the end with the current information.

 

Hope this helps

 

cheers

 

chris

 

Alteryx Certified Partner

Hey @danieljcroberts

 

You need the ability to track change of the Dataset_Status table. There's many ways to keep track of such change, but it doesnt seem like you're doing this currently. Easiest way might be adding a Previous_Status column to the Dataset_Status table. This column should only be updated by your Alteryx workflow (every 15 min) with the status of that run. You can then send emails based on the change between Status and Previous_Status.

 

Alternatively, you can use a secondary table (a .yxdb will work) that stores this Previous_Status column. 

 

I hope this gives you a place to start, if not let me know :-)

 

Marco 

 

Labels