Understanding SQL Grouping and Date Formats

No matter how many times you do it, you always find yourself having to go back and look up silly information on how to do things in something you haven’t done in awhile.

Case in point, I had to do some SQL magic on some data and had completely forgotten how to do proper groupings and summation of data by weeks and months – so here it is for all your future references.

Retrieving Results for a Month

This is easier than doing things by week and is easily done by using a derived table to get an initial result set and then using the outer query to present everything nicely.

 SELECT MONTH, COUNT(TOTAL) AS TOTAL, FORMAT(SUM(TOTAL),'C') AS SUM
FROM (
SELECT FORMAT(DO.SomeDateField,'MMM') + '-' + CAST(FORMAT(DATEADD(YEAR, 1, DA.SomeDateField), 'yy') AS VARCHAR(40)) AS MONTH,
MONTH(DA.SomeDateField) AS CALENDAR_MONTH_ORDER,
CAST(YEAR(DA.SomeDateField)															
Read More

Eliminate Lazy Error Handling

Here is an error I received the other day…

“Authentication Failure”

Here is another…

“Format of the initialization string does not conform to specification starting at index 0, without any typo in connectionString”

And finally my favourite…

“The entity cannot be updated because it is read-only.”

They are all from different applications and implementations and have been my life for the past two days as I tried to figure them out and wasted an insane amount of time trying to figure out what they related to and what I had to do to resolve them.

Here were the resolutions (in order)…

“Multi-Factor authentication is not supported here.”

“You need to enter the string in a special way, but we won’t tell you how.”

“It’s not that the entity was read-only, it’s that a function was trying to modify it while

Read More

Ribbon Work Bench and the Async Service

I ran into an interesting problem this past week in trying to publish some customizations via the RibbonWorkBench to an on-premise Dynamics tenant.

Here is the message we were receiving.

image

This led me down a road of checking permissions and seeing what forms I had in the solution I was working, connections being closed, security around using IFD deployments and on and on.

After a day or so, we realized the problem was that the Async Service Account could not write to the CustomizationImport folder on this server.  When we granted the appropriate permissions, the problem was solved.

(One more subtle reason to consider the cloud).

Read More

Formatting Slack Messages from Dynamics365

In creating my messaging application that sent data from Dynamics365 to SLACK, I wanted to get a little fancier and not only send static content but actually embed the link to the actual record with some content to provide context to the user.

I also wanted to do a bit of formatting (bolding and links) which is relatively easy but does not follow HTML notation.  The formatting reference for SLACK is located here.

For my scenario (displaying messages for Leads), I wanted to bold the entity (because this will be used by other ones at some point) and open directly to the record when clicked.

The url format to accomplish this is as follows;

A new *Lead* has been created – <{0}/main.aspx?etc=4&id={1}&pagetype=entityrecord|{2}>

Where the first parameter is my Dynamics tenant, the “etc” value is

Read More

Integrating Dynamics365 with SLACK

On a recent project, we wanted to notify users when new items were added into Dynamics (by third-party systems).  The initial goal was to use email as the delivery mechanism but seeing as how everyone was using SLACK for messaging I decided this would be a better (and much cooler delivery mechanism).

This is my first time working with the Slack API and I have to say it was surprising how easy it was to put together some demo functionality to prove it all out.

First thing you are going to need to do is to create an application.  When you create your application you can then enable features for your application.

For my application, I created two incoming WebHooks that enabled me to post to the my #general channel and send

Read More