The SkyGuy Blog

A number of years ago I learned a really tough lesson. Being confident is good, but is not a replacement for being smart. We all have been over confident about something in our lives. For most of us tech people, we have a few tech things that we do so often and so regularly that it becomes second nature. We often forget that we make mistakes or things are not always reliable. For me, it was confidence in my programming skills. I thought I was the whole kit and caboodle, the whole shebang, the full monty when it came to programming. It wouldn't take long for me to learn that confidence does not replace actually knowing the thing. Unfortunately, I would learn this lesson in one of the most spectacularly boring ways possible: infinite loop.

Background

It was two years into my bachelors when I landed my first real developer job. I had recently been promoted to Business Systems Analyst for Dynamics 365 CRM. My boss told me that since there was no CRM developer on the team, I could write all the code. But, there was one caveat: don't break anything. At the time, I confidently declared that I would not let him down and I would never break anything. With that, I began converting every business requirement that came my way into plugins. For the uninitiated, a plugin is a piece of C# code that runs on the CRM server. It usually runs whenever a record is created, updated, or deleted. I assumed at the time that pretty much every automated process that happened in the system should be put into a plugin because, you know, “confidence”. I thought I knew everything there was about CRM.

Soon after all this began, I received a requirement from our sales team. They wanted to have a calculation done whenever a new order was placed. This calculation would be based on a set of related records, which would then update the order and create a related invoice record. Of course, this seemed like a great opportunity to create a plugin to handle all this work. While working on this plugin, there was a specific requirement that I should have thought about in deeper detail. They wanted all of this to occur after the record saved. They did not want the salesperson to worry about whether the calculation occurred or have to wait around for it to finish. This is where I made my mistake.

Explanation

Within CRM (or CE if your in the cloud) when you create a plugin, you need to tell CRM when you want the plugin to run. It's called the “Event Execution Pipeline”. After a record is created or update, there are four stages of this pipeline: PreValidation, PreOperation, MainOperation, or PostOperation. You can start your plugin during any of these stages minus MainOperation. The important detail here is that any of the pre stages happen before the record is saved, where the post stage happens after the record is saved. Now, based on the requirements I received and what I “thought” I knew about this pipeline, I made a decision. I would start the plugin during the PostOperation stage, which would update the record with the calculation and the new related invoice record. At the time, I did not realize that when you updated the same record during the PostOperation, it would initiate a new update. The record would then update and run the plugin again, and again, and again, forever. Or at least until you ran out of RAM or database storage. It was a perfect example of an infinite loop. So, I built the plugin, tested it and thought it was good. I deployed the plugin to production on a Friday at the end of the day. I tested it once in production, then went home. I didn't realize that over the next 48 hours it would run endlessly out of control.

Chaos

As a arrived at my desk the following Monday, a salesperson is already there waiting for me. He tells me that his whole morning has been ruined because he can't login to CRM. I attempt to login myself, but the webpage isn't even accessible. Odd. So I try to login to the server where its stored (This was CRM 2016 On Premise by the way) and nothing. The machine wasn't running. I boot it up and began testing for issues. Nothing seemed to be wrong. So we let the sales team start working again. However, it wasn't more than a few hours later when the system crashed again. After many hours of troubleshooting and several server crashes, I realized that it was my plugin. It did not take me long to realize that my plugin was bad, and was infinitely looping forever. We quickly restore a backup from the prior week and I rewrote the plugin to initiate during a different step in the pipeline.

Resolution

Overall, we lost four days of sales data. At the end of the day, most of the data I lost was recoverable. The stuff that wasn't recovered was just a few phone calls away. However, there was a lot of time wasted by me, the sales team, and the other IT resources that were involved. It was a small mistake that had large effects. I probably should have lost my job. Luckily, I had a patient and forgiving boss. I had a short leash after this event though and it took a lot of hard work to regain that trust from my boss. It also took a long time to rebuild my confidence. It was from this event that I learned that confidence and knowledge go hand in hand. I heard the phrase “fake it tell you make it” all the time growing up. However, I learned that I should spend less time pretending I knew everything and more time trying to learn as much as possible. At its core, confidence is built on hard work, humility, and knowledge. So don't be like me and “fake it tell you make it”. Just try to make it instead of having to fake it.

A few weeks ago a customer asked my employer to create a custom page within their Dynamics CE (Customer Engagment) environment. This page would contain the results of a specific table from a SQL database store externally from their CE environment. The customer wanted to be able to query specific data from this table. With that in mind, the architect assigned to the customer approaches me and says, “I think a Canvas App would be perfect for this situation!“.

The Problem

The requirements for the Canvas App seemed simple. The customer wanted to have a list of fields that they could use to query data. They wanted the data to load live as they typed into the individual fields, but they wanted to filter on data that would be found in multiple fields. Below is an image that represents an example of what the fields would look like:

This seemed straight forward to me at the time. I would create the fields, create a gallery to render the data from sql, and create a Power Fx function to search the data. It wasn't until I started writing my Power Fx function that I ran into issues. First, I decided to use an if statement to check the first field in the list. In Power Fx, an if statement basically looks like the following:

If(Condition, action for if condition is true, action for if condition is false)

I took the first field in the Canvas App and applied this logic. To do this, I would need to first check if the field is blank. If the field was blank, I would return the entire sql table. However, if it wasn't blank, then I would take the data from the field, compare it to the corresponding sql field, and create a filter. The resulting function looks like this:

If(IsBlank(Field1.Value), sqltable, Filter(sqltable, Field1.Value in sqlfield1))

Simple right? Up to this point, it was. However, now I needed to do this for all the other fields in the app. With this in mind, I searched Microsoft's Documentation for answers. I could not find any documentation where you could apply multiple Power Fx functions within one single gallery like I was trying to do. I was stumped. However, I did eventually come up with an idea.

The Solution

While trying to find a solution, I decided to try nesting if statements within each other to get filter fields. I tried doing this many different ways, but I only got the function to work properly one way. First, I decided to use an if statement to check if a field was blank. If it was, then the true condition would have another nested if statement. This was to check the following field to see if it was blank.

If(IsBlank(Field2.Value), If(IsBlank(Field1.Value), sqltable, "Else Statement"), "Else Statement")

Now if all the fields were searched and were blank, then we would return the base sql table into the gallery. Second, we would need to filter on each section if a field did not come up blank. They I did this was by first checking to see if the last field in the set was blank. If it was not blank, then I would check and create a filter based on all the fields before it. If that field was blank, it would move up to the next field and do the same logic.

If(IsBlank(Field2.Value), If(IsBlank(Field1.Value), sqltable, Filter(sqltable, Field1.Value in sqlfield1), Filter(sqltable, Field2.Value in sqlfield2, Field1.Value in sqlfield1)))

This seemed to work. When all field were blank it would return the entire table, but if there was data in a field then it would properly attempt to filter on the field and any other fields that had data. However, because of the way the function needed to be nested, it very quickly became difficult to see the different parts of the function. The final result looked something like this:

My Thoughts

In my opinion, while this solution works, it seeems very cluttered and hard to read. I constantly was searching Google to see if other people had found a different solution because the way I was trying to solve this problem felt wrong. However, this was the way I found to solve this problem. As a Software Developer and Engineer, I wish I knew of a way to solve this problem with code, which would of have made my problem more simple to solve.

Did you find a different way to solve this problem? Send me a message, I would love to know what you did.