UI Expression Error (dashboard) @ a!gridField

Question

First off, we are aware that this interface needs to be modernized and we have created a ticket to do so. In the meantime, we need to remediate the error for our users until the interface is modernized.

Background:

We have a report that groups can pull to see their vanpool vehicle switch. What is happening, is if 2 switches (get a different vehicle) are completed for a given vanpool within the same month, an error is thrown on the interface.In the example, the vehicle was switched out for a different one twice in the same month. See code below.

Error Evaluating UI Expression

Expression evaluation error [evaluation ID = APN6ZIQL] in rule 'fma_switchreportdashboard' at function a!gridField_19r1 [line 159]: A grid component [label=“”] has an invalid value for “totalCount”. “totalCount” must not be null or less than the number of items in any of the “data” arrays, but “totalCount” was 1 and the largest column data array had 2 items.

load(
    /*Used to determine what the user should see on the interface*/
  local!userGpBrCount: if(isnull(ri!userLocationDetGpbrList), 0, length(ri!userLocationDetGpbrList)),
  /*GpBr filter for users with multiple group branches, defaults to first gpbr in list*/
  local!gpBrFilter: if(local!userGpBrCount<1, null, if(local!userGpBrCount=1, {ri!userLocationDetGpbrList}[1], index(ri!userLocationDetGpbrList, 1, null))),
  local!dateRangeFrom: today()-7,
  local!dateRangeTo: today(),
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize:20,
    sort: a!sortInfo(
      field: "vanpoolid",
      ascending: false
    )
  ),
  with(
    local!datasubset: if(local!userGpBrCount<1, {},  
    a!queryEntity_18r3(
      cons!ENTITY_FMA_VIEW_SWITCH_REPORT,
      a!query(
        selection: a!querySelection(columns: {
          a!queryColumn(field: "vanpoolid"),
          a!queryColumn(field: "gpbr"), 
          a!queryColumn(field: "vanpoolcoordinator"),  
          a!queryColumn(field: "employer"),  
          a!queryColumn(field: "subsidy"),  
          a!queryColumn(field: "vehicleswitchdate"),  
          a!queryColumn(field: "oldunit"),  
          a!queryColumn(field: "oldodometer"),  
          a!queryColumn(field: "oldyear"),  
          a!queryColumn(field: "oldmake"),  
          a!queryColumn(field: "oldmodel"),  
          a!queryColumn(field: "oldseries"),  
          a!queryColumn(field: "oldtype"),  
          a!queryColumn(field: "newunit"),            
          a!queryColumn(field: "newodometer"),  
          a!queryColumn(field: "newyear"),
          a!queryColumn(field: "newmake"),
          a!queryColumn(field: "newmodel"), 
          a!queryColumn(field: "newseries"),  
          a!queryColumn(field: "newtype"),            
          a!queryColumn(field: "newcontractmileage"),  
          a!queryColumn(field: "newbaserate"),
          a!queryColumn(field: "newaddons"),
          a!queryColumn(field: "newtotalrate"),  
          a!queryColumn(field: "oldbaserate"),
          a!queryColumn(field: "oldtotalrate"),
          a!queryColumn(field: "oldaddons")

        }),
          logicalExpression: a!queryLogicalExpression(
            operator: "AND",
              filters: {
              a!queryFilter(
                field: "gpbr",
                operator: "=",
                value: local!gpBrFilter.gpbr
              ),
              a!queryFilter(
                field: "vehicleswitchdate",
                operator: ">=",
                value:if(rule!APN_isBlank(dateRangeFrom),today()-7,dateRangeFrom)
              ),              
              a!queryFilter(
                field: "vehicleswitchdate",
                operator: "<=",
                value:if(rule!APN_isBlank(dateRangeTo),today(),dateRangeTo)
              ) 
            }
          ),        
        pagingInfo: local!pagingInfo
      )
    )),
  

    local!exportUri: getdatasubsetdownloadlinkfromprocess(
      processModelUuid: cons!FMA_EXPORT_VEHICLE_SWITCH_REPORT_PROCESS,
      input: a!toJson_17r1(
          {
            gpBrFilter: local!gpBrFilter.location_det_id,
            gpBrCode: local!gpBrFilter.gpbr,
            dateRangeFrom:dateRangeFrom,
            dateRangeTo:dateRangeTo
          }
      )  
    ),  
  
    a!dashboardLayout_17r1(
      firstColumnContents:{
        if(local!userGpBrCount<1,
          a!textField(
            value: "You do not have any group branches to view vehicle switch information.",
            readOnly: true()
          ),
          {
            a!sectionLayout_17r1(
              firstColumnContents: {
                if(local!userGpBrCount>1,
                    {
                      a!dropdownField_20r2(
                        label: "Group Branch",
                        labelPosition: "ABOVE",
                        placeholderLabel:"",
                        choiceLabels: ri!userLocationDetGpbrList.gpbr,
                        choiceValues: ri!userLocationDetGpbrList,
                        value: local!gpBrFilter,
                        saveInto: {local!gpBrFilter, a!save(local!pagingInfo.startIndex, 1)},
                        validations: {}
                      )
                    },
                    a!richTextDisplayField(
                      labelPosition: "COLLAPSED",
                      value: a!richTextItem_18r1(
                        text: "Group Branch " & index(ri!userLocationDetGpbrList,"gpbr",{}),
                        style: "STRONG"
                      )
                    )
                  ),
                  a!dateField(
                    label:"Date Range From:",
                    value:dateRangeFrom,
                    saveInto: dateRangeFrom
                  ),
                  a!dateField(
                    label:"Date Range To:",
                    value:dateRangeTo,
                    saveInto: dateRangeTo,
                    validations:if(or(rule!APN_isBlank(dateRangeTo),dateRangeTo < dateRangeFrom),"Select a date greater than the Date Range From",null)
                  ) 

              },
              secondColumnContents: {
                /*Blank field added to second column so gpbr dropdown will 
                only span half of the screen*/
                a!textField(
                  labelPosition: "COLLAPSED",
                  value: null,
                  readOnly: true
                )
              }
            ),
              a!sectionLayout_17r1(
                  firstColumnContents: {
                    /*If there are results in the grid and the export uri generated, then include
                    a link to download the full datasubset (with filters) to excel.*/
                    if(or(rule!APN_isEmpty(local!exportUri), local!datasubset.totalCount<1),
                      {},
                      a!linkField(
                        labelPosition: "COLLAPSED",
                        links: {
                          a!safeLink(
                            label: "Click Here to Export Complete Report to Excel",
                            uri: local!exportUri
                          )
                        }
                      )
                    ),
                    if(rule!APN_isBlank(local!datasubset),{},
                      a!gridField_19r1(
                        totalCount: local!datasubset.totalCount,
                        columns: {
                          a!gridTextColumn(
                            label: "Vanpool",
                            field: "vanpoolid",
                            data: index(local!datasubset.data, "vanpoolid", {})
                          ),
                          /*a!gridTextColumn(
                            label: "GpBr",
                            field: "gpbr",
                            data: index(local!datasubset.data, "gpbr", {})
                          ),*/
                          a!gridTextColumn(
                            label: "Coordinator",
                            field: "vanpoolcoordinator",
                            data: index(local!datasubset.data, "vanpoolcoordinator", {})
                          ),
                          a!gridTextColumn(
                            label: "Employer",
                            field: "employer",
                            data:index(local!datasubset.data, "employer", {})
                          ),
                          a!gridTextColumn(
                            label: "Subsidy",
                            field: "subsidy",
                            data: index(local!datasubset.data, "subsidy", {})
                          ),
                          a!gridTextColumn(
                            label: "Switch Date",
                            field: "vehicleswitchdate",
                            data: index(local!datasubset.data, "vehicleswitchdate", {})
                          ),
                          a!gridTextColumn(
                            label: "Old Unit",
                            field: "oldunit",
                            data: index(local!datasubset.data, "oldunit", {})
                          ),
                          a!gridTextColumn(
                            label: "Old Odometer",
                            field: "oldodometer",
                            data: index(local!datasubset.data, "oldodometer", {})
                          ),
                            a!gridTextColumn(
                            label: "Old YMMS",
                            field: "oldyear",
                            data: apply(rule!FMA_getYmmsForRetirement(
                              year_value : _,
                              make_value :_,
                              model_value :_,
                              series_value :_
                            ) ,
                            merge(
                              index(local!datasubset.data, "oldyear", {}),
                            index(local!datasubset.data, "oldmake", {}), 
                            index(local!datasubset.data, "oldmodel", {}), 
                            index(local!datasubset.data, "oldseries", {})
                            )
                            )
                          ),
                          /*a!gridTextColumn(
                            label: "Old Year",
                            field: "oldyear",
                            data: index(local!datasubset.data, "oldyear", {})
                          ),
                        
  
                          a!gridTextColumn(
                            label: "Old Make",
                            field: "oldmake",
                            data: index(local!datasubset.data, "oldmake", {})
                          ),
                        
  
                          a!gridTextColumn(
                            label: "Old Model",
                            field: "oldmodel",
                            data: index(local!datasubset.data, "oldmodel", {})
                          ),
                        
  
                          a!gridTextColumn(
                            label: "Old Series",
                            field: "oldseries",
                            data: index(local!datasubset.data, "oldseries", {})
                          ),*/
                            a!gridTextColumn(
                            label: "Old Type",
                            field: "oldtype",
                            data: index(local!datasubset.data, "oldtype", {})
                          ),
                        
                          a!gridTextColumn(
                            label: "Old Base Rate",
                            field: "oldbaserate",
                            data: index(local!datasubset.data, "oldbaserate", {})
                          ),
                          a!gridTextColumn(
                            label: "Old Add ons",
                            field: "oldaddons",
                            data: index(local!datasubset.data, "oldaddons", {})
                          ),
                          a!gridTextColumn(
                            label: "Old Total Rate",
                            field: "oldtotalrate",
                            data: index(local!datasubset.data, "oldtotalrate", {})
                          ),
  
                          a!gridTextColumn(
                            label: "New Unit",
                            field: "newunit",
                            data: index(local!datasubset.data, "newunit", {})
                          ),
                             a!gridTextColumn(
                            label: "New YMMS",
                            field: "newyear",
                            data: apply(rule!FMA_getYmmsForRetirement(
                              year_value : _,
                              make_value :_,
                              model_value :_,
                              series_value :_
                            ) ,
                            merge(
                              index(local!datasubset.data, "newyear", {}),
                            index(local!datasubset.data, "newmake", {}), 
                            index(local!datasubset.data, "newmodel", {}), 
                            index(local!datasubset.data, "newseries", {})
                            )
                            )
                          ),
  
                         /* a!gridTextColumn(
                            label: "New Year",
                            field: "newyear",
                            data: index(local!datasubset.data, "newyear", {})
                          ),                      
  
                        
  
                          a!gridTextColumn(
                            label: "New Make",
                            field: "newmake",
                            data: index(local!datasubset.data, "newmake", {})
                          ),
                        
  
                          a!gridTextColumn(
                            label: "New Model",
                            field: "newmodel",
                            data: index(local!datasubset.data, "newmodel", {})
                          ),
                        
  
                          a!gridTextColumn(
                            label: "New Series",
                            field: "newseries",
                            data: index(local!datasubset.data, "newseries", {})
                          ),
                        */
  
                          a!gridTextColumn(
                            label: "New Type",
                            field: "newtype",
                            data: index(local!datasubset.data, "newtype", {})
                          ),
                        
  
                          a!gridTextColumn(
                            label: "New Contracted Milage",
                            field: "newcontractmileage",
                            data: index(local!datasubset.data, "newcontractmileage", {})
                          ),
                        
  
                          a!gridTextColumn(
                            label: "New Base Rate",
                            field: "newbaserate",
                            data: index(local!datasubset.data, "newbaserate", {})
                          ),
                        
  
                          a!gridTextColumn(
                            label: "New AddOns",
                            field: "newaddons",
                            data: index(local!datasubset.data, "newaddons", {})
                          ),
                        
  
                          a!gridTextColumn(
                            label: "New Total Rate",
                            field: "newtotalrate",
                            data: index(local!datasubset.data, "newtotalrate", {})
                          )
                        
  
                        },
                        value: local!pagingInfo,
                        saveInto: local!pagingInfo
                      )
                    )
                  }
          )
      }
      )
      }
    )
  )
)

Mike Schmitt · Answer

This error happens when a view's primary key column is not unique. The totalCount in a query is calculated by counting the (unique) primary key entries, so in your case the query is pulling back 2 rows but they both have the same primary key value so the totalCount is evaluating to 1. 
 The fix for this would be to edit your view such that the primary key value is guaranteed to be unique, or add a new column to it which will be unique, then update your CDT to use that new column as the primary key.

Mike Schmitt · Answer

I usually try to make the primary key of my new view the same as the primary key of the first table it's pulling its data from. When this isn't possible (i.e. when you expect each row in the primary source table to show up multiple times), sometimes what I've been able to do is concatenate together that table's primary key id with the primary key from the other table(s) being joined in, in such a way that we get a text column with a unique string (e.g. "35-7"). It really depends on your data structure in question - it might be easier to check with your company's SQL professional honestly.

Mike Schmitt · Answer

As a separate side-note, you really don't need to spend 25 lines with a!queryColumn() statements. The only reason to use the selection parameter in the query usually is if you are only pulling back a couple of the columns, or if the view or table in question has an unusually large number of columns. In a paged query like this it should be 100% unnecessary. 
 Also I'd recommend converting your grid to the modern a!gridField() component - it's much more versatile and has some added features that were sorely missed in the old version which you're using here (like the ability to use rich text in grid cells).