Incorrect SQL query when export data using an Advanced find date query

Just spotted an interesting bug with a query I was running using Advanced find.

 

My query was display all opportunities that are open and closing in the next 4 months. The results were returned correctly via the advanced find search, but when I exported the data the SQL definition was incorrect.

 

The original query  string looked like this:

 

where ((opportunity0.statecode = 0 and  opportunity0.estimatedclosedateutc >= dbo.fn_NextXMonth(GetUTCDate(), 4) and opportunity0.estimatedclosedateutc < GetUTCDate() ))

 

If you notice the >= and second < are the worng way around, the query should of looked like this:

 

where ((opportunity0.statecode = 0 and  opportunity0.estimatedclosedateutc < dbo.fn_NextXMonth(GetUTCDate(), 4) and opportunity0.estimatedclosedateutc > GetUTCDate() ))

 

Notice also I removed the first = as SQL interprets dates as Date Time and not just Date so my returned results included records that fell out the date range.

 

So my workaround was to manually update the SQL definition in my excel report and save this as a standard report.

Advertisements

Google Maps integration

Place the code in the on load event, change the address fields to your applicable fields, populate the country field if not defaulted with teh right country and then update the iframe source details. You make want to add a checkbox on the map tab to control when the below code is applied to the iframe as you do not want the map loading everytime you open up your record (slows down the loading of the record). Happy Mapping!!

var CRM_FORM_TYPE_CREATE = 1;

var CRM_FORM_TYPE_UPDATE = 2;

var LiveSearchURL = "";

 if(IsOnline())

{

                switch (crmForm.FormType)

                {

                                case CRM_FORM_TYPE_CREATE:

                                                // Point to the Microsoft Live Search home page when creating an account

                                                LiveSearchURL = "http://maps.google.com&quot;;  

                                                break;

                                 case CRM_FORM_TYPE_UPDATE:

                                               // Use Live search with this account’s name

                                                LiveSearchURL = http://maps.google.com/maps?q=;

                                                // Encode the account name and add it to the search string

                                                                LiveSearchURL += crmForm.all.address2_line1.DataValue;

                                                LiveSearchURL += " ";

                                                                LiveSearchURL += crmForm.all.address2_line2.DataValue;

                                                                LiveSearchURL += " ";

                                                                LiveSearchURL += crmForm.all.address2_city.DataValue;

                                                                LiveSearchURL += " ";

                                                                LiveSearchURL += crmForm.all.address2_country.DataValue;

                                                break;

                }

}

 

// Set the IFRAME’s URL

crmForm.all.IFRAME_MAP.src = LiveSearchURL;

Defaulting the search criteria of a lookup

Option 1 – Hard coding the search criteria with a text value

 

crmForm.all.INSERTLOOKUPFIELD.additionalparams = "search=INSERTTEXTSEARCHCRITERIA";

Option 2 – Filtering the lookup search with a value from another lookup

For example when you want to filter the primary contacts lookup on an Account with only the contacts associated to that account by default. The code is split into a function and then  the code to call the function.

Function

document.FilteredLookup = function(criteria, lookup)

    {

    if(criteria == null || lookup == null)

        {

        return;

        }

     var filterCriteria = IsNull(criteria.DataValue) ? "" : criteria.DataValue[0].name;

     lookup.additionalparams = "search=" + filterCriteria;

    }

Code to call the function 

document.FilteredLookup(crmForm.all.INSERTFIELD, crmForm.all.INSERTFIELD);

 

 

Microsoft CRM Data Migration Manager Tips and Tricks

Microsoft CRM Data Migration Manager Tips and Tricks

 

1. Status and Status Reason values need to be converted to the number value before using data migration manager – see http://rc.crm.dynamics.com/rc/regcont/en_us/OP/articles/statestatus.aspx for default values.
2. When importing multiple related files i.e. Assigning Opportunities to Contacts, you have to ensure you reference a mandatory field in the mapping between the two entities, using Contact Full Name will not work.
3. Ensure date fields are correctly formatted to fit with the date  system settings
4. If you want to load in new picklist values at point of data migration then ensure you tick the box on the first step of the migration to update CRM with any new values
5. Review your data before you load it, if duplicates exist and you migrate data to these records migration manager will fail as it will not be able to uniquely identify the correct record to associate the data to
6. Name your imports intelligently as you will you use these names to delete the data if the migration partially fails
7. If importing data with a link to a system user, Microsoft CRM does not automatically match say the owner field to your specified user. You have to include as one of your files a user csv file that is an export of the users set-up in the CRM system, Migration manager will then ask you which field in this file maps to  a user set-up in the system and then which field from the file to use as the relationship to the user record in your source files i.e. I always do a vlookup in the source files of owner and replace with the domain login name as stored in CRM. I will blog later on this in more detail!
8. If you plan to import in contacts and your contacts are not all unique as a tip create a new field in your database called DM Unique key and make this field mandatory, then populate your source files with a unique number for each of your contacts and use this value as your mapping value between records i.e. Importing Accounts and Contacts where the Accounts reference a primary Contact, add to the Accounts csv a new column that references the new Contact DM unique key and use this to map the primary contact to a contact in the system.
9. Microsoft CRM has an import maximum file limit set in the config file ensure your migration files are below this
10. Before Migration remove any line breaks, " and , from any text field
11. Ensure that your text fields do not exceed 3000 in character length
12. Allow plenty of time to prep, test and load your data into CRM, it takes longer than you think and you always hit errors with data  cleanliness

 Hope this helps, have fun!