How do i set cell colors to a grid that has dynamic columns?

Question

i'm creating test results grid for K-9 blood tests. When a pet dog goes to the vet, vet takes some blood samples and for my case the vet performs two categories of blood test.

1. JOK-5
2. K9-DTR

in each blood test the vet tests for quantities of OMG, PIGT, LBQ and BTR. their quantities are in mg/l units. those tests performed in two separate days 01/21/2015 and 10/06/2014. the quantities will be placed on the cells as shown on image. The quantity value is in the property MeasuredValue of the json array in data variable.

this is a react component but don't worry about the react, the ajax for data and grid generation is done in the getData() function.

In the getData() data is assigned to a local variable and using the following code i assign data to rows.

        for(var n=0;n<arrayForRecords.length;n++){
            jQuery("#list483").jqGrid('addRowData', n + 1, arrayForRecords[n]);
        }

in the grid function gridComplete() i do the following process to place data in each cell of each row

            gridComplete:function(){

                //array consists of old rows and new row
                var dataArray = $('#list483').jqGrid('getGridParam', 'data');

                ////////////////////////////////////////////////////////////////////////////
                //
                // get catName and ShortName combination for the newly added row then search  arrayForRecords
                // and get their labdates array then place the MeasuredValues on the matching cells.
                ////////////////////////////////////////////////////////////////////////////
                if(dataArray.length>0){
                    var lastrow=dataArray[dataArray.length-1];

                    var catName = lastrow.CatName;
                    var shortName=lastrow.ShortName;
                    var myLabDates =[];

                    $(arrayForRecords).each(function(index,item){
                        if(item.CatName==catName & item.ShortName==shortName){
                            //get labDates collection
                            myLabDates= item.LabDatesandValues;
                        }
                    });

                    console.log(JSON.stringify(myLabDates));

                    //iterate through the labdates and values and set the values in the matching cell.
                    $(myLabDates).each(function(index,item){
                        if(catName=="Immunization Questions"){

                            //I did not want to check ChoiceText becuase it can be only 'Yes/No/Dont know'
                            $('#list483').jqGrid('setCell', dataArray.length, item.LB, item.CT);
                        }
                        else if(catName=="Onsite Testing"){

                            //when choice text empty put MeasureValue as value in cell
                            if(item.CT==''){
                                //put measure value
                                $('#list483').jqGrid('setCell', dataArray.length, item.LB, item.MV);
                            }
                            else{//when Choice Text has a value put ChoiceText value in cell

                                $('#list483').jqGrid('setCell', dataArray.length, item.LB, item.CT);
                            }

                        }
                        else{//for all the other values

                            //If binary anser is 'POS/NEG' place POS/NEG as value in cell based on MV=1/0
                            if(item.BA=='POS/NEG'){

                                if(item.MV==1){
                                    $('#list483').jqGrid('setCell', dataArray.length, item.LB, 'POS');
                                }
                                else{
                                    $('#list483').jqGrid('setCell', dataArray.length, item.LB, 'NEG');
                                }
                            }//some other values also must insert Yes/No in cell these values are in 'prevention screens'
                            else if(item.BA=='Yes/No'){
                                if(item.MV==1){
                                    $('#list483').jqGrid('setCell', dataArray.length, item.LB, 'Yes');
                                }
                                else{
                                    $('#list483').jqGrid('setCell', dataArray.length, item.LB, 'No');
                                }
                            }
                            else{//for all the others place measurevalue directly on cell

                                $('#list483').jqGrid('setCell', dataArray.length, item.LB, item.MV);
                            }

                        }

                    })

                }

                //this places teh contents on cell row by row: add dates from setCell method.
                $(dataArray).each(function (index){

                 });

            }

the local data in json will be reformatted to a data structure that meets the grid structure and its done in this code: (the reformatted data for the grid structure is in the array arrayForRecords )

the reformatted data strucutre as follows:

aRecord = {CategoryId:catID, CatName:foundCatName, ShortName:foundShortName, ReferenceMax:reference,Units:uom, LabDatesandValues:datesArray};

LabDatesandValues has the following structure: {LB:item.LabDate, MV:measureValOperator, CT:item.ChoiceText, BA:trimmedBAValue, ABF: Abnormal}

LB, MV, CT, BA, ABF properties assigned with the properties that comes from the original array in data variable. ABF contains the abnormal value

the code for reformatting as follows:

         success: function(data){

                //data from client:the url call is a dummy one. 
                var data = [{"CategoryId":63,"CatName":"K9-DTR","ShortName":"LBQ","ReferenceMax":null,"ReferenceMin":null,"RefRangeMax":null,"RefRangeMin":null,"LevelsID":null,"Label":null,"Min":null,"Max":null,"Points":null,"NewPoints":0,"Abnormal":1,"AbnormalFlag":null,"LDescription":null,"MDescription":null,"Email":"test@test.com","Units":"mg/l","BinaryAnswer":null,"MeasuresId":2286,"MeasuredValue":23.77,"LabDate":"01/21/2015","Operator":"","SortOrder":null,"AnswersQCID":null,"AnswersAuthorID":90889,"LabCorpID":"180062","NumOfDupes":0,"SortCat":0,"SortMeas1":null,"SortAnsLabDate":"\/Date(1421778600000)\/","ChoiceText":null},{"CategoryId":63,"CatName":"K9-DTR","ShortName":"BTR","ReferenceMax":null,"ReferenceMin":null,"RefRangeMax":null,"RefRangeMin":null,"LevelsID":null,"Label":null,"Min":null,"Max":null,"Points":null,"NewPoints":0,"Abnormal":0,"AbnormalFlag":null,"LDescription":null,"MDescription":null,"Email":"test@test.com","Units":"mg/l","BinaryAnswer":null,"MeasuresId":2286,"MeasuredValue":3.44,"LabDate":"10/06/2014","Operator":"","SortOrder":null,"AnswersQCID":null,"AnswersAuthorID":90889,"LabCorpID":"180062","NumOfDupes":0,"SortCat":0,"SortMeas1":null,"SortAnsLabDate":"\/Date(1412533800000)\/","ChoiceText":null},{"CategoryId":64,"CatName":"JOK-45","ShortName":"OMG","ReferenceMax":null,"ReferenceMin":null,"RefRangeMax":null,"RefRangeMin":null,"LevelsID":null,"Label":null,"Min":null,"Max":null,"Points":null,"NewPoints":0,"Abnormal":1,"AbnormalFlag":null,"LDescription":null,"MDescription":null,"Email":"test@test.com","Units":"mg/l","BinaryAnswer":null,"MeasuresId":2286,"MeasuredValue":0.9999,"LabDate":"10/06/2014","Operator":"","SortOrder":null,"AnswersQCID":null,"AnswersAuthorID":90889,"LabCorpID":"180062","NumOfDupes":0,"SortCat":0,"SortMeas1":null,"SortAnsLabDate":"\/Date(1412533800000)\/","ChoiceText":null},{"CategoryId":64,"CatName":"JOK-45","ShortName":"PIGT","ReferenceMax":null,"ReferenceMin":null,"RefRangeMax":null,"RefRangeMin":null,"LevelsID":null,"Label":null,"Min":null,"Max":null,"Points":null,"NewPoints":0,"Abnormal":0,"AbnormalFlag":null,"LDescription":null,"MDescription":null,"Email":"test@test.com","Units":"mg/l","BinaryAnswer":null,"MeasuresId":2286,"MeasuredValue":23.3,"LabDate":"10/06/2014","Operator":"","SortOrder":null,"AnswersQCID":null,"AnswersAuthorID":90889,"LabCorpID":"180062","NumOfDupes":0,"SortCat":0,"SortMeas1":null,"SortAnsLabDate":"\/Date(1412533800000)\/","ChoiceText":null}];

                //////////////////// this has unique set of dates //////////////////
                $(data).each(function(index,item){
                   tmpLabDates[index]=item.LabDate;
                    //tmpLabDates[index]=mon+'/'+dt+'/'+year;
                })

                $(data).each(function(index,item){
                    allShortNames[index]=item.ShortName;
                })

                $(data).each(function(index,item){
                    alldupGroups[index]=item.CatName;
                });

                allunqGroups=alldupGroups.filter(onlyUnique);

               //remove redundencies
               unqLabDates = tmpLabDates.filter( onlyUnique );
                allunqShortNames=allShortNames.filter(onlyUnique);

                var catID='';
                var reference='';
                var uom='';
                var aRecord='';

                //added:10/26/2016
                //add an empty to unqLabDates so it generates a column on Lab dates side.this will prevent
                //overllaping of from/to datepickers boxes with static column headers.
                if(data.length==0){
                    unqLabDates.push('');
                }

                //manually reconstructing the data from DB with each row to have its own labdates collection
                    for(var i=0;i<allunqGroups.length;i++){

                        for(var j=0;j<allunqShortNames.length;j++){

                            $(data).each(function(index,item){

                                if(allunqGroups[i]==item.CatName)
                                {
                                    if(allunqShortNames[j]==item.ShortName)
                                    {
                                        //prints catId accurately
                                        catID=item.CategoryId;


                                        //settting the Reference column value based on ReferenceMin and ReferecenMax
                                        if(  (item.ReferenceMin == null) | item.ReferenceMax==null){
                                            reference='-';
                                            // alert('hello');
                                        }
                                        else
                                        {
                                            reference = item.ReferenceMin.toString()+'-'+item.ReferenceMax.toString();
                                        }

                                        //reference= item.ReferenceMax;    //if RefernceMax and Min not null concat min-max value for reference
                                        uom=item.Units;


                                        //this will add measure value with its operator symbol
                                        var measureValOperator = item.Operator + item.MeasuredValue.toString();

                                        if(item.BinaryAnswer!=null){
                                            var trimmedBAValue=item.BinaryAnswer.trim();
                                        }
                                        else{
                                            var trimmedBAValue=item.BinaryAnswer;
                                        }

                                        var labDateAndMeasureValue = {LB:item.LabDate, MV:measureValOperator, CT:item.ChoiceText, BA:trimmedBAValue, ABF: Abnormal};
                                        //var labDateAndMeasureValue = {LB:mon+'/'+dt+'/'+year, MV:item.MeasuredValue};

                                        //repeated ones are the dates:store all labdates for teh current matching combination.
                                        datesArray.push(labDateAndMeasureValue);

                                    }
                                }
                            });

                            var foundCatName=allunqGroups[i];
                            var foundShortName=allunqShortNames[j];

                            //this condition prevents adding blank arrays for labdates. also this stops the short name repeat in a catname
                            if(datesArray.length >0){

                               //'ReferenceMax' property here is string concatenation of ReferenceMin and ReferenceMax (see above).
                                aRecord = {CategoryId:catID, CatName:foundCatName, ShortName:foundShortName, ReferenceMax:reference,Units:uom, LabDatesandValues:datesArray};

                                //add object to the unqiue objects array
                                arrayForRecords.push(aRecord);

                                //clear the labdates array for the labdates of the next set of CatName and ShortName
                                datesArray=[];
                            }

                        }
                    }

//code to configure grid after this

}

the grid places the measureValue in the matching cell.

what i need to do is there is property in json array called Abnormal. when Abnormal == 1 the cell of its 'measureValue' must be colored in red and underlined. when Abnormal== 0 cell color must be set to default and remove underlining.

ABF property contains the abnormal value

How do achieve this?

Here is the complete code for the component is in jsfiddle-k9

Answer 1

I'm not sure that I full understand your code, but it seems that you try to implement pivot table. I'd recommend you to use jqPivot method. It's described in old version of jqGrid (see here), but the old implementation contains many bugs. Because of that I full rewrote the code in free jqGrid, trying to hold the backward compatibility. I added many new features described in the wiki article. It allows to reduce the code for creating the demo with the following code

$("#list483").jqGrid("jqPivot",
    data,
    {
        frozenStaticCols: true,
        xDimension: [
            {/*x0*/ dataName: "CatName", width: 200, label: "Lab Test" },
            {/*x1*/ dataName: "ShortName", skipGrouping: true },
            {/*x2*/ dataName: "ReferenceMin", hidden: true, skipGrouping: true },
            {/*x3*/ dataName: "ReferenceMax", width: 80, align: "center",
                label: "Reference", skipGrouping: true,
                formatter: function (cellvalue, options, rowObject) {
                    // x2 - ReferenceMin, x3 - ReferenceMax
                    return rowObject.x2 === null || rowObject.x3 === null ?
                        "-" :
                        String(rowObject.x2) + "-" + String(cellvalue);
                }
            },
            {/*x4*/ dataName: "Units", width: 50, align: "center", skipGrouping: true },
            {/*x5*/ dataName: "Abnormal", hidden: true, skipGrouping: true }
        ],
        yDimension: [{
            dataName: "LabDate",
            sorttype: "date"
        }],
        aggregates: [{
            member: "MeasuredValue",
            cellattr: function(rowId, cellValue, rawObject, cm, rdata) {
                if (rawObject != null && rawObject.x5 === 1) {//x5 - Abnormal
                    return ' class="ui-state-error"';
                }
            },
            aggregator: "max"
        }]
    },
// grid options
    {
        iconSet: "fontAwesome",
        cmTemplate: { autoResizable: true, width: 90 },
        shrinkToFit: false,
        useUnformattedDataForCellAttr: false,
        //autoresizeOnLoad: true,
        autoResizing: { compact: true },
        /*rowattr: function (item) {
            if (item.x5 === 1) {
                return {"class": "ui-state-error"};
            }
        },*/
        groupingView: {
            groupField: ["x1"],
            groupColumnShow: [false],
            groupText: ['<b>{0}</b>']
        },
        width: 450,
        pager: true,
        rowNum: 20,
        rowList: [5, 10, 20, 100, "10000:All"],
        caption: "<b>K-9 Test Results</b>"
    }
);

which produces the results like on the picture below

If I comment (or remove) the block with

cellattr: function(rowId, cellValue, rawObject, cm, rdata) {
    if (rawObject != null && rawObject.x5 === 1) {//x5 - Abnormal
        return ' class="ui-state-error"';
    }
}

and uncomment the block

rowattr: function (item) {
    if (item.x5 === 1) {
        return {"class": "ui-state-error"};
    }
}

we get the results like

See another demo.

UPDATED: One more demo shows new possibility of jqPivot, which I just implemented. It allows to use additionalProperty: true instead of hidden: true, skipGrouping: true in xDimension. The last demo uses

xDimension: [
    {/*x0*/ dataName: "CatName", width: 200, label: "Lab Test" },
    {/*x1*/ dataName: "ShortName", skipGrouping: true },
    {/*x2*/ dataName: "ReferenceMin", additionalProperty: true },
    {/*x3*/ dataName: "ReferenceMax", width: 80, align: "center",
        label: "Reference", skipGrouping: true,
        formatter: function (cellvalue, options, rowObject) {
            // x2 - ReferenceMin, x3 - ReferenceMax
            return rowObject.x2 === null || rowObject.x3 === null ?
                "-" :
                String(rowObject.x2) + "-" + String(cellvalue);
        }
    },
    {/*x4*/ dataName: "Units", width: 50, align: "center", skipGrouping: true },
    {/*x5*/ dataName: "Abnormal", additionalProperty: true }
]

The main advantage of this: removing unneeded hidden columns from DOM of the grid. The corresonding properties will be saved in the local data and can be used for example in custom formatters, cellattr or rowattr. See the wiki artikle for additional information about additionalProperties.