如何在NetSuite脚本保存搜索中添加近24小时筛选器？

尝试筛选近24小时内激活/停用的供应商，但多次尝试后仍未得到结果，而NetSuite账户中确实存在近24小时内激活的供应商。

测试时间范围：

• 当前时间：2025-09-18T08:57:06.795Z
• 近24小时起始时间：2025-09-17T08:57:06.795Z

当前使用的搜索代码：

function getInputData(context) {
    const now = new Date();
    const last24Hours = new Date(now.getTime() - 24 * 60 * 60 * 1000);
    let vendorSearch = search.create({
        type: "vendor",
        filters:
            [
                [["systemnotes.field", "anyof", "ENTITY.BINACTIVE"],
                    "AND", ["systemnotes.date", "after", last24Hours],
                    "AND", ["systemnotes.newvalue", "is", "T"],
                    "AND", ["transaction.status", "noneof", "VendBill:B", "VendBill:C", "PurchOrd:G", "PurchOrd:H", "VendAuth:C", "VendAuth:G", "VendAuth:H"],
                    "AND", ["transaction.type", "anyof", "VendBill", "PurchOrd", "VendAuth"]],
                "OR",
                [["systemnotes.field", "anyof", "ENTITY.BINACTIVE"],
                    "AND", ["systemnotes.date", "after", last24Hours],
                    "AND", ["systemnotes.newvalue", "is", "F"],
                    "AND", ["transaction.type", "anyof", "VendCred"],
                    "AND", ["transaction.appliedtotransaction", "anyof", "@NONE@"]]
            ],
        columns: [
            search.createColumn({ name: "internalid", summary: "GROUP" }),
            search.createColumn({ name: "entityid", summary: "GROUP" }),
            search.createColumn({ name: "altname", summary: "GROUP" }),
            search.createColumn({ name: "isinactive", summary: "GROUP" }),
            search.createColumn({ name: "date", join: "systemnotes", summary: "MAX", label: "Latest Status Date" }),
            search.createColumn({ name: "newvalue", join: "systemnotes", summary: "MAX", label: "Latest Status Value" }),
            search.createColumn({ name: "internalid", join: "transaction", summary: "GROUP", label: "Transaction Internal Id" }),
            search.createColumn({ name: "transactionnumber", join: "transaction", summary: "GROUP", label: "Transaction Number" }),
            search.createColumn({ name: "type", join: "transaction", summary: "GROUP", label: "Transaction Type" }),
            search.createColumn({ name: "statusref", join: "transaction", summary: "GROUP", label: "Transaction Status" }),
            search.createColumn({name: "subsidiary", join: "transaction", summary: "GROUP", label: "Transaction Subsidiary"
            })
        ]
    });

    return vendorSearch;

}

1. 移除不必要的交易关联筛选 #

当前搜索强制绑定了特定类型/状态的交易条件，但激活/停用供应商的操作本身不需要关联任何交易，这会直接过滤掉无对应交易记录的目标供应商，导致无结果返回。

建议先简化筛选逻辑，仅保留System Notes的核心条件：

filters: [
    ["systemnotes.field", "anyof", "ENTITY.BINACTIVE"],
    "AND", ["systemnotes.date", "after", last24Hours],
    "AND", ["systemnotes.newvalue", "anyof", "T", "F"]
]

2. 修正System Notes的聚合逻辑 #

用MAX聚合systemnotes.date和systemnotes.newvalue可能导致时间与状态值不匹配（比如取到最新时间，但对应的状态值并非该时间点的记录）。若需保留聚合，建议调整列定义，确保时间与值来自同一条System Notes记录：

columns: [
    search.createColumn({ name: "internalid", summary: "GROUP" }),
    search.createColumn({ name: "entityid", summary: "GROUP" }),
    search.createColumn({ name: "isinactive", summary: "GROUP" }),
    search.createColumn({ name: "date", join: "systemnotes", summary: "GROUP", label: "Status Change Date" }),
    search.createColumn({ name: "newvalue", join: "systemnotes", summary: "GROUP", label: "New Status" })
]

3. 处理时区与日期格式问题 #

NetSuite搜索对日期格式有严格要求，直接传入Date对象可能因时区差异导致筛选偏差。建议使用NetSuite内置的format模块转换日期：

const format = require('N/format');
const last24HoursFormatted = format.formatDate({
    value: last24Hours,
    type: format.Type.DATETIME
});
// 筛选条件中使用格式化后的日期
["systemnotes.date", "after", last24HoursFormatted]

4. 分步排查验证 #

• 第一步：运行仅保留System Notes核心条件的搜索，确认是否能找到目标供应商。
• 第二步：若第一步有结果，再逐步添加交易关联条件，排查哪一步导致结果丢失。
• 第三步：在NetSuite UI中手动创建相同条件的保存搜索，对比脚本结果，确认是否为脚本逻辑问题。

内容的提问来源于stack exchange，提问作者Maira S