How to get a count of database rows in Appian web api

I have [also] tried writing a seperate api which [only] returns the row-count for my entity, but it [also] returns error 500...

a!localVariables(
  local!entities: a!queryEntity(
    entity: cons!MY_DATABASE_TABLE_DS,
    query: a!query(
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: -1
      )
    ),
    fetchTotalCount: true
  ).totalCount,
  a!httpResponse(
        headers: {
      a!httpHeader(name: "Content-Type", value: "application/json")
    },
    body: a!toJson(value: local!entities)
  )
)

Hi davidb545
You need to change the way you pass the body of the response

Thanks Ekansh - I have updated the code to this:


with(
local!entities: a!queryEntity(
entity: cons!MY_DATABASE_TABLE_DS,
query: a!query(
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: -1
)
),
fetchTotalCount: true
).totalCount,
a!httpResponse(
headers: {
a!httpHeader(name: "Content-Type", value: "application/json")
},
body: a!toJson({count: local!entities})
)
)

However, it still shows error 500.  I am wondering if there is too much data? There are about 7000 rows in the relevant database table - so I wouldn't have thought it was too big - do you think it is?

cheers,

David.

Ok - I have got the code working now - the final code is this:

with(
local!entities: a!queryEntity(
entity: cons!MY_DATABASE_TABLE_DS,
query: a!query(
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 10
)
),
fetchTotalCount: true
).totalCount,
a!httpResponse(
headers: {
a!httpHeader(name: "Content-Type", value: "application/json")
},
body: a!toJson({count: local!entities})
)
)

The only difference was that I added a batch size of 10.  It [nevertheless] returns the correct number of rows in the database...

thanks heaps for your help.

David :-)

As an aside, you should definitely not use the batchsize of "-1" when just getting the .totalCount, as this returns all rows in the query, which you would then proceed to ignore.  Instead, use a batch size of "0", as this actually pulls the metadata (including totalCount) without doing the work of returning all rows.  This will be significantly better for performance.

thanks heaps for the tip! :-)