Alerts are a great way to keep people informed on important things happening within the SAP Business One system. Alerts are powered by saved SQL queries in the Query Manager. Good uses for alerts are sending daily reminder reports, data validation, or conditionals.
If you'd like to learn more how these alerts work from a user perspective check out our previous post on "Using Alerts in Sap Business One."
Preparing an Alert Query
We are going to be setting up an alert to notify a group of users when a sales quotation has been added to the system over $5,000. We will be using the query below so copy the query into a query window and save it. Name the query "Quote Entered Over 5000".
SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[GrosProfit], T1.[SlpName], (T0.[GrosProfit]/ T0.[DocTotal]) AS 'GP%' FROM OQUT T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode WHERE T0.[DocDate] = CONVERT(CHAR(8), GetDate(), 112) AND T0.[GrosProfit] <> 0 AND T0.[DocTotal] > 5000
The key to writing a good alert query is that the results must be relevant. We want to know within a reasonable amount of time when we have a big deal come in so we can make adjustments quickly to win the sale. In this case, the alert query is written to show quotes created today only. If you are good with Microsoft SQL you can write some very creative alerts.
Setting Up Alerts
To begin, navigate on the SAP Business One main menu to Administration -> Alerts Management. You will be presented with the screen for setting up an alert. The setup window will open up in "Find" mode so you must click the "Add" icon on the main icon toolbar at the top of the screen.
Enter a name for your alert in the name box. This will serve mainly as the title of the alert that the user will see in their "Messages/Alert Overview" window. Name the alert "Quote Entered Over $5,000". Click on the button labeled "Open Saved Query". Here is where you will select your saved query to use in the alert. The "Query Manager" will open up and double click on "Quote Entered Over 5000" query. Now you have added a query to the alert.
Select the priority drop down box and choose your priority. High will accompany each alert with a red exclamation mark in the alerts window. For this example, we are going to choose high because this alert is important to us. Then we set the frequency of which the alert will run the query to see if there are any results. I'm going to set this query to run every 10 minutes. You can set the frequency as small as 1 minute, but excessive alerts can cause performance issues.
Now we are going to select the users that will receive the alerts if the query finds records to report. Check the boxes next to the user and select which contact methods you want to use. The "Internal" option is best for people that use SAP B1 all day as this will notify the user inside the client. Use email, fax, or SMS for road users.
The "save history" check box will produce a new alert message every time the query is run with records found. Without the box checked it will overwrite the previous alert in the messages/alert window. I don't usually use this option. I find that overwriting the messages is better for organization purposes.
Select the "Active" check box to make sure the alert is running and click the "Add" button. Your new alert is now completely setup. The next time a quote is entered into the system over $5,000 the selected users will receive their alert in the "Messages/Alert Window".
Stock SAP Business One Alerts
Open up the "Alerts Management" screen again by navigating to Administration -> Alerts Management. This screen works like many other SAP B1 forms by being able to cycle through the alerts you have setup. Click the right green arrow on the icon toolbar to see the available stock alerts that come pre-installed. These work a little differently as they have custom SAP programming that allow you to do some interesting things. Check them out and see how you might be able to employ them in your business. The available stock alerts are:
- MRP Recommendation Due
- Deviation from Budget
- Deviation from Discount (in %)
- Deviation from % of Gross Profit
- Deviation from Commitment Limit
- Deviation from Credit Limit
- Minimum Stock Deviation
Alert Query SQL Ideas
Here are a few ideas to get your started on thinking about how you can utilize SAP Business One alerts in your business.
- Orphan Purchase Order/Request Report - if a sales order is canceled then this alert will notify the purchasing department so any PO's or PO Requests can be canceled as well.
- Past Due Document Report - this alert can trigger if an order has not been delivered in a certain amount of time.
- Marketing Documents with Missing Tax Codes - if you are planning on automatically assigning tax codes this alert will help you track down the problems.
- Orders Tagged With Dropship Shipping Type In Standard Warehouse - If the order is designated as shipping via dropship and the item rows are coming out of your main warehouse this can cause major inventory issues.
Feel free to ask questions or comment with your ideas for SAP Business One alerts!