In the following code, the myQueryResults is returned as an empty array, even though if I put the same conditions on the Dataset manually in the UI, I get 43 results. I removed the existing credit and existing bill conditions and still had the problem. I removed the condDate and now the script takes a very very long time to run. Why am I getting 0 results in script but 43 results in the UI with the same conditions?

const myQuery = query.load({id: datasetId});
		
		const condDate = myQuery.createCondition({fieldId: "createddate", operator: query.Operator.ON, values: query.RelativeDateRange.TODAY}); //TODO: Change to YESTERDAY if we run this script the day after the invoices and credit memos are created.
		const condExistingCredit = myQuery.createCondition({fieldId: "custbody_fbsg_creditmemo_billcredit", operator: query.Operator.EMPTY}); //values parameter not required for EMPTY and EMPTY_NOT operators.
		const condExistingBill = myQuery.createCondition({fieldId: "custbody_fbsg_invc_vendorbill", operator: query.Operator.EMPTY}); //values parameter not required for EMPTY and EMPTY_NOT operators.

		myQuery.condition = myQuery.and(myQuery.condition, condDate, condExistingCredit, condExistingBill);
		
		log.debug("getTransactions condition", myQuery.condition);
		
		const myQueryResultSet = myQuery.run();
		log.debug("myQueryResultSet", myQueryResultSet);
		const myQueryResults = myQueryResultSet.results;
		log.debug("myQueryResults", myQueryResults);

AI generated answer (using internal Slack threads):

The issue you're experiencing could be due to a number of reasons. Here are a few possibilities: 1. Date Format: Ensure that the date format you're using in your script matches the one in your NetSuite account. If the formats don't match, it could lead to discrepancies in the results. 2. Field IDs: Make sure that the field IDs you're using in your script are correct. If you're using custom fields, ensure that you're using the correct internal IDs. 3. Query Conditions: Check if the conditions you're using in your script are correct. If you're using the

query.Operator.EMPTY

operator, remember that it doesn't require a

values

parameter. 4. Execution Context: The script might be running in a different context than the UI. For example, if the script is running as a scheduled script, it might have different permissions than when you're running it in the UI. 5. Data Access: Ensure that the script has access to the data it needs. If the script is running in a role that doesn't have access to the data, it might return 0 results. 6. Script Performance: If the script is taking a long time to run after removing the

condDate

condition, it might be because the script is trying to process a large amount of data. You might need to optimize your script to handle large data sets. To debug this issue, you could try logging the conditions and results at different points in your script to see where the discrepancy occurs. You could also try running the script with a smaller data set to see if it returns the expected results. If none of these suggestions work, you might need to review your script and the data it's processing to identify the issue.

It turned out I wasn't creating the date condition correctly. A little deeper dive into the API docs showed the right way.