Saratoga Technologies Microsoft CRM Blog

One request we had recently was to show all the notes for a company, in order for a Sales rep to get a snapshot of anything currently going on with the client. This was to include all notes for the Account, Opportunities, tasks, appointments, and leads. This was to be viewable on the main company screen.

In order to accomplish this, we needed to first create SQL views of all the different notes we were going to need with the information we wanted. Instead of doing one massive view of all of the notes, we did a view for each type. For instance, for Opportunities, we created a view called Opportunity_Notes.  It should be noted that since we are creating views, this is an unsupported way of getting the needed data.  Here is the SQL script for the Opportunity_notes that we used:

SELECT dbo.AnnotationBase.ObjectId AS OpportunityId, dbo.OpportunityBase.AccountId, dbo.AccountBase.AccountNumber,
dbo.OpportunityBase.Name, dbo.AnnotationBase.NoteText, dbo.AnnotationBase.Subject, dbo.AnnotationBase.CreatedOn,
dbo.SystemUserBase.FullName AS CreatedBy
FROM dbo.OpportunityBase INNER JOIN
dbo.AnnotationBase ON dbo.AnnotationBase.ObjectId = dbo.OpportunityBase.OpportunityId INNER JOIN
dbo.AccountBase ON dbo.AccountBase.AccountId = dbo.OpportunityBase.AccountId INNER JOIN
dbo.SystemUserBase ON dbo.SystemUserBase.SystemUserId = dbo.AnnotationBase.CreatedBy
WHERE (dbo.AnnotationBase.IsDocument = ‘FALSE’) AND (dbo.AnnotationBase.NoteText IS NOT NULL) AND (dbo.OpportunityBase.AccountId IS NOT NULL)
ORDER BY dbo.AnnotationBase.CreatedOn

From there, we created a .asp page to create the screen to combine all of the notes. Pretty simple, just made a connection to the database, and used DataLists with SqlDataSource to create our record views.

One problem we did have was that our various headers (ie: OpportunityNotes) would show up, even if there were no Opportunity Notes for that Company. To hide the headers, we added a Selected event to each SqlDataSource. Within that event, set a variable to “block”. Then within each datalist, use a DIV with a Style of “display” set to the variable.

Lastly, within the Account Form, we added an IFrame. Within the Form Onload, we set the IFrame source to the .asp that we published on the CRM server, with the Customer Number as a passed variable in the URL. We use the Customer Number from GP, which is unique, and used that as the passed variable instead of the GUID.

Here’s a screenshot of the final product.  Happy coding!