If I run the following query in the SuiteQL suitelet,
SELECT COUNT(*)
from inventoryitemlocations
WHERE
averagecostmli IS NOT NULL;
Then It returns 16869, so that means there are 16,869 rows.
I want to provide a CSV export that gets emailed to users on a scheduled basis, of all the data that would be there if it was SELECT * instead of SELECT COUNT(*)
However, when I create a suitescript to do this, I get much smaller numbers of rows.
If I use Dataset.runPaged, some of the pages (pageSize = 1000) are completely empty. These empty pages appear after several populated pages, and before other populated pages.
This is not the entire problem, however, because even if the empty pages were completely populated, they would still not add up to 16,869 rows, but instead they would amount to around 10,000 or 11,000, give or take, since the number of non-empty rows (full of null values actually) is 7,271 and I counted 4 empty pages.
The same Dataset in the NetSuite UI shows 10,187 rows.
If I use query.runSuiteQLPaged on the exact same query string as I used in the SuiteQL Suitelet, I get the same problem (some empty pages), but with a different number of results than the Dataset in the UI, which is different from the number of rows from the same Dataset using Dataset.runPaged in SuiteScript.
With discrepancies like these, I can't trust the data being returned from runSuiteQLPaged or Dataset.runPaged.
Should I report a defect, or is this an undocumented or unknown-to-me feature where occasionally the data structure returned by the APIs is different, and so I'm not dereferencing it properly or it doesn't work with Page.value.data.asMappedResults() or something?
The Dataset is based on Item table joined with Locations table to get all the same fields.
Even if this is to blame for the difference in number of returned rows between the SuiteQL query and the Dataset, it doesn't account for the differences between the number of rows returned between SuiteQL Suitelet and my script, or between the Dataset in the UI and the exact same dataset in my script.