Dynamic Account Field Updates in NetSuite

Managing field options dynamically is crucial for maintaining accurate and relevant data in business management systems. The following function, updateAccountField, demonstrates how to update a custom account field in NetSuite based on specific criteria such as currency and subsidiary.

Code Overview

The updateAccountField function updates the options of a custom select field on a record. It filters accounts based on the record’s currency and subsidiary, ensuring that only relevant accounts are displayed.

function updateAccountField(rec) {
    try {
        let currency = rec.getValue({ fieldId: "currency" });
        let subsidiary = rec.getValue({ fieldId: "subsidiary" });


        let subsidiaryCurrency = search.lookupFields({
            type: "subsidiary",
            id: subsidiary,
            columns: "currency"
        })?.currency[0]?.value;


        let field = rec.getField({ fieldId: "custpage_grw_026_invo_account" });
        let options = field.getSelectOptions();
        log.debug("options", options);


        // Clear existing options
        options.forEach(function(option) {
            field.removeSelectOption({ value: option.value });
        });


        let suiteQL = `
            SELECT 
                BUILTIN_RESULT.TYPE_INTEGER(ACCOUNT.ID) AS id, 
                BUILTIN_RESULT.TYPE_STRING(ACCOUNT.accountsearchdisplayname) AS account_display_name
            FROM 
                ACCOUNT WHERE 
                ACCOUNT.currency IN (${currency}, ${subsidiaryCurrency})
                AND BUILTIN.MNFILTER(ACCOUNT.subsidiary, 'MN_INCLUDE', '', 'TRUE', ${subsidiary}) = 'T'
                AND ACCOUNT.accttype IN ('Bank')
                AND NVL(ACCOUNT.isinactive, 'F') = 'F'
        `;


        let accountObj = query.runSuiteQL(suiteQL).asMappedResults();


        field.insertSelectOption({
            value: "",
            text: ""
        });


        accountObj.forEach(element => {
            field.insertSelectOption({
                value: element.id,
                text: element.account_display_name
            });
            return true;
        });
    } catch(e) {
        log.error("Error @updateAccountField", e);
    }
}

Key Components

• Retrieve Currency and Subsidiary: The function starts by fetching the currency and subsidiary values from the current record.
• Lookup Subsidiary Currency: Uses search.lookupFields to find the currency associated with the subsidiary.
• Update Select Field Options:
• Fetches the custom field using rec.getField.
• Clears existing options to avoid duplication.
• SuiteQL Query:
• Queries the account records filtered by the record’s currency, subsidiary’s currency, and the subsidiary itself.
• Filters only active bank accounts.
• Insert New Options: Inserts the queried account options into the custom field.