Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
220
Filtering on hidden columns
posted

I am dynamically adding columns to a grid because I don't know the names of all the columns until runtime.  That worked great until I needed to turn on filtering.

There will be four preset views that will filter the data rows, showing and hiding rows based on the definition of the filter, which I do know at design time.

Here's the definition of the grid:

  $(function () {

        // Define the grid
        $("#mainGrid_@Model.GridIndex").igGrid({
            autoGenerateColumns: false,
            width: "100%",
            dataSource: new Array(0), // Just use an empty array to define the dataSource for now. We will populate it with data later
            dataSourceType: "json",
            scrolling: true,
            columns: [
                {
                    headerText: "",
                    key: "RowType",
                    dataType: "numeric",
                    hidden: true
                },
                {
                    headerText: "",
                    key: "RowFormat",
                    dataType: "numeric",
                    hidden: true
                },
                {
                    headerText: "",
                    key: "Oversold",
                    width: "25px",
                    dataType: "string"
                },
                {
                    headerText: "",
                    key: "Status",
                    width: "25px",
                    dataType: "string"
                },
                {
                    headerText: "",
                    key: "OrderNumberInfo",
                    width: "100px",
                    dataType: "string"
                },
                {
                    headerText: "",
                    key: "GeneralDescription",
                    width: "200px",
                    dataType: "string"
                },
                {
                    headerText: "",
                    key: "RowDate",
                    width: "150px",
                    dataType: "string"
                },
                {
                    headerText: "",
                    key: "RowDateFormat",
                    dataType: "numeric",
                    hidden: true
                },
                {
                    headerText: "",
                    key: "MultiIndicator",
                    width: "20px",
                    dataType: "string"
                },
                {
                    headerText: "",
                    key: "FobOrTransfer",
                    width: "100px",
                    dataType: "string"
                },
                {
                    headerText: "Pkg",
                    key: "PackageSize",
                    width: "75px",
                    dataType: "string"
                },
                {
                    headerText: "Brd Ft",
                    key: "BoardFeet",
                    width: "90px",
                    dataType: "numeric",
                    format: "###,###,###"
                }
            ],
            features: [
                {
                    name: "Filtering",
                    type: "local",
                    mode: "simple",
                    filterSummaryAlwaysVisible: false,
                    renderFilterButton: false,
                    columnSettings: [
                        {
                            columnKey: "RowFormat",
                            allowFiltering: false
                        },
                        {
                            columnKey: "Oversold",
                            allowFiltering: false
                        },
                        {
                            columnKey: "Status",
                            allowFiltering: false
                        },
                        {
                            columnKey: "OrderNumberInfo",
                            allowFiltering: false
                        },
                        {
                            columnKey: "GeneralDescription",
                            allowFiltering: false
                        },
                        {
                            columnKey: "RowDate",
                            allowFiltering: false
                        },
                        {
                            columnKey: "RowDateFormat",
                            allowFiltering: false
                        },
                        {
                            columnKey: "MultiIndicator",
                            allowFiltering: false
                        },
                        {
                            columnKey: "FobOrTransfer",
                            allowFiltering: false
                        },
                        {
                            columnKey: "PackageSize",
                            allowFiltering: false
                        },
                        {
                            columnKey: "BoardFeet",
                            allowFiltering: false
                        }
                    ]
                }
            ],
            rowsRendered: function() {
                FormatGrid('@Model.GridIndex', @Html.Raw(Json.Encode(Model.LengthValues)));
            }
        });

        // Add the length value columns to the grid. The AddLengthColumns function is in the main page
        AddLengthColumns('@Model.GridIndex', @Html.Raw(Json.Encode(Model.LengthValues)));

        // Get the data for the grid. The GetData function is in the main page
        GetData('@Model.GridIndex', @Html.Raw(Json.Encode(Model)));


    }); // End onLoad

The functions that add the dynamic columns and perform other duties are here:

  function GetData(gridIndex, matrixLayout) {

        $.ajax({
                type: "POST",
                url: '@Url.Action("GetDataForTable", "Home")',
                data: matrixLayout,
                dataType: "json" // we are expecting JSON data back
            })
            .done(
                function(json) {
                    $("#mainGrid_" + gridIndex).igGrid("option", "dataSource", json);
                    $("#mainGrid_" + gridIndex).igGrid("dataBind");
                })
            .fail(
                function(jqXhr, textStatus, errorThrown) {
                    console.warn(jqXhr.responseText);
                    alert(errorThrown + ' getJSON request failed for grid ' + gridIndex, textStatus);
                })
            .always(
                function() {
                }
            );
    }

    function AddLengthColumns(gridIndex, columnList) {

        // Get
        var cols = $("#mainGrid_" + gridIndex).igGrid("option", "columns");

        // Set - Iterate through the columnList and add new columns for each length value in the list
        $.each(columnList,
            function(index, item) {
                var newColumn = {
                    headerText: item,
                    key: FixLengthHeaderColumnNames(item),
                    width: "75px",
                    dataType: "number",
                    format: "###,###,###"
                };
                cols.push(newColumn);

                // This is how we signal when a column is oversold
                var oversoldColumn = {
                    headerText: "",
                    key: "Oversold" + FixLengthHeaderColumnNames(item),
                    width: '0px',
                    dataType: 'bool'
                }
                cols.push(oversoldColumn);
            }
        );

        // Finish
        $("#mainGrid_" + gridIndex).igGrid("option", "columns", cols);

        // Now set the filtering for all those new columns to false
        var colSettings = $("#mainGrid_" + gridIndex).igGridFiltering("option", "columnSettings");

        $.each(columnList,
            function (index, lengthColumnName){

                var filterColumn = FixLengthHeaderColumnNames(lengthColumnName);
                var oversoldFilterColumn = "Oversold" + FixLengthHeaderColumnNames(lengthColumnName);

                for (var i = 0; i < colSettings.length; i++) {

                    if (colSettings[i].columnKey === filterColumn ||
                        colSettings[i].columnKey === oversoldFilterColumn) {
                        colSettings[i].allowFiltering = false;
                    }
                }
            });

        $("#mainGrid_" + gridIndex).igGridFiltering("option", "columnSettings", colSettings);
    }

    function FixLengthHeaderColumnNames(columnName) {
        return columnName.replace(/[^a-z,A-Z0-9_]/g, '_');
    }

    function FormatGrid(gridIndex, dynamicColumnList) {

        var gridSelector = "#mainGrid_" + gridIndex;
        // Contains all the rows for this grid
        var rows = $(gridSelector).igGrid("rows");
        if (rows.length === 0) return; // This happens on the first bind to an empty array, we need to ignore that binding

        $.each(rows,
            function(rowIndex, row) {

                try {
                    var rowBold = $(gridSelector).igGrid("getCellValue", rowIndex, "RowFormat");

                    if (rowBold !== null &&
                        rowBold === 1 ||
                        rowBold === '1') {

                        $(row).addClass("rowBoldFormat");
                    }
                } catch (e) {
                    console.error("Error trying to bold row " + "Row = " + rowIndex + " " + e);
                }

                try {
                    // Date Red/Green formatting
                    var dateFormat = $(gridSelector).igGrid("getCellValue", rowIndex, "RowDateFormat");
                    if (dateFormat !== null &&
                        dateFormat !== undefined) {

                        var dateCellIndex = $(gridSelector).igGrid("getVisibleIndexByKey", "RowDate");
                        var dateCell = $(gridSelector).igGrid("cellAt", rowIndex, dateCellIndex);

                        if (dateCell !== null &&
                            dateCell !== undefined) {

                            if (dateFormat === 1) {
                                $(dateCell).addClass("dateDisplayGreen");
                            } // 2 is red
                            else if (dateFormat === 2) {
                                $(dateCell).addClass("dateDisplayRed");
                            }
                        }
                    }
                } catch (e1) {
                    console.error("Error trying to format Date. " + e1);
                }

                $.each(dynamicColumnList,
                    function(columnIndex, dynamicColumn) {
                        try {

                            var oversoldCell = $(gridSelector).igGrid("getCellValue", rowIndex, "Oversold" + dynamicColumn);

                            if (oversoldCell !== null &&
                                oversoldCell !== undefined &&
                                oversoldCell === true) {

                                var lengthCellIndex = $(gridSelector).igGrid("getVisibleIndexByKey", dynamicColumn);
                                var lengthCell = $(gridSelector).igGrid("cellAt", lengthCellIndex, rowIndex);
                                $(lengthCell).addClass("overSoldHighlight");

                                var orderCellIndex = $(gridSelector).igGrid("getVisibleIndexByKey", "OrderNumberInfo");
                                var orderCell = $(gridSelector).igGrid("cellAt", orderCellIndex, rowIndex);
                                $(orderCell).addClass("overSoldHighlight");

                                var generalDescriptionCellIndex = $(gridSelector).igGrid("getVisibleIndexByKey", "GeneralDescription");
                                var generalDescriptionCell = $(gridSelector).igGrid("cellAt", generalDescriptionCellIndex, rowIndex);
                                $(generalDescriptionCell).addClass("overSoldHighlight");

                                var rowDateCellIndex = $(gridSelector).igGrid("getVisibleIndexByKey", "RowDate");
                                var rowDateCell = $(gridSelector).igGrid("cellAt", rowDateCellIndex, rowIndex);
                                $(rowDateCell).addClass("overSoldHighlight");

                                var multiIndicatorCellIndex = $(gridSelector).igGrid("getVisibleIndexByKey", "MultiIndicator");
                                var multiIndicatorCell = $(gridSelector).igGrid("cellAt", multiIndicatorCellIndex, rowIndex);
                                $(multiIndicatorCell).addClass("overSoldHighlight");
                            }
                        } catch (e2) {
                            console.error("Highlight error for Grid = " + gridSelector + ". Column = Oversold" + dynamicColumn + ". Row = " + rowIndex + ". Error = " + e2);
                        }
                    });
            });


    }

    function SwitchView(viewName) {

        // loop through each of the grid layouts and set the filter to display the proper view
        var layoutCount = @Model.Layouts.Count;

        switch (viewName) {
        case "ForecastByProduct":
                $("#title").html("Production Forecast Detail");

                $("#productionForecastDetailRow").removeClass("displayInline").addClass("displayHidden");
                $("#productionForecastSummaryRow").removeClass("displayHidden").addClass("displayInline");
                $("#weekOfProductionSummaryRow").removeClass("displayHidden").addClass("displayInline");
                $("#vmiProductionDetailRow").removeClass("displayHidden").addClass("displayInline");
            break;
        case "ForecastByProduction":
                $("#title").html("Production Forecast Summary");

            $("#productionForecastDetailRow").removeClass("displayHidden").addClass("displayInline");
            $("#productionForecastSummaryRow").removeClass("displayInline").addClass("displayHidden");
            $("#weekOfProductionSummaryRow").removeClass("displayHidden").addClass("displayInline");
            $("#vmiProductionDetailRow").removeClass("displayHidden").addClass("displayInline");
            break;
        case "ForecastVmi":
                $("#title").html("VMI Production Forecast Detail");

            $("#productionForecastDetailRow").removeClass("displayInline").addClass("displayHidden");
            $("#productionForecastSummaryRow").removeClass("displayHidden").addClass("displayInline");
            $("#weekOfProductionSummaryRow").removeClass("displayHidden").addClass("displayInline");
            $("#vmiProductionDetailRow").removeClass("displayInline").addClass("displayHidden");
            break;
        case "ForecastWeekOf":
                $("#title").html("Week Of Production Forecast Summary");

            $("#productionForecastDetailRow").removeClass("displayInline").addClass("displayHidden");
            $("#productionForecastSummaryRow").removeClass("displayHidden").addClass("displayInline");
            $("#weekOfProductionSummaryRow").removeClass("displayInline").addClass("displayHidden");
            $("#vmiProductionDetailRow").removeClass("displayHidden").addClass("displayInline");
            break;
        }

        for (var i = 0; i < layoutCount; i++) {
            SwitchGridView(viewName, i.toString());
        }

        return false;
    }

    function SwitchGridView(viewName, gridIndex) {

        var filters = [];
        switch(viewName) {
            case "ForecastByProduct":
                filters = [
                    { fieldName: "RowType", expr: 10, cond: "equals", logic: "OR" }, // On Hand Inventory
                    { fieldName: "RowType", expr: 20, cond: "equals", logic: "OR" }, // Order Info
                    { fieldName: "RowType", expr: 30, cond: "equals", logic: "OR" }, // Available to Ship Inventory
                    { fieldName: "RowType", expr: 40, cond: "equals", logic: "OR" }, // Production
                    { fieldName: "RowType", expr: 50, cond: "equals", logic: "OR" }, // Picked / Ready
                    { fieldName: "RowType", expr: 60, cond: "equals", logic: "OR" }, // Picked / Ready Summary
                    { fieldName: "RowType", expr: 70, cond: "equals", logic: "OR" }, // Purchase Order
                    { fieldName: "RowType", expr: 80, cond: "equals", logic: "OR" }  // Inbound Transfer
                ];
                break;
            case "ForecastByProduction":
                filters = [
                    { fieldName: "RowType", expr: 10, cond: "equals", logic: "OR" }, // On Hand Inventory
                    //{ fieldName: "RowType", expr: 20, cond: "equals", logic: "OR" }, // Order Info
                    { fieldName: "RowType", expr: 30, cond: "equals", logic: "OR" }, // Available to Ship Inventory
                    { fieldName: "RowType", expr: 40, cond: "equals", logic: "OR" }  // Production
                    //{ fieldName: "RowType", expr: 50, cond: "equals", logic: "OR" }, // Picked / Ready
                    //{ fieldName: "RowType", expr: 60, cond: "equals", logic: "OR" }, // Picked / Ready Summary
                    //{ fieldName: "RowType", expr: 70, cond: "equals", logic: "OR" }, // Purchase Order
                    //{ fieldName: "RowType", expr: 80, cond: "equals", logic: "OR" }  // Inbound Transfer
                ];
                break;
            case "ForecastVmi":
                filters = [
                    { fieldName: "RowType", expr: 10, cond: "equals", logic: "OR" }, // On Hand Inventory
                    { fieldName: "RowType", expr: 20, cond: "equals", logic: "OR" }, // Order Info
                    { fieldName: "RowType", expr: 30, cond: "equals", logic: "OR" }, // Available to Ship Inventory
                    { fieldName: "RowType", expr: 40, cond: "equals", logic: "OR" } // Production
                    //{ fieldName: "RowType", expr: 50, cond: "equals", logic: "OR" }, // Picked / Ready
                    //{ fieldName: "RowType", expr: 60, cond: "equals", logic: "OR" }, // Picked / Ready Summary
                    //{ fieldName: "RowType", expr: 70, cond: "equals", logic: "OR" }, // Purchase Order
                    //{ fieldName: "RowType", expr: 80, cond: "equals", logic: "OR" }  // Inbound Transfer
                ];
                break;
            case "ForecastWeekOf":
                filters = [
                    { fieldName: "RowType", expr: 10, cond: "equals", logic: "OR" }, // On Hand Inventory
                    //{ fieldName: "RowType", expr: 20, cond: "equals", logic: "OR" }, // Order Info
                    { fieldName: "RowType", expr: 30, cond: "equals", logic: "OR" }, // Available to Ship Inventory
                    { fieldName: "RowType", expr: 40, cond: "equals", logic: "OR" } // Production
                    //{ fieldName: "RowType", expr: 50, cond: "equals", logic: "OR" }, // Picked / Ready
                    //{ fieldName: "RowType", expr: 60, cond: "equals", logic: "OR" }, // Picked / Ready Summary
                    //{ fieldName: "RowType", expr: 70, cond: "equals", logic: "OR" }, // Purchase Order
                    //{ fieldName: "RowType", expr: 80, cond: "equals", logic: "OR" }  // Inbound Transfer
                ];
                break;

        }

        $("mainGrid_" + gridIndex).igGridFiltering("filter", filters);
    }

The problems I'm running into currently are these. 

  1. The dynamic columns are still displaying an edit box for the filter criteria even though I've specified them as not filtered.

  2. I'm getting the following javascript error: infragistics.lob.js:244 Uncaught TypeError: editorProviders[i].getValue is not a function
    at $.<computed>.<computed>._setNullTextsInFilteringEditors (infragistics.lob.js:244)
    at $.<computed>.<computed>._setNullTextsInFilteringEditors (jquery-ui-1.12.0.js:144)
    at $.<computed>.<computed>._onUIDirty (infragistics.lob.js:244)
    at $.<computed>.<computed>._onUIDirty (jquery-ui-1.12.0.js:144)
    at HTMLTableElement.proxy (jquery-3.4.1.js:10502)
    at HTMLTableElement.dispatch (jquery-3.4.1.js:5237)
    at HTMLTableElement.elemData.handle (jquery-3.4.1.js:5044)
    at Object.trigger (jquery-3.4.1.js:8471)
    at HTMLTableElement.<anonymous> (jquery-3.4.1.js:8549)
    at Function.each (jquery-3.4.1.js:367)

BTW - I am making the ajax call and getting a JSON return for the data, but the data is not showing up in the grid.

What am I doing wrong?