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.
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 ) ) } ) } ) } ) ) )
Discussion posts and replies are publicly visible
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.
Thank you Mike. I will need to add a new column to the view, but not sure exactly how to do that on a view. I am assuming I need to add it somewhere in the start of the code. Also, can this new column be configured to auto increment in order to be unique?
CREATE OR REPLACE VIEW view_switch_report AS SELECT vm.gpbr_cd AS gpbr ,vm.vanpool_id AS vanpoolid ,CONCAT ( p.part_frst_nm,' ',p.part_lst_nm) AS vanpoolcoordinator ,e.emplr_name AS employer ,(CASE WHEN isnull(sd.subsidy) THEN '' ELSE sd.subsidy END) AS subsidy ,vdo.datecompleted AS vehicleswitchdate ,vdo.oldunitnumber AS oldunit ,vmm.current_odo_reading_miles AS oldodometer
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.
Just a guess, but if the "vdo" table has an ID, you might be able to use that as the PK for this view. Also when you actually configure the CDT in Appian, you don't need to set it as auto-increment; that property only applies when inserting data, and since this is a DB view it won't be possible to insert data
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).