Microsoft Dynamics CRM 2016 & CRM Online Training and Introduction Agenda – Recommendations and Suggestions

I have recently been asked by one of my clients to take them through a walkthrough / high level training / demo of Microsoft Dynamics CRM Online (also applies to Dynamics CRM 2016). The audience is a collection of Client senior leadership, programme leadership and various business stakeholders.

I decided to limit handouts to a single A4 Agenda which lists in a chronological order Dynamics CRM modules, features and functionalities to be covered along with Dynamics CRM navigation, layout and capabilities such as search, filtering, sorting, menu bars, etc.

I then thought I’ll share this suggested agenda list with my blog followers to help everyone out if they are looking for a recommended agenda for Dynamics CRM Introduction or basic Microsoft CRM training. It is certainly not an exhaustive list of all agenda items that you could cover and the scope & flow may also need to change from one client to the other. This agenda, however, seemed sensible for my client needs and requirements.

The agenda is available as an a downloadable PDF file at the bottom of the post below or you can simply copy and paste it from the following list.

Here comes the agenda – Hope this helps:

Concepts:

  • Entity (DB table)
  • Record (DB row)
  • Relationships between entities
  • Modules: Sales, Marketing & Service
  • New: Field Services & Project Services

Walkthrough:

  • Navigation – Application Top Menu bar
    • Recent items
    • Quick Create
    • Global Search
    • Advanced Find / Search
  • Personal Options (top application menu)
  • Views: System & Personal -(e.g. active contacts)
    • Open record vs Link/Relationship
    • Select record or multiple records
    • View top menu (full record vs quick create)
    • View Selector (System vs Personal)
    • Set Default Personal View
    • No. of records displayed in view: 25 -250
    • Search within View (Quick find)*
    • Search wildcard (within current view only)
    • Search by related records (parent account)
    • View charts (right side of view)
    • Filtering & Custom Filters*
    • Sorting & double sort columns
    • View Jump to Letter (Bottom navigation)
    • Bulk Edit multiple records
    • View Data Refresh
  • Forms (Multiple) & Records– (e.g.: contact form)
    • Form Selector
    • Record top menu bar
    • Navigate to next/Prev record & pop out
    • Ownership (User vs Team vs Org)
    • Assign to me/another
    • Social Pane: Posts, Notes & Activities
    • Map (OOB – e.g. contact)
    • Lookup, Option set & Composite (name)
    • Click through to email & dial out (Skype)
    • Saving: shortcut, icon or navigation
  • Advanced Find queries & views
  • Charts & Dashboards
    • System vs Personal
    • E.g. chart: Account by City
  • Business Process Flows
  • Activities: Type of entity
    • System vs custom
    • Filtering
  • Queues:
    • Routing
    • Work On
    • Assignment
  • Reporting:
    • Advanced Find
    • Report Wizard
    • SSRS
  • Help Content/ Learning Path
  • Connections (Networking / non-hierarchical)
    • Connection roles
  • Case Management
  • Other Dynamics CRM features & capabilities:
    • Administration
    • Security, Roles, Users, Teams & Bus
    • Processes: WFs, BPF, dialog & actions
    • Templates
    • Product Catalog / catalogue
    • Data Management & Duplicate detection
    • Service management
    • Business Management
    • Solutions: managed vs unmanaged, patching
  • Other Topics / Future sessions:
    • Outlook Client (x2) & filtering
    • Mobility: Mobile & Tablet apps
    • Document Management (OOB)
    • Social Engagement
    • MS Dynamics Marketing
    • Entitlements & SLAs
    • Activity Feeds
    • Interactive Service Hub
    • ADX Portals
    • Feedback/Customer Voice
    • Sales Gamification
    • Marketplace & AppSource
    • Dynamics 365 vs Office 365

Recap on Search:

  • Global Search
  • Advanced Find
  • Search within View (Quick Find)
  • Filtering within Views

Recap on Reports:

  • Advanced Find
  • Report Wizard
  • Dashboards & Charts
  • SSRS

#MSDynCRM #CRM2013 Using FetchXML versus SQL script for writing Microsoft Dynamics CRM SSRS custom reports

This might sound a known fact but you will be surprised that quite a few people don’t know why and when do they need to use FetchXML for writing an SQL Server Reporting Services report as opposed to writing common SQL Server script.

Here is how Microsoft Defines FetchXML:

FetchXML is a proprietary query language that is used in Microsoft Dynamics CRM 2013 and Microsoft Dynamics CRM Online. It’s based on a schema that describes the capabilities of the language. The FetchXML language supports similar query capabilities as query expressions. In addition it is used as a serialized form of query, used to save a query as a user-owned saved view in the userquery entity and as an organization-owned saved view in the savedquery entity.

A FetchXML query can be executed by using the IOrganizationService.RetrieveMultiple method. You can convert a FetchXML query to a query expression with the FetchXmlToQueryExpressionRequest message.
For information about how to use LINQPad to work with FetchXML, see this blog post: Getting FetchXML from LINQPad: http://blogs.msdn.com/b/devkeydet/archive/2012/04/22/getting-fetchxml-from-linqpad.aspx

Now to answer the main question of this post: When should I use FetchXML for reports?

As a fact, you cannot write SSRS reports with direct SQL script on Microsoft Dynamics CRM Online. Hence, if you want to write an SSRS report for Dynamics CRM Online, you have to write your query in FetchXML.

Within your SSRS report, you will need to create a dataset that returns the data you require in your report and then everything else will be the same as per any other SSRS report.

Please note however that you do require Microsoft Dynamics CRM Report Authoring Extension installed to be able to write FetchXML queries and to connect to your Dynamics CRM Online organisation.

Additionally, at the time of this post, you can only write Custom SSRS reports for Dynamics CRM using Visual Studio 2010 with SQL Server Data Tools SSDT OR Business Intelligence Development Studio (SQL 2008 and VS 2008 with BI)

You cannot write SSRS reports in Visual Studio 2012, 2013 at this point of time. SSDT for Visual 2013 is not currently available due to some issues.

Finally, here are some sample FetchXML queries:

Total number of Leads (Count - Aggregate):
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='lead'> 
 <attribute name='fullname' alias='lead_count' aggregate='count'/> 
 </entity> 
</fetch>
Total number of Opportunities (Count - Aggregate):
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='name' alias='opportunity_count' aggregate='count'/> 
 </entity> 
</fetch>
Number of leads by Category:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='lead'> 
 <attribute name='fullname' alias='lead_count' aggregate='count'/> 
 <filter type="and">
 <condition attribute="crm_leadtype" operator="eq" value="100000001" />
 </filter>
 </entity>
</fetch>
<!-- where crm_leadtype is an option set (category) and its value is integer-->
The "Not Equal" filter condition example:
<condition attribute="crm_leadtype" operator="neq" value="100000001" />
FetchXML Average aggregate:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' /> 
 </entity> 
</fetch>
FetchXML Maximum Aggregate:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='estimatedvalue' alias='estimatedvalue_max' aggregate='max' /> 
 </entity> 
</fetch>
FetchXML Sum Aggregate:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum' /> 
 </entity> 
</fetch

More information and samples can be found here:
http://msdn.microsoft.com/en-us/library/gg309565(v=crm.6).aspx

Hope this helps!

Microsoft Dynamics CRM Deployment Automation and Scheduling Automated Deployments using MSBuild scripts, batch files and other tools

Automated Deployment during the development of Dynamics CRM projects implementation is a time, effort and cost saving approach. A combination of MSBuild (MS Build) scripts, batch files and custom built console applications can provide a fully automated Dynamics CRM deployment and development life cycle.

Additionally, Microsoft Developer Toolkit which is released as a free tool part of the Dynamics CRM SDK provides an excellent deployment functionality (amongst many other useful features) but this is the subject of another post about automated deployment using Dynamics CRM SDK developer toolkit : http://www.mohamedibrahim.net/blog/2012/08/09/dynamics-crm-automated-deployment-using-dynamics-crm-developer-toolkit-from-the-sdk/ .

Examples of the most common deployment automation requirements in Dynamics CRM development projects:

1) Automated Deployment by getting the latest code from Visual Studio (via TFS – Team Foundaion Server) and deploying into a Dynamics CRM organisation on a server (local VM or a development/test server).

2) Export of Dynamics CRM default solution or a specific CRM solution package and importing the solution into another environment or Dynamics CRM server (or organisation). This is also known as Promotion: Promoting a CRM solution from one environemnt (such as Development environemtn) to another environment (such as test).

3) Export a CRM Solution from a CRM Server and then check in this exported solution file (the solution zip file) into TFS for backup or reusage.

These are the most commmon automated deployments used in projects but there could be many more based on projects requirements.

To achieve each on of these deployment automation setup there are a number of approaches and methods. I’ll try now to give some samples on how to do these automated deployments mentioned before:

You will need to create a batch file that calls an MSBuild file which in turn can do the operaton. The reason for using a batch file is that you can then setup a windows scheduled task to run the batch file at your chosen time for running the build. You can also add an auto numbering to the batch file if you want to increment the build number after every build. In this case, you will need to store the last build number in a separate text file.

The minimum that you will need in the batch file:

:: Check if Visual Studio command line is available. If not, then go to missing and end of file

if not exist “C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\bin\vcvars32.bat” goto missing

:: Otherwise, call the Visual Studion Command shell.

call “C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\bin\vcvars32.bat”

:: Call the msbuild file

msbuild myprojectbuild.msbuild /property:BuildVersion=%ver% /property:BuildDate=%builddate%>log\log_%builddate%_%ver%.txt

goto :eof

:missing

echo Visual Studio command prompt is missing

pause

goto :eof

 

Your MSBuild file that will get the latest code Visual Studio code from TFS can be too long but I’ll try to give you below the main commands that you will need to perform:

 <?xml version=”1.0″ encoding=”utf-8″ ?>

<!– All rights reserved for Mohamed Ibrahim Mostafa. www.mohamedibrahim.net Please leave this copyright notice if you use this file. –>

<Project xmlns=”http://schemas.microsoft.com/developer/msbuild/2003” InitialTargets=”Release”>

<PropertyGroup>

<!– Build version – alternatively these vallues can be passed from the batch file–>

<BuildVersion>1.1.0.0</BuildVersion>

<BuildDate>20120101</BuildDate>

<TFSUser>mytfsusername</TFSUser>

<TFSPassword>mytfspassword</TFSPassword>

<BuildConfiguration>Release</BuildConfiguration>

</PropertyGroup>

<Target Name=”Get”>

<!– get latest source code from tfs –>

<Exec Command=”tf get Clinet.Name/MySolutionName /force /recursive /noprompt /login:$(TFSUser),$(TFSPassword)” />

</Target>

<!– Build my visual studio CRM solution output binaries

<Target Name=”Build” DependsOnTargets=”Clean”>

<Message Text=”Building CRM solution code…” />

<!– Build all CRM-related outputs including plugins, scheduled tasks and user interface components –>

<MSBuild Projects=”Clinet.Name\MySolutionName\MyVisualStudio.sln” Properties=”Configuration=$(BuildConfiguration)”/>

</Target>

<!– now you have the visual studio solution built. use a console application to deploy it into CRM server as follows –>

<!– Use plugin registeration developer console application (available in SDK) to deploy plugins and workflows –>

<!– or alternatively use the deploy command of the microsoft dynamics crm developer toolkit (from the SDK) to deploy the whole solution into your CRM server organisation–>

<!– using the deploy command of the developer toolkit will be covered in a separate post –>

<!– The following command uses a custom console application that I have built to export Dynamics CRM solutions –>

<Target Name=”Package” DependsOnTargets=”Build”>

<Exec Command=”MyCustomDeploymentTool.exe export solution=Default outputdir=myfolder\CRM discoveryurl=$(DiscoveryUrl) orgname=$(OrganizationName)” />

<!– parameteres discovery url and organisation name are parameters in the property group –>

</Target>

<!–Now check in TFS and label your code using MSBuild commands –>

<Target Name=”Versioning” DependsOnTargets=”Package”>

<Exec Command=”tf add  /login:$(TFSUser),$(TFSPassword)” />

<Exec Command=”tf checkin MyProjectName\CRM_SolutionName_$(BuildDate)_$(BuildVersion).zip /login:$(TFSUser),$(TFSPassword)” />

<!– label TFS with the build number –>

<Exec Command=”tf label $(BuildVersion) MyProjectName/* /recursive /comment:&quot;Automatically labelled by Mohamed Mostafa build script.&quot; /login:$(TFSUser),$(TFSPassword)” />

</Target>

<Target Name=”Release” DependsOnTargets=”Versioning”>

<Message Text=”Completed..”/>    </Target>

</Project>

<!– All rights reserved for Mohamed Ibrahim Mostafa: www.mohamedibrahim.net –>

The console application that you can use to import or export a CRM solution will be the subject of another post. I think this post is too long already and I have spent good few hours writing it.

I hope this helps. If you have any questions, ideas or feedback please write them in a comment below.

If you want the various MSBuild files, batch files, etc then please comment below and I’ll email them to you (I tend to collect requests and send the files to a whole group in bulk).

Embedding ISV web applications in Microsoft Dynamics CRM 4.0 through SiteMap (not as an IFrame) via a left navigation link. Application & link Access restriction through Site Map

There are (at least) two standard ways of implementing an external ISV web application (website, html, .NET, etc..) inside your dynamics CRM application.

The first way is by creating an IFrame inside the form of an entity. This is quite straight forward and can be achieved from the entity customisation section in CRM settings.

The second option is to create a link in the left navigation of your Microsoft Dynamics CRM 4.0 solution. This link can then point to your ISV webpage (aspx page for example) and you will have your web page implemented and embedded inside CRM. If you also do the application css (stylesheet file)  to be same colours and similar styles as CRM colours and styles, your application will be presented nicely inside CRM as if it is part of CRM. The permission and restriction of access to this web application and its left navigation link is also controlled to show/hide the applications to users.

This post is discussing the second option.

Embedding a webpage or web application in CRM and having  CRM’s left navigation linked to it can be done by editing the sitemap customisation file (xml). This left nav link can be shown or hidden to users based on their security roles and this is also controlled in the site map file. If the link is hidden, then obviously the web application will not be accessible and cannot be retrieved by unauthorised users.

The following is an example of a sitemap file that displays two links to two applications on the top of the left navigation menu of CRM 4.0. The privilage for accessing each link is also set in the following example:

<SiteMap>
    <SiteMap>
      <Area Id="Workplace" ResourceId="Area_Workplace" ShowGroups="true"
Icon="/_imgs/workplace_24x24.gif" DescriptionResourceId="Workplace_Description">
        <Group Id="MyWork" ResourceId="Group_MyWork" 
DescriptionResourceId="My_Work_Description">
          <SubArea Id="MyWebApplication" Icon="/_imgs/ico_18_servicecal.gif" 
Url="../../ISV/MyWebApplication/WebPage.aspx" AvailableOffline="false">
            <Titles>
              <Title LCID="1033" Title="My Web Application ONE" />
            </Titles>
            <Descriptions>
              <Description LCID="1033" Description="My Web Application ONE" />
            </Descriptions>
<!-- this is where you control displaying the 1st link-->
<!-- if the user has read and write access to entity Kbarticle (for ex)-->
<!-- then they should be able to view this link. Otherwise, its hidden-->
            <Privilege Entity="kbarticle" Privilege="Read,Write" /> 
          </SubArea><!--another example follows: -->
          <SubArea Id="WebPage2" Icon="/_imgs/ico_18_servicecal.gif"  
Url="../../ISV/WebApplication2/index.htm" AvailableOffline="false">
            <Titles>
              <Title LCID="1033" Title="Web Page 2" />
            </Titles>
            <Descriptions> 
<!-- if the user has create access to entity Kbarticle (for ex)-->
<!-- then they should be able to view this link. Otherwise, its hidden-->
              <Description LCID="1033" Description="Web Page 2" />
            </Descriptions>
            <Privilege Entity="kbarticle" Privilege="Create" />
          </SubArea>
<!-- The following is the standard activity left link for CRM-->
          <SubArea Id="nav_activities" Entity="activitypointer"  
DescriptionResourceId="Activities_SubArea_Description"  
Url="/Workplace/home_activities.aspx" />

This is how you can get site map to control your left navigation buttons and get them to link to ISV applications. Please mind that you might need to change the relative location of your application in the URL parameter.

Hope this helps.

 

Hide tab in any entity form in Microsoft Dynamics CRM 4 using Javascript in onLoad event of the form.

In many cases, you might want to remove one of the tabs of an entity form in Microsoft Dynamics CRM 4. This can be specifically helpful in two cases:

1- If you want to hide and show tabs depending on a picklist value selection such as contact type: Hide Work details if contact is of type internal contact for example

2- If you want to hide the Administration and Notes tab from an entity form while these tabs are locked and cannot be removed from the entity form.

To hide a tab in an entity form, you can use the following Javascript sample script:

//---------------------------Hide a tab by passing tab name to the function
HideTab('Administration');
//replace "Administration" with your tab name
function HideTab (tabText)
{
 var tab = FindTab(tabText);
 if (tab)
 {
  tab.style.display = "none";
 }
}
function FindTab(tabText)
{
 var tabBar = document.getElementById("crmTabBar");
 if (tabBar)
 {
  var tabs = tabBar.childNodes;  

  for (var i = 0, len = tabs.length; i < len; i++)
  {
   var currentTab = tabs[i];
   if (currentTab.innerText === tabText)
   {
    return currentTab;
   }
  }
 }
}
//-----------------end of script--------

Maxjerin has added a new code suggestion. Maxjerin’s code hides a tab based on the onChange event of a field on the form. If the primary contact “name” field is maxjerin, the tab is displayed. Otherwise the tab is hidden.

I have updated the post to include Maxjerin’s code – Thanks Maxjerin:

Here is the code that I used to HIDE/UNHIDE a tab when I wanted to show it for only one particular user and hide for others. The code includes some alert messages just for debugging purpose which you might find useful to understand the code. You may remove them once done.

This section goes into the onChange section of the Primary Contact lookup field

if (crmForm.all.primarycontactid.DataValue == null )
{
var strTabStatus = TabStatus(‘Profile’);
if ( strTabStatus == ”)
{
alert(“Primary Contact none, hiding the tab”);
HideTab(‘Profile’);
}
}
else if (crmForm.all.primarycontactid.DataValue[0].name == ‘maxjerin’)
{
alert(“Primary Contact p f, unhiding the tab”);
UnHideTab(‘Profile’);
//replace “Administration” with your tab name
}
else if (crmForm.all.primarycontactid.DataValue[0].name != ‘maxjerin’)
{
var strTabStatus = TabStatus(‘Profile’);
if (strTabStatus == ” )
{
alert(“Primary Contact other than maxjerin, Hiding the tab”);
HideTab(‘Profile’);
}
}
function HideTab (tabText)
{
var tab = FindTab(tabText);
if (tab)
{
tab.style.display = “none”;
}
}
function UnHideTab (tabText)
{
var tab = FindTab(tabText);
if (tab)
{
tab.style.display = “”;
}
}
function FindTab(tabText)
{
var tabBar = document.getElementById(“crmTabBar”);
if (tabBar)
{
var tabs = tabBar.childNodes;
for (var i = 0, len = tabs.length; i < len; i++)
{
var currentTab = tabs[i];
if (currentTab.innerText === tabText)
{
return currentTab;
}
}
}
}
function TabStatus(tabText)
{
var tabBar = document.getElementById(“crmTabBar”);
if (tabBar)
{
var tabs = tabBar.childNodes;
for (var i = 0, len = tabs.length; i < len; i++)
{
var currentTab = tabs[i];
if (currentTab.innerText === tabText)
{
if (currentTab.style.display == ‘none’)
{
alert(“tab already hidden, TAB NAME: ” + currentTab.innerText );
return “none”;
}
else
{
alert(“tab is visible, TAB NAME: ” + currentTab.innerText );
return “”;
}
}
}
}
}
//-----------------------------------------------------------------------
Please comment below if you have any feedback or anything to add.
Thanks

Return and display picklist value (display value) in custom sql report reporting services report in Microsoft Dynamics CRM 4 entity attribute and StringMap picklist values table

Normally, you should be able to get the picklist display value and picklist item id in the filtered view of any entity. In some cases, you may need to work with StringMap table directly to get the picklist values for a specific field of a given entity. This could be the case if you are not using the filtered view for some reason or if you want to write a report based on the table or the non-filtered view, the following post is providng the query (queries) needed to do so.

To get the display value of any picklist, you need to use the StringMap table. If you are writing a custom ssrs (SQL Server Reporting Services) report for Dynamics CRM 4, you usually need to return the picklist display value not the picklist item id.

For example: in the opportunity entity, you have a picklist attribute “Rating” (attribute name opportunityratingcode). If you are writing a custom SQL report to add to CRM and you want to display the values of the attribute, you will find that the display value of the picklist is not stored in the filtered view of Opportunity (FilteredOpportunity). To get the display value of any picklist, you need to use the StringMap table.

There many ways to get the information from the StringMap table, I will mention two ways here:

1) using Inner Join (simple - one picklist only)
This way, you just simply join the two tables: your entity filteredview and the StringMap table
/* the query uses out of the box attributes, so should run successfully on all CRM organisations*/
SELECT opp.name, opp.estimatedvalue_base, opp.closeprobability, StringMap.Value
FROM Opportunity as Opp INNER JOIN
  StringMap ON Opp.opportunityratingcode = StringMap.AttributeValue
WHERE (StringMap.ObjectTypeCode = 3) AND (StringMap.AttributeName = N'opportunityratingcode')
AND (opp.accountid = 'input your accoung GUID here'

This query will return list of all Opportunities for the specified account display the picklist display value rather than the id.

2) using Temp table (my prefered way for multiple picklists)

This query is longer and uses/declares temp tables but it is the simplest way in case you have several picklists and you want to return the display value of multiple picklists. You just create several temp tables just like @TempSM, where each temp table returns the list of display values for each picklist and then join them together.

1 Picklist query:
/* the query uses out of the box attributes, so should run successfully on all CRM organisations*/
Declare @TempOpp table(OpportunityID uniqueidentifier, OpportunityName nVarchar(max),
Estimatedvalue float, Probability float, Rating nVarchar(max))
Insert into @TempOpp
select opp.opportunityid,opp.name ,
opp.estimatedvalue_base,
opp.closeprobability,
opp.OpportunityRatingCode
From Opportunity opp
where opp.accountid = '97F97A5A-2622-DF11-A1C3-005056BA6B8A'
Declare @TempSM table(AttributeValue nVarchar(max), Value nVarchar(max))
Insert into @TempSM
select AttributeValue, Value
from StringMap
WHERE (StringMap.ObjectTypeCode = 3) AND
(StringMap.AttributeName = N'opportunityratingcode' )
select t1.OpportunityName, t1.Estimatedvalue, t1.Probability, t2.Value as RatingDisplayValue
from @TempOpp as t1 left outer JOIN @TempSM as t2 ON
(t1.Rating = t2.AttributeValue)

This query will return list of all Opportunities for the specified account display the picklist display value rather than the id.

2 Picklists query:
------------------
/* the query uses out of the box attributes, so should run successfully on all CRM organisations*/
Declare @TempOpp1 table(OpportunityID uniqueidentifier, OpportunityName nVarchar(max),
Estimatedvalue float, Probability float, Rating nVarchar(max), Priority nVarchar(max))
Insert into @TempOpp1
select opp.opportunityid,opp.name ,
opp.estimatedvalue_base,
opp.closeprobability,
opp.OpportunityRatingCode,
opp.PriorityCode
From Opportunity opp
where opp.accountid = '97F97A5A-2622-DF11-A1C3-005056BA6B8A'
/* Now we have @TempOpp1 with list of all opportunities for the specified account but with ID for picklist value not display names*/
/*Then we declare a temp table for Rating picklist*/
Declare @TempSM table(AttributeValue nVarchar(max), Value nVarchar(max))
Insert into @TempSM
select AttributeValue, Value
from StringMap
WHERE (StringMap.ObjectTypeCode = 3) AND
(StringMap.AttributeName = N'opportunityratingcode' )
Declare @TempOpp2 table(OpportunityID uniqueidentifier, OpportunityName nVarchar(max),
Estimatedvalue float, Probability float, Rating nVarchar(max), Priority nVarchar(max))
Insert into @TempOpp2
select t1.OpportunityID, t1.OpportunityName, t1.Estimatedvalue, t1.Probability,
t2.Value as RatingDisplayValue,t1.Priority as Priority
from @TempOpp1 as t1 left outer JOIN @TempSM as t2 ON
(t1.Rating = t2.AttributeValue)
/*tempopp2 now has opportunities with the rating display value*/
/*Now creating a temp table for Priority picklist*/
Declare @TempSM1 table(AttributeValue nVarchar(max), Value nVarchar(max))
Insert into @TempSM1
select AttributeValue, Value
from StringMap
WHERE (StringMap.ObjectTypeCode = 3) AND
(StringMap.AttributeName = N'PriorityCode' )
select t1.OpportunityID, t1.OpportunityName, t1.Estimatedvalue, t1.Probability,
t1.Rating as RatingDisplayValue,t2.Value as Priority
from @TempOpp2 as t1 left outer JOIN @TempSM1 as t2 ON
(t1.Rating = t2.AttributeValue)

--------------------------------------------

and so on. You create temp table for each picklist, join it with opportunity temp table and then carry on.

Note: This is one way of doing this. There are obviously many other shorter ways of doing this. I personally prefer temp tables a lot because although longer, they are simpler and easier to use. They also bypass any issues with null values in the right table of the join.

Not related Note: Always use filtered views when writing custom SQL reports on SSRS for CRM as they comply with CRM security.

Hide, Display, Resize and Rename left navigation links, CRM fields and attributes using Javascript for Microsoft Dyanmics CRM 4.

Scripts for Resizing, Hiding and renaming left navigation links, fields and attributes in Microsoft Dynamics CRM 4 using Javascript. This post is just a collection of some Javascript code for various common scripts that is frequently used in CRM form events, CRM entity events, etc. These script blocks can be used in OnLoad, OnSave events for CRM forms and OnChange events for CRM fields.

//------- Resize CRM form in the onload event
window.resizeTo(screen.availWidth * 0.85, screen.availHeight * 0.85);
//-------Rename left menu link / Left Navigation. Example: Contacts
var navItem = document.getElementById(’navContacts’);
navItem.innerHTML = navItem.innerHTML.replace(’>Contacts’,'>Students’);
//------Hide left menu link / Navigation. Example: Workflow
var navLeftItem = document.getElementById(’navAsyncOperations’);
navLeftItem.style.display = ‘none’;
//-----Hide Left navigation menu item link of a CRM form based on
// a value in a picklist on the same form.
//Get contacts left navigation menu item element.
var navLeftItem = document.getElementById(’navContacts’);
//if picklist (customer type code) value is equal to 1 (1st item in picklist) then
//hide left menu item (contacts link is used as an example), otherwise, show it.
if(crmForm.all.customertypecode.DataValue == 1)
{
 navLeftItem.style.display = ‘none’;
}
else
{
 navLeftItem.style.display = ‘inline’;
}
 //-----Hide a CRM field on a form. example: the "name" field.
crmForm.all.name_c.style.display = 'inline';
crmForm.all.name_d.style.display = 'inline';
//-----Hide a CRM field on a form (example: the "name" field)
//based on the selected value in a picklist
if(crmForm.all.customertypecode.DataValue == 1)
{
         crmForm.all.name_c.style.display = 'inline';
         crmForm.all.name_d.style.display = 'inline';
}
else
{
         crmForm.all.name_c.style.display = 'none';
         crmForm.all.name_d.style.display = 'none';
}  ////Add this code to the OnLoad event of the form and
////to the on change event of the picklist attribute (field) as well
//---------Function to hide CRM field in a form. 
//Function accept fieldname as a parameter and
//boolean parameter to remove (hide) entire row on form
HideField('name'); //replace "name" with your field/attribute name
 
function HideField( fieldName, removeEntireRow )
{
 // Always hide the elements, even if we will be hiding the whole row.
 // This allows us to show another field in this row later without this
 // one showing up.
 var elem = crmForm.all[fieldName + "_c"];
 if( elem != null ) elem.style.display = "none";
 elem = crmForm.all[fieldName + "_d"];
 if( elem != null ) elem.style.display = "none";     
 
 if (removeEntireRow)
 {
  var elem = crmForm.all[fieldName + "_d"];
  if( elem != null ) elem.parentElement.style.display = "none";
 }
}

 This post was written quickly from memory and some code blocks here and there, so you might find some minor spelling mistakes. Please comment below if you find any issues with the script blocks or if you want me to extend this post to include additional script.

Scribe: Moving DTS from one location to another and changing source file location in Scribe

A challenging issue with Scribe is how to move a DTS (job) and source files (in case source is a text batch file) from one location to another or from one server to another without loosing mappings, corruptingdata links, lookup criteria, user variables, loosing fields names and basically corrupting the whole DTS.

Again, I have looked online and could barely see any information or help in this regard (probably my mistake as I didn’t search properly!). I found out that the key for moving Scribe project files including source across to a new location or a new server, the key is always the QETXT.ini file. This file is vital for pointing the DTS to which source file it should be looking at. QETXT.ini files have all the field names, so mapping S1 to the name that you have chosen for the first source field. It also has the source file name, the ODBC name and the table name. From there you can do almost everything.

When you move the files across, you will obviously need to re-point to the new source location, but by editing QETXT.ini, you will be able to put back all the source (S1 to field name) mappings, point to the new source file name, ODBC name and everything else.

This has proved very efficient and have worked now with my whole deployment.

One more important piece of advice, always try to get a dedicated folder for every source file. So if you have more than one DTS jobs, make sure that the source for each DTS job is in a separate dedicated folder. This will ensure you have separate QETXT.ini file for each one of them, hence, you can easily update the information inside it. It will still work with one large QETXT.ini file but it’s always better to separate the sources and their associated QETXT.ini files. You can always manually split this file into source specific files and put each source in a separate folder later on (which is what I have done after “inventing” this best practice of having separate source folders!).

Scribe Console: Renaming Source Text files before running a job and after processing and regularly changing source file names.

This post applies for Scribe Insight version 6.5.1. It may well apply to all Scribe version 6.5.x

Have you ever looked in Scribe Insight for a way to rename a source file before processing it. Scribe console creates collaborations where integration processes can be configured so that they wait for a file to be added to a specific location and then run a specified job. Once the file is added the job will run and process the file. Also, Scribe DTS jobs can only be setup to process a source file that has its name always fixed and unchanged. So a DTS can be setup to process a source file named: customersdata.txt. It will never run if another source file is added to the location the Scribe console is looking at. In this case, if you get a source file with the date (and time) stamp in its name will need to rename it so the the DTS can detect it and run. So if the source file comes with time and date stamp that varies every day (for example: customers_1453_21092009.txt), you will need to rename “customers_1453_21092009.txt” to “customersdata.txt” only to get the DTS to work.

After some research, I found out that you can only do this using pre and post processing commands step of the process. Every integration process has step 2 in it called pre and post processing commands. In this step, you can specify a pre-processing and post-processing commands or scripts. This feature lets you specify a pre and post processing file that can do this renaming for you. Accepted pre and post files are: *.vbs, *.js, *.vbe, *.bat, *.cmd, *.exe, *.com

You will then create a pre-processing script that finds all files that start with “customers*” in our example and rename it to customersdata.txt which is the source file name the job is expecting. Post processing can be to rename the file to something else so that you keep a record of processed files.

In step 3 of the Integration process, Scribe also gives you two options (in the form two check boxes) that I find very useful. You can either select to delete the source file after processing or you can select to rename it. So the source file will be processed and renamed to something like customersdata.L1.txt. Unfortunately Scribe doesn’t give you the choice to choose the new name of the file. Hence, you will need to write a post processing script again for renaming it afterwards if you are after a specific file name.

I’m not sure if there is any other way of renaming source files (also called event files by Scribe) before processing them or specifying a new name for them after processing.

Hiding Left Navigation Menu Items in an Entity Form using Javascript in Microsoft CRM 4

This is just a follow on from my previous post (http://www.mohamedibrahim.net/blog/2009/07/30/renaming-left-menu-items-leftnav-entity-microsoft-dynamics-crm-4/). In this post, I explained how to rename left menu items in an entity form using javascript.

These links are created when a new N:1 relationship is created between two entities. The primary entity in this relationship will get a new left navigation link everytime a new N:1 relationship is created.

We have discussed in the previous post that the code to rename left menu navigation items (links to other N:1 entities) is:

var navItem = document.getElementById(’leftNavMenuItemID’);
navItem.innerHTML = navItem.innerHTML.replace(’>CurrentLinkValue’,’>NewLinkValue’);

So for example:

var navLeftItem = document.getElementById(’navContactsMenuItem’); // this will look for the element contact in the left menu of an account form
navLeftItem .innerHTML = navItem.innerHTML.replace(’>Contacts’,’>Employees’); //This will rename Contacts to Employees in the Account form

Now, to Hide specific links in the left menu, you can use the following script:

// Hide left menu navigation links on an entity form
var navLeftItem = document.getElementById(’navContactsMenuItem’);
navLeftItem .style.display = ‘none’;

This script will be in the OnLoad event of the form of the entity that you want to hide links created on the left handside of this entity form.