Simple frustrations and workarounds

While working on an older install of CRM and writing some reports, I had to use VS 2005 for SSRS and it was a frustrating experience. For example, when working on a report design and trying to select a textbox, it seems like you need to click up to 3 times to make it select (sometimes more) so that you can just get the properties. I think this is because the first click will select the Table control row, the second click may select the textbox, or it will select the background cell. The third click will probably select the textbox.

So I have learned some alternative strategies to use the SSRS GUI:
1. When selecting an object, it is easier to use a fence rather than click on it. the Fence does not need to surround the object, just touch it so you can make very small gestures (click-drag) to select single items.
2. Sometimes even 5 clicks will not select the object because VSS is confused. Press the Esc key and try again – that usually works for me.
3. Use the document outline (on the left pane) to select objects – this is a good reason to name them. It also makes it clear what the correct container is for each object – very important to know when your report preview is not laying out the way you expect.
4. Do all my work within a single row of the table before working on other rows or headings because clicking outside the current row will force me to click two more times to get to the objects within that row
5. Use the fence to select many objects at one time, the use one of the many toolbar object alignment features that are actually very good (well worth spending 30-60 minutes playing to figure out how they will do your work for you).

MS CRM field events with bulk update

This is a good news /bad news story. I have a form that is heavily modified and has 1k lines of JavaScript (JS) code behind it. The good news is that it has a bunch of functionality that really makes the system sing - the bad news is that when you put code behind each field's OnChange event using form customization, then those fields are no longer usuable for bulk edit. This functionality is by design because CRM cannot execute your JS code for each record during a bulk update. In my situation however, I don't care; I still wanted both the customization and bulk edit.

My solution is to override the OnChange events without using the form customization UI. When my form loads, I import & execute a stand-alone JS file (in the forms OnLoad event) that contains functions that define OnChange events for fields. for example:

crmForm.all.new_investor.OnChange = function(){
alert("got here");
}

This code will execute when I change the investor field, but will not prevent the bulk edit from working.

Quickbooks XML List ID relationships to Exported IIF refnum

I have a client that has been using exported customer lists in IIF format for a while in order to interface with an external system that creates invoices in IIF format.

As we are getting ready to move them into MS CRM with a QB link solution from Inogic, we discovered that the Inogic system uses the QuickBooks XML interface which exposes the internal ListID of the customer, and not the QB "refnum" used by their other system (via IIF file).

So the problem was that the old external system had a mass of data that used the QB Refnum field (from customer IIF file), but there was no way to correlate that with the XML version of the QB interface so that we could convert their data to the Inogic data. after a bit of playing with numbers, we discovered that the XML ListID's of the customers had a pattern and found that the first half of the ListID (before the hyphen) was really an encoded version of the Refnum.

To decode it, we used the following procedure:

1. Take the ListID and break it into 2 parts from either side of the hyphen and call them Part1 and Part2.
2. If Part1 is less than 7 characters, pad the left with zeros until it is 7 characters.
3. If the right 4 characters of Part1 ends with 0000, 0001,0002, or 0003 then get the first three characters from Part1 and convert them from Hex to decimal - that is the Refnum
4. If the first character in Part1 is "8", then use the last 3 characters from Part1 and convert them from Hex to Decimal and that is the Refnum.

Now that we have a Refnum for each customer that matches the ListID on the XML interface, we can convert all the data in the external system to use the correct customer linkage in CRM.

MS CRM Autofilter gotcha

I am building a report using SSRS that I intend to use as an auto-filtered report inside of MS CRM (see http://blogs.msdn.com/b/crm/archive/2009/03/06/microsoft-dynamics-crm-pre-filtering-tips.aspx). The report pulls data from several views. Everything seemed to be working fine in development until I imported the report into CRM, then parts of the query seemed to stop working.

If you read the end of that article, it points out that all the entities referenced in the FROM or JOIN clauses will have a default filter applied to them that will limit what is included in the query to only records that were modified in the last 30 days.

In my case, I was using a parent entity with 4 outer joins, and I was only retrieving some of the data because some of the related tables had data that was not modified recently.

My solution to this problem was to create a single report that uses only one view with the CRMAF alias, and it used a sub-report that did not have the CRMAF alias on the views. This permitted me to associate the parent report with the entity, and pass the ID’s to the sub-report where there was no filtering.

In my case, I was creating a custom invoice for a custom invoice entity, so I created my parent report with just the one filtered view and gave it an alias of CRMAF_custominvoice. I embedded the sub-report in a table (tablix for VS 2008) control detail band, and gave the sub-report the ID value to pass as a parameter. In the sub-report, I had the rich and complex SQL that I wanted using filtered views, but not using the CRMAF alias.

Brokered Deposits - FDIC is wrong

I recently learned that the FDIC is working on implementing rules to limit (or prevent) brokered deposits at banks, ostensibly to prevent banks from being victimized by nefarious deals. Once again the government is doing its level best to show they are doing something even if it is wrong.

For those of you that do not know what a brokered deposit is, here is a link that explains it nicely:http://www.epfc.com/issuing_cd/faqs.html

In this case, our legislature has put too much power in the hands of the FDIC and they are going in the wrong direction. Rather than making the banks act responsibly and put rules into place that would hold individuals responsible for not doing their fiduciary due diligence, they are just about to regulate an entire sector of business (brokers) out of existence. Does this make sense?

If this rule is implemented, it will still be OK for you to buy a CD from a bank in bad financial condition, as long as you buy it directly. Think of it like buying a used car; are you in any better position if you buy it from an individual instead of using a dealer?

I would rather see the FDIC do their job and monitor banks that are in bad condition and make that information available to anyone. When a bank is in bad condition, cut them off so that they do not create a Ponsi-scheme-like situation. And if they do continue to transact bad deals, hold someone accountable and kick him out of the business or throw him in jail.

The more government regulation you create, the more people will rely on the goverment to keep them safe. People will have expectations that all investments are safe because the government said so. If the goverment keeps its nose out, then it would be up to the buyers and sellers to do their due diligence and act responsibly.

Take this story about traffic lights for example from John Stossel . The summary is that when you remove traffic lights, the drivers of cars expect that each intersection is a dangerous place and they will proceed through it more cautiously. As it turns out, accidents decrease to nearly zero and there are fewer traffic jams because cars rarely need to stop. Additionally, pollution in those areas have dropped significantly.

If you have a bank that has too many accidents, then take away their license and get them off the financial road.

Open custom entity windows using ETN instead of ETC

I am connecting an Adobe Flex application to MS CRM and one of the tasks we need to do is open a CRM window of a custom entity. If you look at the URL of the custom entity, it contains a parameter that says "etc=1000x" where the number is a reference to the custom entity edit form. If you are developing on the production system (not a good idea) that number is safe to use. However, if you develop on a different system, there is a good chance that the "etc" number will be different.

I recommend that you use the "etn=new_customentity" parameter instead of the "etc=" because the name of the custom entity will always be the same between the development and production systems. The value for etn will be the custom entity name which you will find on the entity customization form.

Helpful tip: if you want to see the full URL of a crm form, press F11 to make the browser full size, then move your mouse to the top of the window and the URL bar will appear - then you can copy the URL and paste it into an editor for your inspection.

Please let me know if this helps and leave a comment.
Thanks!

Flex XML - easy to use!

Today I am working on a project that uses an Adobe Flex application to call a .NET back end web service to call some SSRS reports. For one reason or another, I needed to pass an XML string from the front end app to the back end, and found it was incredibly simple. In one of my Actionscript files of my app, I created an XML type variable and added the parameters for the report. The code looked like this:

             var rp:XML = <reportParameters/>;

             var service:CrmService = new CrmService();

             service.addgetAllCustomersEventListener(customerResult);

             service.addEventListener(FaultEvent.FAULT,serviceFaultHandler);

           

             var custreq:GetAllCustomers_request = new GetAllCustomers_request ;

             rp.appendChild(<start>{startdt.text}</start>);

             rp.appendChild(<end>{enddt.text}</end>);

             custreq.reportParameters = rp.toXMLString();

             service.getAllCustomers_request_var = custreq;

            service.getAllCustomers_send();

As you can see, you dont even need quotes around the XML tags, you can do free-form mixing of the tags with the Actionscript code. The variables appear in the curly braces {}. The method called toXMLString() converts all this into a string that looks like this:

<reportParameters><start>1/12/2010</start><end>3/14/2010</end></reportParameters>

The really exciting part is that the XML object managed the containing <reportParameters> tags so I didnt have to worry about closing it properly.
I really like this because now I do not have to write a lot of tedious code with all the open and closing quotes around each literal string and concatenating them together. I hope someone finds this useful.
I hate writing code where I have to carefully manage all beginning and ending

I watched in horror last night as Anderson Cooper hosted Mike Moore pontificating his socialistic, feel-good agenda without any balanced point of view. He is a film maker that gets people to listen to him by demonizing some stereotypes. Shouldn't AC, as a journalist, be asking Moore some hard questions instead of just giving him a podium so that he can rant?

Fundamental economics has proven that if a business is making a profit, then more competition will spring up and squeeze the profit margins so that everyone benefits. And the key to making that work is when individual people are making choices about what is best for them. By having the insurance companies bypass the individual and work directly with providers, we lose the efficiencies of a working market. And contrary to Mr. Moore’s claims, as reported by CNN (http://www.cnn.com/2009/POLITICS/07/06/canadian.health.care.system/index.html), government healthcare in Canada is not only incapable of providing a service as good as , people are dying to get service.

When Pres. Obama was campaigning for office, I liked that he espoused a belief that individuals were the solution to our problems, but so far the majority of politicians in DC have completely ignored the individual as a solution, and have been focused on building new institutions that they can control.

Yes, the US healthcare system does need to be changed, but we need more people to take responsibility for taking better care of themselves, and becoming smarter consumers.

Compare previous field values in CRM 4 using Javascript

A customer asked me to have a pull-down field populate a "comment" field (it was actually a large varchar field) with the text the user selected in the pull-down (this was to support a legacy data integration issue). That seemed easy enough, but there was a catch: if the user changes the pull-down to another value, then they wanted the previously selected text to be removed from the comment field.

I am not going into the details of how to add JS to CRM code, but I can recommend looking at http://blogs.inetium.com/blogs/azimmer/archive/2009/03/08/five-tips-for-productive-form-scripting-in-crm-4-0.aspx


First, I needed a function that would add and remove text from the "comment" field. I loaded this in the form OnLoad event:

function updateComment1 (tf, cComment){
try {
 if (tf == true) {
     if(crmForm.all.new_comments.DataValue != null && crmForm.all.new_comments.DataValue.length > 0){
         crmForm.all.new_comments.DataValue =  crmForm.all.new_comments.DataValue + Chr(13)+ cComment;
     }else{
         crmForm.all.new_comments.DataValue = cComment;
     }
 }else{
     crmForm.all.new_comments.DataValue =  crmForm.all.new_comments.DataValue.replace(cComment,"");
     crmForm.all.new_comments.DataValue =  crmForm.all.new_comments.DataValue.replace(/\r\n\r\n/m,"\r\n");
 }

}catch(err){
 // do nothing - the comments are empty and we dont care
}
}

This code will take two arguments: one will tell it what to do, and the second is the string to either insert or remove from the faux comment field. If the first argument is true, then the string is inserted into the comment field, otherwise the string is removed (if it exists). The caveat to this is that if the user changes the spelling of the string, then this will not remove it.

Next I created a function to accept the pull-down field as an argument and handle sending the update to the first function:

function updateComment2 (oField) {
 updateComment1(false, oField.prev_value) // this will remove the old text from comments
 if(oField.SelectedText.substr(0,7)!="No Pref"){
     updateComment1(true, oField.SelectedText) // this will put the new text in the field
 }
 oField.prev_value = oField.SelectedText;    // reest the previous value for the next time around
 
}

In this system, the pull-down has a default value of "No Pref" which means the user did not have a preference for this item, and the reference to this should be removed from the comment field. Notice that there is also a property of the field called "prev_value" which is not normally part of MS CRM form fields. When the form loads, I added a couple lines of code to capture the current state of these controls:

crmForm.all.new_preference1.prev_value = crmForm.all.new_preference1.SelectedText;
crmForm.all.new_preference2.prev_value = crmForm.all.new_preference2.SelectedText;

Using this trick will allow you to add any number of new properties to a form field.

Then in each of the fields OnChange events, I added a call to my updateComment2 function. For example, the OnChange event of the new_preference1 field would look like:

updateComment2(crmForm.all.new_preference1);

When the user changes the pull-down, it calls my custom function updateComment2 and passes a reference to itself which I will use to inspect its previous value and remove the old the string from the comment field before adding the new selected text back in.

I could have combined some of this code, but have it broken down this way because I am using these functions in other places in the form by checkboxes, radio buttons and such and this gives me some flexibility to re-use code.

SSRS Select permission was denied on the object

Note to self: When I create a SSRS report for CRM, always use "filtered" views in the query for the report - never use the other tables or views. Here is the situation:

I had created a report for use in an Iframe of CRM, and it was getting the error "Select permission was denied on the object ..." and it named one of my tables in CRM. The query ran just fine when I was logged in as the administrator, but when the users got it, it gave me that error. Permissions for objects (entities) in MS CRM are applied to the filtered views in order to enforce access controls to the data. When you view a CRM entity form and the browser tries to render your report in the Iframe, SSRS is using your network credentials to determine which records you are allowed to see.

Dont try to take a shortcut and use base entities or non-filtered views when creating a report - you will be wasting your time. If your database is optimized properly, the report should run just as fast when using the filtered views.

SSRS query execution failed for dataset

I had written a nifty new report that I was implementing for a client in CRM 4.0 to be displayed in an Iframe in the Account entity form. After uploading the report using SSRS report manager (http://server_name/reports) and customizing the account form, it worked great for me, but the users got the error "Reporting Services Error...An error has occured during report processing. Query Execution failed for dataset 'DataSource1' "

The first thing to do is turn on the "Enable Remote Errors" feature of SSRS.
1. Open SQL Management Studio and connect to your instance of Reporting Services
2. Right click on the server name in the object explorer and choose properties
3. Click on the Advanced option (on left side) then change EnableRemoteErrors to True

Open your browser and re-run the report to see the actual error you are getting.

SSRS security with CRM 4.0

So I am knocking my brans out trying to add a couple security groups to a custom report folder I created on SSRS and the answer is deceivingly simple.

The problem was that after I created the folder, it needed permissions for a couple additional groups: ReportingGroup and SQLAccessGroup. when I typed one of those names in the New Role Assignment for my folder security, it said the group did not exist. The solution is to copy the group name _and_ the GUID that follows it and paste it into the new group name field.

CRM Auto Filtering

I have been reading up on MS CRM auto filtering which allows you to create a report that you can associate with an entity and then use it on a view or a single record (http://blogs.javista.com/2009/03/18/microsoft-dynamics-crm-pre-filtering-tips/). I started creating some reports this way, but then decided that I also wanted to call the reports programatically and for that I would need parameters. My solution is to make my actual report work with parameters, then create a 'wrapper' report that uses the auto filtering capability, then calls the real report as a sub-report.