• State Suggested Answer
  • Replies 4 replies
  • Answers 2 answers
  • Subscribers 9 subscribers
  • Views 3253 views
  • Users 0 members are here
Related Discussions
Home » Discussions » User Interface

Query Entity throwing Retrieval Error while retrieving data

Malleswari Talakola

I have a view which contains  1706 rows. I am trying to retrieve the whole data without passing any filters.

But facing the retrieval error and Memory Circuit error.

I applied selection, filters etc to optimize the code.

I am trying to display this entire data in a Grid. So, grid should would irrespective of the filters provided. I understand the issue is because of timeout/large data.

Please help me on how to optimize it to work.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types:CTRSTL7" targetNamespace="urn:com:appian:types:CTRSTL7">
  <xsd:complexType name="CTRS_TL7_V_CLOSED_FORMAL_CASE">
    <xsd:annotation>
      <xsd:appinfo source="appian.jpa">@Table(name="TL7_V_CLOSED_FORMAL_CASE")</xsd:appinfo>
      <xsd:documentation><![CDATA[CDT for the Data for Closed Formal Case Reports Data]]></xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="TL7_V_CLOSED_FORMAL_CASE_ID" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Id @Column(name="TL7_V_CLOSED_FORMAL_CASE_ID", columnDefinition="NUMBER")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="TL7_CASE_ID" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="TL7_CASE_ID", nullable=false, columnDefinition="NUMBER")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="TL7_R_DISPOSITION_ACT_ID" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="TL7_R_DISPOSITION_ACT_ID", columnDefinition="NUMBER")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="CLOSURE_REASON" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="CLOSURE_REASON", columnDefinition="VARCHAR2(60)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="AGENCY" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="AGENCY", columnDefinition="CHAR(5)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="FORMAL_CASE_NUMBER" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="FORMAL_CASE_NUMBER", columnDefinition="VARCHAR2(15)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="COMPLAINANT_NAME" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="COMPLAINANT_NAME", columnDefinition="VARCHAR2(102)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="DATE_FILED" nillable="true" type="xsd:date">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="DATE_FILED", columnDefinition="DATE")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="DAYS" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="DAYS", columnDefinition="NUMBER")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="DATE_CLOSED" nillable="true" type="xsd:date">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="DATE_CLOSED", columnDefinition="DATE")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="COMMENTS" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="COMMENTS", columnDefinition="CLOB")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="FISCAL_YEAR" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="FISCAL_YEAR", columnDefinition="NUMBER")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

a!localVariables(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 1700,
    sort: a!sortInfo(
      field: "DATE_FILED",
      ascending: true()
    )
  ),
  local!closedFormalComplaintsData: rule!CTRS_TL7_QE_V_getClosedFormalCaseData(
    dispositionActId: ri!dispositionActid,
    pagingInfo: local!pagingInfo
  ).data,
  local!complainsAggregateData: rule!CTRS_TL7_QE_V_getClosedFormalCaseDataCount(
    dispositionActId: ri!dispositionActid,
    
  ).data,
  {
    a!gridField(
      label: union(
        index(
          local!closedFormalComplaintsData,
          "CLOSURE_REASON",
          {}
        ),
        index(
          local!closedFormalComplaintsData,
          "CLOSURE_REASON",
          {}
        )
      ),
      labelPosition: "ABOVE",
      data: local!closedFormalComplaintsData,
      columns: {
        a!gridColumn(
          label: "#",
          value: fv!identifier
        ),
        a!gridColumn(
          label: "Discrimination Agency",
          value: fv!row.agency,
          sortField: "AGENCY"
        ),
        a!gridColumn(
          label: "Formal Case Number",
          value: a!linkField(
            links: a!recordLink(
              label: fv!row.FORMAL_CASE_NUMBER,
              recordType: cons!CTRS_TL7_REC_CASE,
              identifier: fv!row.TL7_CASE_ID
            )
          )
        ),
        a!gridColumn(
          label: "Complainant name",
          value: fv!row.COMPLAINANT_NAME,
          sortField: "COMPLAINANT_NAME"
        ),
        a!gridColumn(
          label: "Formal Date",
          value: fv!row.DATE_FILED,
          sortField: "DATE_FILED"
        ),
        a!gridColumn(
          label: "Total Days",
          value: fv!row.DAYS,
          sortField: "DAYS"
        ),
        a!gridColumn(
          label: "Closure Date",
          value: fv!row.DATE_CLOSED,
          sortField: "DATE_CLOSED"
        ),
        a!gridColumn(
        label: "Comments",
        value: a!richTextDisplayField(
          value: a!richTextIcon(
            icon: "commenting",
            caption: if(isnull(fv!row.COMMENTS),"N/A",fv!row.COMMENTS)
          )
        ),
        sortField: "COMMENTS"
       )
       
       
        
      },
      pagingSaveInto: local!pagingInfo,
      initialSorts: local!pagingInfo.sort,
      pageSize: local!pagingInfo.batchSize
      
    ),
    a!gridField(
      label: "Average",
      data: local!complainsAggregateData,
      columns: {
        a!gridColumn(
          label: "Number of Days",
          sortField: "TOTAL_DAYS",
          value: fv!row.TOTAL_DAYS,
          align: "END"
        ),
        a!gridColumn(
          label: "Number of Records",
          sortField: "RECORD_COUNT",
          value: fv!row.RECORD_COUNT,
          align: "END"
        ),
        a!gridColumn(
          label: "Average Days",
          sortField: "AVERAGE_DAYS",
          value: fv!row.AVERAGE_DAYS,
          align: "END"
        )
      }
    )
  }
)

Attached the Details.

/*if(and(isnull(ri!dispositionActId),isnull(ri!fiscalYear)),{},*/
a!queryEntity(
  entity: cons!CTRS_TL7_ENTITY_V_CLOSED_FORMAL_CASE,
  query: a!query(
    selection:if(
      rule!CTRS_UTILITY_RULE_checkisNull(ri!selection),{},
      a!querySelection(
        columns: {
          a!queryColumn(
            field: "AGENCY"
          ),
          a!queryColumn(
            field: "FORMAL_CASE_NUMBER"
          ),
          a!queryColumn(
            field: "COMPLAINANT_NAME"
          ),
          a!queryColumn(
            field: "DATE_FILED"
          ),
          a!queryColumn(
            field: "DAYS"
          ),
          a!queryColumn(
            field: "DATE_CLOSED",
           
          ),
          a!queryColumn(
            field: "COMMENTS"
          )
        }
      )
    ),
 
    logicalexpression: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "TL7_R_DISPOSITION_ACT_ID",
          operator: "=",
          value: ri!dispositionActId,
          ignoreFiltersWithEmptyValues: true
        ),
        a!queryFilter(
          field: "FISCAL_YEAR",
          operator: "=",
          value: ri!fiscalYear,
          ignoreFiltersWithEmptyValues: true
        )
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: 
      a!pagingInfo(
        startIndex: 1,
        batchSize: - 1,
        sort: a!sortInfo(
          field: "TL7_V_CLOSED_FORMAL_CASE_ID",
          ascending: true
        )
    
     
    )

  ),
  fetchTotalCount: true
)

  Discussion posts and replies are publicly visible

  • 0
    Appian Employee

    Can you explain your use case for showing that much data in a grid? To be honest, even if we resolved the query issue, this would not be a great user experience. Could you use filtering, searching, or paging to limit the amount of data shown in the grid (while still allowing users to find what they need)?

  • 0 in reply to Peter Lewis

    I have Database view containing 1700 rows of data. I need to display that data in a!gridField as Reports to user, also let them Export to Word or Excel the entire data.

    Sometimes, they can pass the filter "year" to retrieve data where the count would come down.

    To optimize it,I added Pagination on the grid and set the Batch size to 10. So, all the records pulled properly for every next 10 records.

    But, i want a serial number to be created for each row, like 1,2,3,4,5 etc., instead it is displaying the TL7_V_CLOSED_FORMAL_CASE_ID which is primary key of the view.

    I realized this cant be achieved through fv!identifier and i cannot use any other functions inside grid.

    One way i understand is to modify the Database view. But, i want to know if we can achieve it from Interface it self.

    Please guide me!!

    Note: ri!dispositionActId = {integer}number data type.

    a!localVariables(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 10,
    sort: a!sortInfo(
      field: "DATE_FILED",
      ascending: false()
    )
  ),
  local!closedFormalComplaintsData: rule!CTRS_TL7_QE_V_getClosedFormalCaseData(
    dispositionActId: ri!dispositionActid,
    pagingInfo: local!pagingInfo
  ),
  local!complainsAggregateData: rule!CTRS_TL7_QE_V_getClosedFormalCaseDataCount(
    dispositionActId: ri!dispositionActid,
    
  ).data,
  {
    a!gridField(
      label: union(
        index(
          index(
            local!closedFormalComplaintsData,
            "data",
            ""
          ),
          "CLOSURE_REASON",
          {}
        ),
        index(
          index(
            local!closedFormalComplaintsData,
            "data",
            ""
          ),
          "CLOSURE_REASON",
          {}
        )
      ),
      labelPosition: "ABOVE",
      data: local!closedFormalComplaintsData,
      columns: {
        a!gridColumn(
          label: "#",
          value: fv!identifier
        ),
        a!gridColumn(
          label: "Discrimination Agency",
          value: fv!row.agency,
          sortField: "AGENCY"
        ),
        a!gridColumn(
          label: "Formal Case Number",
          value: a!linkField(
            links: a!recordLink(
              label: fv!row.FORMAL_CASE_NUMBER,
              recordType: cons!CTRS_TL7_REC_CASE,
              identifier: fv!row.TL7_CASE_ID
            )
          )
        ),
        a!gridColumn(
          label: "Complainant name",
          value: fv!row.COMPLAINANT_NAME,
          sortField: "COMPLAINANT_NAME"
        ),
        a!gridColumn(
          label: "Formal Date",
          value: fv!row.DATE_FILED,
          sortField: "DATE_FILED"
        ),
        a!gridColumn(
          label: "Total Days",
          value: fv!row.DAYS,
          sortField: "DAYS"
        ),
        a!gridColumn(
          label: "Closure Date",
          value: fv!row.DATE_CLOSED,
          sortField: "DATE_CLOSED"
        ),
        a!gridColumn(
          label: "Comments",
          value: a!richTextDisplayField(
            value: a!richTextIcon(
              icon: "commenting",
              caption: if(
                isnull(
                  fv!row.COMMENTS
                ),
                "N/A",
                fv!row.COMMENTS
              )
            )
          ),
          sortField: "COMMENTS"
        )
      },
      pagingSaveInto: local!pagingInfo,
      /*pageSize: 5*/
      
    ),
    a!gridField(
      label: "Average",
      data: local!complainsAggregateData,
      columns: {
        a!gridColumn(
          label: "Number of Days",
          sortField: "TOTAL_DAYS",
          value: fv!row.TOTAL_DAYS,
          align: "END"
        ),
        a!gridColumn(
          label: "Number of Records",
          sortField: "RECORD_COUNT",
          value: fv!row.RECORD_COUNT,
          align: "END"
        ),
        a!gridColumn(
          label: "Average Days",
          sortField: "AVERAGE_DAYS",
          value: fv!row.AVERAGE_DAYS,
          align: "END"
        )
      }
    )
  }
)

  • 0
    Certified Lead Developer
     in reply to Malleswari Talakola

    One thing I suggest in this case is to expose the primary key to the users and give it a name they'll remember and know to associate to each individual entry in the row - "Case ID" or "Serial Number" both seem like a good start.

  • 0

    If you get "error retrieving data" error, it is possible that your CDT and Data Store Entity does not line up correctly. It is possible that one field that you added in your CDT did not refresh in the DSE.

    My suggestion to isolate this issue is to run the query independently.

    Comment each field return and find out which field shows an error.

    Once, you isolate that field, go and check if your CDT and Table are lined up with the columns and fields.

    Then, go to the Data store and reimport the DSE.

    Verify and publish.

© 2024 Appian. All rights reserved.