Monday 22 December 2008

Display the last Note on an Opportunity Report

Hi Guys

One of my clients recently requested that we design an opportunity report that also provided the last note added, so they could have an easy summary report to present to their board of directors.

After a few hours scratching my head and I must admit a few nearlys it got time to call on the knowledge of others, and with thanks to Joel from Customer Effective, I was able to produce the correct SQL statement and then design the report is Visual Studio...

anyways here is the SQL:

select *
from(select filteredannotation.objecttypecode, filteredannotation.notetext, filteredannotation.objectid, filteredopportunity.accountidname, filteredopportunity.customeridname,filteredannotation.isdocument, filteredannotation.createdon, filteredopportunity.stepname, filteredopportunity.estimatedvalue, filteredopportunity.estimatedclosedate, filteredopportunity.closeprobability, filteredopportunity.statecodename,ROW_NUMBER()OVER(Partition By filteredannotation.objectid Order By filteredannotation.createdon DESC) AS RowNmbrfrom filteredannotation, filteredopportunitywhere filteredannotation.objectid = filteredopportunity.opportunityid) as test
where rownmbr = 1

So once this query was added to a new dataset, the report was designed and showed only the last note added!

So special thanks to Joel and a Merry Christmas to everyone!!!

Monday 1 December 2008

CRM Client 4.0, Vista Business & Hosted CRM

Hi Guys

I have had a very trying day involving the CRM client, Vista Business and a number of firewall applications!

I have seen that a lot of people out there have had some issues with this setup, and there a number of registry hacks etc out there that people have tried.

However upon installing a fresh copy of Vista and Office on a test machine and enabling full blocking and tracking in Mcaffe firewall I found that there are three components of the CRM Client install that require external access, they are:

C:\Program Files\Microsoft Dynamics CRM\Client\ConfigWizard\Microsoft.Crm.Client.Config.exe

C:\Program Files\Microsoft Dynamics CRM\Client\ConfigWizard\Microsoft.Crm.Client.AutoUpdate.exe

C:\Program Files\Microsoft Dynamics CRM\Client\res\web\bin\ Microsoft.Crm.Application.Hoster.exe

I found once I had gone back through my clients personal firewall settings (which there were several) and enabled these three programs full access to the net, the install completed and they were able to use CRM, which meant that I could go home, Happy Days!