Getting the Option Set Values from SQL
Today I needed to get a list of all the options sets for a particular option set. One option would be to do a DISTINCT query on the filtered view but that might not return me all of the values as some might not be used.
I did a quick search and found out that the option set values are stored in the FilteredStringMap view, here is the query:
The best way of filtering System Views by diferent parameters or by roles is to capture the Retrieve Multiple queries and adding filters to the related queryexpresion.
It sound like something very difficult but this can be archieved easyly by creating a plugin and registering it over the Retrieve Multiple Call of the related entity.
This plugin must be registered with Pre-Operation, Synchronous and Server settings as shown on the image.
Plugin Registration for Retrieve Multiple
The development of the plugin is quite easy.
First of all the plugin has to inherit from the following base class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using Microsoft.Xrm.Sdk;
using System.Globalization;
namespace L.Plugin
{
public abstract class LPluginBase : IPlugin
{
static protected IPluginExecutionContext GetExecutionContext(System.IServiceProvider serviceProvider)
{
IPluginExecutionContext pluginExecutionContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
return pluginExecutionContext;
}
public abstract void Execute(System.IServiceProvider serviceProvider);
}
public static class MessageNames
{
public static String Create
{
get
{
return "Create";
}
}
public static String Delete
{
get { return "Delete"; }
}
}
public static class ExecutionStage
{
public static int PreEvent
{
get { return 20; }
}
public static int PostEvent
{
get { return 40; }
}
}
}
Then the plugin that intercepts the call and adds a new filter base on a field of the calling entity will look like:
namespace L.DW.RetrieveCaching
{
using System;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using L.Plugin;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Messages;
/// <summary>
/// PreValidateHazardousEventRevisionRetrieveMultiple Plugin.
/// </summary>
public class PreValidateHazardousEventRevisionRetrieveMultiple : LPluginBase
{
public override void Execute(IServiceProvider serviceProvider)
{
if (serviceProvider == null)
{
throw new ArgumentNullException("serviceProvider");
}
Microsoft.Xrm.Sdk.IPluginExecutionContext context =
(Microsoft.Xrm.Sdk.IPluginExecutionContext)serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));
// Get the OrgService
IOrganizationServiceFactory serviceFactory =
(IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
// Get a reference to the tracing service.
ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
// Check that all of the following conditions are true:
// 1. plug-in is running synchronously
// 3. plug-in is running on the 'RetrieveMultiple' event
if (context.Mode == 0 && context.MessageName.Equals("RetrieveMultiple"))
{
// The InputParameters collection contains all the data passed in the message request.
if (context.InputParameters.Contains("Query"))
{
if (context.InputParameters["Query"] is QueryExpression)
{
if (UserHasRole(context.InitiatingUserId, "Drinking Water Team User", service))
{
// Get the QueryExpression from the property bag
QueryExpression objQueryExpression = (QueryExpression)context.InputParameters["Query"];
// We can modify the original query to just show record with Completed or Completed with issues estaus.
ConditionExpression statusCompletedCond = new ConditionExpression()
{
AttributeName = "L_status",
Operator = ConditionOperator.In,
Values = { 3, 4 }
};
objQueryExpression.Criteria.AddCondition(statusCompletedCond);
}
}
}
}
}
We could make something even more complex by filtering the results from the status of a field in a N to N relationship.
namespace L.DW.RetrieveCaching
{
using System;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Lema.Plugin;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Messages;
/// <summary>
/// PreValidateControlMeasureRetrieveMultiple Plugin.
/// </summary>
public class PreValidateControlMeasureRetrieveMultiple : LPluginBase
{
public override void Execute(IServiceProvider serviceProvider)
{
if (serviceProvider == null)
{
throw new ArgumentNullException("serviceProvider");
}
if (serviceProvider == null)
{
throw new ArgumentNullException("serviceProvider");
}
Microsoft.Xrm.Sdk.IPluginExecutionContext context =
(Microsoft.Xrm.Sdk.IPluginExecutionContext)serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));
// Get the OrgService
IOrganizationServiceFactory serviceFactory =
(IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
// Get a reference to the tracing service.
ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
// Check that all of the following conditions are true:
// 1. plug-in is running synchronously
// 3. plug-in is running on the 'RetrieveMultiple' event
if (context.Mode == 0 && context.MessageName.Equals("RetrieveMultiple"))
{
// The InputParameters collection contains all the data passed in the message request.
if (context.InputParameters.Contains("Query"))
{
if (context.InputParameters["Query"] is QueryExpression)
{
if (UserHasRole(context.InitiatingUserId, "Water Team User", service))
{
// Get the QueryExpression from the property bag
QueryExpression objQueryExpression = (QueryExpression)context.InputParameters["Query"];
objQueryExpression.Distinct = true;
// Calculate completed Revisiones
string[] CompletedGuids = getAllOpenRevisionsGuid(service);
// Add the filter using the N to N middle table
LinkEntity linkEntity1 = new LinkEntity();
linkEntity1.JoinOperator = JoinOperator.Natural;
linkEntity1.LinkFromEntityName = "L_controlmeasure";
linkEntity1.LinkFromAttributeName = "L_controlmeasureid";
linkEntity1.LinkToEntityName = "L_controlmeasure_hazardouseventrevision";
linkEntity1.LinkToAttributeName = "L_controlmeasureid";
// Condition : where hazarrevisionID is in the returned Guidvalues
ConditionExpression statusCompletedCond = new ConditionExpression("L_hazardouseventrevisionid", ConditionOperator.In, CompletedGuids);
linkEntity1.LinkCriteria.AddCondition(statusCompletedCond);
objQueryExpression.LinkEntities.Add(linkEntity1);
}
}
}
}
}
NOTE: Probably this second option of filtering by the N to N relationship could be done without making 2 queries but I couldn’t make it work, so as my teacher used to say, divide and you will win đ !!! Using 2 queries helped me to filter the results based on a N to N relationship.
I hope it was helpful!
Cheers,
Hache
http://danielcai.blogspot.ie/2011/12/filtered-lookup-for-existing-button-of.html
For rollup 12 issues:
FILTERING THE RESULTS USING THE SOAP END POINT
//filters an add existing lookup view (1:N)
function addExistingFromSubGridCustom (gridTypeCode, gridControl, fetch, layout, viewName) {
var viewId = "{1DFB2B35-B07C-44D1-868D-258DEEAB88E2}"; // a dummy view ID
var relName, roleOrd;
if (typeof (gridControl.GetParameter) === "function") { //post rollup 12
relName = gridControl.GetParameter("relName");
roleOrd = gridControl.GetParameter("roleOrd");
}
else { //pre rollup 12
relName = gridControl.getParameter("relName");
roleOrd = gridControl.getParameter("roleOrd");
}
//creates the custom view object
var customView = {
fetchXml: fetch,
id: viewId,
layoutXml: layout,
name: viewName,
recordType: gridTypeCode,
Type: 0
};
//pops the lookup window with our view injected
var lookupItems = LookupObjects(null, "multi", gridTypeCode, 0, null, "", null, null, null, null, null, null, viewId, [customView]);
//once the lookup window is closed, we need the parent record ID and ETC before associating selected records
if (lookupItems && lookupItems.items.length > 0)
{
var parentId;
var parentTypeCode;
if (typeof (GetParentObject) == "function")
{ //post rollup 12 has its own function to get this
var parent = GetParentObject();
parentId = parent.id;
parentTypeCode = parent.objectTypeCode;
}
else
{ //pre rollup 12 still needs to use the old way
var parent = typeof (crmFormSubmit) == "undefined" ? $get("crmFormSubmit") : crmFormSubmit; //according to daniels blog crmFormSubmit should already be defined, but it's not...
if (parent) {
parentId = parent.crmFormSubmitId.value;
parentTypeCode = parent.crmFormSubmitObjectType.value;
}
else {
parentId = window.parent.crmFormSubmit.crmFormSubmitId.value;
parentTypeCode = window.parent.crmFormSubmit.crmFormSubmitObjectType.value;
}
}
//associates the selected records
AssociateObjects(parentTypeCode, parentId, gridTypeCode, lookupItems, IsNull(roleOrd) || roleOrd == 2, "", relName);
}
}
//filters the Emision Point 1:N lookup view from Amendment to show only Emision Points Regarding the ammendment!!
function filterAddExistingAmpoints (gridTypeCode, gridControl, primaryEntityName, entityID)
{
var cols = ["amendment"];
var retrievedGroup = CrmServiceToolkit.Retrieve(primaryEntityName, entityID, cols);
//fetch to retrieve filtered data
var fetch = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
" <entity name='amemissionpoint'>" +
" <attribute name='amemissionpointid' />" +
" <attribute name='name' />" +
" <attribute name='shortcode' />" +
" <attribute name='group' />" +
" <attribute name='emissionpointtype' />" +
" <attribute name='createdon' />" +
" <order attribute='missionpointtype' descending='false' />" +
" <filter type='and'>" +
" <condition attribute='amendment' operator='eq' value='" + retrievedGroup.getValue('amendment') +"' />" +
" </filter>" +
" </entity>" +
"</fetch>";
//columns to display in the custom view (make sure to include these in the fetch query)
var layout = "<grid name='resultset' object='1' jump='amemissionpointid' select='1' icon='1' preview='1'>" +
" <row name='result' id='amemissionpointid'>" +
" <cell name='name' width='200' />" +
" <cell name='shortcode' width='30' />" +
" <cell name='emissionpointtype' width='100' />" +
" <cell name='createdon' width='100' />" +
" <cell name='group' width='100' />" +
" </row>" +
"</grid>";
addExistingFromSubGridCustom(gridTypeCode, gridControl, fetch, layout, "Filtered Emision Points");
}
To hide the lookup view button “new” use the following UNSUPPORTED solution.
//Removing the New button from lookup.
var lookupControl = Sys.Application.findComponent(lookupname);
if (lookupControl != null)
{
lookupControl._element._behaviors[0].AddParam("ShowNewButton", 0);
}
SharePoint Integration
The step by step tutorial can be found here.
Modifying the Buttons on the View
If we want to modify the buttons on the document View (New , Add…) we will have to modify the CRMListComponent following this post instructions.
http://community.dynamics.com/crm/f/117/t/84936.aspx#.UVwVOpPvtqU
A great post about attaching files to notes.
Great post about licencing
Kevin Machayya posted links in this article to an updated version of the Microsoft Dynamics CRM 2011 Pricing and Licensing Guide. This is the definitive document for figuring out what you can and canât do in various situations with different types of licence.
You can download it via Partnersource here (authorised LiveID required):
CRM 2011 Pricing and Licensing Guide Feb 2012 update
<edit> or the direct link here: http://crmdynamics.blob.core.windows.net/docs/Pricing_Licensing_Guide.pdf (Iâm not sure if this only works when logged in with an appropriate LiveID)
The guide covers differences between user and device CALs, and the different types for âFullâ, âLimitedâ and the new (for CRM 2011, as opposed to 4.0) âEmployee Self Serviceâ (ESS) CAL, which was covered in the previous version of the guide but I am still amazed by the number of users and even partners who seem to have never heard of it.
View original post 926 more words
We had some performance problems with one of our client using CRM 4.0 due to the high level of jobs/Workflows executing at the same time on their system. We end up with several deathlock problems where some workflows were Suspended waiting for resources that other suspended workflows where keeping.
To resolve this issue we decided to delete all the workflows with suspended Status. The problem deleting this records from the “AsyncOperationBase ” datatable using SQL server is the relationships that this records have with the following tables
To solve the problem we created a SQL Transaction were we use a table to keep all the AsyncOperationBaseIDs of all the records we wanted to delete, then delete all the related records in the “WorkflowLogBase,BulkDeleteFailureBase and WorkflowWaitSubscriptionBase” tables to finally be able to delete those record on the “AsyncOperationBase ” datatable. Everything wrap in a transaction that will rollback in case of error.
The SQL will look like this.
-- we First create some indexes to speed up the execution
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
-- Records in the AsyncOpetion table have relationship with records in WorkflowLogBase,BulkDeleteFailureBase and WorkflowWaitSubscriptionBase
declare @DeleteRowCount int
Select @DeleteRowCount = 2000
--We Create Temp table to keep all the AsyncOperationId from AsyncOperationBase that we want to delete
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
begin tran
--Fill up the temp table with the query we want. In this case we select AsyncOperations with name NMYPROCESS and
-- StatusCode = 10 Suspended. And only the records of the following Types 1,9,12,25,27,10 (Microsoft Recomendation)
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
where Name like '%MYPROCESS' -- Name like MyPROCESS
and StatusCode in (10) -- Suspended
and OperationType in (1, 9, 12, 25, 27, 10)
-- Microsoft recomends deleting only this Operation types:Event, CollectSqmData,Workflows, PersisMatchCode,FullTextCatalogIndex ,UpdateContractStates
Select @rowCount = 0
Select @rowCount = count(*) from @DeletedAsyncRowsTable
select @continue = case when @rowCount <= 0 then 0 else 1 end
if (@continue = 1) begin
-- deleting from WorkflowLogBase
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where W.AsyncOperationId = d.AsyncOperationId
-- deleting from BulkDeleteFailureBase
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where B.AsyncOperationId = d.AsyncOperationId
-- deleting from WorkflowWaitSubscriptionBase
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where WS.AsyncOperationId = d.AsyncOperationID
-- Finaly deleting from AsyncOperationBase
delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where A.AsyncOperationId = d.AsyncOperationId
/*Delete the temp table*/
delete @DeletedAsyncRowsTable
end
-- If error rollback otherwise commit.
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
end
--Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
If instead deleting Suspended Jobs you want to delete job in other states the following table describes all the StatusCode Values in SQL.
StatusCode Values
| State | Status | Numeric status value in SQL |
| Ready | WaitingForResources | 0 |
| Suspended | Waiting | 10 |
| Locked | InProgress | 20 |
| Locked | Pausing | 21 |
| Locked | Canceling | 22 |
| Completed | Succeeded | 30 |
| Completed | Failed | 31 |
| Completed | Canceled | 32 |
In the other hand, If you want to delete othe kind of Async Operations this table shows all the OperationType values .
| Value | Field | Description |
| 1 | Event | Specifies a system event. |
| 2 | BulkEmail | Specifies a bulk e-mail operation. |
| 3 | Parse | Specifies an import file parse operation. |
| 4 | Transform | Specifies an operation to transform parse data. |
| 5 | Import | Specifies an import operation. |
| 6 | ActivityPropagation | Specifies a campaign activity propagation operation. |
| 7 | PublishDuplicateRule | Specifies an operation to publish a duplicate detection rule. |
| 8 | BulkDetectDuplicates | Specifies a bulk duplicate detection operation. |
| 9 | CollectSqmData | Specifies an operation to collect SQM data. |
| 10 | Workflow | Specifies a workflow operation. |
| 11 | QuickCampaign | Specifies a quick campaign operation. |
| 12 | PersistMatchCode | Specifies an operation to update the matchcode. |
| 13 | BulkDelete | Specifies a bulk delete operation. |
| 14 | DeletionService | Specifies a Deletion Service operation. |
| 15 | IndexManagement | Specifies an index management operation. |
| 16 | CollectOrgStats | Specifies an operation to collect an organization’s statistics. |
| 17 | ImportingFile | Specifies an import subprocess operation. |
| 18 | CalculateOrgStorageSize | Specifies an operation to calculate an organization’s storage size. |
| 19 | CollectOrgDBStats | Specifies an operation to collect an organization’s database statistics. |
| 20 | CollectOrgSizeStats | Specifies an operation to collection an organization’s size statistics. |
| 21 | DatabaseTuning | Specifies an operation for database tuning. |
| 22 | CalculateOrgMaxStorageSize | Specifies an operation to calculate an organization’s maximum storage size. |
| 23 | BulkDeleteChild | Specifies a bulk delete subprocess operation. |
| 24 | UpdateStatisticIntervals | Specifies an operation to update statistic intervals. |
| 25 | FullTextCatalogIndex | Specifies an operation to generate an organization full text catalog index. |
| 26 | DatabaseLogBackup | |
| 27 | UpdateContractStates | Specifies an operation to update contract states. |
| 28 | ShrinkDatabase | |
| 29 | ShrinkLogFile | |
| 30 | ReindexAll | Specifies an index management operation. |
| 31 | StorageLimitNotification | |
| 32 | CleanupInactiveWorkflowAssemblies | |
| 38 | ImportSampleData |
If you want more info about using Transactions in SQL a great post about it here
I hope it was usefull,
Have a nice day,
Hache
Great post about how to use Fetch with JavaScript
[tweetmeme source=âBenHoskâ only_single=false]
The chaps over at Customer Effective blog have written a really interesting blog post this week. Â Itâs called
Execute Fetch from Javascript in CRM 2011
They actually also wrote an interesting article about why Microsoft buying skype made sense, which you can read here, I found this interesting because I was thinking Microsoft had completely overpaid but then the article  mentions skype have 636 million users, Microsoft paid $8.6 billion and that works out at $14.70 per/user price.  Now thatâs not much for each users and you would probably think that skype is going to grow the number of users it has.  You can see Microsoft bundling skype in with Xbox, CRM and office etc.  The last thought I had was Microsoft has money burning a hole in itâs pocket.  The only danger is that someone else/Google could create a skype competitor but then I thought the sameâŚ
View original post 762 more words
hachecrm2011 