Hello there,
I am receiving error "Interface Definition: Expression evaluation error: An error occurred while executing a smart service: The custom cell positions and custom cell values arrays must have the same length. (APNX-1-4505-035)" even though i have exact count on position and values which are 50 columns in count.
Any help to fix this issue??
a!exportDataStoreEntityToExcel( entity: cons!VW_ENTITY, selection: a!querySelection( columns: { a!queryColumn( field: "count" ), a!queryColumn( field: "case_id" ), a!queryColumn( field: "case_source" ), a!queryColumn( field: "parent_case_id" ), a!queryColumn( field: "master_account_number" ), a!queryColumn( field: "fund" ), a!queryColumn( field: "account_number" ), a!queryColumn( field: "client_type" ), a!queryColumn( field: "transaction_type" ), a!queryColumn( field: "transaction_sub_type" ), a!queryColumn( field: "reconciliationType" ), a!queryColumn( field: "rejected_flag" ), a!queryColumn( field: "adjustment" ), a!queryColumn( field: "case_status" ), a!queryColumn( field: "case_task" ), a!queryColumn( field: "case_task_status" ), a!queryColumn( field: "monetary" ), a!queryColumn( field: "current_assignee" ), a!queryColumn( field: "lt_qt_lmt" ), a!queryColumn( field: "amount_estimated" ), a!queryColumn( field: "amount_dollars" ), a!queryColumn( field: "request_received_on" ), a!queryColumn( field: "sorting_complete" ), a!queryColumn( field: "sorting_completed_by" ), a!queryColumn( field: "sorting_completed_on" ), a!queryColumn( field: "processing_completed_by" ), a!queryColumn( field: "processing_crew_id" ), a!queryColumn( field: "processing_completed_on" ), a!queryColumn( field: "nigo" ), a!queryColumn( field: "selected_audit_count" ), a!queryColumn( field: "audit_decisons_by" ), a!queryColumn( field: "audit_complete" ), a!queryColumn( field: "audit_completed_on" ), a!queryColumn( field: "audit_return_count" ), a!queryColumn( field: "audit_return_reasons" ), a!queryColumn( field: "decided_by_IA" ), a!queryColumn( field: "ia_risk_score" ), a!queryColumn( field: "ia_reason" ), a!queryColumn( field: "reason_for_audit" ), a!queryColumn( field: "approval_decisons_by" ), a!queryColumn( field: "approval_complete" ), a!queryColumn( field: "approval_return_count" ), a!queryColumn( field: "approval_return_reasons" ), a!queryColumn( field: "cycle_time" ), a!queryColumn( field: "sorting_time" ), a!queryColumn( field: "preprocessing_time" ), a!queryColumn( field: "processing_time" ), a!queryColumn( field: "audit_time" ), a!queryColumn( field: "approval_time" ), a!queryColumn( field: "correction_review_time" ) } ), startingCell: "A2", customCellPositions: {"A1","B1","C1","D1","E1","F1","G1","H1","I1","J1","K1","L1","M1","N1","O1","P1","Q1","R1","S1","T1", "U1","V1","W1","X1","Y1","Z1","AA1","AB1","AC1","AD1","AE1","AF1","AG1","AH1","AI1","AJ1","AK1","AL1","AM1","AN1","AO1","AP1", "AQ1","AR1","AS1","AT1","AU1","AV1","AW1","AX1"}, customCellValues: { local!qwrExportData.count, local!qwrExportData.case_id, local!qwrExportData.case_source, if( isnull( local!qwrExportData.parent_case_id ), local!qwrExportData.case_id, local!qwrExportData.parent_case_id ), local!qwrExportData.master_account_number, local!qwrExportData.fund, local!qwrExportData.account_number, local!qwrExportData.client_type, local!qwrExportData.transaction_type, local!qwrExportData.transaction_sub_type, local!qwrExportData.reconciliationType, local!qwrExportData.rejected_flag, local!qwrExportData.adjustment, local!qwrExportData.case_status, local!qwrExportData.case_task, local!qwrExportData.case_task_status, local!qwrExportData.monetary, local!qwrExportData.current_assignee, local!qwrExportData.lt_qt_lmt, local!qwrExportData.amount_estimated, local!qwrExportData.amount_dollars, local!qwrExportData.request_received_on, local!qwrExportData.sorting_complete, local!qwrExportData.sorting_completed_by, local!qwrExportData.sorting_completed_on, local!qwrExportData.processing_completed_by, local!qwrExportData.processing_crew_id, local!qwrExportData.processing_completed_on, local!qwrExportData.nigo, local!qwrExportData.selected_audit_count, local!qwrExportData.audit_decisons_by, local!qwrExportData.audit_complete, local!qwrExportData.audit_completed_on, local!qwrExportData.audit_return_count, local!qwrExportData.audit_return_reasons, local!qwrExportData.decided_by_IA, local!qwrExportData.ia_risk_score, local!qwrExportData.ia_reason, local!qwrExportData.reason_for_audit, local!qwrExportData.approval_decisons_by, local!qwrExportData.approval_complete, local!qwrExportData.approval_return_count, local!qwrExportData.approval_return_reasons, local!qwrExportData.cycle_time, local!qwrExportData.sorting_time, local!qwrExportData.preprocessing_time, local!qwrExportData.processing_time, local!qwrExportData.audit_time, local!qwrExportData.approval_time, local!qwrExportData.correction_review_time } )
Discussion posts and replies are publicly visible
As a quick test, have you tried removing your startingCell input, since you are defining customCellPositions anyway?
yes i tried that as well and receiving same error
Another idea might be to ensure null values are not creating funky behavior in in customCellValues by reducing the value count, to test I would comment out your local variables there and try:
a!forEach( items: enumerate(50), expression: "test" )
If this works, check/replace each line for null values with blank "".
Additionally, if any of the local values are multiple, this could cause issues by raising the count of values.
Otherwise, we have seen issues with exporting over 50 columns in some situations, but your message is not related to this - still could cut the list by 1 to see if 49 shows the same issues, if this is not due to nulls.
I tried to add the null check in various formats - no luck .Tried to reduce the columns count as well - no luck.
Not sure if am doing anything wrong.
a!exportDataStoreEntityToExcel( entity: cons!FAS_DSE_VW_QUALITY_WORK_EXPORT, selection: a!querySelection( columns: { a!queryColumn( field: "count" ), a!queryColumn( field: "case_id" ), a!queryColumn( field: "case_source" ), a!queryColumn( field: "parent_case_id" ), a!queryColumn( field: "master_account_number" ), a!queryColumn( field: "fund" ), a!queryColumn( field: "account_number" ), a!queryColumn( field: "client_type" ), a!queryColumn( field: "transaction_type" ), a!queryColumn( field: "transaction_sub_type" ), a!queryColumn( field: "reconciliationType" ), a!queryColumn( field: "rejected_flag" ), a!queryColumn( field: "adjustment" ), a!queryColumn( field: "case_status" ), a!queryColumn( field: "case_task" ), a!queryColumn( field: "case_task_status" ), a!queryColumn( field: "monetary" ), a!queryColumn( field: "current_assignee" ), a!queryColumn( field: "lt_qt_lmt" ), a!queryColumn( field: "amount_estimated" ), a!queryColumn( field: "amount_dollars" ), a!queryColumn( field: "request_received_on" ), a!queryColumn( field: "sorting_complete" ), a!queryColumn( field: "sorting_completed_by" ), a!queryColumn( field: "sorting_completed_on" ), a!queryColumn( field: "processing_completed_by" ), a!queryColumn( field: "processing_crew_id" ), a!queryColumn( field: "processing_completed_on" ), a!queryColumn( field: "nigo" ), a!queryColumn( field: "selected_audit_count" ), a!queryColumn( field: "audit_decisons_by" ), a!queryColumn( field: "audit_complete" ), a!queryColumn( field: "audit_completed_on" ), a!queryColumn( field: "audit_return_count" ), a!queryColumn( field: "audit_return_reasons" ), a!queryColumn( field: "decided_by_IA" ), a!queryColumn( field: "ia_risk_score" ), a!queryColumn( field: "ia_reason" ), a!queryColumn( field: "reason_for_audit" ), a!queryColumn( field: "approval_decisons_by" ), a!queryColumn( field: "approval_complete" ), a!queryColumn( field: "approval_return_count" ), a!queryColumn( field: "approval_return_reasons" ), a!queryColumn( field: "cycle_time" ), a!queryColumn( field: "sorting_time" ), a!queryColumn( field: "preprocessing_time" ), a!queryColumn( field: "processing_time" ), a!queryColumn( field: "audit_time" ), a!queryColumn( field: "approval_time" ), a!queryColumn( field: "correction_review_time" ) } ), filters: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: if( ri!filter = 1, "processing_completed_on", ri!filter = 2, "request_received_on", "audit_completed_on" ), operator: ">=", value: todatetime(ri!startDate) ), a!queryFilter( field: if( ri!filter = 1, "processing_completed_on", ri!filter = 2, "request_received_on", "audit_completed_on" ), operator: "<=", value: todatetime(ri!endDate) ), if( ri!caseFilter = 4, a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten(ri!monetaryOnly)) ), if( ri!caseFilter = 5, a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten(ri!clericalOnly)) ), if( ri!caseFilter = 6, a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten({ri!clericalOnly, ri!monetaryOnly})) ), {} ) ) ), if( ri!caseFilter = 7, a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten(ri!onboardingOrContracts)) ), /*Added this below logic to include all OS-OPS & IS-OPS data to be retrieved as part of the export*/ if( ri!caseFilter = 1, concat( a!queryFilter( field: "transaction_type_ref_id", operator: "not in", value: tointeger(a!flatten(ri!onboardingOrContracts)) ), a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger( a!flatten( { ri!clericalOnly, ri!monetaryOnly, ri!correspondenceOnly } ) ) ) ), a!queryFilter( field: "transaction_type_ref_id", operator: "not in", value: tointeger(a!flatten(ri!onboardingOrContracts)) ) ) ), if( ri!caseFilter = 6, a!queryFilter( field: "selected_audit_count", operator: ">=", value: 1 ), {} ) }, /*correspondence*/ logicalexpressions: if( ri!caseFilter = 3, a!queryLogicalExpression( operator: "OR", filters: { a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten(ri!correspondenceOnly)) ), a!queryFilter( field: "rejected_flag", operator: "=", value: "Y" ), }, ), {} ), ignoreFiltersWithEmptyValues: false ), documentName: if( ri!caseFilter = 7, "IS-OPS Quality Work Export " & now(), ri!caseFilter = 2, "OS-OPS Quality Work Export " & now(), "OS-OPS & IS-OPS Quality Work Export " & now(), ), startingCell: "A2", customCellPositions: {"A1","B1","C1","D1","E1","F1","G1","H1","I1","J1","K1","L1","M1","N1","O1","P1","Q1","R1","S1","T1", "U1","V1","W1","X1","Y1","Z1","AA1","AB1","AC1","AD1","AE1","AF1","AG1","AH1","AI1","AJ1","AK1","AL1","AM1","AN1","AO1","AP1", "AQ1","AR1","AS1","AT1","AU1","AV1","AW1","AX1"}, customCellValues: { rule!MS_isBlank(local!qwrExportData.count), rule!MS_isBlank(local!qwrExportData.case_id), rule!MS_isBlank(local!qwrExportData.case_source), if( rule!MS_isBlank(local!qwrExportData.parent_case_id), local!qwrExportData.case_id, local!qwrExportData.parent_case_id), rule!MS_isBlank(local!qwrExportData.master_account_number), rule!MS_isBlank(local!qwrExportData.fund), rule!MS_isBlank(local!qwrExportData.account_number), rule!MS_isBlank(local!qwrExportData.client_type), rule!MS_isBlank(local!qwrExportData.transaction_type), rule!MS_isBlank(local!qwrExportData.transaction_sub_type), rule!MS_isBlank(local!qwrExportData.reconciliationType), rule!MS_isBlank(local!qwrExportData.rejected_flag), rule!MS_isBlank(local!qwrExportData.adjustment), rule!MS_isBlank(local!qwrExportData.case_status), rule!MS_isBlank(local!qwrExportData.case_task), rule!MS_isBlank(local!qwrExportData.case_task_status), rule!MS_isBlank(local!qwrExportData.monetary), rule!MS_isBlank(local!qwrExportData.current_assignee), rule!MS_isBlank(local!qwrExportData.lt_qt_lmt), rule!MS_isBlank(local!qwrExportData.amount_estimated), rule!MS_isBlank(local!qwrExportData.amount_dollars), rule!MS_isBlank(local!qwrExportData.request_received_on), rule!MS_isBlank(local!qwrExportData.sorting_complete), rule!MS_isBlank(local!qwrExportData.sorting_completed_by), rule!MS_isBlank(local!qwrExportData.sorting_completed_on), rule!MS_isBlank(local!qwrExportData.processing_completed_by), rule!MS_isBlank(local!qwrExportData.processing_crew_id), rule!MS_isBlank(local!qwrExportData.processing_completed_on), rule!MS_isBlank(local!qwrExportData.nigo), rule!MS_isBlank(local!qwrExportData.selected_audit_count), rule!MS_isBlank(local!qwrExportData.audit_decisons_by), rule!MS_isBlank(local!qwrExportData.audit_complete), rule!MS_isBlank(local!qwrExportData.audit_completed_on), rule!MS_isBlank(local!qwrExportData.audit_return_count), rule!MS_isBlank(local!qwrExportData.audit_return_reasons), rule!MS_isBlank(local!qwrExportData.decided_by_IA), rule!MS_isBlank(local!qwrExportData.ia_risk_score), rule!MS_isBlank(local!qwrExportData.ia_reason), rule!MS_isBlank(local!qwrExportData.reason_for_audit), rule!MS_isBlank(local!qwrExportData.approval_decisons_by), rule!MS_isBlank(local!qwrExportData.approval_complete), rule!MS_isBlank(local!qwrExportData.approval_return_count), rule!MS_isBlank(local!qwrExportData.approval_return_reasons), rule!MS_isBlank(local!qwrExportData.cycle_time), rule!MS_isBlank(local!qwrExportData.sorting_time), rule!MS_isBlank(local!qwrExportData.preprocessing_time), rule!MS_isBlank(local!qwrExportData.processing_time), rule!MS_isBlank(local!qwrExportData.audit_time), rule!MS_isBlank(local!qwrExportData.approval_time), rule!MS_isBlank(local!qwrExportData.correction_review_time) }, saveInFolder: cons!FAS_ADHOC_REPORTS_FOLDER, onSuccess: a!save(local!exportDocId, fv!newDocument), /*This displays an error if there is an issue executing the the save*/ onError: a!save( local!errorMessage, "Error Exporting File to CSV" ) ),
{ if(isnull(index(local!qwrExportData,"count","")), "", index(local!qwrExportData,"count","")), if(isnull(index(local!qwrExportData,"case_id","")), "", index(local!qwrExportData,"case_id","")), if(isnull(index(local!qwrExportData,"case_source","")), "",index(local!qwrExportData,"case_source","")), if( isnull( index(local!qwrExportData,"parent_case_id","") ), index(local!qwrExportData,"case_id",""), index(local!qwrExportData,"parent_case_id",""), ), if(isnull(index(local!qwrExportData,"master_account_number","")), "", index(local!qwrExportData,"master_account_number","")), if(isnull(index(local!qwrExportData,"fund","")), "", index(local!qwrExportData,"fund","")), if(isnull(index(local!qwrExportData,"account_number","")), "", index(local!qwrExportData,"account_number","")), if(isnull(index(local!qwrExportData,"client_type","")), "",index(local!qwrExportData,"client_type","")), if(isnull(index(local!qwrExportData,"transaction_type","")), "",index(local!qwrExportData,"transaction_type","")), if(isnull(index(local!qwrExportData,"transaction_sub_type","")), "",index(local!qwrExportData,"transaction_sub_type","")), if(isnull(index(local!qwrExportData,"reconciliationType","")), "", index(local!qwrExportData,"reconciliationType","")), if(isnull(index(local!qwrExportData,"rejected_flag","")), "", index(local!qwrExportData,"rejected_flag","")), if(isnull(index(local!qwrExportData,"adjustment","")), "", index(local!qwrExportData,"adjustment","")), if(isnull(index(local!qwrExportData,"case_status","")), "", index(local!qwrExportData,"case_status","")), if(isnull(index(local!qwrExportData,"case_task","")), "", index(local!qwrExportData,"case_task","")), if(isnull(index(local!qwrExportData,"case_task_status","")), "", index(local!qwrExportData,"case_task_status","")), if(isnull(index(local!qwrExportData,"monetary","")), "", index(local!qwrExportData,"monetary","")), if(isnull(index(local!qwrExportData,"current_assignee","")), "", index(local!qwrExportData,"current_assignee","")), if(isnull(index(local!qwrExportData,"lt_qt_lmt","")), "", index(local!qwrExportData,"lt_qt_lmt","")), if(isnull(index(local!qwrExportData,"amount_estimated","")), "",index(local!qwrExportData,"amount_estimated","")), if(isnull(index(local!qwrExportData,"amount_dollars","")), "",index(local!qwrExportData,"amount_dollars","")), if(isnull(index(local!qwrExportData,"request_received_on","")), "", index(local!qwrExportData,"request_received_on","")), if(isnull(index(local!qwrExportData,"sorting_complete","")), "",index(local!qwrExportData,"sorting_complete","")), if(isnull(index(local!qwrExportData,"sorting_completed_by","")), "",index(local!qwrExportData,"sorting_completed_by","")), if(isnull(index(local!qwrExportData,"sorting_completed_on","")), "", index(local!qwrExportData,"sorting_completed_on","")), if(isnull(index(local!qwrExportData,"processing_completed_by","")), "", index(local!qwrExportData,"processing_completed_by","")), if(isnull(index(local!qwrExportData,"processing_crew_id","")), "", index(local!qwrExportData,"processing_crew_id","")), if(isnull(index(local!qwrExportData,"processing_completed_on","")), "", index(local!qwrExportData,"processing_completed_on","")), if(isnull(index(local!qwrExportData,"nigo","")), "",index(local!qwrExportData,"nigo","")), if(isnull(index(local!qwrExportData,"selected_audit_count","")), "",index(local!qwrExportData,"selected_audit_count","")), if(isnull(index(local!qwrExportData,"audit_decisons_by","")), "", index(local!qwrExportData,"audit_decisons_by","")), if(isnull(index(local!qwrExportData,"audit_complete","")), "", index(local!qwrExportData,"audit_complete","")), if(isnull(index(local!qwrExportData,"audit_completed_on","")), "", index(local!qwrExportData,"audit_completed_on","")), if(isnull(index(local!qwrExportData,"audit_return_count","")), "", index(local!qwrExportData,"audit_return_count","")), if(isnull(index(local!qwrExportData,"audit_return_reasons","")), "",index(local!qwrExportData,"audit_return_reasons","")), if(isnull(index(local!qwrExportData,"decided_by_IA","")), "", index(local!qwrExportData,"decided_by_IA","")), if(isnull(index(local!qwrExportData,"ia_risk_score","")), "", index(local!qwrExportData,"ia_risk_score","")), if(isnull(index(local!qwrExportData,"ia_reason","")), "", index(local!qwrExportData,"ia_reason","")), if(isnull(index(local!qwrExportData,"reason_for_audit","")), "", index(local!qwrExportData,"reason_for_audit","")), if(isnull(index(local!qwrExportData,"approval_decisons_by","")), "",index(local!qwrExportData,"approval_decisons_by","")), if(isnull(index(local!qwrExportData,"approval_complete","")), "",index(local!qwrExportData,"approval_complete","")), if(isnull(index(local!qwrExportData,"approval_return_count","")), "", index(local!qwrExportData,"approval_return_count","")), if(isnull(index(local!qwrExportData,"approval_return_reasons","")), "",index(local!qwrExportData,"approval_return_reasons","")), if(isnull(index(local!qwrExportData,"cycle_time","")), "",index(local!qwrExportData,"cycle_time","")), if(isnull(index(local!qwrExportData,"sorting_time","")), "",index(local!qwrExportData,"sorting_time","")), if(isnull(index(local!qwrExportData,"preprocessing_time","")), "",index(local!qwrExportData,"preprocessing_time","")), if(isnull(index(local!qwrExportData,"processing_time","")), "",index(local!qwrExportData,"processing_time","")), if(isnull(index(local!qwrExportData,"audit_time","")), "",index(local!qwrExportData,"audit_time","")), if(isnull(index(local!qwrExportData,"approval_time","")), "",index(local!qwrExportData,"approval_time","")), if(isnull(index(local!qwrExportData,"correction_review_time","")),"",index(local!qwrExportData,"correction_review_time","")) },
above foreach is working fine and it is not an issue
Interesting - can you reduce it down to 1 cell only to try and get it working in the simplest format?
The a!forEach() produces a list of 50 values, so when that is working it is telling me that the list of customCellValues is returning a different value than 50. Sometimes this can be due to nested lists, etc, but it should not be a complex solution once we can pinpoint the issue.
I would also be interested in what value is returned if you comment the current expression out and test a count() on the values such as:
count( { if(isnull(property(local!qwrExportData,"count","")), "", property(local!qwrExportData,"count","")), if(isnull(property(local!qwrExportData,"case_id","")), "", property(local!qwrExportData,"case_id","")), if(isnull(property(local!qwrExportData,"case_source","")), "",property(local!qwrExportData,"case_source","")), if( isnull( property(local!qwrExportData,"parent_case_id","") ), property(local!qwrExportData,"case_id",""), property(local!qwrExportData,"parent_case_id",""), ), if(isnull(property(local!qwrExportData,"master_account_number","")), "", property(local!qwrExportData,"master_account_number","")), if(isnull(property(local!qwrExportData,"fund","")), "", property(local!qwrExportData,"fund","")), if(isnull(property(local!qwrExportData,"account_number","")), "", property(local!qwrExportData,"account_number","")), if(isnull(property(local!qwrExportData,"client_type","")), "",property(local!qwrExportData,"client_type","")), if(isnull(property(local!qwrExportData,"transaction_type","")), "",property(local!qwrExportData,"transaction_type","")), if(isnull(property(local!qwrExportData,"transaction_sub_type","")), "",property(local!qwrExportData,"transaction_sub_type","")), if(isnull(property(local!qwrExportData,"reconciliationType","")), "", property(local!qwrExportData,"reconciliationType","")), if(isnull(property(local!qwrExportData,"rejected_flag","")), "", property(local!qwrExportData,"rejected_flag","")), if(isnull(property(local!qwrExportData,"adjustment","")), "", property(local!qwrExportData,"adjustment","")), if(isnull(property(local!qwrExportData,"case_status","")), "", property(local!qwrExportData,"case_status","")), if(isnull(property(local!qwrExportData,"case_task","")), "", property(local!qwrExportData,"case_task","")), if(isnull(property(local!qwrExportData,"case_task_status","")), "", property(local!qwrExportData,"case_task_status","")), if(isnull(property(local!qwrExportData,"monetary","")), "", property(local!qwrExportData,"monetary","")), if(isnull(property(local!qwrExportData,"current_assignee","")), "", property(local!qwrExportData,"current_assignee","")), if(isnull(property(local!qwrExportData,"lt_qt_lmt","")), "", property(local!qwrExportData,"lt_qt_lmt","")), if(isnull(property(local!qwrExportData,"amount_estimated","")), "",property(local!qwrExportData,"amount_estimated","")), if(isnull(property(local!qwrExportData,"amount_dollars","")), "",property(local!qwrExportData,"amount_dollars","")), if(isnull(property(local!qwrExportData,"request_received_on","")), "", property(local!qwrExportData,"request_received_on","")), if(isnull(property(local!qwrExportData,"sorting_complete","")), "",property(local!qwrExportData,"sorting_complete","")), if(isnull(property(local!qwrExportData,"sorting_completed_by","")), "",property(local!qwrExportData,"sorting_completed_by","")), if(isnull(property(local!qwrExportData,"sorting_completed_on","")), "", property(local!qwrExportData,"sorting_completed_on","")), if(isnull(property(local!qwrExportData,"processing_completed_by","")), "", property(local!qwrExportData,"processing_completed_by","")), if(isnull(property(local!qwrExportData,"processing_crew_id","")), "", property(local!qwrExportData,"processing_crew_id","")), if(isnull(property(local!qwrExportData,"processing_completed_on","")), "", property(local!qwrExportData,"processing_completed_on","")), if(isnull(property(local!qwrExportData,"nigo","")), "",property(local!qwrExportData,"nigo","")), if(isnull(property(local!qwrExportData,"selected_audit_count","")), "",property(local!qwrExportData,"selected_audit_count","")), if(isnull(property(local!qwrExportData,"audit_decisons_by","")), "", property(local!qwrExportData,"audit_decisons_by","")), if(isnull(property(local!qwrExportData,"audit_complete","")), "", property(local!qwrExportData,"audit_complete","")), if(isnull(property(local!qwrExportData,"audit_completed_on","")), "", property(local!qwrExportData,"audit_completed_on","")), if(isnull(property(local!qwrExportData,"audit_return_count","")), "", property(local!qwrExportData,"audit_return_count","")), if(isnull(property(local!qwrExportData,"audit_return_reasons","")), "",property(local!qwrExportData,"audit_return_reasons","")), if(isnull(property(local!qwrExportData,"decided_by_IA","")), "", property(local!qwrExportData,"decided_by_IA","")), if(isnull(property(local!qwrExportData,"ia_risk_score","")), "", property(local!qwrExportData,"ia_risk_score","")), if(isnull(property(local!qwrExportData,"ia_reason","")), "", property(local!qwrExportData,"ia_reason","")), if(isnull(property(local!qwrExportData,"reason_for_audit","")), "", property(local!qwrExportData,"reason_for_audit","")), if(isnull(property(local!qwrExportData,"approval_decisons_by","")), "",property(local!qwrExportData,"approval_decisons_by","")), if(isnull(property(local!qwrExportData,"approval_complete","")), "",property(local!qwrExportData,"approval_complete","")), if(isnull(property(local!qwrExportData,"approval_return_count","")), "", property(local!qwrExportData,"approval_return_count","")), if(isnull(property(local!qwrExportData,"approval_return_reasons","")), "",property(local!qwrExportData,"approval_return_reasons","")), if(isnull(property(local!qwrExportData,"cycle_time","")), "",property(local!qwrExportData,"cycle_time","")), if(isnull(property(local!qwrExportData,"sorting_time","")), "",property(local!qwrExportData,"sorting_time","")), if(isnull(property(local!qwrExportData,"preprocessing_time","")), "",property(local!qwrExportData,"preprocessing_time","")), if(isnull(property(local!qwrExportData,"processing_time","")), "",property(local!qwrExportData,"processing_time","")), if(isnull(property(local!qwrExportData,"audit_time","")), "",property(local!qwrExportData,"audit_time","")), if(isnull(property(local!qwrExportData,"approval_time","")), "",property(local!qwrExportData,"approval_time","")), if(isnull(property(local!qwrExportData,"correction_review_time","")),"",property(local!qwrExportData,"correction_review_time","")) } )
Note I did swap out the index() calls for property(), they currently will work the same in this scenario but my preference is to use property() to return values based on field name, and index() when returning values based on integer position. There is some debate on the necessity of being particular there
Chris said:my preference is to use property() to return values based on field name, and index() when returning values based on integer position
Together, we can ensure property() prevails!
Thank you the inputs on index()/property() usage.
count() is returning 900.
I tried with 1 column as well and error remains same. But count on 1 column returned 18.
No.of rows/data that need to be exported to excel are 18rows, so 18 is multiplied by no.of columns. we have 50 columns --> 18*50 = 900.
This is not right somehow.
Let me explain my requirement, generally we are querying all data from a view and exporting based on some filters. Now business want a particular column's value to be updated with default value when it is null. Ex: if parent case id is null then update it with case id as its value etc.
Due to this requirement i am trying to set the customcells and customvalues.
Do you think I can achieve this in any other ways, i don't want to update database instead during the export only. It is used as smart service on Interface, no options for process model. So i don't find other ways to update it.
I added actual CODE below that was prior to these changes.
Please suggest!!
{ /*Quality Work Export Saves*/ a!save( local!foundData, rule!FAS_checkQualityWorkExportData( startDate: local!startDate, endDate: local!endDate, filter: local!filter, caseFilter: local!caseFilter, onboardingOrContracts: local!onboardingOrContracts, clericalOnly: local!clericalOnly, monetaryOnly: local!monetaryOnly, correspondenceOnly: local!correspondenceOnly ) ), if( not(rule!APN_isEmpty(local!foundData)), a!exportDataStoreEntityToExcel( entity: cons!FAS_DSE_VW_QUALITY_WORK_EXPORT, selection: a!querySelection( columns: { a!queryColumn( field: "count" ), a!queryColumn( field: "case_id" ), a!queryColumn( field: "case_source" ), a!queryColumn( field: "parent_case_id" ), a!queryColumn( field: "master_account_number" ), a!queryColumn( field: "fund" ), a!queryColumn( field: "account_number" ), a!queryColumn( field: "client_type" ), a!queryColumn( field: "transaction_type" ), a!queryColumn( field: "transaction_sub_type" ), a!queryColumn( field: "reconciliationType" ), a!queryColumn( field: "rejected_flag" ), a!queryColumn( field: "adjustment" ), a!queryColumn( field: "case_status" ), a!queryColumn( field: "case_task" ), a!queryColumn( field: "case_task_status" ), a!queryColumn( field: "monetary" ), a!queryColumn( field: "current_assignee" ), /*a!queryColumn(*/ /*field: "amount_type"*/ /*),*/ a!queryColumn( field: "lt_qt_lmt" ), a!queryColumn( field: "amount_estimated" ), a!queryColumn( field: "amount_dollars" ), a!queryColumn( field: "request_received_on" ), a!queryColumn( field: "sorting_complete" ), a!queryColumn( field: "sorting_completed_by" ), a!queryColumn( field: "sorting_completed_on" ), a!queryColumn( field: "processing_completed_by" ), a!queryColumn( field: "processing_crew_id" ), a!queryColumn( field: "processing_completed_on" ), a!queryColumn( field: "nigo" ), a!queryColumn( field: "selected_audit_count" ), a!queryColumn( field: "audit_decisons_by" ), a!queryColumn( field: "audit_complete" ), a!queryColumn( field: "audit_completed_on" ), a!queryColumn( field: "audit_return_count" ), a!queryColumn( field: "audit_return_reasons" ), a!queryColumn( field: "decided_by_IA" ), a!queryColumn( field: "ia_risk_score" ), a!queryColumn( field: "ia_reason" ), a!queryColumn( field: "reason_for_audit" ), a!queryColumn( field: "approval_decisons_by" ), a!queryColumn( field: "approval_complete" ), a!queryColumn( field: "approval_return_count" ), a!queryColumn( field: "approval_return_reasons" ), a!queryColumn( field: "cycle_time" ), a!queryColumn( field: "sorting_time" ), a!queryColumn( field: "preprocessing_time" ), a!queryColumn( field: "processing_time" ), a!queryColumn( field: "audit_time" ), a!queryColumn( field: "approval_time" ), a!queryColumn( field: "correction_review_time" ) } ), filters: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: if( local!filter = 1, "processing_completed_on", local!filter = 2, "request_received_on", "audit_completed_on" ), operator: ">=", value: todatetime(local!startDate) ), a!queryFilter( field: if( local!filter = 1, "processing_completed_on", local!filter = 2, "request_received_on", "audit_completed_on" ), operator: "<=", value: todatetime(local!endDate) ), if( local!caseFilter = 4, a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten(local!monetaryOnly)) ), if( local!caseFilter = 5, a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten(local!clericalOnly)) ), if( local!caseFilter = 6, a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten({local!clericalOnly, local!monetaryOnly})) ), {} ) ) ), if( local!caseFilter = 7, a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten(local!onboardingOrContracts)) ), /*Added this below logic to include all OS-OPS & IS-OPS data to be retrieved as part of the export*/ if( local!caseFilter = 1, concat( a!queryFilter( field: "transaction_type_ref_id", operator: "not in", value: tointeger(a!flatten(local!onboardingOrContracts)) ), a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger( a!flatten( { local!clericalOnly, local!monetaryOnly, local!correspondenceOnly } ) ) ) ), a!queryFilter( field: "transaction_type_ref_id", operator: "not in", value: tointeger(a!flatten(local!onboardingOrContracts)) ) ) ), if( local!caseFilter = 6, a!queryFilter( field: "selected_audit_count", operator: ">=", value: 1 ), {} ) }, /*correspondence*/ logicalexpressions: if( local!caseFilter = 3, a!queryLogicalExpression( operator: "OR", filters: { a!queryFilter( field: "transaction_type_ref_id", operator: "in", value: tointeger(a!flatten(local!correspondenceOnly)) ), a!queryFilter( field: "rejected_flag", operator: "=", value: "Y" ), }, ), {} ), ignoreFiltersWithEmptyValues: false ), documentName: if( local!caseFilter = 7, "IS-OPS Quality Work Export " & now(), local!caseFilter = 2, "OS-OPS Quality Work Export " & now(), "OS-OPS & IS-OPS Quality Work Export " & now(), ), saveInFolder: cons!FAS_ADHOC_REPORTS_FOLDER, onSuccess: a!save(local!exportDocId, fv!newDocument), /*This displays an error if there is an issue executing the the save*/ onError: a!save( local!errorMessage, "Error Exporting File to CSV" ) ), a!save( local!errorMessage, "No data was found for this period " ) ) },
So, customCellPositions and customCellValues can only be used when you have an exact set of say, 50 values and 50 specific cells you want them in - they do not work as "starting cells" essentially, for a set of rows. As you are returning multiple rows, the values multiply to 900, so you cannot use this configuration for multiple rows. However if you want the data to start at a specific cell, you can use your single startingCell parameter, such as "A2".
Unfortunately as the a!exportDataStoreEntityToExcel() function exports data directly from the database, you will need to update the logic there. Assuming you are using a view to export under cons!FAS_DSE_VW_QUALITY_WORK_EXPORT, the view will need to be adjusted with something such as case() statements to check for empty values and add defaults. Or, if the view is used in different functions, duplicate the view to one that is used only for this purpose with the modifications. Hopefully that is possible in your environment..
sounds good and thank you so much for your consistent help.