Skip to main content
December 8, 2021
Question

ERROR while exporting grid data to excel

  • December 8, 2021
  • 5 replies
  • 0 views

I have the below expression rule, and which is pointing to an entity using a constant.

a!queryEntity(
  entity: cons!AT_EMPLOYEE2,
  query: a!query(
    logicalExpression: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "status",
          operator: "=",
          value: ri!status
        )
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 10,
      sort: a!sortInfo(
        field: "employeeId",
        ascending: false
      )
    )
  ),
  fetchTotalCount: false
).data

and I have the below code, and which is containing a grid and the data getting loaded based on the "Status" dropdown selection.

I am getting an error while exporting the data into an excel. Can someone advise what needs to be done.

The error is at the line " entity: 'type!{urn:com:appian:types:AT}AT_employee2(rule!AT_getEmployeeDetailsByParameters(status: local!status)',"

a!localVariables(
  local!status,
  local!exportDocId,
  local!errorMessage,
  
{
  a!boxLayout(
    label: "Box",
    contents: {
      a!dropdownField(
        label: "Status",
        placeholder: "-- Select a Status --",
        choiceLabels: {
          "New",
          "Updated",
          "Open"
        },
        choiceValues: {
          "N",
          "U",
          "O"
        },
        value: local!status,
        saveInto: local!status
      )
    },
    style: "STANDARD",
    marginBelow: "STANDARD"
  ),
  if(
    isnull(local!exportDocId),
    /*This link field contains the link that starts the export*/
    a!linkField(
      labelPosition: "COLLAPSED",
      links: a!dynamicLink(
        label: "Export to Excel File",
        saveInto: {
          a!exportDataStoreEntityToExcel(
            entity: 'type!{urn:com:appian:types:AT}AT_employee2(rule!AT_getEmployeeDetailsByParameters(status: local!status)',
            documentName: "Excel Export " & now(),
            saveInFolder: cons!AT_DOCUMENTSFOLDER,
            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 Excel"
            )
          )
        }
      )
    ),
    {}
  ),
  if(
    /*This only displays the download link if a valid document was created*/
    not(isnull(local!exportDocId)),
    /*This changes the link to a download link for the newly created document */
    a!linkField(
      links: a!documentDownloadLink(
        label: "Download Excel File",
        document: local!exportDocId
      )
    ),
    a!textField(value: local!errorMessage, readOnly: true)
  ),
  a!gridField(
    label: "Read-only Grid",
    labelPosition: "ABOVE",
    data: rule!AT_getEmployeeDetailsByParameters(status: local!status),
    columns: {
      a!gridColumn(
        label: "Status",
        sortField: "status",
        value: fv!row.status
      ),
      a!gridColumn(
        label: "Dateadded",
        sortField: "dateadded",
        value: fv!row.dateadded
      ),
      a!gridColumn(
        label: "Added By",
        sortField: "addedBy",
        value: fv!row.addedBy
      ),
      a!gridColumn(
        label: "Coverletter",
        sortField: "coverletter",
        value: fv!row.coverletter
      ),
      a!gridColumn(
        label: "Gender",
        sortField: "gender",
        value: fv!row.gender
      ),
      a!gridColumn(
        label: "Mothertongue",
        sortField: "mothertongue",
        value: fv!row.mothertongue
      ),
      a!gridColumn(
        label: "Date Of Birth",
        sortField: "dateOfBirth",
        value: fv!row.dateOfBirth
      ),
      a!gridColumn(
        label: "Age",
        sortField: "age",
        value: fv!row.age
      ),
      a!gridColumn(
        label: "Lastname",
        sortField: "lastname",
        value: fv!row.lastname
      ),
      a!gridColumn(
        label: "Firstname",
        sortField: "firstname",
        value: fv!row.firstname
      ),
      a!gridColumn(
        label: "Employee Id",
        sortField: "employeeId",
        value: fv!row.employeeId
      )
    },
    validations: {}
  ),
  
  
}
)

5 replies

andrewh0007
December 8, 2021

Is it a cast issue where it can't cast from AT_employee2 to Data Store Entity? The issue seems to be that you're wanting to export data store entity but providing a CDT (which isn't the same thing).

Check out the supplied Appian documentation which has helpful information and examples: https://docs.appian.com/suite/help/21.4/Export_To_Excel_Smart_Service.html

December 9, 2021

Hi,

I am getting all the columns from a table based on the "Status" column filter using Expression Rule and showing in the grid. It is perfectly already and no issues.

When I try to export the data to excel using "exportDataStoreEntityToExcel" service is it expecting an entity.

But, I can provide a constant which can refer the entire table.

I want only certain data based on the filter which I will pass. In this case it is a "Status" column.

Thanks

andrewh0007
December 10, 2021

Apologies, I misunderstood. I think you'll just need to pass through the filtering details to the exportDataStoreEntityToExcel smart service. There's a lot of good details in the documentation: https://docs.appian.com/suite/help/21.4/Export_To_Excel_Smart_Service.html.

I think the only other options would be exporting plug ins such as https://community.appian.com/b/appmarket/posts/excel-tools or https://community.appian.com/b/appmarket/posts/sql-to-excel.

Personally I would just refactor and complete the same query again in the provided exportDataStoreEntityToExcel smart service.

It is frustrating that this isn't a thing though. I believe there used to be an export CDT to Excel service but I think it's been deprecated. Would be keen to know if anyone has a set up that works well!

shubhamk0004
December 8, 2021

Hi,

Make sure your rule "AT_getEmployeeDetailsByParameters" has same data type regarding status. and try to change the variable name "local!status" to others... sometimes it is not working. I has same issue, so I changed the variable name and it worked.

December 9, 2021

Hi,

I am able to load the data in the grid based on the "Status" selection. It is working absolutely fine.

But the issue is with "Export to Excel". When I do this, I am getting the below run time error 

Expression evaluation error [evaluation ID = 8dd83:ab211] in rule 'at_employeedatagrid' : An error occurred while executing a save: Expression evaluation error at function a!exportDataStoreEntityToExcel [line 39]: Could not cast from DataSubset to Data Store Entity. Details: CastInvalid