Export to Excel in Service Backed Record Giving Error

Hi

 

I am working on version 18.3.

I have created a service backed record for which the code is as below:

 

1. Code of List View Source Expression

 

# Passed rsp!query and rsp!searchText as inputs to this rule

 

with(
  local!queryInformation: rule!parseRspQuery(ri!rspQuery),
  local!filterFields: index(local!queryInformation.filters, "field", {}),
  local!filterValues: index(local!queryInformation.filters, "value", {}),

  
  local!status: displayvalue(
    "status",
    local!filterFields,
    local!filterValues,
    ""
  ),
  local!typeDetail: a!forEach(
    displayvalue(
      "typeDetail",
      local!filterFields,
      local!filterValues,
      ""
    ),
    tostring(fv!item)
  ),
  
  a!queryEntity(
    entity:cons!GENERIC_VW_DE,
    query:a!query(
      selection: a!querySelection(
        columns:{
          a!queryColumn(
            field:"id",
            visible:true
          ),
          a!queryColumn(
            field:"code",
            visible:true
          ),
          a!queryColumn(
            field:"number",
            visible:true
          ),
          a!queryColumn(
            field:"typeDetail",
            visible:true
          ),
          a!queryColumn(
            field:"status",
            visible:true
          ),
          a!queryColumn(
            field:"fullName",
            visible:true
          )
        }
       ),
       logicalExpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          /* Default Filter */
      		 a!queryFilter(
            field:"typeDetail",
            operator:"in",
            value:cons!TYPES_OF_DETAIL
          ),
		  /*Status and Type Detail are user filters */
    	 if(
            rule!APN_isBlank(local!status),
            {},
              a!queryFilter(
              field:"status",
              operator:"=",
              value:local!status
            )
          ),
          if(
            rule!APN_isBlank(local!typeDetail),
            {},
              a!queryFilter(
              field:"typeDetail",
              operator:"in",
              value:local!typeDetail
            )
          )
		},
  		logicalExpressions: 
  		  a!queryLogicalExpression(
              operator: "OR",
              filters: {
        			if(
        				rule!APN_isBlank(ri!rspSearchText),
        				{},
                    {
                    a!queryFilter(
        				  field:"code",
        				  operator:"includes",
        				  value:ri!rspSearchText
        		     ),
                    a!queryFilter(
        				  field:"number",
        				  operator:"includes",
        				  value:ri!rspSearchText
        		     ) ,
                   a!queryFilter(
        				  field:"typeDetail",
        				  operator:"includes",
        				  value:ri!rspSearchText
        		     ),
				  a!queryFilter(
        				  field:"status",
        				  operator:"includes",
        				  value:ri!rspSearchText
        		    ),
		         a!queryFilter(
        				  field:"fullName",
        				  operator:"includes",
        				  value:ri!rspSearchText
        		  ) 
                }
              )
             }
          ) 
      ),
      pagingInfo:local!queryInformation.pagingInfo
    )
  )
)








/*Code for rule!PH_parseRspQuery*/

/* Code for rule!parseRspQuery() */

with(

    /* 
    * Get the first data type containing one or more of the following:
    * Logical Expression, Query Filter, Search Query. It is called
    * a logicalExpression|filter|search -- abbreviated as LFS
    */
    local!parentLFS: index(ri!rspQuery, "logicalExpression|filter|search", null),
    
    /* 
    * Check to see if the above data type has a nested LFS in it. This can happen
    * if the user has clicked multiple filters. If all filters were in a single
    * set, for example a group of states on a multi select filter, we want to use the
    * parent as the main LFS so that the expression correctly consolidates the filters.
    */
    local!childLFS: if(
    index(local!parentLFS, "operator", "") = "OR",
    null,
    index(local!parentLFS, "logicalExpression|filter|search", null)
    ),
    
    /*
    Get the LFS containing the filter(s) and/or search.
    Basically, if the child exists, it will contain all the information.
    If it doesn't then we want to parent.
    */
    local!mainLFS: if(isnull(local!childLFS), local!parentLFS, local!childLFS),
    
    /* Get runtime types of LFS (type!QueryFilter or type!Search) */
    local!types: cast(
    'type!{http://www.appian.com/ae/types/2009}Type?list',
    a!forEach({local!mainLFS}, runtimetypeof(fv!item))
    ),
    
    /*
    Get the search data type, if there is one. It has its own type that we can look for.
    */
    local!searchData: index({local!mainLFS}, where(local!types='type!{http://www.appian.com/ae/types/2009}Search'), null),
    
    /* Gets filter data types if there are any. We can find them by their type. */
    local!singleSelectFilters: index({local!mainLFS}, where(local!types='type!{http://www.appian.com/ae/types/2009}QueryFilter'), {}),
    
    /* Gets logical expression that, for our case, could hold multiple state filters */
    local!multiSelectFilterLogicalExpressions: index(
    {local!mainLFS},
    where(local!types='type!{http://www.appian.com/ae/types/2009}LogicalExpression'),
    {}
    ),
    
    /*
    * Converts multiple filters to single filter for each multiple filter.
    * This assumes that all options in a single filter operate on the same field
    */
    local!multiSelectSingleFilter: if(
    length({local!multiSelectFilterLogicalExpressions}) = 0,
    {},
    a!forEach(
    local!multiSelectFilterLogicalExpressions,
    a!queryFilter(
    field: fv!item.'logicalExpression|filter|search'[1].field,
    operator: "in",
    value: fv!item.'logicalExpression|filter|search'.value
    )
    )
    ),
    
    /* Gets paging info */
    local!paging: index(ri!rspQuery, "pagingInfo", topaginginfo(1,100)),
    
    /* 
    Creates and returns a Dictionary containing the pagingInfo, search text, and filters 
    currently applied to the record list.
    */
    {
    pagingInfo: local!paging,
    searchText: tostring(
    index(
    cast('type!{http://www.appian.com/ae/types/2009}Search',local!searchData), 
    "searchQuery", 
    ""
    )
    ),
    filters: cast(
    'type!{http://www.appian.com/ae/types/2009}QueryFilter?list',
    {
    local!singleSelectFilters,
    local!multiSelectSingleFilter
    }
    )
    }
)

 

2. Code for Record View Source Expression

 

# Passed rp!id as inputs to this rule

a!queryEntity(
    entity:cons!GENERIC_VW_DE,
    query:a!query(
      selection: a!querySelection(
        columns:{
          a!queryColumn(
            field:"id",
            visible:true
          ),
          a!queryColumn(
            field:"code",
            visible:true
          ),
          a!queryColumn(
            field:"number",
            visible:true
          ),
          a!queryColumn(
            field:"typeDetail",
            visible:true
          ),
          a!queryColumn(
            field:"status",
            visible:true
          ),
          a!queryColumn(
            field:"fullName",
            visible:true
          )
        }
       ),
	   
	   filter:a!queryFilter(
               field:"id"  /* Primary Key Column*/,
               operator:"=",
               value:ri!id
            ),
	   
         pagingInfo:topagingInfo(1,1)
    )
  )

 

3. Code for User Filter

{
  /*Type Details User filter*/
  a!facet(
    name:"Type Details",
    options:{
      a!facetOption(
        id:2,
        name:"Type 1",
        filter:{
          a!queryFilter(
            field:"typeDetail",
            operator:"=",
            value:"Type 1"
          )
        }
      ),
      a!facetOption(
        id:3,
        name:"Type 2",
        filter:{
          a!queryFilter(
            field:"typeDetail",
            operator:"=",
            value:"Type 2"
          )
        }
      ),
      a!facetOption(
        id:4,
        name:"Type 3",
        filter:{
          a!queryFilter(
            field:"typeDetail",
            operator:"=",
            value:"Type 3"
          )
        }
      ),
     }
  ),
  /*Status User filter*/
  a!facet(
    name:"Status",
    options:{
      a!facetOption(
        id:5,
        name:"Active",
        filter:{
          a!queryFilter(
            field:"status",
            operator:"=",
            value:"A"
          )
        }
      ),
      a!facetOption(
        id:6,
        name:"Inactive",
        filter:{
          a!queryFilter(
            field:"status",
            operator:"=",
            value:"I"
          )
        }
      ),
      a!facetOption(
        id:7,
        name:"Closed",
        filter:{
          a!queryFilter(
            field:"status",
            operator:"=",
            value:"C"
          )
        }
      )
     },
     allowMultipleSelections:false
  )
}

 

In my Record List Grid, the data is coming correctly, my user filters and general search is working correctly. Also if I click on any link I am diverted to that corresponding record.

 

The problem is with Export to Excel. The excel that is being downloaded doesn't have data but contains the error:

Unable to convert row data to Generic_VW data type: TypedValue[it=94,v={TypedValue[it=3,v=id]=TypedValue[it=3,v=74634], TypedValue[it=3,v=code]=TypedValue[it=3,v=AL001], TypedValue[it=3,v=number]=TypedValue[it=3,v=123], TypedValue[it=3,v=typeDetail]=TypedValue[it=3,v=Type 1], TypedValue[it=3,v=status]=TypedValue[it=3,v=A], TypedValue[it=3,v=fullName]=TypedValue[it=3,v=ABC]}]

 

If it had been a problem with any type in my entity (which the above error suggests), then the error should come in the record list grid also but as I mentioned above, the record list grid is working fine.

 

Can any one please suggest what mistake am I doing.

 

Thanks in advance!!

  Discussion posts and replies are publicly visible

Parents
  • Hi Komal,

    Seems the system is not able to match the data with your CDT.

    Try below expression.

    with(
      local!queryInformation: rule!parseRspQuery(ri!rspQuery),
      local!filterFields: index(local!queryInformation.filters, "field", {}),
      local!filterValues: index(local!queryInformation.filters, "value", {}),
    
      
      local!status: displayvalue(
        "status",
        local!filterFields,
        local!filterValues,
        ""
      ),
      local!typeDetail: a!forEach(
        displayvalue(
          "typeDetail",
          local!filterFields,
          local!filterValues,
          ""
        ),
        tostring(fv!item)
      ),
      
      local!dataSubSet:
    		  a!queryEntity(
    			entity:cons!GENERIC_VW_DE,
    			query:a!query(
    			  selection: a!querySelection(
    				columns:{
    				  a!queryColumn(
    					field:"id",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"code",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"number",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"typeDetail",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"status",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"fullName",
    					visible:true
    				  )
    				}
    			   ),
    			   logicalExpression: a!queryLogicalExpression(
    				operator: "AND",
    				filters: {
    				  /* Default Filter */
    					 a!queryFilter(
    					field:"typeDetail",
    					operator:"in",
    					value:cons!TYPES_OF_DETAIL
    				  ),
    				  /*Status and Type Detail are user filters */
    				 if(
    					rule!APN_isBlank(local!status),
    					{},
    					  a!queryFilter(
    					  field:"status",
    					  operator:"=",
    					  value:local!status
    					)
    				  ),
    				  if(
    					rule!APN_isBlank(local!typeDetail),
    					{},
    					  a!queryFilter(
    					  field:"typeDetail",
    					  operator:"in",
    					  value:local!typeDetail
    					)
    				  )
    				},
    				logicalExpressions: 
    				  a!queryLogicalExpression(
    					  operator: "OR",
    					  filters: {
    							if(
    								rule!APN_isBlank(ri!rspSearchText),
    								{},
    							{
    							a!queryFilter(
    								  field:"code",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ),
    							a!queryFilter(
    								  field:"number",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ) ,
    						   a!queryFilter(
    								  field:"typeDetail",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ),
    						  a!queryFilter(
    								  field:"status",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							),
    						 a!queryFilter(
    								  field:"fullName",
    								  operator:"includes",
    								  value:ri!rspSearchText
    						  ) 
    						}
    					  )
    					 }
    				  ) 
    			  ),
    			  pagingInfo:local!queryInformation.pagingInfo
    			)
    		  ),
    		  
    	a!dataSubset(
    		startIndex: local!queryInformation.pagingInfo.startIndex,
    		batchSize: local!queryInformation.pagingInfo.batchSize,
    		sort: local!queryInformation.pagingInfo.sort,
    		totalCount: local!dataSubSet.totalCount,
    		data: cast(type!<Your CDT>,local!dataSubSet.data),
    		identifiers: local!dataSubSet.identifiers
    	  )
    )
    
    
    
    

    Thanks

  • Hi  

    Thanx for replying.
    I tried the above code, but now I am getting the below error in my record list view i.e. my record list view is not getting populated. The error is:

    An error occurred while retrieving the data for “Index 1”. Details: Expression evaluation error in rule 'test_recordlistsourceexpression' at function 'cast' [line 93]: Could not cast from Generic_VW to Number (Integer). Details: CastInvalid (APNX-1-4205-003)


    I do understand this error pops up in case of type mismatch. But I am not able to understand, in the earlier code that i shared if there was problem with any of my CDT's attribute type then my record list view itself won't be populated but that is getting populated fine and also my user filters are working fine.

    Any suggestions.

  • Please check the datastore entity, I think the CDT is not correctly mapped to the datastore, and also check the Record and DataStore entity is mapped to the same CDT.

    Thanks
  • Hi

    There is no issue of CDT, it i mapped to the datastore correctly and the Record and Datastore entity are mapped to the same CDT.
    Any other suggestions.
  • Ok, remove the selections, and try

     a!queryEntity(
    			entity:cons!GENERIC_VW_DE,
    			query:a!query(
    			 
    			   logicalExpression: a!queryLogicalExpression(
    				operator: "AND",
    				filters: {
    				  /* Default Filter */
    					 a!queryFilter(
    					field:"typeDetail",
    					operator:"in",
    					value:cons!TYPES_OF_DETAIL
    				  ),
    				  /*Status and Type Detail are user filters */
    				 if(
    					rule!APN_isBlank(local!status),
    					{},
    					  a!queryFilter(
    					  field:"status",
    					  operator:"=",
    					  value:local!status
    					)
    				  ),
    				  if(
    					rule!APN_isBlank(local!typeDetail),
    					{},
    					  a!queryFilter(
    					  field:"typeDetail",
    					  operator:"in",
    					  value:local!typeDetail
    					)
    				  )
    				},
    				logicalExpressions: 
    				  a!queryLogicalExpression(
    					  operator: "OR",
    					  filters: {
    							if(
    								rule!APN_isBlank(ri!rspSearchText),
    								{},
    							{
    							a!queryFilter(
    								  field:"code",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ),
    							a!queryFilter(
    								  field:"number",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ) ,
    						   a!queryFilter(
    								  field:"typeDetail",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ),
    						  a!queryFilter(
    								  field:"status",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							),
    						 a!queryFilter(
    								  field:"fullName",
    								  operator:"includes",
    								  value:ri!rspSearchText
    						  ) 
    						}
    					  )
    					 }
    				  ) 
    			  ),
    			  pagingInfo:local!queryInformation.pagingInfo
    			)
    		  )

  • Hi Vinay Kumar Rai (vinayr)

    In your previous comment, the suggestion that you gave for casting my ultimate result, that has worked for me with the below minute tweaking in cast:

    with(
      local!queryInformation: rule!parseRspQuery(ri!rspQuery),
      local!filterFields: index(local!queryInformation.filters, "field", {}),
      local!filterValues: index(local!queryInformation.filters, "value", {}),
    
      
      local!status: displayvalue(
        "status",
        local!filterFields,
        local!filterValues,
        ""
      ),
      local!typeDetail: a!forEach(
        displayvalue(
          "typeDetail",
          local!filterFields,
          local!filterValues,
          ""
        ),
        tostring(fv!item)
      ),
      
      local!dataSubSet:
    		  a!queryEntity(
    			entity:cons!GENERIC_VW_DE,
    			query:a!query(
    			  selection: a!querySelection(
    				columns:{
    				  a!queryColumn(
    					field:"id",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"code",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"number",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"typeDetail",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"status",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"fullName",
    					visible:true
    				  )
    				}
    			   ),
    			   logicalExpression: a!queryLogicalExpression(
    				operator: "AND",
    				filters: {
    				  /* Default Filter */
    					 a!queryFilter(
    					field:"typeDetail",
    					operator:"in",
    					value:cons!TYPES_OF_DETAIL
    				  ),
    				  /*Status and Type Detail are user filters */
    				 if(
    					rule!APN_isBlank(local!status),
    					{},
    					  a!queryFilter(
    					  field:"status",
    					  operator:"=",
    					  value:local!status
    					)
    				  ),
    				  if(
    					rule!APN_isBlank(local!typeDetail),
    					{},
    					  a!queryFilter(
    					  field:"typeDetail",
    					  operator:"in",
    					  value:local!typeDetail
    					)
    				  )
    				},
    				logicalExpressions: 
    				  a!queryLogicalExpression(
    					  operator: "OR",
    					  filters: {
    							if(
    								rule!APN_isBlank(ri!rspSearchText),
    								{},
    							{
    							a!queryFilter(
    								  field:"code",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ),
    							a!queryFilter(
    								  field:"number",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ) ,
    						   a!queryFilter(
    								  field:"typeDetail",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ),
    						  a!queryFilter(
    								  field:"status",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							),
    						 a!queryFilter(
    								  field:"fullName",
    								  operator:"includes",
    								  value:ri!rspSearchText
    						  ) 
    						}
    					  )
    					 }
    				  ) 
    			  ),
    			  pagingInfo:local!queryInformation.pagingInfo
    			)
    		  ),
    	
    	
    		  
    	a!dataSubset(
    		startIndex: local!queryInformation.pagingInfo.startIndex,
    		batchSize: local!queryInformation.pagingInfo.batchSize,
    		sort: local!queryInformation.pagingInfo.sort,
    		totalCount: local!dataSubSet.totalCount,
    		data: a!forEach(
              items:local!dataSubSet.data,
              expression:cast(typeof(typeof(type!<Your CDT>)),fv!item)
            ),
    		identifiers: local!dataSubSet.identifiers
    	  )
    )
    
    
    
    

     

    I would like to know a few things:

    1. Why are we required to do this casting as in my a!queryEntity also the entity is of the same type that we are casting to our local!dataSubSet in a!dataSubset(). Basically want to know the need for this casting.

    2. Also I am facing a weird scenario wherein out of 3 of my expression backed records, 1 is working without using cast() and the other two require this casting. Can you please throw some light on why is it so.

     

    Thanks!!

Reply
  • Hi Vinay Kumar Rai (vinayr)

    In your previous comment, the suggestion that you gave for casting my ultimate result, that has worked for me with the below minute tweaking in cast:

    with(
      local!queryInformation: rule!parseRspQuery(ri!rspQuery),
      local!filterFields: index(local!queryInformation.filters, "field", {}),
      local!filterValues: index(local!queryInformation.filters, "value", {}),
    
      
      local!status: displayvalue(
        "status",
        local!filterFields,
        local!filterValues,
        ""
      ),
      local!typeDetail: a!forEach(
        displayvalue(
          "typeDetail",
          local!filterFields,
          local!filterValues,
          ""
        ),
        tostring(fv!item)
      ),
      
      local!dataSubSet:
    		  a!queryEntity(
    			entity:cons!GENERIC_VW_DE,
    			query:a!query(
    			  selection: a!querySelection(
    				columns:{
    				  a!queryColumn(
    					field:"id",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"code",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"number",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"typeDetail",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"status",
    					visible:true
    				  ),
    				  a!queryColumn(
    					field:"fullName",
    					visible:true
    				  )
    				}
    			   ),
    			   logicalExpression: a!queryLogicalExpression(
    				operator: "AND",
    				filters: {
    				  /* Default Filter */
    					 a!queryFilter(
    					field:"typeDetail",
    					operator:"in",
    					value:cons!TYPES_OF_DETAIL
    				  ),
    				  /*Status and Type Detail are user filters */
    				 if(
    					rule!APN_isBlank(local!status),
    					{},
    					  a!queryFilter(
    					  field:"status",
    					  operator:"=",
    					  value:local!status
    					)
    				  ),
    				  if(
    					rule!APN_isBlank(local!typeDetail),
    					{},
    					  a!queryFilter(
    					  field:"typeDetail",
    					  operator:"in",
    					  value:local!typeDetail
    					)
    				  )
    				},
    				logicalExpressions: 
    				  a!queryLogicalExpression(
    					  operator: "OR",
    					  filters: {
    							if(
    								rule!APN_isBlank(ri!rspSearchText),
    								{},
    							{
    							a!queryFilter(
    								  field:"code",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ),
    							a!queryFilter(
    								  field:"number",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ) ,
    						   a!queryFilter(
    								  field:"typeDetail",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							 ),
    						  a!queryFilter(
    								  field:"status",
    								  operator:"includes",
    								  value:ri!rspSearchText
    							),
    						 a!queryFilter(
    								  field:"fullName",
    								  operator:"includes",
    								  value:ri!rspSearchText
    						  ) 
    						}
    					  )
    					 }
    				  ) 
    			  ),
    			  pagingInfo:local!queryInformation.pagingInfo
    			)
    		  ),
    	
    	
    		  
    	a!dataSubset(
    		startIndex: local!queryInformation.pagingInfo.startIndex,
    		batchSize: local!queryInformation.pagingInfo.batchSize,
    		sort: local!queryInformation.pagingInfo.sort,
    		totalCount: local!dataSubSet.totalCount,
    		data: a!forEach(
              items:local!dataSubSet.data,
              expression:cast(typeof(typeof(type!<Your CDT>)),fv!item)
            ),
    		identifiers: local!dataSubSet.identifiers
    	  )
    )
    
    
    
    

     

    I would like to know a few things:

    1. Why are we required to do this casting as in my a!queryEntity also the entity is of the same type that we are casting to our local!dataSubSet in a!dataSubset(). Basically want to know the need for this casting.

    2. Also I am facing a weird scenario wherein out of 3 of my expression backed records, 1 is working without using cast() and the other two require this casting. Can you please throw some light on why is it so.

     

    Thanks!!

Children