• State Suggested Answer
  • Replies 4 replies
  • Answers 2 answers
  • Subscribers 9 subscribers
  • Views 3252 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

Parents Reply Children
  • 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.

© 2024 Appian. All rights reserved.