Force a column to appear first in a grid dynamically

[Janicen]

Hi,

I have a service output of base type infotable. The datashape attached only has 1 column called timestamp. I add all my other columns dynamically in my service. In the result, the timestamp column appears last. Instead I want this column to be the first in the grid. How do I force this column to appear first?

Many Thanks.

[Sathishkumar_C]

try to generate grid configuration json dynamically with columns order. Generate the targets values according to your requirement.

"columnDefs": [
            {
                "targets": 0,
                .
                .
            },
            {
                "targets": 1,
                .
                .
            }
           ]

 Refer few articles below,

• Grid widget dynamic config - styling and column sorting
• Configuring the Grid using JSON Data

[Janicen]

where do I pass the 

columnDefs

in the creation of my table?

let outputTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
infoTableName: "InfoTable",
dataShapeName: "TimestampOnly_DS"
});

Regards

[Sathishkumar_C]

You can create separate service (GetGridConfiguration()) and the output should be JSON. The complete json format you can reuse from above mentioned community post.

There is widget property called "Grid Configuration". You have pass as JSON from the service.

[Rocko · Accepted solution]

I can't reproduce this behaviour. Here, the timestamp column is first.

-Do you have a datashape assigned to your output Infotable variable? if not, try assigning TimestampOnly_DS to it. In Mashup, remove binding, save, reload service, rebind

-in your service, I suppose you add the fields to the infotable using AddField? you can pass an ordinal parameter to that which gives the order of the columns:

result.AddField({name:"f2", baseType:"STRING",ordinal:2});

result.AddField({name:"f3", baseType:"STRING",ordinal:1});

Use that, again, remove binding, save, reload service, rebind.

Note that the order of the columns is partially determined at bind time. When you bind to a grid the component tried to find out the columns not only by looking at the columns returned in the service declaration, it (tries to) run the service and checks the result.

Hence, whenever you change the "result style" of your service, try to remove the binding, save, reload service, rebind.

[Sathishkumar_C]

1. Create server GetGridConfiguration() and service code something like below and Output as JSON

var result = {
  "enableFooter": false,
  "columns":    {                               // Required
    "idFieldName": "id",                        // {string} Required. The name of the 'id' column containing the primary key.
    "header":       {                           // Optional
      "titleCase":   "",                        // *{string} header title case setting. Options: 'lower', 'upper', 'camel'
      "overflow":    "tooltip"                  // *{string} header overflow setting. Options: 'fitted', 'wrapped', 'clipped', 'ellipsis'
    },
    "columnDefs":   [                           // Optional. Column definitions for each column in the data InfoTable.
      {
        "targets":             0,               // {integer} the index of the column to which this data applies.
        "fieldName":           "f1",            // {string} the data field name.
        "title":               "F1",            // *{string} the column display name.
        "width":               "100px",         // *{string} the column width in px. Options: '*' (auto-width) or a number.
        "autoWidth":           false,           // *{boolean} enables auto width e.g. '*' setting above.
        "overflow":            "wrapped",       // *{string} cell overflow setting. Options: 'fitted', 'wrapped', 'clipped', 'ellipsis'
        "headerTextAlignment": "center",        // *{string} header text alignment. Options: 'left', 'right', 'center'.
        "textAlignment":       "left",          // *{string} cell text alignment. Options: 'left', 'right', 'center'.
        "hidden":              false,           // *{boolean} hide column. If context menu is enabled, end-user can unhide column. Data of this column will be available for state formatting.
        "inLayout":            true,
        "columnFormatter":     {
          "type":              "integer",
          "format":            "0"
        }
      },
      {
        "targets":             1,
        "fieldName":           "f2",
        "title":               "F2",
        "width":               "50px",
        "autoWidth":           false,
        "overflow":            "tooltip",
        "headerTextAlignment": "center",
        "textAlignment":       "center",
        "hidden":              false,
        "inLayout":            true,
        "columnFormatter":     {
          "type":              "datetime",
          "format":            "MM.DD.YYYY"
        }
      }
    ],
    "enableSorting":    true,                   // Optional. Enable sorting capabilities for the grid.
    "multiColumnSortOrder": [                   // Optional. Sorts grid by multiple columns. The most prominent column is listed last.
      {
        "id":         'id',                     // {string} column 'fieldName'
        "direction":  'asc'                     // {string} direction of the sort, e.g. ascending or descending. Options: 'asc', 'des'
      }
    ]
  },
  "rows":       {                               // Optional
    "selection":                  "multi",      // *{string} row selection setting. Options: 'none, 'single', 'multi'
    "defaultSelectedRows":        "",           // *{string} default row selection setting starting with row 1. Options: '1', '1,2,3', '1-5,10'
    "minHeight":                  "50",         // {string} row height setting in px.
    "autoScroll":                 true          // {boolean} scroll to the selected rows on update or resize
  },
  "styles":     {                               // Optional unless styles are defined in state formatters
    "tableWrapperStyle":              {         // {string} name of the style.
      "backgroundColor":          "#e6e6e6",    // {string} the background style color
      "secondaryBackgroundColor": "",           // {string} the secondary background style color for a gradient.
      "foregroundColor":          "",           // {string} the font color.
      "textDecoration":           "",           // {string} text decoration settings. Options: 'underline', 'line-through', 'overline'
      "image":                    "",           // {string} MediaEntity url
      "lineColor":                "#b3b3b3",    // {string} the color of the cell outline.
      "borderStyle":              "solid",      // {string} the style of the cell outline. Options: 'dotted', 'dashed', 'solid', 'double', 'groove', 'ridge', 'inset', 'outset', 'none', hidden.
      "borderWidth":              "1px",        // {string} the width of cell border in px.
      "fontFamily":               ["helvetica", "arial"],  // {string[]} array of fonts.
      "fontStyle":                "normal",     // {string} the style of the font. Options: 'normal', 'italic', 'oblique'
      "fontSize":                 "11px",       // {string} font size.
      "fontWeight":               "normal"      // {string} font weight. Options: 'normal', 'bold', 'bolder', 'lighter', number
    },
    "tableHeaderStyle":              {
      "backgroundColor":          "#202020",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "#ffffff",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "#c0c0c0",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "11px",
      "fontWeight":               "bold"
    },
    "rowBorderStyle":                {
      "backgroundColor":          "",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               "",
      "fontStyle":                "",
      "fontSize":                 "",
      "fontWeight":               ""
    },
    "cellBorderStyle":               {
      "backgroundColor":          "",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "",
      "textDecoration":           "underline",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "",
      "borderWidth":              "2px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "11px",
      "fontWeight":               "bold"
    },
    "rowBackgroundStyle":            {
      "backgroundColor":          "",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "",
      "borderWidth":              "2px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "11px",
      "fontWeight":               ""
    },
    "rowAlternateBackgroundStyle":   {
      "backgroundColor":          "#f5f5f5",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "",
      "borderWidth":              "",
      "fontFamily":               "",
      "fontStyle":                "",
      "fontSize":                 "11px",
      "fontWeight":               ""
    },
    "rowHoverStyle":                 {
      "backgroundColor":          "#dddddd",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "#333333",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "11px",
      "fontWeight":               "normal"
    },
    "rowSelectedStyle":              {
      "backgroundColor":          "#0088cc",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "#ffffff",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "",
      "borderWidth":              "",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "11px",
      "fontWeight":               "bold"
    },
    "paginationButtonStyle": {
      "backgroundColor":          "",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "#202020",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "12px",
      "fontWeight":               "bold"
    },
    "paginationHoverStyle": {
      "backgroundColor":          "#252525",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "#ffffff",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "12px",
      "fontWeight":               "bold"
    },
    "paginationSelectedStyle": {
      "backgroundColor":          "#0088cc",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "#ffffff",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "12px",
      "fontWeight":               "bold"
    },
    "toolbarStyle": {
      "backgroundColor":          "#fafafa",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "#202020",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "12px",
      "fontWeight":               ""
    },
    "rowFormatterStyle":             {
      "backgroundColor":          "#636262",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "white",
      "textDecoration":           "underline",
      "image":                    "",
      "lineColor":                "black",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "14px",
      "fontWeight":               "bold"
    },
    "sortAscendingStyle": {
      "backgroundColor":          "",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "",
      "textDecoration":           "",
      "image":                    "/Thingworx/MediaEntities/GridSortAscendingArrow",
      "lineColor":                "",
      "borderStyle":              "",
      "borderWidth":              "",
      "fontFamily":               "",
      "fontStyle":                "",
      "fontSize":                 "",
      "fontWeight":               ""
    },
    "sortDescendingStyle": {
      "backgroundColor":          "",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "",
      "textDecoration":           "",
      "image":                    "/Thingworx/MediaEntities/GridSortDescendingArrow",
      "lineColor":                "",
      "borderStyle":              "",
      "borderWidth":              "",
      "fontFamily":               "",
      "fontStyle":                "",
      "fontSize":                 "",
      "fontWeight":               ""
    },
    "rowIconStyle": {
        "backgroundColor":          "",
        "secondaryBackgroundColor": "",
        "foregroundColor":          "",
        "textDecoration":           "",
        "image":                    "/Thingworx/MediaEntities/GridRowIcon",
        "lineColor":                "",
        "borderStyle":              "",
        "borderWidth":              "",
        "fontFamily":               "",
        "fontStyle":                "",
        "fontSize":                 "",
        "fontWeight":               ""
    },
    "rowExpansionIconStyle": {
        "backgroundColor":          "",
        "secondaryBackgroundColor": "",
        "foregroundColor":          "",
        "textDecoration":           "",
        "image":                    "/Thingworx/MediaEntities/GridRowExpansionIcon",
        "lineColor":                "",
        "borderStyle":              "",
        "borderWidth":              "",
        "fontFamily":               "",
        "fontStyle":                "",
        "fontSize":                 "",
        "fontWeight":               ""
    },
    "rowCollapseIconStyle": {
        "backgroundColor":          "",
        "secondaryBackgroundColor": "",
        "foregroundColor":          "",
        "textDecoration":           "",
        "image":                    "/Thingworx/MediaEntities/GridRowCollapseIcon",
        "lineColor":                "",
        "borderStyle":              "",
        "borderWidth":              "",
        "fontFamily":               "",
        "fontStyle":                "",
        "fontSize":                 "",
        "fontWeight":               ""
    },
    "snowFormatterStyle":             {
      "backgroundColor":          "#00ffff",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "white",
      "textDecoration":           "underline",
      "image":                    "",
      "lineColor":                "black",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "14px",
      "fontWeight":               "bold"
    },
    "coldFormatterStyle":             {
      "backgroundColor":          "",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "black",
      "textDecoration":           "underline",
      "image":                    "/Thingworx/MediaEntities/SubsystemNotRunning",
      "lineColor":                "black",
      "borderStyle":              "",
      "borderWidth":              "",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "14px",
      "fontWeight":               "bold"
    },
    "warmFormatterStyle":             {
      "backgroundColor":          "",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "black",
      "textDecoration":           "underline",
      "image":                    "/Thingworx/MediaEntities/SubsystemRunning",
      "lineColor":                "black",
      "borderStyle":              "",
      "borderWidth":              "",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "14px",
      "fontWeight":               "bold"
    },
    "cellValidationErrorStyle":             {
      "backgroundColor":          "",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "black",
      "textDecoration":           "none",
      "image":                    "",
      "lineColor":                "red",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "11px",
      "fontWeight":               "normal"
    },
    "cellValidationErrorTooltipStyle":             {
      "backgroundColor":          "red",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "white",
      "textDecoration":           "none",
      "image":                    "",
      "lineColor":                "white",
      "borderStyle":              "solid",
      "borderWidth":              "1px",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "11px",
      "fontWeight":               "normal"
    },
    "tooltipStyle": {
      "backgroundColor":          "#0000ff",
      "secondaryBackgroundColor": "",
      "foregroundColor":          "#ffffff",
      "textDecoration":           "",
      "image":                    "",
      "lineColor":                "",
      "borderStyle":              "none",
      "borderWidth":              "",
      "fontFamily":               ["helvetica", "arial"],
      "fontStyle":                "normal",
      "fontSize":                 "12px",
      "fontWeight":               ""
    },
  },
  "search": {                                    // Optional search capabilities
    "multiColumn":  {                            // Optional global grid search control
      "enabled":  true,                          // {boolean} enable the grid search toolbar
      "location": "top-left"                     // {string} location of grid search toolbar. Options: 'top-right', 'top-left', 'bottom-right', 'bottom-left'
    }
  },
  "resetButton": {                               // Optional reset button that allows the end-user to reset the user specific grid settings. It clears all the grid cookie values.
    "enabled": true,                             // {boolean} show the reset button in the Mashup.
    "location": "top-right"                      // {string} Location of the reset button. Options:  top-right', 'top-left', 'bottom-right', 'bottom-left'
  }
};

2. Map JSON output with "Configuration" property

[Janicen]

Setting The ordinal:0 ensured that the timestamp column is first in the table.

Table.AddField({ name: "timestamp", baseType: "DATETIME",ordinal:0}); // Adding a field name

Thank you @Rocko