Hi, i'm trying to create a query to search for Vendor Bills that were last modified on or after certain datetime. I keep running into no result even though there should be results. Could some one take a look at this condition and give me suggestions please?

var myQueryConditionLastModifiedDate = myQuery.createCondition({
                    fieldId: 'lastmodifieddate',
                    operator: query.Operator.ON_OR_AFTER,
                    values: ['2/12/2024 11:30 am']
                });

If I try to run a query with this Condition, I get:

'Search error occurred: Parse of date/time "1/1/2012 11:30 am" failed with date format "yy/MM/dd" in time zone America/Los_Angeles\nCaused by:\n\tjava.text.ParseException: Unparseable date: "1/1/2012 11:30 am"'

Thanks, but still no luck 😞 I don't know if there's something else going on. Could you take a look at this query to see if anything jumps out?

var myQuery = query.create({
                    type: query.Type.TRANSACTION
                });

                var myVendorJoin = myQuery.autoJoin({
                    fieldId: 'custbody_al_original_carrier'
                });

                var myVendorJoinConditionXlr8PdTP = myVendorJoin.createCondition({
                    fieldId: 'custentity_xlr8_tp_paid_via_triumphpay',
                    operator: query.Operator.IS,
                    values: true
                });
                var myVendorJoinConditionCategory = myVendorJoin.createCondition({
                    fieldId: 'category',
                    operator: query.Operator.ANY_OF,
                    values: [CONSTANTS.CARRIER_FACTOR]
                });

                var myQueryConditionStatus = myQuery.createCondition({
                    fieldId: 'status',
                    operator: query.Operator.ANY_OF,
                    values: ['VendBill:A'] // Open
                });

                var myQueryConditionXlr8NoSend = myQuery.createCondition({
                    fieldId: 'custbody_xlr8_no_send',
                    operator: query.Operator.IS,
                    values: false
                });

                var myQueryConditionType = myQuery.createCondition({
                    fieldId: 'type',
                    operator: query.Operator.ANY_OF,
                    values: ['vendorbill']
                });

                var myQueryConditionRelatedSO = myQuery.createCondition({
                    fieldId: 'custbodycustbody_al_related_salesorder',
                    operator: query.Operator.ANY_OF_NOT,
                    values: [null]
                    // operator: query.Operator.EMPTY_NOT,
                    // values: []
                });

                var myTransactionLinesJoin = myQuery.autoJoin({
                    fieldId: 'transactionlines'
                });
                var myTransactionLinesJoinML = myTransactionLinesJoin.createCondition({
                    fieldId: 'mainline',
                    operator: query.Operator.IS,
                    values: true
                });
                var myTransactionLinesJoinSub = myTransactionLinesJoin.createCondition({
                    fieldId: 'subsidiary',
                    operator: query.Operator.ANY_OF,
                    values: [CONSTANTS.MEXICO, CONSTANTS.US]
                });

                var myQueryConditionLastModifiedDate = myQuery.createCondition({
                    fieldId: 'lastmodifieddate',
                    operator: query.Operator.ON_OR_AFTER,
                    values: [new Date('2/12/2024 11:30 am')]
                });

                myQuery.condition = myQuery.and(
                    myVendorJoinConditionXlr8PdTP,
                    myVendorJoinConditionCategory,
                    myQueryConditionStatus,
                    myQueryConditionXlr8NoSend,
                    myQueryConditionType,
                    myQueryConditionRelatedSO,
                    myTransactionLinesJoinSub,
                    myTransactionLinesJoinML,
                    myQueryConditionLastModifiedDate
                );

                // Columns
                var myQueryInternalId = myQuery.createColumn({
                    fieldId: 'id',
                    groupBy: false,
                    context: {
                        name: 'RAW'
                    },
                    alias: 'Internal ID'
                });
                var myQueryLoadNum = myQuery.createColumn({
                    fieldId: 'custbody_al_load_number',
                    groupBy: false,
                    context: {
                        name: 'RAW'
                    },
                    alias: 'LoadNumber'
                });
                var myQueryXLR8Id = myQuery.createColumn({
                    fieldId: 'custbody_al_accelerate_id',
                    groupBy: false,
                    context: {
                        name: 'RAW'
                    },
                    alias: 'XLR8 PrimaryKey'
                });
                var myQueryTranId = myQuery.createColumn({
                    fieldId: 'tranid',
                    groupBy: false,
                    context: {
                        name: 'RAW'
                    },
                    alias: 'InvoiceNumber'
                });
                var myQueryTranNum = myQuery.createColumn({
                    fieldId: 'transactionnumber',
                    groupBy: false,
                    context: {
                        name: 'RAW'
                    },
                    alias: 'TranNumber'
                });

                myQuery.columns = [
                    myQueryInternalId,
                    myQueryLoadNum,
                    myQueryXLR8Id,
                    myQueryTranId,
                    myQueryTranNum
                ]

                var mySQLQuery = myQuery.toSuiteQL();
                var resultSet = mySQLQuery.run();

                var results = resultSet.results;
                // log.debug('results.length', results.length);

what jumps out to me is how horrendous the N/query syntax is.

@Helena Have you ruled out time zone conflicts?

Couple things: 1. You can reduce the verbosity of the Query API by taking a more declarative approach like so 2. One problem is that the

type

filter will not contain

vendorbill

but `VendBill`; you either need to switch to

recordtype = vendorbill

or

type = VendBill

for that condition

Thanks a lot @erictgrubaugh, after changing "type" to "recordtype" it works like a charm !