Introduction
Communication among your team is important. Getting the right information to your team at the right time is crucial. At WayScript, we want to create tools that get your team working faster, with the right information in hand.
WayScript is a rapid development platform built for developers. We're in Beta and would love to get your feedback.
In this tutorial, we'll take a look at how we can create a Slack notification bot that will be supplied with data from your SQL database. In this specific example, we'll look at how we can create an inventory notification system that alerts our team whenever the quantity of a product falls below demand. The idea is that when can get a notification to our communication channel to let us know to place a new purchase order to cover our demand.
Prerequisites
Some content you might find helpful:
Working with database
Working with Slack
Getting Started Working with Databases
In this example, the database that is being used is one hosted by Heroku. To connect your database, you just need to have the host url, username, password, and table name. These are provided on the settings of your database on most services. Once we add this database to WayScript, we're able to write SQL statements that will be performed on the database.
Running a query on your database
In this example, we're using the SQL module to pull all records from our database. We input the SQL code into the module the same way we would write this locally.
This will return all the records from our table. These outputs are then able to be used anywhere throughout our workflow. We can pass these as list type objects to other modules, do calculations on them, etc. We get these list type objects as variables once we click to import them on our generated table.
Calculating Sumations of Products
Since we're returned a list from the SQL query, we'll need a way to sum up our product movement. That way we can get a summation of each to compare to our conditional in the following step. Python works well for this and that's what I used in this tutorial, but feel free to use any of the other logic provided. Here's the Python code I typed up:
import pandas as pd
serial_number = variables['Serial_Number']
products = variables['Products']
movement = variables['Movement']
d = {"Products": products, "Movement": movement}
df = pd.DataFrame(d)
print(df)
a_product_df = df[df['Products'] == "Product A"]
product_a_sum = a_product_df['Movement'].astype(float).sum()
b_product_df = df[df['Products'] == "Product B"]
product_b_sum = b_product_df['Movement'].astype(float).sum()
c_product_df = df[df['Products'] == "Product C"]
product_c_sum = c_product_df['Movement'].astype(float).sum()
variables['product_a_sum'] = product_a_sum
variables['product_b_sum'] = product_b_sum
variables['product_c_sum'] = product_c_sum
Returning a Slack notification
In this example, since we're wanting a notification to occur only when a product amount is below a certain threshold, we can integrate programming logic into our workflow. This calls for an if statement. This if statement can be used to trigger certain actions in your workflow whenever a criteria is met. Since I'm working with three products in this example, I've created multiple conditions in the if statement to cause an activation of the following step (the Slack notification) to occur.
When this condition is true, we want to return a Slack notification to our channel. We can do this with a workflow that has the Slack module integrated under where the if statement would be triggered. Here is an example of this build:
Activation of our script
Finally, we need to activate our script. Of course, we don't want to be constantly querying our database as it would cause speed decreases for others reading and writing to it. Not to mention that should a stock level fall below a certain amount, we don't want to be constantly notified. In this example, let's use a time trigger that will query our database every hour:
Finishing Up
Here's the overview of our script:
Create a trigger to activate your script at the desired time interval
Query your database for the desired information
Process this data using the list types provided in your preferred programming logic
Check conditionals
Return Slack notifications if conditional met
If you would like to clone this script for yourself, you can find it here: SQL Inventory - Slack Bot
Additionally, if you would like to view a video tutorial of this build, you can find that below.
Top comments (0)