Tip #453: Tipster guide to calculated fields

It’s Friday, Karl, Friday! That means, instead of our fingers, we let our magnificent voices do the talking.

This video explores Dynamics CRM calculated fields feature. It demonstrates how to perform basic field calculations on an entity, how to use some to the date functions available, and how to populate data from other entities. In addition to some demos, it talks about limitations, and other items to be mindful of when implementing them.

YouTube player

Give us your feedback, all of it: good, bad, and ugly, I’m sure we can take it. Suggest new topics either in comments or by sending your ideas to jar@crmtipoftheday.com.

Tip #452: How to use SSL certificates internally

tl;dr

Get a wildcard SSL certificate for your domain, reserve some names for internal CRM (e.g. internalcrm.contoso.com) and use internal DNS to resolve those addresses internally-only. If blah.foobar.local is required, domain CA should be used to issue an internal only certificate with trust implied.

The Stop

Dynamics CRM TipperOur own Joel “Standing on the shoulders of other MVPs” Lindstrom recklessly caused another mini truckstop by not being able to find an answer on his own.

Question

According to DigiCert, no public CA will provide certificate after Nov 1, 2015 that covers an internal resource. I read that as server or server.domain.local. Apparently this is for any address not “verifiable” from the internet. It also will not cover any resource on RFC 1918 (10.x.x.x or 172.16.x.x-172.31.x.x, as well as 192.168.x.x)

For CRM IFD which ALWAYS uses an internal and external address that are not the same address, this might mean publishing the internal address, externally (at least for the cert) which isn’t recommended by Microsoft. Normally the internal address isn’t published externally, but we could do that. My concern is if an internal user hits one of the 4 URLs (Internal, External, Discovery, or Auth) on an INTERNAL RFC 1918 IP, will there be an issue.

Is there any CRM off specific guidance on this?

Answer 1

Feridun “Best Twitter Handle for CRM MVP” Kadir patiently explains the mechanics:

For all the IFD implementations that I have done, I’ve used a wildcard certificate for the company’s domain name. eg. *.contoso.com. No internal resource is published.

Then for internal CRM access I use a name like internalcrm.contoso.com but arrange for the name to only resolve on the internal DNS server. (highlight mine – t.j)

Answer 2

There must be something going on with SSL certificates in U.K., because David “British Scientist” Jennaway chimed in as well:

In most cases I’d do as Feridun’s example. However, if there is a need to use an internal name like server.domain.local, then you can use an internally issued certificate. This would require ensuring the trust path of the certificates, but that’s just something to weigh against the reasons for using an internal name.

I don’t see that IP addresses are relevant here. It may be you can register a certificate for an IP address, rather than a domain name, which would explain the reference to RFC 1918, but that’s not applicable to IFD.

Tip #451: Locking field in business rule can be tricky

Business rule to lock a field Empowering users in Dynamics CRM is great but one needs to be prepared to troubleshoot adventures of a power user. If you have a rule that locks the field when certain conditions are met, beware what happens when this field is editable. That’s right, the last edit will be lost. Why? because locking the field is a common-speak for disabling the control and, as most of readers know, CRM cannot be stuffed to send saves the bandwidth by not sending content of the disabled controls.

Unfortunately, there is no definite answer to this challenge in “no code” land but the solution is fairly trivial – all we need to do is to force content submission for the control by wiring something like the following to the form OnSave event:

function account_onsave()
{
   if(Xrm.Page.getControl("emailaddress1")
              .getDisabled()) {
      Xrm.Page.getAttribute("emailaddress1")
              .setSubmitMode("always");
   }
}

Tip #450: System Settings – Previews Tab

As of CRM Online version 7.1 there is another new tab on the Systems Setting configuration window. The new previews tab is where the CRM Admin will enable access for their organization to some really cool new features – IFRAME in tablets, the Web API Developer Preview and my personal new favorite the CRM App for Outlook.

CRM Online System Settings

CRM Online System Settings

Tip #449: CRM Online Trial Setup – Update

Our Tip #91: CRM 2013 Online Trials Direct Link provided the url to start the signup process for a 30 day trial of CRM Online. Several months ago Microsoft started offering a concierge service to improve the first use experience. But if you are an experienced end user or a partner that is just spinning up a trial for demo or experimentation or cause you need a development environment, then you don’t need the assisted support and the emails and phone calls that go with it.

So what you need to do is to scroll down to the bottom of the page and click on No thanks, I’ll get started on my own as shown in the screen capture below.

freetrial

 

Tip #448: Tipster guide to Dynamics CRM for Phones: Customizer View

Dynamics CRM recently released an updated Dynamics CRM for Phones app as part of the Spring Update 1. It’s Friday and it’s time for a video walk-through.

YouTube player

Give us your feedback, all of it: good, bad, and ugly, I’m sure we can take it. Suggest new topics either in comments or by sending your ideas to jar@crmtipoftheday.com.

Tip #447: To filter or not to filter – functional view

This seems to be the week of unsupported tips. I know, we said it before: Swim between the flags, and Use filtered views even if you use stored procedures.

But what do you do if this particular report’s query is a monstrosity, adding custom indexes don’t help, and any attempt to restructure it just makes it worse? Sounds familiar?

Consider this simple pre-filtered SQL that returns us all invoiced and delivered line items in the last financial year:

declare @fromutc datetime, @toutc datetime
select 	@fromutc = dbo.fn_LocalTimeToUTC('1-jul-2014'),
	    @toutc   = dbo.fn_LocalTimeToUTC('1-jul-2015')

select 
   CRMAF_FilteredContact.contactid, 
   CRMAF_FilteredContact.fullname, 
   i.datedelivered, 
   i.name, 
   id.actualdeliveryon, 
   id.baseamount, 
   id.tax, 
   id.extendedamount
from
   FilteredContact as CRMAF_FilteredContact
   inner join FilteredInvoice i 
      on CRMAF_FilteredContact.contactid = i.contactid
   inner join FilteredInvoiceDetail id 
      on id.invoiceid = i.invoiceid
where
	id.actualdeliveryonutc 
	between @fromutc
	    and @toutc

If everything else fails (including scheduling the report overnight), my last resort is to isolate some of the views into a separate function and, gulp, replace the filtered views with the base views. In the case above, that could be something like that:

create function [dbo].[OutsideTheFlags]
   (@fromutc datetime, @toutc datetime)
returns table
as return (
select 
   i.ContactId,
   i.datedelivered, 
   i.name, 
   id.actualdeliveryon, 
   id.baseamount, 
   id.tax, 
   id.extendedamount
from
   Invoice i 
   inner join InvoiceDetail id 
      on id.invoiceid = i.invoiceid
where
	id.actualdeliveryon 
	between @fromutc
	    and @toutc
)
GO	
-- This is important. Otherwise won't run
GRANT SELECT 
ON [dbo].[OutsideTheFlags]
TO [DOMAIN\ReportingGroup {GUID}]
GO

and then the reporting query becomes:

declare @fromutc datetime, @toutc datetime
select 	@fromutc = dbo.fn_LocalTimeToUTC('1-jul-2014'),
	    @toutc   = dbo.fn_LocalTimeToUTC('1-jul-2015')

select 
   CRMAF_FilteredContact.contactid, 
   CRMAF_FilteredContact.fullname, 
   i.datedelivered, 
   i.name, 
   i.actualdeliveryon, 
   i.baseamount, 
   i.tax, 
   i.extendedamount
from
   FilteredContact as CRMAF_FilteredContact
   inner join dbo.OutsideTheFlags(@fromutc, @toutc) i  
      on CRMAF_FilteredContact.contactid = i.contactid

Original query: 1.9 sec, new one: 0.2 sec, 900%+ improvement. YMMV, of course. (And, yes, I cheated, times still need to be converted to local)

When performing this unsupported gymnastics, be very, very careful: you’ll lose lookups and optionsets expansion, the dates will be UTC-only, etc. Not to mention that the above gives blanket access to all invoices for all users. Test, test, and test.

The usual disclaimer: use at your own risk, contains small parts, choking hazard, swim between the flags. Not tested on animals, or children. Good luck making it work for CRM Online.

Tip #446: How to avoid overwriting audit and duplicate detection flags

tl;dr

To avoid overwriting some of the system entities flags for the customers, manually remove those flags from the managed solution files. WARNING! Super-duper unsupported.

Some juicy details

If you are a Dynamics CRM ISV or an infrastructure group within a large enterprise then, most likely, you distribute one or more managed solutions to your customers (external or internal). (If you are not ISV or infrastructure developer, what are you doing with managed solutions?)

Some of these solutions may include system entities such as accounts, contacts, etc. If they do then at some point in the past you received a call/email from a disgruntled customer:

You #@$&^%! solution switched audit for the accounts off!

If you have foreseen this and enabled audit on system entities in your solution then at some point in the past you received a call/email from an even more disgruntled customer:

You #@$&^%! solution switched audit for the accounts on and, since we were not aware, it blew up our data storage allowance!

What can I say? I know! (╯°□°)╯︵ ┻━┻
Audit On/Off

Breathe [in|out]

All you want to do is to leave those pesky flags out of your solution, right? And you are prepared to sell your developer’s soul (what’s left of it) and go unsupported, right? Keep reading then.

  1. Export your managed solution file as you normally would
  2. Extract customizations.xml file
  3. Open it in your favorite editor, find and delete the elements <IsAuditEnabled> for audit and <IsDuplicateCheckSupported> for duplicate detection
  4. Save, copy back into the solution zip file
  5. Test, test, and then test again
  6. Send it to your customers and keep the fingers crossed

Until Dynamics CRM delivers an ability for a developer to choose what flags and features to package within their solution, removing the elements seems to be the only way to ensure that your solution does not trample the existing settings.

Feel free to experiment with the other flags, but usually they are more or less innocuous.

Tip #445: How to Excel in CRM like Captain Redlaces

Captain Redlaces Sewell reports that there is an issue in CRM 2015 update 1 when exporting to Excel. If you open the file directly from CRM, the table will not be correctly formatted.

What you need to do is save the document, then open the document from the save location. But do not to use the ‘Save and Open’ option offered by the download – it’s a trap!

Tip #444: When any time will do

Dynamics CRM TipperWhy sometimes it’s easy to write a mini truckstop tip? Because all you have to do is to stop and listen to a conversation between two people passionate about Dynamics CRM.

Jamie “Xerox” Miley challenged one of the CRM foundation stones, Advanced Find:

I am trying to understand the utility of writing this clause. I came across it earlier today and had a head-scratcher moment.

Question: What is the difference between filtering a datetime on “Any Time” and excluding the filter?

Any date will do

Feridun “Best Twitter Handle for CRM MVP” Kadir paused for a split second before nailing the answer:

Hmm, interesting question. I investigated this by looking at the FetchXML for Any Time which is

<fetch version="1.0" output-format="xml-platform" 
       mapping="logical" distinct="false">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="customerid" />
    <attribute name="estimatedvalue" />
    <attribute name="statuscode" />
    <attribute name="opportunityid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="estimatedclosedate" 
                 operator="not-null" />
    </filter>
  </entity>
</fetch>

This is exactly the same as for the test “Contains Data”.

So Jamie, excluding the filter “Any Time” means that, as per your screenshot, all open records are returned. Adding the filter, means that only open records where Est. Close Date has a value are returned.

Perhaps there is some historical reason in earlier versions of CRM for Any Time.