Deleting Data in Microsoft Dynamics CRM 2013 online through web service

A while ago, in one of my projects which was integration between Microsoft Dynamics GP and Dynamics CRM 3013 online , I ran into storage problem in CRM side. since the main purpose of integration was bringing most of GP data such as product catalog and sales transaction information to CRM online, I totally forgot about the workflows that I have put in place to track the changes in CRM and they keep creating system jobs . Therefore after a week I got a warring on CRM portal for Storage Limit  and after neglecting it , interestingly the New button got removed from most of my forms.

The good thing was that I could create a Bulk Record Deletion under Data Management but since most of the workflow system jobs were in waiting or progress status, in spite of shortage of storage,  they were creating new system events .

here are two ways to Delete system job using CRM SOAP service:

1.Deleting Records using delete method of IOrganizationService.Delete 

By default CRM online wont allow to retrieve more than 5000 records by default. system job entity name is ‘asyncoperation’

Add these dll’s to your project:

  •                   microsoft.crm.sdk.proxy.dll
  •                   microsoft.xrm.sdk.dll
protected void Button1_Click(object sender, EventArgs e)

{

ClientCredentials Credentials = new ClientCredentials();

ClientCredentials oClientCredential = new ClientCredentials();

oClientCredential.UserName.UserName = "yourCRMAddress.onmicrosoft.com";

oClientCredential.UserName.Password = "password";

Uri OrganizationUri = new Uri("https://YOURORGANIZATIONNAME.api.crm.dynamics.com/XRMServices/2011/Organization.svc");

Uri HomeRealmUri = null;

using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, oClientCredential, null))

{

IOrganizationService service = (IOrganizationService)serviceProxy;

for (int i = 0; i < 100; i++)

{

EntityCollection myEntity = getRecords(service);

for (int j = 0; j < 5000; j++)

{

Guid MyGUID = myEntity.Entities[j].Id;

service.Delete("asyncoperation", MyGUID);

}

}

}

}

private static EntityCollection getRecords(IOrganizationService service)

{

try

{

QueryExpression query = new QueryExpression();

query.EntityName = "asyncoperation";

query.ColumnSet = new ColumnSet() { AllColumns = true };

query.Criteria = new FilterExpression();

query.Criteria.FilterOperator = LogicalOperator.And;

query.Criteria.Conditions.Add

(

new ConditionExpression("operationtype", ConditionOperator.Equal, "10")

);

//   query.Criteria.Conditions.Add

//(

//    new ConditionExpression("startedon", ConditionOperator.OnOrAfter, "2014-02-11")

//);

EntityCollection entities = service.RetrieveMultiple(query);

return entities;

}

catch (Exception ex)

{

return null;

}

}

<b>2.  Deleting using BulkDeleteRequest which will create a bulk record deletion record in CRM</b><b> </b>

private static void BulkDelete(IOrganizationService service)

{

BulkDeleteRequest request = new BulkDeleteRequest

{

JobName = "Delete All asyncoperation",

ToRecipients = new Guid[] { },

CCRecipients = new Guid[] { },

RecurrencePattern = string.Empty,

QuerySet = new QueryExpression[]

{

new QueryExpression { EntityName = "asyncoperation" }

}

};

BulkDeleteResponse response = (BulkDeleteResponse)service.Execute(request);

Guid jobId = response.JobId;

bool deleting = true;

while (deleting)

{

QueryExpression query = new QueryExpression { EntityName = "bulkdeleteoperation" };

query.Criteria.AddCondition("asyncoperationid", ConditionOperator.Equal, jobId);

query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 3);

query.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 30);

EntityCollection results = service.RetrieveMultiple(query);

if (results.Entities.Count &gt; 0)

{

deleting = false;

}

}

}

Advertisements

Microsoft Dynamics GP and CRM Price list Integration

As I discussed in my previous post about GP and CRM integration,  here is the query for getting all the price levels in GP and combining them with Currency (in my example 2 currencies).

you can use this query in your integration application or your code to read the data from GP database and make it ready to send to Dynamics CRM price list.

SELECT [IV40800].[PRCLEVEL],
[IV40800].[DSCRIPTN],
[MC40200].[CURNCYID],
[MC40200].[ISOCURRC]
FROM [IV40800], [DYNAMICS].[dbo].[MC40200] [MC40200]
WHERE
[MC40200].[CURNCYID] = 'USD'
or
[MC40200].[CURNCYID] = 'CDN'

Microsoft Dynamics GP and Dynamics CRM Integration prerequisite 1

Before starting to put integration in place for Dynamics GP ERP system and Dynamics CRM make sure to check these requirements:

  • functional currency: each system should have the same functional currency. one of the first steps of integration is to bring all the functional currencies from GP to CRM.

Why it is important? Because certain entities in CRM like price list (in GP its called price level) are currency based but there is no currency defined in price levels in GP, if you have multiple currencies in GP , you should bring combination of all price level and system currencies in to CRM. It means if you have 2 currencies for instance CDN and USD in GP, for each price level you should create two records in CRM database one for CDN and the other one for USD.

stay tuned, I will update GP query for this post later on.

GP price level:

Pricelevel

Dynamics CRM price list:

Pricelist