Servoy Forum

Why would databaseManager.getFoundSetCount(fsCashReceipts) return -1 when fsCashReceipts.getSize() returns 200. I know there are over 200 records in this foundset.

Below is the entire chunk of code. After I loadRecords I run databaseManager.getFoundSetCount(fsCashReceipts) and it returns -1. It should be well over 200. fsCashReceipts.getSize() returns 200.

//Cash Receipts (pay_head, pay_line, rec_dist)
var cashReceiptsQuery = datasources.db.picas.pay_head.createSelect();
cashReceiptsQuery.where.add(cashReceiptsQuery.columns.pay_hd_date.le(cutoffAR));

//Do not delete if Cash Received on these receivables after the cutoff date
var payLineQuery = datasources.db.picas.pay_line.createSelect();
payLineQuery.where
.add(payLineQuery.columns.cust_no.eq(cashReceiptsQuery.joins.pay_head_to_pay_line.columns.cust_no))
.add(payLineQuery.columns.rec_inv_no.eq(cashReceiptsQuery.joins.pay_head_to_pay_line.columns.rec_inv_no))
.add(payLineQuery.columns.pay_ln_type.eq(cashReceiptsQuery.joins.pay_head_to_pay_line.columns.pay_ln_type))
.add(payLineQuery.columns.pay_hd_date.gt(cutoffAR));
cashReceiptsQuery.where.add(cashReceiptsQuery.or
.add(cashReceiptsQuery.columns.pay_hd_type.isin([scopes.picasAccountsReceivable.CASHRECEIPTTYPES.PENDING,scopes.picasAccountsReceivable.CASHRECEIPTTYPES.VOIDED]))
.add(cashReceiptsQuery.not(cashReceiptsQuery.exists(payLineQuery))));

//Do not delete if Receivable has a date greater than the cutoff date or if the Receivable is not Paid
var receivableQuery = datasources.db.picas.receivable.createSelect();
receivableQuery.where
.add(receivableQuery.columns.cust_no.eq(cashReceiptsQuery.joins.pay_head_to_pay_line.columns.cust_no))
.add(receivableQuery.columns.rec_inv_no.eq(cashReceiptsQuery.joins.pay_head_to_pay_line.columns.rec_inv_no))
.add(receivableQuery.columns.rec_inv_type.eq(cashReceiptsQuery.joins.pay_head_to_pay_line.columns.pay_ln_type));
receivableQuery.where.add(receivableQuery.or
.add(receivableQuery.columns.rec_inv_date.gt(cutoffAR))
.add(receivableQuery.columns.rec_status.not.eq(scopes.picasAccountsReceivable.INVOICESTATUS.PAID)));
cashReceiptsQuery.where.add(cashReceiptsQuery.not(cashReceiptsQuery.exists(receivableQuery)));

cashReceiptsQuery.result.addPk();
cashReceiptsQuery.result.distinct = true;

var fsCashReceipts = datasources.db.picas.pay_head.getFoundSet();
fsCashReceipts.loadRecords(cashReceiptsQuery);

Hi Brenda,

Does the logfile show any errors?

Rob

As a side note, loadRecords returns a boolean that should really be checked. If you pass in something that may be invalid, it will run, and not load anything and return false. Probably want something like:

Code: Select all

if(fsCashReceipts.loadRecords(cashReceiptsQuery)){
   databaseManager.getFoundSetCount(fsCashReceipts)
}