Add more buttons on the form subgrid

Sometime, your business requirement need add some buttons in the form sub-grid like picture below (Account form, sub-grid Contacts). Add more buttons

How I can do that

1. Prepare 2 web-resource png image 16x16 file.

  • 1 for normal button
  • 1 for hover button

2. Copy function below to your main js web-resource file.

function createButton(grid, buttonName, buttonTooltip, buttonImage, buttonImageHover, callback) {
    if (grid == null || grid.length == 0) return;
    if (buttonName == null || buttonName.length == 0) return;
    if (buttonImage == null || buttonImage.length == 0) return;
    var dom = (Xrm == undefined || Xrm.Internal == undefined || Xrm.Internal.isTurboForm() == undefined || Xrm.Internal.isTurboForm() == false) ? document : parent.document;
    var button = dom.getElementById(grid + "_addImageButton");
    if (button == null || button.parentNode == null || button.parentNode.parentNode == null) return;
    var tooltip = "";
    if (buttonTooltip != null && buttonTooltip.length > 0)
        tooltip = " title='" + buttonTooltip + "' alt='" + buttonTooltip + "' ";
    var div = dom.createElement("div");
    div.className = "ms-crm-contextButton";
    div.innerHTML = "<a href='#' id='" + buttonName +"' style='display:block;cursor:pointer;'" + tooltip + "><img id='"+ buttonName +"Image' src='" + buttonImage + "'" + tooltip + "></a>";
    button.parentNode.parentNode.appendChild(div);
    if (buttonImageHover != null) {
        dom.getElementById(buttonName).onmouseover = function () {
            dom.getElementById(buttonName + "Image").src = buttonImageHover;
        }
        dom.getElementById(buttonName).onmouseout = function () {
            dom.getElementById(buttonName + "Image").src = buttonImage;
        }
    }
    dom.getElementById(buttonName).onclick = callback;
}

3. On the on-load form, add the following code to add button to form sub-grid.

createButton("Contacts", "btnViewChart", "View Chart", "../WebResources/pl_chart.png", "../WebResources/pl_chart_hover.png", function() {
    alert("View Chart Click");
});

Function parameters

  • grid: is the name of the sub-grid you add on form, in this case: Contacts, (required)
  • buttonName: because you can add more buttons, so you need enter the unique buttonName, in this case: btnViewChart (required)
  • buttonTooltip: the tooltip of this button, in this case: View Chart (optional)
  • buttonImage: the normal image button that you prepare in step 1, in this case: ../WebResources/pl_chart.png (required)
  • buttonImageHover: the hover button that you prepare in step 1, in this case: ../WebResources/pl_chart_hover.png (optional)
  • callback: a callback function after user click on button. (required)

4. Published and tested. I already tested on CRM2016 SP1 and CRM2016 Online.

NOTE

UnSupported code, use it with your own risk.

Get size of all tables in database

Sometime you need to get size of all tables in database. This SQL code will help you.

DECLARE @tmpTable TABLE
    (
      [RowCount] INT ,
      [TableName] NVARCHAR(MAX)
    )
DECLARE @ResultTable TABLE
    (
      [Name] NVARCHAR(MAX) ,
      [Rows] INT ,
      [Reserverd] NVARCHAR(MAX) ,
      [Data] NVARCHAR(MAX) ,
      [IndexSize] NVARCHAR(MAX) ,
      [Unused] NVARCHAR(MAX)
    )
INSERT  INTO @tmpTable
        ( [RowCount] ,
          [TableName]
        )
        SELECT  [RowCount] = MAX(si.rows) ,
                [TableName] = so.name
        FROM    sysobjects so ,
                sysindexes si
        WHERE   so.xtype = 'U'
                AND si.id = OBJECT_ID(so.name)
        GROUP BY so.name
        ORDER BY 2 DESC
DECLARE @cursor CURSOR ,
    @tablename VARCHAR(MAX)
SET
@cursor = cursor for
select [TableName] from @tmpTable
OPEN @cursor
WHILE 1 = 1
    BEGIN
        FETCH FROM @cursor INTO @tablename
        IF @@fetch_status <> 0
            BREAK
        INSERT  INTO @ResultTable
                ( [Name] ,
                  [Rows] ,
                  [Reserverd] ,
                  [Data] ,
                  [IndexSize] ,
                  [Unused]
                )
                EXEC sp_spaceused @tablename
    END
SELECT  [Name] ,
        ROUND(( CAST(REPLACE(data, ' KB', '') AS FLOAT) / 1024 ), 2) AS DataInMb ,
        [Rows] ,
        [Reserverd] ,
        [Data] ,
        [IndexSize] ,
        [Unused]
FROM    @ResultTable
ORDER BY ( CAST(REPLACE(data, ' KB', '') AS INT) ) DESC

And here the result after you run this SQL code.

Get size of all tables result

SQL 

Get years, months, days between 2 date

Sometime you want get how many years, months, days from 2 date.

The UDF here help you

CREATE FUNCTION [dbo].[fnGetYMD]
(
    @d1 DATETIME,
    @d2 DATETIME
)
RETURNS NVARCHAR(MAX)
AS
BEGIN  
    IF (@d1 >= @d2) RETURN '0 day'  
    DECLARE @y INT
    DECLARE @m INT
    DECLARE @d INT
    SET @y = DATEDIFF(yy, @d1, @d2) - CASE WHEN (MONTH(@d1) > MONTH(@d2)) OR (MONTH(@d1) = MONTH(@d2) AND DAY(@d1) > DAY(@d2)) THEN 1 ELSE 0 END
    SET @d1 = DATEADD(yy, @y, @d1)
    SET @m = DATEDIFF(m, @d1, @d2) - CASE WHEN DAY(@d1) > DAY(@d2) THEN 1 ELSE 0 END
    SET @d1 = DATEADD(m, @m, @d1)
    SET @d = DATEDIFF(d, @d1, @d2)
    DECLARE @ret NVARCHAR(MAX) = ''
    IF @y <> 0
    BEGIN
        IF @y = 1
            SET @ret = '1 year '
        ELSE
            SET @ret = CONVERT(NVARCHAR(MAX), @y) + ' years '
    END
    IF @m <> 0
    BEGIN
        IF @m = 1
            SET @ret = @ret + '1 month '
        ELSE
            SET @ret = @ret + CONVERT(NVARCHAR(MAX), @m) + ' months '
    END
    IF @d <> 0
    BEGIN
        IF @d = 1
            SET @ret = @ret + '1 day'
        ELSE
            SET @ret = @ret + CONVERT(NVARCHAR(MAX), @d) + ' days'
    END
    RETURN @ret
END

And the result from my birthday until to 11-08-2013

Get years, months, days between

SQL