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!
A Data Type is different than a Data Store Entity (A Data Type linked to a Data Store).
The only option I would would be to write this combined result set to another Entity (via a cast) and then run the Export from the new entity..
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.
do you mean directly write data to DB and then query them? or some different way?
I recommend trying the "OR" operator instead of "AND" to see if it produces the desired result.
The export takes a datastore entity. Meaning it goes directly against the table. You can't query. Thus you need to write that combined set (that you calculated) to some other table and run the export against that table.