Tip #354: No code contextual sequential auto-number

When you add a child entity, it is best practice to populate the name field, but sometimes it can be difficult to come up with a name that makes sense. Say you have an entity that is the child of an account/company record. If you leave the name field blank, if any record looks up to that record, the lookup field will be blank. Plus, say you want to ask the account manager about the record, without a distinct name, it will be difficult to let them know to which record you are referring (sure you can email a link, but follow along).

One approach is to auto number the records with the name of the parent to provide a distinct record name (Acme-1, Acme-2, Acme-2). However, there is no standard way to auto-number records. Sure you could write a plugin, but if you don’t have those skills or you don’t want to take the time, there are additional approaches that do not require custom code.

So in the scenario where you want to auto number records with a combination of the parent record and a sequential number, the following is a no-code approach that I have used.

  1. Add a whole number field to the parent entity called “Counter.” Using a system business rule, default the value to 0.
  2. Add a workflow that runs on create of the child entity.
  3. Add a step to the workflow that increments the counter field on the parent record by 1.
  4. Add a step to the workflow that updates the child entity record and sets the name field to a combination of account name – counter field value.

The result will be a dependable synchronous automatic number that should work for basic requirements. Note that it will not re-use numbers should one of the child records get deleted. If more complex auto-numbering is required, a plugin is the recommended approach.

VN:F [1.9.22_1171]
Rating: 5.0/5 (2 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #353: Notes and activities – where did they go

If you follow our advice and do not do anything prematurely, the default form for your entity will look nice and lean, mostly due to the absence of the middle pane which is default home for activity feeds, activities and notes.

If you decide that you do need activities and notes, after all, you will find that system duly adds the functionality but leaves your original form intact. To bring the pane, edit the form and click Insert > Notes command:
Insert notes pane

When you do that you may find out that Notes are back but not the Activities tab. If that happens, follow these steps:

  • Create new solution and add your entity into it
  • Export the solution as unmanaged
  • Extract customizations.xml file from the archive
  • Find <FormPresentation> for the form and make sure that the value is 1 and not 0
  • Save the file, update it within the solution zip file, import it back and publish

This behaviour is due to the bug that looks like fixed in CRM Online (both 2015 and Update 1 Preview).

If you’re still yearn for social, make sure that your entity is enabled under Settings > Activity Feeds Configuration

Thanks to Larry “Tex” Lentz for keeping the subject alive, Shan “Grillmaster” McArthur for the terse but effective solution and to Adam “Last Word” Vero for this final warning:

You can’t use a notes control for an entity that is not enabled for Notes, so unfortunately you cannot have just activities, or activity feed posts.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #352: When you can’t publish a report

Sometimes when writing SSRS reports for CRM, you will find that the report works fine in Visual Studio, but will fail when you upload them to Dynamics CRM.  This can be frustrating, because it can sometimes be difficult to identify the cause of the issue—it works fine when you preview it in Visual Studio, but when you upload it, the upload will fail, and typically the error message created will be very generic—something to the extent of “Call your System Administrator.”

Typically when you see this problem, it has to do with SSRS functionality specific to CRM reports.  Here are some of the top causes that I see reported by users:

  1. Data source—When you create an SSRS report, you have the choice of using an Embedded data source or a shared data source.  For CRM reports, you must use an embedded data source.  If you use a shared data source, your report will preview in Visual Studio, but you will not be able to upload it.
  2. Hidden parameters—If you use the special hidden CRM parameters, such as the CRM_URL parameter to build dynamic hyperlinks to CRM records, if you don’t set them up properly, the report upload to CRM will fail, but it will still preview OK in Visual Studio.  Make sure that the parameters are set to hidden and allow null values.
  3. Pre-filters—When using CRM Report Pre-Filtering, if you use pre-filtering incorrectly, sometimes the report may fail to upload correctly.  In this case, like the others, the report will preview without error in Visual Studio, but will have issues uploading to CRM.

Troubleshooting

So now that we’ve talked about what some of the common causes for the “works in Visual Studio, but not in CRM” issue, how do you troubleshoot what is causing your report issue?  The best place to start is the event log of the CRM Server.  If you get a “Contact your Administrator” error message, frequently you will see a more detailed error message.  Recently I had a user experiencing this issue, and I checked the event log on the CRM server.  I found the following error message captured in the event log:

The parameter ‘CRM_URL’ has no default. A default is required for all non-nullable parameters without a prompt or the valid values list has to contain Null.

After modifying the SSRS report CRM_URL parameter to accept null values, the report could be successfully uploaded.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #351: Tracing for plugins and custom workflow activities

CRM Online continues to improve on the existing features, as Andre “I’ve got 88 in my handle” Margono noticed when looking at the tracing functionality. ITracingService was available to the savvy developers since CRM 2011, however, the catch always has been that trace is only available when things go wrong, exception kind of wrong. Enter CRM Online 2015 Update 1 (currently in preview).

From the words of Andre himself:

I just noticed the new plugin/custom workflow tracing and logging functionality in my preview instance and now the content has been updated with the information: https://msdn.microsoft.com/en-us/library/gg328574.aspx#loggingandtracing

I think it is a great addition to the plugin/WF capability to keep track of the performance of the custom code where it might breaks.

Plus with this note: “If your custom code executes within a database transaction, and an exception occurs that causes a transaction rollback, all entity data changes by your code will be undone. However, the PluginTraceLog records will remain after the rollback completes.” the tracing functionality won’t interrupt the transaction as it happened today if I want to put custom logging functionality.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #350: Avoiding duplicate contacts when upgrading

When you upgrade CRM for Outlook, one thing to watch out for is duplicate contacts in Outlook. The reason contacts can get duplicated is that if you use Outlook synchronization for contacts and you remove or reinstall the client, the local synchronization database gets replaced. When you reinstall the client and the synchronization runs, a new copy of your synchronized contacts will be downloaded.

To avoid this happening, try to upgrade in place. In-place upgrades do not break the synchronization, and contacts should not be duplicated. If you are going one version in the upgrade, this is the recommended approach. And consider moving to server synchronization so you won’t have to worry about this for future upgrades.

If you are upgrading across multiple versions, in-place upgrades are not practical, and as a result, you will need to uninstall the Outlook client and install the new version. In this case, duplicate contacts cannot be avoided, and a strategy of remediation is your best course of action.

  1. Turn off synchronization in the Outlook client.
  2. Move all synchronized contacts to a different folder (you can tell which are synchronized by looking at the icon. It’s the two-headed guy).
  3. Uninstall the old version of the Outlook client.
  4. Install the new client and synchronize.
  5. Once the contacts are re-synchronized, you can delete the old contacts from the folder you moved them to.
VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #349: Bulk delete in bulk

After using Bulk Delete Wizard in Dynamics CRM
Bulk Delete Wizard
one can be mistaken for thinking that bulk delete is only capable deleting one advanced find result screen at the time.

But if mentioning of C# invokes more that just musical associations for you, welcome to the realm of SDK, where things are not what they seem in user interface. As per SDK documentation:

With bulk deletion you can perform the following operations:… Delete data across multiple entities…

BulkDeleteRequest class contains a member variable QuerySet which, as the name suggests, is an array of QueryExpression objects.

Bonus

If you ever spent time trying to make your bulk delete to run faster by modifying query page size, stop it. System duly ignores all your attempts and creates its own PageInfo object that processes 1,000 records batches.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #348: Watch your audit partition size

We have had several tips this week regarding managing storage on CRM online, and I wanted to finish the week with one more way that the storage gremlins can creep up on you: Audit partitions.

I recently was reviewing one of my environment’s audit logs, and I found one of these things was not like the other.

IMG_0735

Due to some unfortunate circumstances, the most recent audit partition is 164 GB (that’s right, with a G).

What you need to know about audit partitions:

  1. You can delete them, starting with the oldest one.
  2. You cannot delete the current audit partition until the end date. They happen quarterly, so the end of the current partition is 3/31.
  3. You can delete them via the SDK, but it has the same limitations as number 2.

So what this means is that if you have a very large audit partition and it is using all of your available storage, there is no supported way for you to shrink it or delete it. You have to wait until the end of the period, then you can delete it.

For on premises users, you can go the unsupported route and delete audit records via T-SQL. If you use CRM Online, open a ticket with Microsoft support or add additional storage should this happen to you.

 

 

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #347: How to use datetime fields in WHERE clause

Gotta love user Q&A sessions, like the one at Convergence 2015 that I had a privilege to take part in. Questions from users are always refreshing, very often challenging and sometimes they are just the reminder that any small bit of information that one possesses is not necessarily the common knowledge.

On this occasion question was about poor performing reporting queries dealing with the activity records. There were tons of good suggestions but one thing that the user seems to have missed is how to properly use CRM datetime fields in the queries. Let’s say we’d like to filter all activities that started last month. The first query that comes to mind:

select activityid, subject 
from FilteredActivityPointer
where scheduledstart >= '1-feb-2015' 
  and scheduledstart < '1-mar-2015'

Reasonable? Yes. Best performing? Heck, no. The problem is that filtered views, among other things, convert datetime CRM fields into the timezone of the current user. To do that, datetime column names have suffix “utc” added to them and the original names are used to display local time equivalent. For example, every entity in fitered view would have createdon (local time) and createdonutc (the actual value stored in the database).

How this local time is calculated? Using scalar-value T-SQL functions. What chances are of the scalar-value functions in WHERE clause using an index? None. Hello, table scans.

The Spießrutenlaufen Rule of Filtered Views

Whenever CRM datetime field needs to be in the WHERE SQL clause, the utc version of the field should be used.

The above would become:

select activityid, subject 
from FilteredActivityPointer
where scheduledstartutc >= 
            dbo.fn_LocalTimeToUTC('1-feb-2015') 
  and scheduledstartutc < 
            dbo.fn_LocalTimeToUTC('1-mar-2015')

How much do you save? YMMV but on my test server against random CRM organization the execution time of the first query was 2280ms, whilst the second has finished under 80ms.

Like a boss meme96% improvement.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #346: Scheduling daily bulk delete jobs

I noticed that my CRM Online storage was getting low, so I created a scheduled bulk delete job to remove system jobs. My environment has many workflows, and since upgrading to CRM 2015 and creating a bunch of rollup fields, I have noticed a slight increase in the number of system job records being created. I want to have a job that runs daily and deletes successfully completed jobs. However, the minimum frequency of bulk deletion job recurrence is 7 days.

Adam Vero gave me a great, simple suggestion–schedule multiple bulk delete jobs, one for each day of the week. Set each to recur every 7 days. The end result is a daily schedule.

UPDATE: Turns out there is an even easier way. Even though the drop-down doesn’t give you a daily option, you can still choose 1 by typing in the field. Like the phone call duration field, the “run the job after every X days” field gives you pre-set options, but also allows you to type in virtually any number you want. So simply type in “1″ rather than selecting a value from the drop down.

Screenshot 2015-03-17 23.08.31

 

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+