Changing Unit of Measure Schedules in Dynamics CRM when integrated with Dynamics GP using the Connector

Environment: Dynamics CRM 2011 / Dynamics GP 2010 / Connector for Microsoft Dynamics

Recently a client changed the base unit of measure from Box to Each for one of their unit of measure schedules. The connector, of course, wouldn’t properly integrate the new mapping because it had already been assigned to product lists. The sequence to get it changed is as follows -

GP is the master for the Unit of Measure schedule, so start there.

• In GP, find any products that are using the Unit of Measure schedule you want to change and switch them to another schedule. Note that you’ll be warned that you will have to recreate the price lists and purchasing options for this product.
• Run the Price Level to Price List map in the Connector. After it runs, verify that your products in CRM are switched to the new Unit Group
• Delete the Unit of Measure schedule in GP
• In CRM, deactivate and then delete the old Unit Group
• In GP, create the new Unit of Measure schedule with the correct Base Unit
• Run the Unit of Measure Schedule to Unit Group map and check the log to verify that the change was brought over successfully.
• In CRM, verify that the new unit group is available.
• In GP, change the products to the correct Unit of Measure schedule
• Recreate the Price Lists and Purchasing options for those products
• Run the Price Level to Price List map in the Connector. After it runs, verify that your products in CRM are switched to the new Unit Groups.

Unit of Measure Schedule Troubleshooting with CRM Connector for GP

When running the CRM Connector for GP, one of the maps that integrates the two systems is a UofM Schedule to Unit Group map that transfers the GP Unit of Measure schedules into CRM.

One of the requirements for the map to run successfully is that the UoM schedules in GP can’t have repeating values in the schedule. Take for example the situation below.

Schedule for Gallon
Unit Name	Quantity	Equivalent
Gallon	1.000	Gallon
Gallon	0.018	Drum
Drum	55	Gallon
Schedule for Drum
Unit Name	Quantity	Equivalent
Drum	1	Drum
Drum	55	Gallon
Gallon	0.018	Drum

A limitation of CRM is that a Unit Group cannot contain the unit name more than once. During the map’s integration, only the first 2 lines of each schedule would be integrated, the connector stops processing with the first duplicate is detected.

Finding those duplicates can be a little tricky though. You’ll be able to see them in the log for the map in the Connector for Dynamics mapping client. However, this isn’t very descriptive. Here’s an example of an error message.

[UofM Schedule to Unit Group] has encountered an error while processing key [GAL]. A Unit named GAL already exists in the specified Unit Group which may or may not have the same Base Unit.  Microsoft Dynamics CRM does not currently permit Units to have the same name when they belong to the same Unit Group.

Once you do find the U of M schedule that’s causing problems, it’s sometimes challenging to find the duplicates. It would also be handy to know if there’s going to be any problem rows before you Activate the map.

I’ve written a script to help you find (and mark) the problem rows before you even start the integration.

Search for Duplicate UofM Schedule Rows

SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)

Return all the rows from IV40202 that are duplicates

SELECT *
FROM IV40202 WHERE (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
IN (
SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)
)

To make the records easier to find in GP, we can use the Long Description field for the row as an indicate.
NOTE: THIS WILL OVERWRITE THE LONG DESCRIPTION FIELD FOR ANY RECORD THAT’S A DUPLICATE!!

Update Long Description to indicate problem rows

UPDATE IV40202
SET UOFMLONGDESC = 'Duplicate'
WHERE (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
IN (
SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)
)

Now, if you open the Unit of Measure Schedule in the GP client and pull up one of the problem schedules, you should be able to expand the rows and see the lines that are causing or will cause problems.

Update:

In the above example tables, you’ll encounter errors when you try to delete the duplicate rows. GP doesn’t want you to have the base unit of measure in the schedule more than once, even though it allows you to enter it. In this case, you’d have to remove the extra row from the SQL table. Beware, modifying data directly in SQL is NOT supported by Microsoft.

CRM: Comparing Time Worked with Time Billed on Resolved Cases

This article applies to CRM 4.0

We’re a technical service company and we manage the work effort needed to resolve issues by using cases and tasks in CRM. At the end of a case, however, the person resolving the case determines how much of the time worked was billable.

On the one hand, we want to be fair and balanced to our clients, but on the other hand we put bread on the table by selling our knowledge and experience by the hour. There’s not an easy way to report on the discrepancies between how much work was done and how much was billed.

Thanks to David Jennaway, I’ve got a handy-dandy T-SQL script that I used to come up with an Excel spreadsheet that pulls the data from a SQL view. I’ve modified his original script somewhat to bring back the Account Name rather than the Incident ID.

select
    i.accountidname,
    i.title,
    ir.actualend,
    max(timespent) as BillableTime,
    isnull(sum(a.actualdurationminutes), 0) as TotalActivityTime

from filteredactivitypointer a
    join filteredincident i on a.regardingobjectid = i.incidentid
    left outer join filteredincidentresolution ir on i.incidentid = ir.incidentid and ir.statecode = 1
    and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1)
where i.statecode = 1

I actually saved this as a view, then called it from an Excel spreadsheet. That lets you pull the data into a pivot table, etc.

You can also then build on this and figure out your top accounts using a script like this -

SELECT
    Accountidname as AccountName,
    COUNT(accountidname) AS Cases,
    SUM(BillableTime) AS TimeBilled,
    SUM(TotalActivityTime) as TimeWorked,
    SUM(TotalActivityTime) - SUM(BillableTime) as Variance
from acc_ResolvedCaseTimeComparison
GROUP BY ACCOUNTIDNAME
ORDER BY TimeBilled DESC

Where myresolvedcaseview is the name that you gave your SQL view. This query brings back each account, the number of resolved cases, the amount of time worked, time billed, and the variance.

Note, you’re running directly against the SQL data, so it depends on specific security rights to the database to see the data.

Troubleshooting CRM Performance

Once in a while, the SQL box hosting CRM at the office begins to bog down. Here are a few scripts and procedures that I use to help troubleshoot what can be causing the slowdowns.

First of all – start with the basics. Does the server have adequate disk space. How badly fragmented are the drives?

NOTE: The built-in Windows Defrag won’t touch files larger than 2 GB so you may have to defrag by moving the large files onto another volume, defragging, then bringing them back.

Find a SQL process with high CPU utilization

Here’s a general SQL script that will show you the process causing high CPU usage. You can then use the SPID to kill the offending process. Insert the name of your organization database into the script as noted.

USE Master
GO

DECLARE @DATABASE_ID INT
SET @DATABASE_ID = DB_ID(‘organizationname_MSCRM');

SELECT ST.TEXT,
SP.*
FROM DBO.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE SP.DBID = @DATABASE_ID
ORDER BY CPU DESC
GO

Once you know which process is stuck, you can kill it using the following command. Insert the correct SPID you gathered from the script above. NOTE: KILLING A RUNNING PROCESS IS NORMALLY A BAD IDEA. Only use this to kill a process that’s hung.

Kill spid

Cleaning up completed workflows

As workflows complete, their records remain in the AsyncOperationsBase table. Over time, this table can grow to enormous size. To resolve the problem, run the following script against your organization_MSCRM database, replacing with your orgname where noted. This is a one-time running script. If you want it to run on a recurring basis, you could set it up as a SQL job.

NOTE: There is sometimes business value in being able to see completed workflows. Make sure you really want them gone before running the script.

IF EXISTS (SELECT name from sys.indexes
                  WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin     
begin tran     
insert into @DeletedAsyncRowsTable(AsyncOperationId)
      Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
      where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)    
       Select @rowCount = 0
      Select @rowCount = count(*) from @DeletedAsyncRowsTable
      select @continue = case when @rowCount <= 0 then 0 else 1 end     
        if (@continue = 1)        begin
            delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
            where W.AsyncOperationId = d.AsyncOperationId            
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
            where B.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where WS.AsyncOperationId = d.AsyncOperationID
            delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
            where A.AsyncOperationId = d.AsyncOperationId            
            delete @DeletedAsyncRowsTable     
end      
commit
end
--Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

The above script was taken from Microsoft KB968520

Finding and removing waiting workflows

Here’s a script to list open workflow operations on the sql server. These are Workflows that are stuck in a waiting state. Too many and the server starts bogging down. I start getting worried at 50,000 and then at 100,000 we usually see the server start to croak.

Run this against your organization_mscrm database.

Select DISTINCT(asyncoperation.Name ), COUNT(asyncoperation.Name ) AS NUM
from asyncoperation
where StateCode = 1
    and RecurrenceStartTime is null
    and DeletionStateCode = 0
Group BY asyncoperation.Name
ORDER BY NUM DESC

An example might be a workflow that no longer exists, but has live versions of itself stuck in a waiting state. To remove these orphaned workflows, use the following steps. You can get the workflow name from the script above. Insert into the marked locations below.

PLEASE NOTE: MS DOES NOT SUPPORT editing data directly in the database. You do this at your own risk, and for the sake of all that’s holy, backup your database first.

delete from workflowwaitsubscriptionbase

where asyncoperationid in

(select asyncoperationid from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1)

delete from workflowlogbase

where asyncoperationid in

(select asyncoperationid from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1)

delete from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1

Async service on multi-core

KB Article 2489162 on Customersource / Partnersource has a good tip on improving CRM Async performance on multi-core servers. Apparently the Async service doesn’t automatically take advantage of multi-core processors. Login to CS/PS and search for article 2489162 to find the article titled How to improve Microsoft CRM Async Performance on multi-core servers

Returning Waiting Workflows in CRM

Our CRM system seems to have slowed over time. One of the reasons is an increase in the number of workflows that are in a waiting state.

I have a SQL script that I ran against the company_MSCRM database to return a list of all the workflow names that were in the wait state, exported the list to Excel and subtotaled it to get a unique count for the number of each workflow. Sort by count and you get a good list of where you need to start optimizing workflows to hit and stop rather than wait for something to happen.

In our case, the number 1 culprit was a workflow that waits for the regarding attribute to be set to an account rather than a case. As a service organization, we try to push all of our service work thru cases. Instead of doing a check and then existing, the workflow ‘waits’ for the regarding to equal an account. This means that every open activity in CRM has a live workflow waiting for the regarding to equal an account.

Here’s the SQL script I used to return the waiting workflow names.

Select asyncoperation.Name
from asyncoperation
where StateCode = 1
    and RecurrenceStartTime is null
    and DeletionStateCode = 0
ORDER BY asyncoperation.Name

This code is based on an article by JonSCRM on MSDN.

Resolving Contract Line Errors

Steps to correct contract line allotments

Dynamics CRM 4 does not provide a way to revise case resolution allotments after the case has been resolved. Reactivating the case and reclosing it will double-dip allotments from the contract lines, leaving them incorrect.
This must be resolved behind the scenes, using SQL scripts.
NOTE: This is not a supported resolution as it directly modifies data rather than relying on the CRM interface. Always make sure and backup a SQL database before modifying data directly.
This article applies specifically to Dynamics CRM v4.0

Get the Case Resolution Activity ID

Use this procedure to get the activity id related to the closed case.
Open the case. Click the History menu item on the left. The top activity should be the case resolution activity. Open it, then click CTRL+N to open the activity in a new browser window.
The Activity ID is the long string between the {} characters

http://server:5555/company/cs/cases/closecase.aspx?id={68C018E8-5D08-E011-B563-001A4B4D64BE}&_CreateFromType=112&_CreateFromId=%7b616C59EA-2DE8-DF11-BA3B-001A4B4D64BE%7d

Get the Contract Line ID

Click through the case to the contract line and use the same procedure as above to get the contract line id.

Double-Check Your Work

Before you start updating all willy-nilly, make sure you have the correct records.
Using SQL Management Studio, connect to the CRM Company database. Use the following scripts to return the records you’re looking to update. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

SELECT *
FROM ContractDetailBase
WHERE ContractDetailId = '<CONTRACT LINE ID>'

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '<RESOLUTION ID>'

Sample:

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '81A0A2C9-DE1F-DF11-9063-001A4B4D64BE'

Verify that you’re looking at the correct record and note the current values. In the ContractDetailBase table, we’re specifically looking at the AllotmentsUsed and AllotmentsRemaining fields. For the IncidentResolutionBase table, we’re changing the TimeSpent field.

Update Your Values

If everything matches up and we’re sure we have the right records, use the scripts below to update the correct fields. Replace the <NEW VALUE> tag with the number you wish to use. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

UPDATE IncidentResolutionBase
SET TimeSpent = '<NEW VALUE>'
Where ActivityId = '<CONTRACT LINE ID>’

UPDATE ContractDetailBase
SET AllotmentsUsed = '<NEW VALUE>', AllotmentsRemaining = '<NEW VALUE>'
WHERE ContractDetailId = '<RESOLUTION ID>'

You’ll first adjust the Resolution Activity to the correct value. Make a note of how much it as changed and then adjust the Allotments Used on the Contract Line accordingly. Then, use the same number and adjust the Allotments Remaining as well.
For example, we closed a case and resolved it for 30 minutes, when actually it should have been resolved for 45 minutes. The Contract Line is for a total of 600 minutes (10 hours) and after we closed this case, 4 hours (240) had already been used up.
Old Values

Case Resolution: TimeSpent = 30
Contract Line: Allotments Used = 240
Contract Line: AllotmentsRemaining = 360

Corrected Values

Case Resolution: TimeSpent = 45
Contract Line: Allotments Used = 255
Contract Line: AllotmentsRemaining = 345

CRM 2011 Partner Notes

Notes from the CRM 2011 Partner Roadshow in Chicago. Disclaimer that this page is likely to contain errors, if for no other reason than my poor fingers can barely keep up. Plus, Reuben likes to jump around a lot which makes it fun to organize :-)

Morning session – Sales Overview

New Feature Overview

• User Interface
• CRM Ribbon
• Visual Page Flow
• Adaptive by Task
• One-click to Customize
• Outlook Experience
• Multi-app client
• Richer Outlook experience
• Preview Panes
• Enhanced performance and sync (200x, no lag on startup)
• Role Tailored Forms and Views
• Sales mgr and salesperson have different input and views of data based on assigned roles
• Multiple forms per entity
• Web and Mobile
• Drag and Drop customization
• Advanced Personalization
• Personal Views
• Pin favorite record or view to home screen
• Most-recently used lists
• Navigation preferences
• Field Level Security
• FULL field level security
• Profiles and rules
• Read / Write / Create
• Interactive Override (manager can provide live override for CSR)
• Productivity
• Custom Activity Types
• Re-occurring appointments
• Dynamic Marketing Lists
• Set a criteria, list auto-fills
• Product Catalog enhancements
• Excel Enhancements
• Real-time export to Excel
• Add or edit data from Excel and reimport
• PowerPivot support
• Edit using Office Web apps
• Real-time Dashboards
• Real-time presentation
• Salesforce limits how frequently you can refresh
• Oracle data is 24 hours old
• Personal dashboards
• Drag-and-drop creation
• Extensible thru web resources (pull in silverlight objects, other web apps)
• Guided Processes
• Process dialogs (call center if then, record pulls and updates)
• .NET4 workflow engine
• Adaptive processes
• Workflow ‘Light’ – wizard based user entry
• URL addressable Guided / Visual processes
• Telemarketing – 4 steps over and over
• URL addressable processes
• Inline Visualization
• Ad-hoc analysis
• Data drill-down
• End-user creation
• .NET 4 chart controls
• Goal Management
• Business goal tracking
• User and team goals
• Monetary and activity analysis ($100k in revenue / 200 phone calls)
• Hierarchical with roll-up
• Conditional Formatting
• Native Outlook presentation
• User-defined rules
• Personal formatting
• Simple configuration
• Enhanced Queues
• Queues against any entity
• Work state management
• User and team queues
• Native workflow support
• Pervasive Auditing
• System wide auditing
• Record or field level
• Pre-configured audit rules
• Comprehensive audit history
• Business Connections
• Business relationships
• Social connections
• Influence tracking
• Process integration
• Contextual SharePoint
• Contextual Documents
• Custom content types
• Check-in/check-out
• Process collaboration (start a workflow in SharePoint and then trigger CRM events)
• Team Management
• Team ownership of records
• Team security roles
• Team roll-up reporting
• Internal / external teams
• Declarative Design
• One click to customize
• Drag and drop UI
• Navigation editor
• Entity header and footer
• Lock a field to always display, even while scrolling
• Cloud Solutions
• Multi-app server
• Managed properties
• Advanced import/ export
• Cloud install & deploy
• Dynamics Marketplace
• Solutions management
• In-app experience
• CfMD certification
• Click and try experience
• Developer Tools
• WCF and .NET 4.0
• Web Resources
• .NET data types
• Open Data Protocol (OData)
• ….Over 500 additional enhancements…

 

Afternoon Technical session with @ReubenK

System Requirements for CRM 2011

• Client: 32 or 64 bit Windows 7 or Vista / IE7.0 + / Office 2003 + / .NET 4.0 / SQL Express 2008
• Server: 64-bit 2008 or R2 only / IIS 7 / .NET 4.0
• SQL: 64-bit SQL Server 2008 or R2 only running on 64-bit Windows Server 2008 or R2 only
• Developer: Visual Studio 2010
• IT: System Center Config Mgr 2007 or R2 / Systems Mgt Server 2003 or R2 / System Center Operations Mgr 2007 or R2
• Email Router: Win7 32 / 64 or on 64-bit Windows Server 2008 or R2
• Exchange: Exchange server 2007, 2010 or online 12 & 14
• Sharepoint: 2007 / 2010 / Online 12 & 14

Reporting Enhancements

• Able to upload to CRM Online if the reports use FetchXML to retrieve data.
• Advanced Find now can show you the FetchXML query to use to build the report
• Reports can be packaged into a solution for portability

Field Level Security

• Secure data at field level
• Security applies across all access mechanisms
• Security profiles evaluated at the application server
• All components of app – Reports, Search, Dupe Detection play by FLS rules
• Only applies to custom entities and custom attributes on existing entities.

Business Data Auditing

• Who changed, what changed, when was it changed
• XML document piggybacking the record tracking the delta
• Available for out of box as well as custom entities

Visualizations and Dashboards

• Wizards for user generated chart development
• Org or user specific charts and share or assign them
• Drill down enabled

Document Libraries

• Link any CRM entity with its own library
• List parts for SP 2010
• Support multiple SharePoint sites
• Connect with SP Online or On-Premise
• Create libraries via workflow or on-demand

@ReubenK: Working towards a simplified licensing model for online services – BPOS, CRM, Exchange. MS has to centralize billing first.

Customization Publication options

• Unmanaged: becomes a native part of the target system and can be modified
• Managed: plugs in similar to Windows Feature enabling with versioning, etc.
• Includes all changes, including form changes / changes to system entities / label changes
• Includes javascript, which also includes global java libraries – all solution aware
• Will be dependency aware so that layers built on top won’t be negatively impacted if a lower layer is removed
• Lot of granularity on what the client can do with the package (no export, no modify, etc.)
• Working towards digital signatures

Dialog Processes

• Used to create a dialog (live walk-thru wizard) for users
• Designed like workflows (uses workflow engine) with new object Pages which are pop-up forms with Prompt and response and branching capabilities
• Can branch on responses received during the dialog, but also on other values within CRM (This customer is Gold level, provide more options to them)
• Dialogs are web referencable, so could be used in a call center for repetitive data entry

Multiple Forms

• Used to simplify user experience, not to protect data
• Create a custom form
• Assign it to a security role
• If the user is a member of that role, they will be able to see the form
• If a user is a member of multiple security roles, they will be able to switch between all forms available to them

Field Level Security

• Used to secure sensitive information
• Applies across all views of the data – forms, reports, advanced finds, etc.

Mobile

• Mobile Express will be extended
• Uses the multi-form capabilities of CRM 2011 to display stripped down forms
• Mobile forms also support the role-tailored forms capabilities
• Long-term strategy is towards building out device-specific experiences where native objects like the iPhone calendar would be integrated
• Windows devices will be 1st priority

Process Center

• Workflow upgrades 3-4x faster (no conversion)
• Workflow runtime 4x faster
• Online currently does NOT support custom workflow assemblies
• Async service upgraded for stability
• Self-managing and cleaning
• Automatically delete completed workflow jobs if successful

Security and Authentication

• Team based security allows user to obtain privilages outside their Business Unit
• Supports external authenticators (like FaceBook or OAuth)
• Delete / Rename Business Units (Even root BU)
• CRM 2011 can be installed using claims based mode
• CRM _could_ be deployed without ANY Active Directory (pending verification of how the user setup would work)
• At the platform level, claims based authentication would be setup on SQL as well (or via a middle authentication tier)

Azure Integration

• App fabric service bus can navigate firewalls, no infrastructure req’d and billed on consumption.Claims aware integration with Service Bus
• Send messages from CRM online to on-premise securely

Extensability Enhancements

• In CRM 2011, native CRM data-types are same as .NET data-types
• LINQ provider available for querying CRM data via API
• Enhanced SDK provides choice for integration (SOAP, REST, abstracted, service bus)
• CRM includes service buse en-queue by adding a message thru plugin-registration tool
• ATOM and JSON support thru OData protocol
• Uniform URL addressibility and conventions

Plug-In Execution Enhancements

• Able to participate in SQL transactions (the entire transaction, not individual records)
• Create traces to return to users

Demo

Demo of CRM web page loaded as a Web Resource in a managed solution that maps band show locations onto a Bing map…in ~20 lines of code

Again, these solutions can be packaged and pushed up to the marketplace as managed or unmanaged code to generate revenue or mindshare.

Why to Cloud?

• Bandwidth now available globally
• Security models now open
• Integration now open for wider development models
• Availability now can be guaranteed
• Multi-tenancy optimizes shared infrastructure
• Design Principles Mainfram –> Client Server –> SaaS
• Roll-out changed from “Big Bang” to Grow with You

 

@ReubenK – “Integration is not a commodity play” It is highly specialized and has not yet become ‘productized’.

• Microsoft has invested $2.3B in cloud infrastructure with geo-replicated customer data – rolling out international data centers
• Public and private cloud flexibility
• 30,000 engineers working on cloud services
• Financially backed uptime guarantees
• SAS 70 and ISO 27001 compliant
• Carbon footprint innovation
• Rapid innovation model
• New capabilities in Azure every 3-6 months

Microsoft working hard for industry specific certifications (FDA)

Azure

• Windows – SQL – App Fabric
• “Windows servers in the cloud”
• Claims aware with multiple identity options
• Bring your own language
• Portal accelerators available for deploying apps into Azure
• Service Bus allows hybrid of online <—> on-premise solutions. Dynamics GP running in-house with a connector to CRM Online
• CRM Adapter for GP to be expanded to AX and NAV. Will also have CRM to CRM capabilities
• Connect CRM with InfoUSA to bring back authentic address information
• Does NOT integrate with JigSaw (asked multiple times today)

BPOS

• Working towards a common billing / licensing model to be able to checkoff the features you want – Exchange, CRM, SharePoint

Report Publishing

• Confirmed that IFD and SSRS Connector still needed for internet deployment of Reports

Hidden Attribute Max Length Prevents Relationship Mapping

We have setup a custom attribute called Toll Free that we use to track 800 numbers.

In trying to setup a relationship map between the Lead and Account entity, I received the well known error:

“This attribute map is invalid. The selected attributes should be of the same type. The length of the target attribute should be equal to or greater than the length of the source attribute and the formats should match. The target attribute should not be used in another mapping.”

In looking, I noticed that we have the attribute set to a max of 25 characters in the lead and the default 100 characters in the account. Oh, well there’s the problem. I edited the attribute in the Account and shortened it. Problem solved, right?

Nope, same error. After checking all the other relationships to make sure it wasn’t used elsewhere, I was about to tear out some hair. Then I stumbled on Ronald Lemmen’s article about the error. His article didn’t solve the problem, but in the comments, someone had the solution.

When an attribute is created, a second database field called MaxLengthCRMAttribute set to 2x the value of the length of the attribute. Here’s the key, when you shrink the length, it doesn’t shrink the max length. However, when you increase the length, it does.

So I edited the Lead attribute that had been created, set it to 100, published then set it back to 25 and published again. After that, I was able to add the relationship because the max length field values now matched.

Modding Dynamics CRM Reports in SSRS

There are many things that can be handled natively within the Dynamics CRM structure. Every once in a while, you’ll run into something that needs a little help from the outside world. Don’t we all need a little help from time to time?

A good example of this would be adding a logo to a Quote or Sales Order. No good way to do that within the CRM framework itself. However, thanks to the open nature of the Dynamics CRM solution, it’s fairly easy to modify the report outside of CRM, make our changes and then import it back in.

For this example, we’re going to modify the Quote form and add our companies logo. You’ll need to have at least a basic understanding of how CRM works, as well as Visual Studio or Visual Studio Business Intelligence Design Studio (BIDS) and SQL Reporting Services (SSRS).

Overview

Reports in Dynamics CRM are actually SSRS reports, wrapped in a CRM framework that passes report parameters, user authentication and other information over to the report to give it the context in which it should run.

Within SSRS, reports generally are not built directly on the Tables / Fields stored in SQL. Microsoft provides objects called Filtered Views that wrap all of the raw data in the context of the security available to the user running the report as well as the context of where the report is being run, including what records to run the report against. These are all parameters passed by the CRM wrapper that surrounds the SSRS report when presented from within Dynamics CRM.

Exporting Reports

We start by locating the report in the Report Center. To find the report center, click Workplace and then reports.

To export the report, click the Edit Report button, then choose Download Report from the Action Menu. Save the report into a project folder where you’ll be storing the files for this project.

This .RDL is what we’ll be editing in BIDS to make our changes.

Modifying the Report – Fixing the Data Source

Open your BIDS app and create a new report server project. On the right side, right-click the Reports folder and choose Add existing reports. Navigate to the report.rdl file that you downloaded and bring it into the project.

Weird CRM thing here…: When CRM exports the RDL file it normalizes it for editing (code phrase for I don’t know what it’s doing, but it changes some things around). One of the thing that happens on a regular basis is that the datasource that’s embedded in the RDL file gets reset to the default CRM company Adventure_Works_Cycle_MSCRM. You’ll need to reset that.

Click the Data tab and then the […] link to the right of the dataset selector.

Open the dataset and hit the […] again to get to the Connection string. Correct the server name (which will likely have defaulted to localhost) and the catalog (enter the name of your company’s CRM database)

 

Making Changes

Go ahead and make your changes to the report. Remember if you have to rebuild the SQL query to use the Filtered Views whenever possible. These provide the prefiltering capability to the user and wrap the report in the security context of the user to prevent unintended data visibility.

When you are done making changes, you now need to upload the report to CRM.

Uploading the report

After your changes have been made, you’ll want to upload the report to Dynamics CRM.

Please, please, please: Save that original report file somewhere you can get back to. I’m not saying you might going to screw something up, I'm saying you WILL screw something up someday and it’s a lot easier to republish that original .RDL file than to try and restore it back in.

Edit the report you want to replace and click the Browse button.

There you go, you’ve modified your first Dynamics CRM SSRS report. Don’t you feel like you’ve accomplished something!

A better solution is to create a new report and upload the .RDL file to that, leaving the original one. This gives you something to roll back to in case … um, SQL screwed something up.

Sub-Reports

Note that some of the report (quotes, sales orders, invoices) present their data through sub-reports. These are framed into the main report. The sub-reports would also need to be downloaded and modified. To find these, change the default filter view for the Reports listview to All reports, including sub-reports.

This article is based on an ExpertsExchange tip by member CRM_INFO. The thread is located here -http://www.experts-exchange.com/Microsoft/Applications/Microsoft_Dynamics/Q_24348850.html

Hidden Mappings in Dynamics CRM

The source for this post is an article by Jamie Miley, located here.

While working on a Dynamics CRM v4.0 project for a client, I need to map a custom attribute from the Quote Product form to the Sales Order Product form. After searching through the mappings, I determined it wasn’t available. Well, actually, it’s available just hidden from common view.

To find it, run the following SQL query against the <orgname>_MSCRM database -

Select * from entitymapbase where targetentityname = 'salesorderdetail'

Find the row that has the appropriate SourceEntityName and copy the GUID for that row. Use the following URL and paste the GUID at the end.

http://<yourservername>:<port>/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=

This brings up the hidden mapping and the best part is that this is a supported workaround!

Updating ReadOnly and Disabled Fields

This is a repost + comments from an article by Ryan Farley. Many thanks to him and the information he puts out. The full article is located here >>. Relevant portions are copied below.

When you need to set fields disabled or readonly at runtime, you need to keep in mind the type of field you use. Setting a HTML input field is a simple line of Javascript, however, the type of CRM field you've chosen will have an impact on exact items you need to change at runtime. I personally like the look of disabled over readonly. A disabled control will show it's value greyed, indicating to the user that they cannot modify it's contents. A readonly field appears normal. The user only finds out that they cannot change it's value until they attempt to do so.

Use the following code to toggle the field status -

// set it as readonly crmForm.all.new_textfield.readOnly = true; // OR set it as disabled crmForm.all.new_textfield.disabled = true;

 

Here’s the tricky part. By default, read-only fields aren’t saved back to the database. Makes sense, right. However, what if you’re updating a field with some code and want that value saved even though you don’t want the user to be able to change it?

Use the ForceSubmit action.

crmForm.all.<field>.DataValue="some value";

crmForm.all.<field>.ForceSubmit = true;

Setting defaults for System Fields in CRM

In Dynamics CRM v4.0, the system fields don’t allow you to set a default value. Unfortunate, but there are ways around it.

Add the following code to the OnLoad event for the form. Add as many defaults as you need. The code checks to see if this is a new form (CRM_FORM_TYPE_CREATE) and then sets the values you provide.

//Set default values for system fields
var CRM_FORM_TYPE_CREATE = "1";

if (crmForm.FormType==CRM_FORM_TYPE_CREATE)
   {
   crmForm.all.fieldname.DataValue = 60;
   }

Requiring 1 of X fields in CRM

There may be times when you don’t want to require one specific field to contain data, but at least one in a group. An example would be deciding that we can’t enter a lead if we don’t have a way to contact the lead – phone number, email, mobile, etc.

What we have to do is make sure that at least one of the contact fields for a lead contain data. There’s no way to make sure this happens. We could set them all Business Recommended, but that tells the user that they should all be filled in if possible and that’s not what we need.

Gather the actual names of the fields you want in the required group.

Use the following code to require at least one of the fields to contain data in order to save. Note that this example is based on the Lead form. Place the code in the OnSave event for the form.

The first portion is a concatenated If statement that checks each of the fields for data. If the entire statement evaluates as false, then the second portion of the code pops a prompt to the user letting them know why the save failed and then stops the save process.

Also, the if statement is all on one line, even though it wraps below and

//Contact Check
//Check for at least one contact method
{
if (crmForm.all.telephone1.DataValue==null && crmForm.all.emailaddress1.DataValue==null && crmForm.all.mobilephone.DataValue==null && crmForm.all.telephone3.DataValue==null && crmForm.all.telephone2.DataValue==null && crmForm.all.websiteurl.DataValue==null)
{
alert("Provide at least one phone number or an email address");
event.returnValue=false;
return false;
}
}

Creating Report Subscriptions in Microsoft Dynamics CRM 4

Something that gets asked a lot is “Why can’t the data come to me? Why do I have to go somewhere and get it?”

There are ways to make that happen. CRM MVP Donna Edwards has posted an article on the Dynamics CRM Team Blog about exactly that topic. Find the article here - http://tinyurl.com/n5dsy7

Plus, it utilizes one of my new favorite tools – SQL Reporting Services.

Opportunities don't display Pipeline Phase in CRM

I've run into some cases where Dynamics CRM will not correctly display the pipeline phase in the status area of the Opportunity record. Most often, the Opportunity simply display "None" even though the Opportunity may show up correctly on Reports like the Sales Pipeline Report.

There is a quick work-around. It appears that if you add the Pipeline Phase record as a read-only record on the Opportunity form, it will correctly display in the status area.

So, dig into Setting / Customization / Customize Entities / Opportunity / Form and add the record. Make sure the check the box that sets the field as Read-Only.

Update: If you need to develop a workflow that sets the pipeline phase, you'll need to remove it from the Opportunity form or it won't show up under Additional Fields.