This blog post is for all developers of all levels that are looking for ways to improve efficiency and time-saving ideas. It begins by providing some background on me and how my experience with Microsoft Excel has evolved and aided me as a developer. Next, we cover a scenario where Excel can be leveraged to save time. Finally, we go over a step-by-step example using Excel to solve the problem.

Background

As a teenager growing up in the 80s, I was fortunate enough to have access to a computer. One of my favorite applications to use as a kid was Microsoft Excel. With Excel, I was able to create a budget and a paycheck calculator to determine my meager earnings from my fast food job. As my career grew into software development, leveraging all of the tools at my disposal as a solution against repetitive and mundane tasks made me more efficient. Over the years, colleagues have seen solutions I have used and have asked me to share how I came up with and implemented them. In this two-part blog post, I will share the techniques that I have used to generate C#, XML, JSON, and more. I will use data-loading in Microsoft Power Apps and Dynamics as a real-word example; however, we will need to start with the basics.

The Basics

Before going into the data-loading example, I wanted to provide a very simple example. Keep in mind that there may be more effective solutions to this specific example that do not use Excel; however, I am using it to illustrate this simple example. Let’s say you had a data model and a contact model that, for the most part, were the same with the exception of some property names, and you needed to write methods to map them. You know the drill:

var contact = new Contact();
contact.FirstName = datamodel.firstName;
contact.LastName = datamodel.lastName;
contact.PhoneNumber = datamodel.phoneNumber;
contact.CellPhone = datamodel.mobileNumber;

Not a big deal, right? Now let’s say you have a hundred of these to do and each model may possibly have 50+ properties! This would very quickly turn into a time consuming and mundane task; not to mention you would likely make a typo along the way that another developer would be sure to let you know about in the next code review. Let us see how Excel could help in this situation.

In this scenario, the first thing you will need is the row data for the contact and data models. One way would be using the properties. Consider the classes below:

Use Properties to Identify Classes

  1. Create 3 Excel worksheets called Primary, Secondary, and Generator
  2. Copy/paste the property statements from Contact into Primary worksheet and ContactDataModel into a Secondary worksheet.
  3. Select Column A in the Primary worksheet
    Create three Excel Worksheets
  4. In Excel, select the Data tab and then Text to Columns
  5. Choose Delimited, then Next
    Choose Delimited
  6. Uncheck all boxes and then check the Space checkbox, then Finish
    Uncheck All Boxes
  7. Your worksheet should look like the following:
    Sample of Worksheet
  8. Repeat 3-7 with the Secondary worksheet
  9. Select cell A1 and then press the = key
  10. Select the Primary worksheet and then cell D1
  11. Press the Enter key, you should return to the Generator worksheet and the text “FirstName” should be in cell A1
  12. Select cell B1 and then press the = key
  13. Select the Secondary worksheet and then cell D1
  14. Press the Enter key, you should return to the Generator worksheet and the text “firstName” should be in cell A1
  15. Drag and select A1:B1. Click the little square in the lower-right corner of your selection and drag it down to row 25 or so. (Note: you would need to keep dragging these cells down is you added more classes.)
    You will notice that by dragging the cells down, it incremented the rows in the formula.
    Incremented Rows in the Formula
    Press CTRL+~ to switch back to values.
  16. Select cell C1 and enter the following formula:
    =IF(A1=0,””,A1 & “=” &B1&”;”)
    As a developer, you probably already understand this, but the if statement is checking to see if A1 has a value of 0 and simply returns an empty string if so. Otherwise, string concatenation is built.
  17. Similar to an earlier step, select cell C1 and drag the formula down to row 25. Your worksheet should look like:
    Select and Drag Formula
  18. You can now copy/paste the values in column C into the code:
    Copy and Paste Values into Column C

As you continue on, Excel keeps track of the most recent Text to Columns settings used; so, if you pasted another set into the Primary and Secondary worksheets, you should be able to skip steps 1-5 for remaining classes. In the sample class file and workbook, I have included Address models as an illustration.

Next Steps

This example has covered the basic concepts of code generation with Microsoft Excel: extracting your data and writing the formulas that generate the necessary code. Depending on what you are trying to accomplish, these requirements may grow in complexity. Be sure to consider the time investment and payoff of using code generation and use where it makes sense. One such investment that has paid off for me is data loading in Microsoft Power Apps which we will cover in the next post: Code Generation with Microsoft Excel: A data-loading exercise in Microsoft Power Apps.

Download Example Workbook

Download Address Models

Executive Summary

This blog post is for Power Apps developers as well as end-users that are looking for a wildcard-like solution for Lookup controls in Microsoft Power App and Dynamics. It begins by describing the problem that a client had with finding records where only part of the name was known. Next, it covers the workarounds that were offered to the client as well as the client’s response. Next, the solution is explained in detail, including the necessary code and instructions for setting up within the Power Apps solution. Finally, a video of the final product is provided demonstrating how the implementation behaves.

The Problem

In 2019, I worked with a client that wanted to migrate a CRM system that had been developed in FileMaker (an Apple Subsidiary). The client was an Apple / Mac user through and through; however, he did like Microsoft Office products. He had researched Power Apps and wanted to migrate his system to it and integrate it with Microsoft Outlook. After spending a few months migrating the system to Power Apps the client was pleased but was a little frustrated with some of the user interface elements. He did not like the way that the Lookup controls functioned and claimed that it was not finding all the records.

After meeting with the client, I figured out the problem. In the example below, we are using fictitious company names. Let us pretend that we are adding an Account for a Contact and are having trouble remembering the exact Account name. We remember that the word “Generic” is in the name, but cannot remember the rest.

If you start by typing “gen” into the Lookup control, notice that the lookup only returns items that begin with “gen”.

Lookup Control

This scenario is what the client was upset about, but I had some workarounds for him.

The Workarounds

I explained to the client that if he entered a wildcard before typing the search term that it would return the results that he was expecting. I also informed him that he could configure and use Relevance Search to locate the name and then enter a full name into the Lookup control. The client found these workarounds to be frustrating and wished that it behaved like his FileMaker solution. He did not want to have to explain wildcards or relevance search to office staff and wanted the lookup to behave how he viewed to be correct.
If you start typing “*gen” into the Lookup control, it will return Accounts that contain “gen”, including “Advanced Generic Corporation”, the company we are looking for.

Relevance Search in Lookup Control

I put together a prototype using a Single Line of Text field with an Auto Complete control like below. When you enter “gen” into the Auto Complete control, it behaves like the client wanted, returning matches that started with or contained the term. The client also likes the fact that it highlighted the text that matched too. My initial prototype had the Lookup and the Autocomplete next to each other. The user could use the OOTB Lookup or use the Auto Complete control. Once a user was selected a record in the Auto Complete control, the Lookup would be resolved based on the record selected. Unfortunately, the client found this to be “clunky” and insisted that it work like user interfaces do on Apple products.

Auto Complete in Lookup Control

The Solution

I started working on a solution making these 2 controls work together to achieve the client’s expectations. The implementation would follow these rules:

  1. On load:
    1. If the Lookup was a value, show it and hide the Auto Complete field (note: the lookup is what is important). Otherwise, show the Auto Complete and hide the Lookup.
    2. Populate a list of all entity values to be used by the Auto Complete.
  2. When the Auto Complete changes, try to resolve it against a pre-populated list of Entity values. In this example, I use the Account name field, but it could be any entity.
    1. If there is a single match, set the Lookup field to that value, show the Lookup and hide the Auto Complete.
    2. If there is more than one match, alert the user and force them to use Lookup.
    3. If no match is found, do nothing.
  3. When the Lookup changes, if an item has been selected, hide the Auto Complete field. If not, then hide the Lookup and show the Auto Complete (i.e. if the user clears the lookup).

The JavaScript

There are a variety of ways the scenario could be achieved. I prefer to establish a single event on the form and wire up events within my JavaScript. I also broke this code up for cleanliness and reusability. For this blog post, I have all the JavaScript in a single file; however, some methods could be moved to a shared.js file and reused across multiple forms. Here is a breakdown of the code:

  • configureForm – a method called when the form is loaded. It sets up the on-change events for the fields and initializes the fields.
  • onAccountAutoCompleteChange – event handler for the on-change event of the Auto Complete textbox. It simply calls onAutoCompleteChanged with the necessary parameters.
  • onAccountLookupChange –calls the onLookupChanged with the necessary parameters and is used to initialize the fields.

These methods could be moved to a shared file if desired:

  • populateAccountSet –populates the accountSet array. It calls populateSet with the necessary parameters.
  • populateSet – populates an array with the desired entity field values. These values are used to resolve against when the user selects a value in the Auto Complete control.
  • onAutoCompleteChanged – performs the logic established above for the Auto Complete field.
  • onLookupChanged – performs the logic established above for the Lookup field.
var accountSet = new Array();
var formContext = null;
function configureForm(executionContext) {
    formContext = executionContext.getFormContext(); 
    populateAccountSet();
    formContext.data.entity.attributes.getByName('new_accounttext').addOnChange(onAccountAutoCompleteChange);
    formContext.data.entity.attributes.getByName('new_account').addOnChange(onAccountLookupChange);
    onAccountLookupChange();
}

function onAccountAutoCompleteChange() {
    onAutoCompleteChanged("new_accounttext", "new_account", "account", accountSet);
}

function populateAccountSet() {
    populateSet("account", "?$select=accountid,name", accountSet, "accountid", "name");
}

function populateSet(entity, query, set, idField, nameField) {
    if (set !== null && set.length > 0) return;
    Xrm.WebApi.retrieveMultipleRecords(entity, query).then(
        function success(result) {
            if (result.entities.length > 0) {
                for (var i = 0; i < result.entities.length; i++) {
                    set.push(
                        new Array(
                            result.entities[i][idField],
                            result.entities[i][nameField]
                        )
                    );
                }
            }
        },
        function (error) {
            Xrm.Utility.alertDialog(error.message, null);
        });
}

function onAutoCompleteChanged(autoCompleteField, lookupField, entityType, dataSet) {
    var value = formContext.data.entity.attributes.getByName(autoCompleteField).getValue();
    if (value !== null) {
        var valueLowerCase = value.toLowerCase();
        var matches = [];
        for (var i = 0; i < dataSet.length; i++) { if (dataSet[i][1] !== null && dataSet[i][1] !== undefined) { if (valueLowerCase === dataSet[i][1].toLowerCase()) { matches.push(dataSet[i]); } } } if (matches.length > 0) {
            if (matches.length > 1) {
                var friendlyEntityType = entityType.replace("new_", "");
                friendlyEntityType = friendlyEntityType.replace("_", " ");
                var alertStrings = {
                    text: "More than one record exists; please use default " + friendlyEntityType + " control."
                };
                Xrm.Utility.alertDialog(alertStrings);
                formContext.data.entity.attributes.getByName(autoCompleteField).setValue(null);
                formContext.data.entity.attributes.getByName(lookupField).setValue(null);
                formContext.getControl(autoCompleteField).setVisible(false);
                formContext.getControl(lookupField).setVisible(true);
            } else {
                var lookupVal = new Array();
                lookupVal[0] = new Object();
                lookupVal[0].id = matches[0][0];
                lookupVal[0].name = matches[0][1];
                lookupVal[0].entityType = entityType;
                formContext.data.entity.attributes.getByName(lookupField).setValue(lookupVal);
                onLookupChanged(autoCompleteField, lookupField);
            }
        }
    } else {
        formContext.getControl(lookupField).setVisible(true);
    }
}

function onLookupChanged(autoCompleteField, lookupField) {
    var lookupVal = formContext.data.entity.attributes.getByName(lookupField).getValue();
    if (lookupVal !== null && lookupVal !== undefined) {
        formContext.getControl(autoCompleteField).setVisible(false);
        formContext.getControl(lookupField).setVisible(true);
    } else {
        formContext.getControl(autoCompleteField).setVisible(true);
        formContext.getControl(lookupField).setVisible(false);
        formContext.data.entity.attributes.getByName(autoCompleteField).setValue(null);
    }
}

function onAccountLookupChange() {
    onLookupChanged("new_accounttext", "new_account");
}
 

Updating the PowerApps Solution

For this script to be used, you must first create a Web Resource for it. In this example, I created one called accountwildcard.

Create a Web Resource

Next, you need to add an event handler function to the OnLoad event; be sure to check the box Pass execution context as the first parameter. Add the library to the form and tell it to call the configureForm method for the OnLoad event.

Adding and Event Handler Function

Your form properties should look like this then you are done.

You will also want to make sure the labels for both fields are the same to make the transition look clean.

Make sure both labels are the same

The Final Product

As you can see below, the transition is smooth and seamless. When you type, the Auto Complete control serves up wild-carded results. After hitting tab or moving outside of the control, the Lookup is populated with the match and the Auto Complete is hidden. If you clear the Lookup, it hides itself and shows the Auto Complete again.

This is a specialized solution and I would recommend steering your client towards using a wild card in Lookup control; however, if you have a client that is insistent on this functionality, I hope this saves some time. The client I did this for is pleased with the results and has had no issues with it since.

To benefit the most from this post, you should understand the following concepts:

  • Dynamics CRM Workflows
  • DocuSign Integration with Dynamics CRM
  • DocuSign merge fields and merge-back

The Problem

I recently experimented with integrating DocuSign with Dynamics 365 — specifically, the merging of data into a DocuSign form and then writing the data back into Dynamics. After reading the DocuSign for Dynamics 365 CRM – 6.1 documentation I found that DocuSign Drop Downs and Radio Button controls are not supported for Dynamics merging and write backs. I started work on a solution that would use a Checkbox field in DocuSign and a Two Options field in Dynamics. I had all my text fields working correctly and assumed it would be straightforward as there were both Boolean fields.

I was disappointed to find out that the solution would not merge. After researching online and trying a few suggestions, I finally decided to add a temporary text field to my DocuSign form and see what Dynamics was putting into it, and found that the value was “Yes.” Then I looked at the form data in DocuSign…and it had the value “X.” I tried replacing the values for “Yes” and “No” in the Dynamics Two Options field with “X” and “O”, but that didn’t work either.

The Solution

I finally decided to change the “Yes” and “No” values to “true” and “false.”

This time, when the data was merged, the checkbox was checked!

And once the client receives the email, fills out the form, and the .pdf files are sent…this is when the ‘X’ we saw in the form data is used:

Finally, I verified it worked end-to-end by unchecking the box in Dynamics and saving the record:

After firing off the workflow to merge data in DocuSign form, the box is unchecked now:

Send the email off to be filled, check the box and add a new allergy:

Now, wait for the Dynamics envelope status workflow to complete. Check the record and the record will update successfully in Dynamics.

Conclusion

Albeit small, I’m surprised I didn’t find it documented. So if you’ve come across this issue working with DocuSign Checkbox fields and Dynamics 365, I hope this post saves you some time!

I recently encountered an issue when trying to create an Exact Age column for a contact in Microsoft Dynamics CRM. There were several solutions available on the internet, but none of them was a good match for my specific situation. Some ideas I explored included:

  1. Creating a calculated field using the formula DiffInDays(DOB, Now()) / 365 or DiffInYears(DOB, Now()) – I used this at first, but if the calculated field is a decimal type, then you end up with a value like 23 years old which is not desirable. If the calculated field is a whole number type, then the value is always the rounded value. So, if the DOB is 2/1/1972 and the current date is 1/1/2019, the Age will be 47 when the contact is actually still 46 until 2/1/2019.
  2. Using JavaScript to calculate the Age – The problem with this approach is that if the record is not saved, then the data becomes stale. This one also does not work with a view (i.e., if you want to see a list of client ages). The JavaScript solution seems more geared towards the form of UI experience only.
  3. Using Workflows with Timeouts – This approach seemed a bit complicated and cumbersome to update values daily across so many records.

Determining Exact Age

Instead, I decided to plug some of the age scenarios into Microsoft Excel and simulate Dynamic CRM’s calculations to see if I could come up with any ideas.

Note: 365.25 is used to account for leap years. I originally used 365, but the data was incorrect. After reading about leap years, I decided to plug 365.25 in, and everything lined up.

Excel Formulas

Setting up the formulas above, I was able to calculate the values below. I found that subtracting the DATEDIF Rounded value from the DATEDIF Actual value produced a negative value when the month/day was after the current date (2/16/2019 at the time). This allowed me to introduce a factor of -1 when the Difference was less than or equal to 0.  Using this finding, I set up the solution in CRM.

Excel Calculations

The Solution

  1. Create the necessary fields.
    Field  Data Type  Field Type  Other  Formula 
    DOB  Date and Time  Simple  Behavior: User Local   
    Age Actual  Decimal Number  Calculated  Precision: 10  DiffInDays(new_dob, Now()) / 365.25 
    Age Rounded  Whole Number  Calculated    DiffInDays(new_dob, Now()) / 365.25 
    Age Difference  Decimal Number  Calculated  Precision: 10  new_ageactual – new_agerounded 
    Age  Whole Number  Calculated    See below 
  1. Create a business rule for DOB; setting it equal to birthdate when birthdate contains data. This way when birthdate is set, the DOB is set automatically. This arrangement is necessary for other calculated fields.
    Business Rules
  2. Set up the Age calculated field as follow:
    Age Calculated Field

Once these three steps have been completed, your new Age field should be ready to use. I created a view to verify the calculations. I happened to be writing this post very late on the night of 2/16/2019. I wrote the first part before 12:00 a.m., then I refreshed the view before taking the screenshot below. I was happy to see Age Test 3 record flip from 46 to 47 when I refreshed after 12:00 a.m.

Age Solution Results

Determining Exact Age at Some Date in the Future

The requirement that drove my research for this solution was the need to determine the exact age in the future. Our client needed to know the age of a traveler on the date of travel. Depending on the country being visited and the age of the traveler on the date of departure, different forms would need to be sent in order to prevent problems when the traveler arrived at his or her destination. The solution was very similar to the Age example above:

The Solution

  1. Here is an overview of the entity hierarchy:
    Age at Travel Entities
  2. Create the necessary fields.
    Entity  Field  Data Type  Field Type  Other  Formula 
    Trip  Start Date  Date and Time  Simple  Behavior: User Local   
    Contact  DOB  Date and Time  Simple  Behavior: User Local   
    Trip Contact  Age at Travel Actual  Decimal Number  Calculated  Precision: 10  DiffInDays(contact.dobnew_trip.start) / 365.25 
    Trip Contact  Age at Travel Rounded  Whole Number  Calculated  n/a  DiffInDays(contact.dobnew_trip.start) / 365.25 
    Trip Contact  Age at Travel Difference  Decimal Number  Calculated  Precision: 10  new_ageattravelactual – new_ageattravelrounded 
    Trip Contact  Age at Travel  Whole Number  Calculated  n/a  See below 
  1. Create a business rule for Contact DOB; setting it equal to birthdate when birthdate contains data. This way when birthdate is set, the DOB is set automatically. This arrangement is necessary for other calculated fields.
    Business Rules
  2. Set up the Trip Contact’s Age at Travel calculated field as follow:
    Age at Travel Calculated Field

Once these steps have been completed, your new Age at Travel field should be ready to use. I created a view to verify the calculations.

You’ll notice that in the red example, the trip starts on 8/14/2020. The contact was born on 9/29/2003 and is 16 on the date of travel but turns 17 a month or so later. In the green example, the trip is also on 8/14/2020. The contact was born 4/12/2008 and will turn 12 before the date of travel.

Age at Travel Solution Results

Conclusion

While there are several approaches to the Age issue in Dynamics CRM, this is a great alternative that requires no code and works in real time. I hope you find it useful!

sharepoint 2013 logoI came across an interesting bug while trying to add a user the Administrators of a Search Service Application in SharePoint 2013. When I tried adding the user, and clicking OK, and error is returned: “User does not have permission to perform this action” along with a correlation ID. Further investigation in the ULS logs revealed that the problem was SQL permission related: “The EXECUTE permission was denied on the object ‘proc_MSS_GetConfigurationProperty’, database ‘SPSearch’, schema ‘dbo’.” Additionally performing a search fails and logs the error: “There was an exception in the Database. Please retry your operation and if the problem presists, contact an administrator.” (The error message has a typo too).  Read More…
sharepoint 2013 logoRecently, I encountered an issue with SharePoint 2013 search crawls where .pdf files smaller than 1 MB reported a warning: “The item has been truncated in the index because it exceeds the maximum size”. The default MaxDownLoadSize for documents in SharePoint is 64MB, which was more than enough the handle these relatively small .pdf files.

After I reached out to some co-workers; one suggested that the error might be a false-positive and the entire document had been crawled. I tested this by first searching for words at the end of the document and no matches were found; this would be expected if it were truncated. Next, I tried searching for text in the middle of the document, no matches were found either. I thought it must have truncated a lot of text and tried searching for text contained at the very beginning of the document. No results were found! So when the warning said it truncated the item, it had truncated the whole document. Read More…

sharepoint 2013 logoIntroduction

One of the many challenges that SharePoint developers face is returning meaningful search results that allow users to access information efficiently. Oftentimes, data retrieved from search could be more effective if we could modify it slightly. Other times, search results would be enhanced if we could include related information that does not reside within SharePoint. FAST for SharePoint 2010 provided pipeline extensibility which allowed us to modify content on the “pipeline” using a PowerShell script or a compiled application. SharePoint 2013 introduced Content Enrichment which allows us to enrich content during the content processing phase using a WCF Web Service as seen in Figure 1. In this 3-part series, we will examine Content Enrichment being leveraged to enhance data three different ways. In part one, we will develop a simple Content Enrichment Service that combines two existing SharePoint managed properties into a single managed property. In part two, we will enhance data by taking a single managed property and querying a database to obtain related details. Finally, in part three, we will enhance content by taking a single managed property and obtaining details from a web service.

Read More…