Hello team,
in my application for reporting i use a!exportDataStoreEntityToExcel smart service to download Excel report based on the filters selected. This part is working great. As a new requirement i need to limit the access, the users will be able to download report only for contracts which they created / they approved from sender side / they approved from receiver side. The way i tried to achieve this is to create an expression rule to query the entity, make queries with 3 different filters (when user is created, approver on sender side, approver on receiver side), save the results into 3 separate local variables, combine them into the final local variable which will be then used as the entity parameter in the excel smart service. But i am not able to cast it to proper format, all the time i receive an error message: Could not cast from ICH_V_ContractLineItemsReportCSV to Data Store Entity. Details: CastInvalid.
Example:
local!variable1:
cast( 'type!{urn:com:appian:types:ICH}ICH_V_ContractLineItemsReportCSV?list', a!queryEntity_22r2( fetchTotalCount: TRUE, entity: cons!ICH_ENTITY_V_ContractLineItemsReportCSV, query: a!query( selection: if( rule!GBL_isBlankOrEmpty(ri!fields), null, a!querySelection( columns: a!forEach( items: ri!fields, expression: a!queryColumn(field: fv!item) ) ) ), logicalExpression: a!queryLogicalExpression( ignoreFiltersWithEmptyValues: true, operator: "AND", filters: { a!queryFilter( field: "sBusinessOwner", operator: "in", value: ri!sBusinessOwner, applyWhen: rule!GBL_isNotBlank(ri!sBusinessOwner), ), a!queryFilter( field: "sFinanceOwner", operator: "in", value: ri!sFinanceOwner, applyWhen: rule!GBL_isNotBlank(ri!sFinanceOwner), ), a!queryFilter( field: "rBusinessOwner", operator: "in", value: ri!rBusinessOwner, applyWhen: rule!GBL_isNotBlank(ri!rBusinessOwner), ), a!queryFilter( field: "rFinanceOwner", operator: "in", value: ri!rFinanceOwner, applyWhen: rule!GBL_isNotBlank(ri!rFinanceOwner), ), a!queryFilter( field: "requestor", operator: "in", value: ri!createdBy, applyWhen: rule!GBL_isNotBlank(ri!createdBy), ), a!queryFilter( field: "childScheduleDate", operator: "<=", value: ri!childScheduleDate, applyWhen: rule!GBL_isNotBlank(ri!childScheduleDate) ), }, ), pagingInfo: if( rule!GBL_isBlankOrEmpty(ri!pagingInfo), a!pagingInfo( startIndex: 1, batchSize: 1500, ), ri!pagingInfo ) )).data)
output format of this local!variable is List of CDT. But in the a!exportDataStoreEntityToExcel in the entity parameter its not workingwith error message: Expression evaluation error : Could not cast from ICH_V_ContractLineItemsReportCSV to Data Store Entity. Details: CastInvalid.
Is it possible to cast the local variable to output format which can be used in the excel smart service?
Thanks
Discussion posts and replies are publicly visible
Hi,When providing the entity parameter, make sure to pass the data store entity you want to query. In this case, it should be cons!ICH_ENTITY_V_ContractLineItemsReportCSV. You can then apply the necessary filters separately using the "filters" parameter(as highlighted in the picture), which will allow you to export the required data.For more information, refer https://docs.appian.com/suite/help/23.2/Export_To_Excel_Smart_Service.html#a!exportdatastoreentitytoexcel()Hope it helps!
this is clear, but my problem is i need a combined result of this output for 3 different filters. i cannot run all my filters at once (when requestor=loggedinuser(), when senderapprover=loggedinuser(), when receiverapprover=loggedinuser()), because this filters cannot be used with "AND" logic, but need to be run separately and and they combined result is the required output.
I recommend trying the "OR" operator instead of "AND" to see if it produces the desired result.