Anyone ever notice a common practice with Microsoft: how they make something that is ALMOST useful, but stop right at the place where they are juuust at the cusp of something being great and just make it OK?  It's something that we notice here at my work...

Anyways, for just about the past 2 years I've been trying on-and-off to find a way to customize and automatically trigger alerts.  I've been told that is a bad idea, but unfortunately we need it.  Last month I attended SharePoint Experts SharePoint programming class, and since I've been tinkering a bit w/ Web Part coding, etc.  (Aside: It was a good class...you probably got more out of it if a) you are experienced with SharePoint and b) you go having an idea of what you want SharePoint to do, rather than an expectation of learning what SharePoint CAN do.  Imagination is helpful when it comes to programming web parts).

Since the class I've gotten a requirement to find a way to automatically create an alert for every creator of an item in a specific task list.  Evidently the people in my company are too stupid to actually check on the requests they make, and instead pepper others with status update questions.  One could make a point that this is really a training issue rather than a coding issue, but if every item does need an alert going to the creator, it would save time to programmatically do that rather than have everyone create an alert every time they make an item.

I've been playing with the SPAlert class, and let me tell you, it's a pain in the butt.  First of all, I'd like a function to find all the alerts on a given list or item.  Here's where that first rant plays in: Microsoft evidently thought that the only time someone might want to query the alerts is on a per user basis only.  Uh huh...  So I had to build a procedure to get the creator of the list item, which isn't as easy as you'd think.  I would have thought that since every item has a creator, there might be, oh I don't know, a Creator property on the SPItem class.  Nope.  You need to do this:

SPSite oSite = new SPSite("Your site URL");
SPList oList = oSite.OpenWeb().Lists["Your list name"];
SPListItem oItem = oList.GetItemById("Item ID");
string userValue = oItem["Created By"].ToString();
int index = userValue.Index Of(';');
int id = Int32.Parse(userValue.Substring(0, index));
SPUser itemCreator = oSite.OpenWeb().SiteUsers.GetByID(id);

Yeah...that's great.  This was actually in the SDK, if you looked under SPListItem and scrolled waaaaaaaaaaay to the bottom.  So MS knew you might want to do this, but didn't put the functionality in to do it for you.  Oh yeah, and you can't inherit from SPListItem to put this in yourself, b/c there's no default constructor.  Sweet!

So you get the user, and then you have to loop through all the alerts that the user has.  You need to compare if the AlertType, the List, and (if you are doing an Item alert like me) the Item ID are all the same on each alert.  If someone has a better way to do this, let me know.  Once you determine that the user doesn't already have an alert set up (b/c the user might also have a list level alert already set up), you can add an alert.  I haven't gotten that far yet.

I'm considering just writing a stored procedure to do that check for me.  I've looked into the DB a bit, and there are various values depending on the alert type/frequency you are searching.  The immediate alerts are in the ImmedSubscriptions Table, and the daily/weekly alerts are in the SchedSubscriptions table.  Here's what the values are:


When I finish my code (including SPs) I'll post it here if someones interested.  What I have yet to overcome is how exactly do I trigger the alert creation.  I'm considering writing a web control I can plug into create item page, but I don't know if I can both call the Submit form routine and the create alert stuff I'm making.  I'm also considering creating a service to check on new list items and adding the alerts on the back end.  I'm honestly not sure.  If someone has ideas on that, I'd love to hear them.

Anyways, it's good to be back after 2 years.  Hopefully I'll post w/ more frequency.

Tips I learned:
if you ever end up with a really long list [mine had 10000+ items] don't get an item by doing SPList[ItemID].  I ended up getting memory leaks and things died.  Use SPList.GetItemById(ItemID).  You'll be a happier person for it