require(["N/query"], function (query) {

const PENDING_STATUS = 1;

const bankPaymentsQuery = query.create({

type: "customrecord_bankpay",

});

const tranJoin = bankPaymentsQuery.join({

fieldId: "custrecord_bankpay_record",

});

const gatewayJoin = bankPaymentsQuery.autoJoin({

fieldId: "custrecord_bankpay_gateway",

});

const payTypeJoin = bankPaymentsQuery.autoJoin({

fieldId: "custrecord_bankpay_paytype",

});

const statusJoin = bankPaymentsQuery.autoJoin({

fieldId: "custrecord_bankpay_status",

});

const firstCondition = bankPaymentsQuery.createCondition({

fieldId: "custrecord_bankpay_status",

operator: query.Operator.EQUAL,

values: PENDING_STATUS,

});

const secondCondition = bankPaymentsQuery.createCondition({

fieldId: "custrecord_bankpay_batchid",

operator: query.Operator.EQUAL,

values: 1,

});

bankPaymentsQuery.condition = bankPaymentsQuery.and(

firstCondition,

secondCondition

);

bankPaymentsQuery.columns = [

gatewayJoin.createColumn({

fieldId: "name",

alias: "gateway",

}),

payTypeJoin.createColumn({

fieldId: "name",

alias: "paytype",

}),

tranJoin.createColumn({

fieldId: "tranid",

alias: "transaction",

}),

statusJoin.createColumn({

fieldId: "name",

alias: "status",

}),

];

const resultSet = bankPaymentsQuery.run();

console.log(resultSet.asMappedResults());

});

I would highly recommend just writing SuiteQL instead of the wordy query building version

that is an option but still it should work right?

it's hard to say, I do remember having problems with using autoJoin instead of explicitly joining. Maybe try not using autoJOin

i will give it a try like that