automate email notification when slice data is updated

[jerpawe1987]

I have created an inventory management app.  I want to receive automated emails when the restock level threshold is met.  Currently the app uses a slice with the condition [Current Stock] <= [Restock Level] to display what item need restock. 

How can I get an email when the sliced data detail is updated?

[WillowMobileSys]

I assume you want to send the notification when ANY Inventory row meets the restock threshold.  If so, then don't think of this as a Slice data problem.  It is an Inventory data problem.

Additionally, the Inventory rows will initially hit the threshold when there is an activity that adjusts the count below the level.  So you just need to create a Bot on the Inventory Table to send a notification when the adjust count goes below the level.  

Create a Bot on the Inventory Table and in the Event Condition use this expression:

AND([_THISROW_BEFORE].[In Stock] <> [_THISROW_AFTER].[In Stock],
    [_THISROW_AFTER].[In Stock] <= [Reorder Threshold]
)

make sure to adjust the column names to your specific columns.

NOTE:  This will send an email on every decrement to the [In Stock] value thereafter.  If you wish to send the email ONLY when the re-order point has been reached then, you can modify the expression like this:

AND([_THISROW_BEFORE].[In Stock] <> [_THISROW_AFTER].[In Stock],
    [_THISROW_BEFORE].[In Stock] > [Reorder Threshold],
    [_THISROW_AFTER].[In Stock] <= [Reorder Threshold]
)

After setting up the Event to trigger the Bot, just add a step to send the email.  It is easy to do, so I will leave it to you but if you have issues just post back in the community. 

[jerpawe1987]

Okay not sure where I am going wrong, but I am not getting the emails when I make any changes in the app.  If I go to test the bot, and Execute the Process and Task, both say successful and I get the email.  I even tried removing the condition and just get an email anytime a change is made on my Inventory Sheet but no email is sent when interacting with the app.  Any thoughts?  

[WillowMobileSys]

---

@jerpawe1987 wrote:

anytime a change is made on my Inventory Sheet but no email is sent when interacting with the app.  Any thoughts?  

---

Please show these things:

1. What change is being made and where that should be triggering the Bot?
2. What is the the [Reorder Threshold] for that Product?
3. What is the actual implementation of the Bot's Event Condition to confirm syntax?

[jerpawe1987]

I think I know the problem, the way it is setup the changes are actually happening to a virtual column (Current Stock) and then when I add or remove stock entries are added on different sheets.  If I make actually changes to the physical columns then I get an email notification.  Is there any way to trigger an email based on a change in a virtual column?

[jerpawe1987]

So the virtual column was not the issue, I created bots to add the virtual column data (Current Inventory 2) to a physical column (Current Inventory) and confirmed that the data in the physical columns changes.  So this issue is the changes are being triggered from different tabs in the sheet.  If I want to add stock I go to the Purchases tab and use a Purchase form to add the data, which does update the the Current Stock value on the Inventory Tab, however the email automation doesn't work still. It only works if I directly edit the Inventory Tab not when using a bot to update the Inventory sheet.  Any suggestions on a workaround?

[WillowMobileSys]

Ok, so we need to understand more about your data structure.   I made an assumption above that the details you needed for the Bot Condition were in an Inventory table.  Since the Bot you created was set to run against Product, I am getting a sense now that may not be the case.  Please let us know where, what table(s),  you keep the [In Stock] and [Restock Level] values?

Data Change Bots will trigger based on changes made ONLY to the source table you provide in the Bot Event.  In your example, that source table was "Product".

Since the Bot appears to be designed to send an email when "Restock Level Reached", the question is what value in which table changes to allow you to detect that the email needs to be sent?

For example, let's say you have your Restock Level setting stored in the Product table. but you also have a dedicated Inventory table (which it sounds like you do)  where you are adjusting quantity on hand as Orders (in or out) are made. Then it is the Inventory row change that you want your Bot triggered against.  The Event likely needs to simply compare against the [Product].[Restock Level] to decide if the Bot process runs or not.

By the way,  the above is just an example. I personally would not place a Restock Level setting in the Product table.  I would place it in the Inventory table.

[jerpawe1987]

There are 3 tables - Parent Table is called Product (Which contains a list of the products.  Includes Product Barcode, Product, Category, Image, Initial Stock, Restock Level.  Then 3 virtual columns of Current Inventory with formula of SUM(SELECT(Purchases[Quantity],[Product Barcode] = [_THISROW].[Product Barcode])) - SUM(SELECT(Deployed[Quantity],[Product Barcode] = [_THISROW][Product Barcode])) +[Initial Stock], Related Purchases and Related Deployments.  The last 2 columns use REF_ROWS("Purchases", "Product Barcode") and REF_ROWS("Deployed", "Product Barcode") The data is edited on either the Purchases or Deployments Sheets and then the virtual column (Current Inventory) is updated based on the info from the Purchases or Deployments.  Then I have a data slice called Restock Needed, which just uses a formula of [Current Stock] <= [Restock Level].  I am guessing I would have to get something on the Purchases and Deployments sheet to make a data change event work.  I think as a workaround I am just going to schedule daily or weekly emails on the data slice Restock Needed.  Thanks for the feedback. 

[WillowMobileSys]

I see.  So your Current Inventory is itself a Virtual Column.  It is correct that Virtual Column value changes do not trigger Bots. 

However, you can create Bots that are triggered based on values the VC uses - one for each contributing value that can change.  In your case that would be a Bot for Purchase Quantity add or edit,  Deployed Quantity add or edit and in the event if Initial Stock is changed.  3 Bots in total.  Each Bot would check of the Current Inventory VC value meets the threshold to send the Restock email.

And yes these Bots are kind of messy because you have several Products in each Purchase or Deployed row so would need to check each of those Products on each Bot trigger.

I hope this helps!