Skip to main content
ladislavm0002
July 29, 2023
Question

Combine list of dictionaries to single Data Store Entity

  • July 29, 2023
  • 6 replies
  • 2 views

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

6 replies

July 29, 2023

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!

ladislavm0002
July 29, 2023

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.

July 29, 2023

I recommend trying the "OR" operator instead of "AND" to see if it produces the desired result.

mathieud0001
July 29, 2023

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..

ladislavm0002
July 29, 2023

do you mean directly write data to DB and then query them? or some different way?

mathieud0001
July 29, 2023

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.