You’re offline. This is a read only version of the page.
445
Does anyone know where I can find good documentation on how to creat custom business alerts?
Thank you,
Donnette
*This post is locked for comments
My friend needs some help regarding how one can to create custom business alerts? He has a pending assignment and he wanted to do it as soon as possible. There is not much information about his quarry on internet as he also wants some reliable source for assistance. It would be a great help if someone can help him out regarding sorting this issue out. For this purpose I tried to find some relevant information and came to know that he can also refer to the Business alerts in GP assists documentation. He is just supposed to Open the Define Business Alert notification system and tap the help icon on it or go for the option of clicking F1.
The data driven subscription should check for count of rows returned by the "business alert". If the business alert results set yields zero rows there will be a blank for the subsricption list. Here is an example code I have for my data-driven subscription where dbo.vCOA_SUSPENSE is the actual report.
SELECT distinct 'recipient1@microsoft.com;recipient2@microsoft.com' ToParm,
'recipient3@microsoft.com' CCParm
FROM two.[dbo].[VCOA_suspense]
Here vcoa_Suspense is a view that I built that looks for missing GL codes based on naturals used in an interface.
Configured in this fashion, this subscription runs the view and checks for rows and only sends the report if it yields any.
Best Regards
Hello,
I would use SRS then. Once I build a report with the custom SQL I want, I can quickly add a data driven subscription to mimic this alert functionality.
If you don't then, other contributors here have chimed in on this topic and would recommend adding a trigger to accomplish the same.
I have attended Jon Lowther's class at the GPUG summit, loved the class.
Best of luck
Hi Ven
thanks for the reply
I did try that before posting. I think count (SOP10200_T2.ITEMNMBR) > 1 gives you the orders that have more than one line. Where as I need to find orders where the item number duplicates anywhere within the order, hence the group by.
Further to Jon Lowther's post -
Did you try adding the count function in the Business alert formula?
I had the below and it worked for me
drop the count onto the business Alert formula from the operator group by clicking on it and
pick the SOP10200 table and ITEMNMBR COLUmn make sure the table name and column name both are enclosed in the count parentheses like count (SOP10200_T2.ITEMNMBR) > 1
Click next and click on Message and report radio button from the Send To and add a list of recipients (full email addresses). Message text is optional and do a quick test mail. and next to include the columns you want to see in the report alert (mine had SOP10200_T2.ITEMNMBR, SOP10200_T2.ITEMDESC AND SOP10200_T2.SOPNUMBE) click next and finish up the scheduling etc. Make sure you leave the Enable Business Alert checked.
Hope this helps.
resurrecting an old post hoping for assistance.
I need a business alert that will check for the same item number more that once on a sales order as the third party shipping company software will not accept that.
I have the sql
select sop10200.SOPNUMBE, sop10200.ITEMNMBR, count(sop10200.ITEMNMBR)
from sop10200, SOP10100
where sop10200.SOPNUMBE = SOP10100.SOPNUMBE
and sop10200. SOPTYPE = SOP10100. SOPTYPE
and SOP10100.SOPTYPE = 2
and SOP10100.VOIDSTTS=0 -- Assume that VOIDSTTS=0 means Normal
group by sop10200.SOPNUMBE, sop10200.ITEMNMBR
having count(sop10200.ITEMNMBR)>1
business alerts does not seem to have the group by however.
can someone translate this into T-SQL, or suggest a different way to get business alerts to identify sales orders where item number repeats / is on the order more than once?
thanks!
Thank you JOHN!
At present I am still on GP10 and my Test Upgrade Server to GP2010 is being rebuilt (I test multiple times before I upgrade production). But I do not think that Business Alerts between the two have changed that overly much. In addition, my company does not use Inventory so I do not have any real data to test with but try this and see if it helps to get you headed in the right direction.
First, as it has been a while for me, I clicked on the help button and searched for "alerts" and got a ton of hits on how to setup, create, and maintain Business Alerts to refresh my memory.
Then I started the Business Alerts wizard and selected create a new alert.
Selected my company database, gave it an alert id and description.
On the next screen, I choose Inventory for the series, and Item Master for the table.
On the screen after that I selected my Item Master Table, and the Item Type field and clicked the Add Column button. (And as I have no inventory in our system this is where I start to get vague.)
Then you will need to select an operator most likely "=" and then enter and add whatever constant you need for a specific Item Type, then select the operator AND select the Item Cost field and an operator, I am guessing actually two operators NOT and BETWEEN then enter the constant <some dollar amount for the lowest price> another operator BETWEEN and then the final constant <some dollar amount for the highest price>. Given that I used the Item Type of 21 and 21.00 dollars for the low amount and 25.00 dollars for the high amount I ended up the this in the BUSINESS ALERT FORMULA box.
IV00101_T1.ITEMTYPE = 21 AND IV00101_T1.CURRCOST NOT BETWEEN 21.00 AND 25.00
After that that I clicked the next button and filled out all the EMAIL and SCHEDULE screens and that should be that.
PLEASE keep in mind that my company does not use the Inventory part of GP so I do not have a way to really test this. Give it a try and let me know how it goes.
till later
GP2010 and SQL2008
Our supply chain manager wants several alerts on the inventory items, item resource planning, and bill of materials.
For example: SCM wants to know anytime an item is set-up, with a certain item type, that does not have a standard costs.
It's easy to design a report with this data, but he wants to know when it happens and he doens't want to be required to run a report or query to see if something is missing.
Donnette
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (