How to switch queries in a select statement using filtering

I had a situation where I needed to change a SQL query altogether based on a parameter. So tried nulling and then using
COALESCE, IIF and CASE but they didn’t work all, I came up with a rather inefficient solution. It’s inefficient but I can
scale it for my small queries. The only quirk is that the output is the same, but that is to be expected if you’re try to
transfer data from a controller to a view.

Components:

Query1 and Query2 are two separate queries which output “num” and “person”.

@switchvalue is the parameter value you’re getting from a form or a querystring.

Query:

DECLARE @switchvalue INT = 1
    DECLARE @query1 TABLE (
    Num INT,
    Person VARCHAR(20)
    );
    DECLARE @query2 TABLE (
    Num INT,
    Person VARCHAR(20)
    );
    INSERT INTO @query1 (Num, Person)
    VALUES (0, 'John');
    INSERT INTO @query2 (Num, Person)
    VALUES (1, 'Rebecca');
    SELECT * FROM (
    SELECT 0 AS switch, * FROM @query1
    UNION ALL
    SELECT 1 AS switch, * FROM @query2
    ) a WHERE a.switch = @switchvalue

Filed under: SQLTagged with: , , ,

Mimic DNN theme to embed pages into Dynamics CRM

This is a theme you can install on DotNetNuke Community Edition to create pages which are embeddable in Dynamics CRM as web resources. There are a lot of open source tools available for DNN which can allow you to do quick web app building. Some of my favorite tools include the SqlViewPro and InjectAnything from RedTempo. Mix them all together and you got a great presentation tool you can use in Dynamics CRM Dashboards. Especially since CRM has Xrm.Page.context.getUserId() and DNN has the [User:Username] token.

It’s not a replacement for sophisticated BI tools but gets me to do some interesting stuff. So here it is.

Mimic – (Zip File)

Filed under: CSS, JS, Other, SharePoint

Workaround for Transparent PNG Thumbnails in SharePoint Image Libraries

PNG files with transparency display the transparent bits as black instead of transparent in SharePoint image libraries. Since it’s been this way for years now, it’s unlikely Microsoft will fix the issue. Here’s a hacky workaround for when you really need it (like… when you have an icon library with black icons with transparent backgrounds).

sharepoint transparent png

Step 1 – Create Two Plain Text Columns

I like to call one LibURL and the other ImageURL. For LibURL, set the default to the URL of the library (sans forms/view.aspx) with no trailing slash (e.g. https://whatever.sharepoint.com/sites/sitename/libraryname).

Step 2 – Create a Workflow

I’m assuming you’re passably familiar with SharePoint workflows, if not head to Youtube and search for a beginner guide to SP2013 workflow. Create a list workflow on your image library and add the action “set field in current item” and tell it to set the ImageURL field to “Server-Relative URL”. Have it run when items are modified or created, and publish.

Step 3 – Create a Calculated Column

This calculated column will create the HTML needed to display our transparent thumbnails. This is the jankiest part, as it relies on the fact that calculated “number” columns in SharePoint render HTML. You could also set this with workflow instead (into a rich text column) if you’re worried about the reliance on a quirk, but calculated columns are kinda nifty. Set your data type to “number.” If you used the same column names as I did, your formula will be the following:

=CONCATENATE("<a href=",CHAR(34),[LibURL],[ImageURL],CHAR(34),"><img style=",CHAR(34),"max-width:80px;",CHAR(34)," src=",CHAR(34),[ImageURL],CHAR(34)," /></a>")

Step 4 – Fix your View

Image libraries default to the out-of-box thumbnail view. They should also create a second view called “all items” – this is a more typical list view. Head over to that view (or create a list view), enable your thumbnail column (the calculated one), move it to the first position, hide the image type icon (because it’s not super helpful), and try it out! Your new custom thumbnail should like to the full-size version of the image.

sharepoint custom png

This article was written by and originally posted by Christine Payton on her website christine-payton.com at this link.

Filed under: SharePointTagged with: , ,

How to link directly to a Dynamics CRM 2016 view or record.

You can link directly to a Dynamics CRM view or record by formatting your URL like this:

https://<domain>/<org>/main.aspx?etn=account&pagetype=entitylist&viewid=%7bb210XXX63-BXCA-X731-81F5-93XXXXXX8301%7d&cmdbar=true&web=true&navbar=on

These are the parameters at play here:

  1. https://<domain>/<org>/main.aspx
  2. ?etn=account (entity)
  3. &pagetype=entitylist (or entityrecord)
  4. &viewid=%7b210XXX63-BXCA-X731-81F5-93XXXXXX8301%7d (%7b is escaped “{” and %7d is escaped “}”)
  5. &cmdbar=true (this is the bar that starts with “+ New”)
  6. &web=true (this makes sure the navigation bar will show)
  7. &navbar=on (the navigation bar)

Note: If you want to find the viewid, go to your CRM record or view, click “Email a link” and then “of Current View”, then find the viewid in the link.

For more information, see MSDN article Open forms, views, dialogs, and reports with a URL

 

 

Filed under: Other

Making Embedded Content Work In A Responsive iFrame

You have an iframe:

<iframe src="https://www.youtube.com/embed/wnJ6LuUFpMo" frameborder="0" gesture="media" allowfullscreen></iframe>

It’s not responsible. Put a div around it:

<div class="video-container">
<iframe src="https://www.youtube.com/embed/wnJ6LuUFpMo" frameborder="0" gesture="media" allowfullscreen></iframe>
</iframe>
</div>

Now format the div by adding this css:

.video-container {
position: relative;
padding-bottom: 56.25%;
padding-top: 35px;
height: 0;
overflow: hidden;
}

.video-container iframe {
position: absolute;
top:0;
left: 0;
width: 100%;
height: 100%;
}

Result: the iframe is now responsive, sort of. See:

Source: Making Embedded Content Work In A Responsive iFrame

Filed under: CSS

Differences between goals, requirements and deliverables

What is a goal?

A goal is an overarching statement that is meant to provide overall context for an outcome of a project. (Mochal) This outcome is a summary statement of the business value of the project.

An example of a goal within a company would be: “To make all warranty documents and all technical service processes related to warranties electronic.”

A goal differs from a requirement in its complexity and audience. The goal is defined at the beginning of the project while a requirement is meticulously collected by the project manager to meet that goal afterwards in the planning phase.

What is a requirement?

A requirement is a detailed requisite collected by a project manager from stakeholders through surveys, questionnaires, analysis and other tools to meet a project goal.

An example of a requirement for an electronic warranty project would be the existence of a workflow which allows documents to be signed by various stakeholders electronically instead of through mail.

A requirement is usually a component of a project activity that meets a project goal. It is more complex in its definition and lays the ground work for a project deliverable.

What is a deliverable?

A project deliverable is the intended outcome of an approved task stemming from a project requirement. Project deliverables are developed in the implementation phase of the project and are made available for stakeholders to verify whether they meet pre-defined acceptable criteria for the intended goal. (McConnell, 2010)

An example of a project deliverable would be a database and workflow which hosts warranty documents and allows warranties to be emailed in a workflow via a signing application like DocuSign. If this deliverable meets the requirements acceptable criteria, it will lead in aiding the success criteria of the overall goal of the project.

References:

McConnell, E. (2010, July 2). Boundaries, Deliverables and Requirements as Key Components of Project Scope. Retrieved October 22, 2017, from http://www.mymanagementguide.com/project-boundaries-project-deliverables-and-project-requirements-as-key-components-of-project-scope/

Mochal, T. (n.d.). Defining Project Goals and Objectives. Retrieved October 22, 2017, from https://kidasa.com/defining-project-goals-and-objectives/

Filed under: NotesTagged with: , , ,

Scribe Workbench: Conversion failed when converting date and/or time from character string.

Problem:

During an insert step in Scribe Workbench, I wanted to populate a CreatedDateTime field with the current time. This field is a datetime2 in the SQL server. So I put in NOW( ) like this:

image

But, instead of capturing a timestamp and putting it in, I got the following error in the execution log:

image

Solution:

Change NOW( ) to the following: TEXT( NOW( ), “MM-dd-YYYY hh:mm:ss”)

Like this:

image

This will take the value captured from the NOW( ) as a string and convert it into type datetime2. No more errors! 🙂

Filed under: ETL