Messing around with the CRM Database

Today I encountered an interesting bug in CRM: impossible to delete an invoice, coming along with the nice “Generic SQL Error”.
Digging a little, I quickly found that the SQL error was related to the foreign key constraint between InvoiceBase and InvoiceExtensionBase, namely.

The delete statement conflicted with the REFERENCE constraint “FK_InvoiceExtensionBase_InvoiceBase”. The conflict occurred in the database…, table…, column ‘InvoiceId’.

Counting records of both these tables  in the DB, I found different results… Hmmm

It just so happened that for performance reasons, my customer recently decided to transpose some plugin code directly in SQL code, and that included the creation of invoices. Quite crazy you might say, ok let’s not start this debate, as we are all fully aware of the risks (especially with CRM 2013 where the extension tables simply disappear…). To achieve that task, he run the SQL profiler and executed all the tasks he wanted to have in SQL. He then reorganized the logged SQL commands in to a few stored procedures. Except that he went probably a bit too far, as he inserted the invoiceid into the table InvoiceExtensionBase, as well.

Si what I had was records in the table InvoiceExtensionBase, starting from the day the plugin was replaced by the stored proc. And the consequence was that it was then impossible to delete an invoice. Interesting conclusion then: since the invoice entity had not been customized, the CRM platform simply did not insert anything into the extension table. So deleting all the records from InvoiceExtensionBase solved the problem.

Create a new order from contact through a custom button

Here’s an exemple of the potential use of the new_pluginlauncher entity evoked in my previous post.

In this code, I have put a “Create Order” button on the contact form.

function newOrder() {
    var url = ‘_controls/lookup/lookupinfo.aspx?AllowFilterOff=0&DefaultType=1084&DefaultViewId=%7b30969771-F303-4B8E-AE91-1E4F5A7FEEFB%7d&DisableQuickFind=0&DisableViewPicker=0&LookupStyle=single&ShowNewButton=1&ShowPropButton=1&browse=false&objecttypes=1084′;
    var params = ‘toolbar=no,location=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=600′;
    var lookupWindow = window.open(url, ‘entity’, params);
    lookupWindow.onbeforeunload = function (test) {
        var result = lookupWindow.getDialogReturnValue();
        if (result && typeof(result) != ‘undefined’) {
            var quoteId = result.items[0].id;
            var entityType = 923620004; /* Contact */
            var actionType = 923620006; /* Create order */
            var recordGuid = Xrm.Page.data.entity.getId();
            var recordName = Xrm.Page.getAttribute(‘fullname’).getValue();
            createPluginLauncher(entityType, actionType, recordGuid, recordName, quoteId);
        }
    }
}
Clicking this button thus displays a lookup form, allowing the user to choose an offer. From there, I retrieve the selected offer id using  “lookupWindow.getDialogReturnValue();”. This is unsupported of course.
I can then pass that Id to a record of the new_pluginlauncher, allowing me to call ConvertToSalesOrder in a plugin, to return the new order’s guid to js, and to popup a form displaying this order. How to do that? The plugin on new_pluginlauncher is on precreate: create the order, and set the guid in the record before it’s created.
Here’s the create plugin launcher function:
/* Create plugin launcher */
function createPluginLauncher(entityType, actionType, recordGuid, recordName, quoteId) {
    var newPluginLauncher = {
        new_EntityType: { Value: entityType },
        new_ActionType: { Value: actionType },
        new_RecordGuid: recordGuid,
        new_QuoteId: quoteId,
        new_name: recordName
    };
    SDK.JQuery.createRecord(
        newPluginLauncher,
        ‘new_pluginlauncher’,
        function (newPluginLauncher) {
            Xrm.Utility.openEntityForm(“salesorder”, newPluginLauncher.new_OrderId);
        },
        function (error) {
            alert(error.message);
        }
    );
}

CRM 2011: trigger a plugin from a stored procedure

No, it’s not possible, you’re dreaming. Or is it? Well, sort of. What’s possible is the following:

  1. I have created an entity called “new_pluginlauncher”. I have made a plugin firef upon the creation of a record of this entity, from which  I can do anything, depending on the parameters passed when creating the record. I can do things such as win or reopen a quote, create an order, close a case, well whatever really. As long as the correct guids and parameters are set upon record creation.
  2. I have created an SSIS package, based on Kingswaysoft,  that does that: create a record of that new_pluginlauncher entity
  3. I have deployed the SSIS package appropriately in the SQL Server instance
  4. I have created a stored procedure that calls that SSIS package

Bingo! From now on, I can access plugin code, on demand, from a SQL Server stored procedure.

And the best thing is: this is completely supported.

 

Here’s the code of the stored procedure. Pay attention to allowing xp_cmdshell.

ALTER Proc [dbo].[proc_CreatePluginLauncher]
@entitytype varchar(20),
@actiontype varchar(20),
@recordid varchar(50)
as
begin
declare @command varchar(8000),
@packagelocation varchar(1000),
@packagename varchar(200),
@param varchar(2000)
set @packagelocation = ‘\SSISDB\CRM\CreateCRMPluginLauncher\’
set @packagename = ‘CreatePluginLauncher.dtsx’
set @param = ‘select ‘ +
@entitytype + ‘ as EntityType, ‘ +
@actiontype + ‘ as ActionType, ‘ +
@recordid + ‘ as RecordGuid’
set @command =
‘dtexec /ISSERVER ‘ + @packagelocation + @packagename + ‘ ‘ +
‘/set \package.variables[User::SQLCommand].Value;”\”‘ + @param + ‘\”" ‘
—-now execute dynamic SQL by using EXEC.
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @command
select @returncode
end

CRM 2011: Add javascript library to ribbon button

Say you want to use CrmRestKit or any other library from a ribbon button click, within an associated view. You can add the library to the parent entity form or to the associated entity, it won’t work.

As I found here, what you need to do is, in the command of the button click, using ribbon workbench for example (or directly in the xml), as follows.
The main thing here is to add the required libraries, and assign them isNaN as function name.

 

<Actions>

<JavaScriptFunction Library="$webresource:new_json2.js" FunctionName="isNaN" />
<JavaScriptFunction Library="$webresource:new_sdk.rest.js" FunctionName="isNaN" />
<JavaScriptFunction Library="$webresource:new_task_formscripts.js" FunctionName="Task_Update">
<CrmParameter Value="FirstSelectedItemId"/>
</JavaScriptFunction>
</Actions>

Windows is dying

Everyone can see that now. Windows 8 is a disaster in terms of sales. Windows 8.1 has a very limited installed base. Microsoft is in trouble in that regard. Why is that?

Some say it’s the “best Windows version ever“. Well, let me totally disagree on that. It’s a failure. Here’s why:

  • Frustrating for tablet users: I own a Surface 1. It’s a nice tablet for surfing the web.
    • Except when you have hover menus (fixed in 8.1): you could simply not access the menu. It took them a whole year to fix that. Imagine the frustration: tap the menu, see it display for a split second, then disappear. And you just can’t surf to your page. And you need to get the laptop, wait, etc. A whole year to fix that!
    • Except when you need to change some settings that are available only in the desktop. Small menus, not at all touch oriented. How do you call that a tablet OS then?
    • Except when you want to stream music to a bluetooth device. Good luck to find the exact menu. Why not ask the user to type it in command line while they’re at it?
    • Except when you want to stream video to your xbox: good luck with that one as well.
    • Except when you want to stream IE to your tv: good luck with that one. I love to see that happen so easily with an ipad.
    • Except when you are looking for your home banking apps in the store, and for any truely useful app in that regard
    • Etc…
  • Frustrating for the desktop user:
    • Forced to go through a touch oriented UI as main UI. Why ?????

The “everything at once” concept can probably work, but the realization is terrible. My advice for now, and I like Microsoft and own some of their devices, is don’t get one. A Surface is just too hard to love. The hardware is so beautiful, but the software is a failure. No wonder that the whole Windows 8 management team is basically out. It’s actually a relief to see that someone, up there, actually has some common sense and acknowledges that Windows 8 is nothing but non sense.

I am also sick and tired of reading blogs about the next Windows’ new “features” that are always limited to: “The back button will be back!” or  “Floating windows in metro mode!”. What the f… Can we talk about true features here? Can we talk about innovation? Not just some ui gimmick going away and then back with every new release?

I want to the next Windows to be “just great”. To be smart, no non sense, useful and usable. Come on guys, talk to people before designing non sense stuff. What do you think? That putting colored squares is about to get everyone excited? Come on now…

And, this is your last chance to deliver something good. Windows 7 was ok, but not good, sorry. Nothing wow in there. But, it just works, and that’s a start. Competition is moving fast. Chromebooks are a joke and will not go beyond a certain level, there is no potential for this. But Android laptops are a different story. You guys want to sell Windows when Android is free? Fine, but then help oem’s get something for their money, something amazing, with no flaw.

Windows must keep on dominating the pc market, even if that market is decreasing. And windows must make a dent in the mobile market. Or it will die. So stop frustrating your users, finish the product, and make it good.

CK Editor and Rollup 15: problem

Apparently CRM 2011 UR15 has an impact on the way CRM fetches web resources from the server.
Since I installed it this morning, I get a JS error on my ckeditor enabled form, and a white area instead of my nice wysiwyg…
The console shows 404 errors, and CRM trying to get js and css files from the userdefined url, instead of webresources…

So what I did is :

  1. Delete all web resources related to ckeditor, as they are not necessary anymore
  2. Create a folder named by the organization in C:\Program Files\Microsoft Dynamics CRM\CRMWeb
    1. If your org is “SalesForce”, then the folder is: C:\Program Files\Microsoft Dynamics CRM\CRMWeb\SalesForce
  3. In there, create another folder called “userdefined”
  4. In there, copy all the ckeditor files (styles.css, config.js, all folders, everything)

Bingo, it works!

And the webresources are suddenly much less cluttered…

CR 2011: refresh grid – ur12

Since UR12 (update rollup 12), in CRM 2011, the crmGrid.refresh() function doesn’t exist anymore.

Here’s how to do it now:

document.getElementById(‘crmGrid’).control.refresh();

 

CRM 2011: Display a picture stored in note on a form

With CRM, you can store a picture as a file attached to a note, and display that picture on the form. This is quite handy!

1/ First, create a wab resource of the picture type, with a default picture, and add it to the form

2/ Maje sure you use the CrmRestKit and it to your form

3/ Then, add this code in the onLoad JS:

function updatePicture(imageTitle, controlName) {
    var entityId = Xrm.Page.data.entity.getId();
    if (entityId) {
        var cols = ["AnnotationId", "DocumentBody", "MimeType", "NoteText"];
        var filter = “ObjectId/Id eq guid’” + entityId + “‘ and substringof(‘” + imageTitle + “‘,NoteText)”;
        var note = GetFirstRecord(“Annotation”, cols, filter);
        if (note) {
            var src = “data:” + note.MimeType + “;base64,” + note.DocumentBody;
            var pictureControl = document.getElementById(controlName);
            pictureControl.setAttribute(“src”, src);
        }
    }
}
function GetFirstRecord(entityName, columns, filter) {
    var record = null;
    CrmRestKit.ByQuery(entityName, columns, filter, false)
        .fail(function (xhr, status, e) { alert(‘Error’); })
        .then(function(data) {
            if (data != null && data.d != null && data.d.results != null && data.d.results.length > 0)
                record = data.d.results[0];
        });
    return record;
}
And you’re done!

Embed a nice wysiwyg html editor in CRM 2011: ckeditor

TinyMCE is not alone in this world, and I find ckeditor to be really nice.
By the way, I find it to be much nicer than htmlbox, which is however referred in a nice blog post that was an inspiration for this post.

A few notes:
- This example requires jQuery
- Don’t forget to publish all at the end!
- With the standard edition, 3 languages, approximately 65 web resources were created.
So how to install it in CRM 2011 (works with rollup 12):

Step 1/ Download ckeditor:

Choose the package that suits you best: basic, standard or full, or custom. I prefer custom to limit the number of languages downloaded.

Step 2/ Remove languages from the plugins.

Even if you go custom and select only English, some plugins will still have a lang folder, with 50 js files, one per language. Remove the unecesseary ones.

Step 3/ Update ckeditor.js to make it work in the CRM context.

To retrieve some files, by design, ckedtor adds a “?t=DAED” or something at the end of the file urls, as some kind of timestamp. CRM will return a fault exception when adding url parameters to webresources url. Thus remove this code from the file :

&&(b+=(0<=b.indexOf(“?”)?”&”:”?”)+”t=”+this.timestamp)

to get it to work in CRM.

Step 4/ Use the webresourceutility from the sdk:

Open the solution in Visual Studio, compile it, then  run the exe file in the bin folder. From there, create a new package, select the ckeditor folder, select all files to create new web resources, and create them!

Step 5/ Put this code in an onLoad method of the form.

Say your field is called new_html.

function onLoad() {
Xrm.Page.getAttribute(‘new_html’).setSubmitMode(‘always’);
$(‘#new_html_d’).append(
‘<textarea id=”wysiwyg” style=”height: 100%;” name=”wysiwyg”>’ + $(‘#new_html’).val() + ‘</textarea>’
);
$(‘#new_html_d > table’).hide();
CKEDITOR.replace(‘wysiwyg’);
}

What this function does:
- Submit mode: because CRM will not always be aware that the fied’s content has changed.
- Append text area: this will be the element to which we apply the wysiwyg editor.
- Hide the original field
- Apply the editor

Step 6/ Now the function onSave

function onSave() {

var data =CKEDITOR.instances.wysiwyg.getData();
Xrm.Page.getAttribute(‘new_html’).setValue(data);
return true;
}

This function obviously gets the text from the editor, and sets it in the original field, as it will be the one saved in the DB.
This is why we need to set submit mode to always. Indeed CRM ignores the modifications in ckeditor, and the content of new_html is never changed before we click save. So when save happens, we need to make sure that it is submitted.

Step 7/ Add the events in the form

Form Properties, onLoad and onSave events, nothing fancy here.

Step 8/ Add js file in the form

Don’t forget to add ckeditor.js web resource in the js files of the form!
Publish all and you’re done!

Apple buying Primesense: not a rumour after all

So my source was wrong. I’ll let him know as soon as today that his intel is of poor quality…

Now on the buyout itself: Apple preparing Apple TV then? I mean what else? Or a game console? Possible…
Besides these 2 things, where could Apple fit such a camera? I can’t find a decent answer.

So this leads me to the conclusion, about Apple schedule, that Apple TV won’t see the light of stores before 2015.

Soooo, this leaves us with the iWatch in 2014. Or does it?