Returning large data from query in small batch

Yes, add appropriate paging info to your query.

You should never configure a query to pull back all data, as this is not a scalable design paradigm. Set the paging info batch size to some reasonable number, like 10 or 20, and you should have better results.

Hello,

use a!pagingInfo(1,15) to return a batch of only 15 out of 7000 rows.

docs.appian.com/.../fnc_system_a_paginginfo.html

If I add paginginfo, like 15 - the query is only returning 15 rows - How does the user get to 16-30 and beyond? That's what I'm not understanding clearly. So if I change my query to grab 15 rows as per the paging info batch size, and load it in to the paging grid on the UI, it only loads those 15 rows? how do I page over to the next 16-30?

Thanks

Hello,

you can use Paging Grid to display the records and then when the user clicks on the next or previous links on the paging grid the system would know what to do.

For more details look into below link.

docs.appian.com/.../Paging_Grid_Component.html

You should declare your paging info as a local variable in a load() function, and then reference it in your grid code.

Look at the link Puspendu Pal provided. There is example code farther down the page illustrating what I mean.

Hi johnh0001,

Use pagingInfo in load(), query the data and use gird field in with(). but make sure querying the data should be data subset not list of variant.Follow the code snippet, it might help you.

load(
local!pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 10
),
with(
local!workflow:queryEntity(
pagingInfo: local!pagingInfo
),
a!gridField(
label: "sample",
columns: {
a!gridTextColumn(
label: "Workflow Number",
data: index(
local!workflow.data,
"wfNumber",
{}
)
),
a!gridTextColumn(
label: "Request ID",
data: index(
local!workflow.data,
"requestId",
{}
)
),
a!gridTextColumn(
label: "Absence Name",
data: index(
local!workflow.data,
"absenceName",
{}
)
)
},
value: local!pagingInfo,
saveInto: local!pagingInfo,
totalCount: local!workflow.totalCount
)
)
)

Hi joh4 I believe you need to explore paging grid SAIL recipe to understand the SAIL component properly.

For quick reference, you can have a look on this link mention below:
docs.appian.com/.../recipe_display_array_of_data_in_a_grid.html

Hope this will help you.

create a query entity rule such as : a!queryEntity(entity: cons!entity, query: a!query(filters{whatever filters you want},
pagingInfo:ri!!pagingInfo
))

then in the main rule create local!pagingInfo(startIndex: 1, batchSize: 10)
Then call the query entity rule;
with(
local!query: rule!query(pagingInfo: local!pagingInfo),
then display it in grid
)
Do not do another todatasubset() on local!query.data...then your totalcount will only show 10 items in the grid instead of display 1-to of 7000 records.
This approach ensures that you are only querying 10 items at a time from the 7000 records. Another thing to note is if you have filters on that query rule, then if you have filtering through any text boxes or dropdown, do a!save(local!pagingInfo.startIndex, 1) on the saveInto of those fields, otherwise you will get error for startindex.

Create a query entity rule like
a!queryEntity(
entity: cons!TEST_ENTITY,
query:
a!query(
filters:
{
Filters you want to add
},
pagingInfo:ri!!pagingInfo
)
)

Create a new rule in which we will iterate the loop according to the total count of the datasubset

with(
local!batchSize: 1000,
local!filters: a!fromJson(
ri!input
),
local!pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: - 1
),
local!iterations: roundup(
cast(
typeof(
1
),
local!filters.totalCount
) / local!batchSize,
0
),
local!dt: apply(
rule!TestReportViewDataOnly(
pagingInfo: _,
filters: local!filters
),
apply(
topagingInfo(
_,
local!batchSize
),
1 + enumerate(
local!iterations
) * local!batchSize
)
),
.
.
.
.
)

I hope this will help you

Hi Arpit,

Thanks for sharing a rule to call the query enity recursively with variable batchsize. But the problem is that it is return as list of datasubset as output. For example

Suppose if there are 790 records and batchsize is 100, we are getting an single output as  list of 8 datasubsets. How to merge the output of 8 datasubset into 1 datasubset. Tried many functions but not able to merge the data at the end.

Actual output is

local!datasubset1 - List of 8 datasubset with batchsize of 100

Expecting an output as

local!datasubset1 - List of 1 datasubset with 790 rows

Thanks in advance for reply.