Set Purchase Order Exchange Rate

This use case shows you how to convert the transaction total on a purchase order to a user-specific currency rather than the vendor's currency. The converted purchase order total is stored in a custom field on the purchase order record so that you can use it for reporting without changing the standard Total field.

Note:

This use case solution updates the custom fields every time the purchase order is edited. If you'd rather have this field only set when the purchase order is created, you need to use a user event script.

For more information about currencies and exchange rates in NetSuite, see Currency Management.

This project is available on the SuiteCloud Project Repository on Oracle Samples GitHub. It includes the complete project customization setup with custom objects, unit tests, package file configuration, and more.

Customization Details

This customization for this use case includes:

  • A custom field (Custom Currency PO Amount) to store the purchase amount in the currency of the person who created the purchase order.

  • A custom field (Custom Currency Exchange Rate) to store the exchange rate between the transaction currency and the custom currency.

  • A script parameter (Custom Currency) to store the currency set by the user.

  • A client script triggered on the saveRecord entry point.

Steps in this tutorial to complete this customization:

Before You Begin

The following table lists features, permissions, and other requirements necessary for performing this tutorial and implementing the solution:

Required Features

Ensure the following features are enabled in your account:

  • Client SuiteScript – Lets you apply client scripts to records and forms.

  • File Cabinet – Lets you store your script files in the NetSuite File Cabinet.

  • Multiple Currencies – Lets you select the currency on transactions and use exchange rates.

  • Purchase Orders – Lets you create and track the orders for items you buy from vendors.

  • A/P – Lets you create and track bills based on vendor terms.

For more information, see Enabling Features.

Required Permissions

You need a role with access to the following:

  • Scripts – Edit access

  • Script Deployments – Edit access

  • Transaction Body Fields – Create access

  • Purchase Orders – Edit and Delete access

  • Currencies – View access

  • Currency Exchange Rates – View access

For more information, see NetSuite Permissions Overview.

Other Requirements

None

Step 1: Create the Custom Fields

This customization uses two custom fields. The Custom Currency PO Amount field stores the purchase amount in the currency of the person who created the order. The Custom Currency Exchange Rate field stores the exchange rate between the transaction currency and the custom currency. Both fields are custom transaction body fields added to the purchase order.

To create the Custom Currency PO Amount field:

  1. Go to Customization > Lists, Records & Forms > Transaction Body Fields > New

  2. Enter values for each field as listed in the following table:

    Field

    Value

    Label

    Custom Currency PO Amount

    ID

    _currency_po_amount

    NetSuite prefixes the ID with custbody.

    Description

    This custom field stores the purchase amount in the currency for the user who is creating or editing the order.

    Type

    Currency

    Store Value

    Checked

    Applies To

    Purchase

    Display

    Subtab: Main

  3. Click Save.

To create the Custom Currency Exchange Rate field:

  1. Go to Customization > Lists, Records & Forms > Transaction Body Fields > New.

  2. Enter values for each field as listed in the following table:

    Field

    Value

    Label

    Custom Currency Exchange Rate

    ID

    _currency_exchange_rate

    NetSuite prefixes the ID with custbody.

    Description

    This custom field stores the exchange rate for the custom currency set by the user.

    Type

    Currency

    Store Value

    Checked

    Applies To

    Purchase

    Display

    Subtab: Main

  3. Click Save.

For more information about creating custom transaction body fields, see the following help topics:

Step 2: Write the Script

This script converts the transaction total on a purchase order to the user's currency instead of using the vendor's currency. The converted purchase order total is stored in a custom field on the purchase order record so that you can use it for reporting without changing the standard Total field.

Script Summary

The following table summarizes the script:

Script: Set Purchase Order Exchange Rate

Script Type

SuiteScript 2.x Client Script Type

Modules Used

  • N/runtime Module

  • N/currency Module

  • N/currentRecord Module – This module is available to all scripts as a provided context object. You don't need to explicitly load this module as a dependency in your define or require statement, however, you can if you want. This tutorial explicitly loads this module.

  • N/log Module – This module is available to all scripts as a global object. However, you should explicitly load this module to avoid conflicts with other objects that may be named ‘log’.

Entry Points

For more information about script types and entry points, see SuiteScript 2.x Script Types.

The Complete Script

This tutorial includes the complete script along with individual steps you can follow to build the script in logical sections. The complete script is provided below so that you can copy it, paste it into your text editor, and save it as a .js file (for example, cs_poExchangeRate.js).

If you would rather create your script step by step, follow the steps in Build the Script.

Note:

This tutorial script uses the define function, which is required for an entry point script (a script you attach to a script record and deploy). If you want to copy the script into the SuiteScript Debugger and test it, you must use the require function. For more information, see SuiteScript Debugger.

Important:

This sample uses SuiteScript 2.1. For more information, see SuiteScript 2.1.

              /**
 * @NApiVersion 2.1
 * @NScriptType ClientScript
 * @NModuleScope SameAccount
 */

define(['N/runtime', 'N/currentRecord', 'N/currency', 'N/log'], (runtime, currentRecord, currency, log) => {
    function saveRecord(context) {
        try {
            const stUserCurrency = runtime.getCurrentScript().getParameter({
                name: 'custscript_custom_currency'
            });
            if (stUserCurrency === " " || stUserCurrency === null || stUserCurrency === undefined) {
                throw "Please enter a value for Custom Currency at Home > User Preferences > Custom.";
            }
            const purchaseOrder = context.currentRecord;
            const stTranCurrency = purchaseOrder.getValue({
                fieldId: 'currency'
            });
            const stTranDate = purchaseOrder.getValue({
                fieldId: 'trandate'
            });
            const stTotal = purchaseOrder.getValue({
                fieldId: 'total'
            });
            let flTotalAmount = parseFloat(stTotal);
            let exchangeRate = currency.exchangeRate({
                source: stTranCurrency,
                target: stUserCurrency,
                date: stTranDate
            });
            const flExchangeRate = parseFloat(exchangeRate);
            const flAmountInUserCurrency = parseFloat(flTotalAmount * flExchangeRate);
            purchaseOrder.setValue({
                fieldId: 'custbody_currency_exchange_rate',
                value: flExchangeRate
            });
            purchaseOrder.setValue({
                fieldId: 'custbody_currency_po_amount',
                value: flAmountInUserCurrency
            });
        } catch(e) {
            if (e.getDetails !== undefined) {
                log.error({
                    title: 'Process Error', 
                    details: JSON.stringify(e)
                });
            } else {
                log.error({
                    title: 'Unexpected Error', 
                    details: JSON.stringify(e)
                });
            }
            throw (e);
        }
        return true;
    }
    return {
        saveRecord: saveRecord
    };
}); 

            

Build the Script

You can write the script using a step-by-step approach that includes the following:

Note:

The code snippets included below don't include indentation. Refer to The Complete Script for suggested formatting.

Start with required opening lines

You need JSDoc comments and a define function at the top of the script file. The JSDoc comments in this script indicate that it's a SuiteScript 2.x (2.0 or 2.1) client script. The script uses four SuiteScript modules specified in the define statement:

  • N/runtime – provides access to script parameters

  • N/currentRecord – provides access to the purchase order record

  • N/currency – provides a SuiteScript method to calculate an exchange rate

  • N/log – lets you log execution details

Start a new script file using any text editor and add the following JSDoc comments and define function at the top of the file:

Note:

This tutorial script uses the define function, which is required for an entry point script (a script you attach to a script record and deploy). If you want to copy the script into the SuiteScript Debugger and test it, you must use the require function. For more information, see SuiteScript Debugger.

                /**
 * @NApiVersion 2.1
 * @NScriptType ClientScript
 * @NModuleScope SameAccount
 */

define(['N/runtime', 'N/currentRecord', 'N/currency', 'N/log'], (runtime, currentRecord, currency, log) => {
}); 

              

Create the entry point function

This script is triggered on the saveRecord entry point when the purchase order record is saved. A try-catch block is logs any errors that might occur during script execution. Most of the script code goes in the try block.

Add the following function definition and initial try-catch block statements at the top of the define function:

                function saveRecord(context) {
    try {
    } catch(e) {
        if (e.getDetails !== undefined) {
            log.error({
                title: 'Process Error',
                details: JSON.stringify(e)
            });
        } else {
            log.error({
                title: 'Unexpected Error',
                details: JSON.stringify(e)
            });
        }
        throw (e);
    }
    return true;
} 

              

Get the currency script parameter

This script uses a script parameter to determine the currency set by the user. If this script parameter is not specified, the script ends.

Add the following code within the try block:

                const stUserCurrency = runtime.getCurrentScript().getParameter({
    name: 'custscript_custom_currency'
});
if (stUserCurrency === " " || stUserCurrency === null || stUserCurrency === undefined) {
    throw "Please enter a value for Custom Currency at Home > User Preferences > Custom.";
} 

              

Get the purchase order data

You need to get the currency value and the purchase order date from the purchase order record.

Add the following code within the try block:

                const purchaseOrder = context.currentRecord;
const stTranCurrency = purchaseOrder.getValue({
    fieldId: 'currency'
});
const stTranDate = purchaseOrder.getValue({
    fieldId: 'trandate'
}); 

              

Convert the purchase order total to a decimal value

Now you need to convert the purchase order total to a decimal number. A decimal number is required to calculate the amount total in the user’s custom currency.

Add the following code into the try block:

                const stTotal = purchaseOrder.getValue({
    fieldId: 'total'
});
let flTotalAmount = parseFloat(stTotal); 

              

Get the exchange rate between the transaction currency and the user’s custom currency

You need to convert the exchange rate between the transaction currency and the currency for the user into a decimal value using the exchangeRate method provided by the N/currency module.

Add the following code into the try block:

                let exchangeRate = currency.exchangeRate({
    source: stTranCurrency,
    target: stUserCurrency,
    date: stTranDate
});
const flExchangeRate = parseFloat(exchangeRate); 

              

Calculate the custom currency amount

Now you need to calculate the custom currency amount using the exchange rate and the purchase order total.

Add the following code within the try block:

                const flAmountInUserCurrency = parseFloat(flTotalAmount * flExchangeRate); 

              

Set the custom currency exchange rate and custom currency PO amount custom fields

Then, set the custom fields on the purchase order to display the custom currency’s exchange rate and the calculated purchase amount.

Add the following code into the try block:

                purchaseOrder.setValue({
    fieldId: 'custbody_currency_exchange_rate',
    value: flExchangeRate
});
purchaseOrder.setValue({
    fieldId: 'custbody_currency_po_amount',
    value: flAmountInUserCurrency
}); 

              

Create the return statement

This script associates the saveRecord function with the saveRecord client script entry point.

Add the following code immediately above the closing }); in your script:

                return {
    saveRecord: saveRecord
}; 

              

Save your script file

You need to save your script file so you can upload it to the NetSuite File Cabinet. Before you save your script file, you may want to adjust the indentation to improve the readability of the script. Refer to The Complete Script for suggested indentation.

When you're happy with your script, save it as a .js file (for example, cs_poExchangeRate.js).

Step 3: Create the Script Record

Now that you’ve completed the script, you can upload it to the File Cabinet and create a script record for it.

For more information about creating script records, see Creating a Script Record.

To create the script record:

  1. Upload your script to the NetSuite File Cabinet.

  2. Go to Customization > Scripting > Scripts > New.

  3. Select your script from the Script File list and click Create Script Record. The Script page is displayed.

  4. On the Script page, enter the following values:

    Field

    Value

    Name

    Set Purchase Order Exchange Rate

    ID

    _cs_po_set_exchange_rate

    NetSuite prefixes the ID with customscript.

    Description

    This script converts the transaction total on a purchase order to a user-specific currency rather than the currency associated with the vendor.

  5. Set any other fields on the script record as required.

  6. Click Save and Deploy. The Script Deployment page appears. Continue with Step 4: Deploy the Script.

Step 4: Deploy the Script

After you create the script record, you can create a script deployment record for it. A script deployment record determines how, when, and for whom the script runs.

For more information about script deployment records, see Script Deployment.

To deploy the script:

  1. Complete the steps in Step 3: Create the Script Record.

  2. On the Script Deployment page, enter the following values:

    Field

    Value

    Applies To

    Purchase Order

    ID

    _cs_po_set_exchange_rate

    NetSuite prefixes the ID with customdeploy.

    Status

    Testing

    The Testing status lets the script owner test the script without affecting other users in the account.

    Log Level

    Debug

    The Debug level writes all log.debug statements and errors to the Execution Log subtab of the script deployment record.

    Audience > Roles

    Check Select All

  3. Click Save.

Step 5: Create and Set the Script Parameter

This tutorial uses a script parameter to specify the user’s custom currency and the corresponding exchange rate.

For more information about creating and setting script parameters, see Creating Script Parameters (Custom Fields).

To create the script parameter:

  1. Go to Customization > Scripting > Scripts.

  2. Find your script in the list and click Edit next to the script name.

  3. On the Script page, click the Parameters subtab and click New Parameter. The Script Field page appears.

  4. On the Script Field page, enter the following values to create the two script parameters. Click Save after you enter each value.

    Label

    ID

    Type

    List/Record

    Description

    Preference

    Custom Currency

    _custom_currency

    NetSuite prefixes the ID with custscript, using custscript_custom_currency in the script.

    List/Record

    Currency

    This parameter specifies the user’s custom currency.

    User

    This setting lets each user set their own value for the script parameter on the Set Preferences page. The user preference overrides the value set on the script deployment page.

  5. To save the script parameter, click Save. The Script page appears.

  6. To save the script record with the updated script parameter, click Save.

After you create the script parameter, you must set the value of the parameter.

To set the script parameter:

  1. Go to Home > Set Preferences to set your parameter value as a user preference.

  2. On the Custom Preferences subtab, in the Custom Currency field, select the currency to use for the custom exchange rate.

  3. Click Save.

Step 6: Test the Solution

After you create the script record and deploy your script, you can test it by creating a new purchase order and reviewing it to verify the currency and exchange rate are correct.

To test the solution:

  1. Go to Transactions > Purchases > Enter Purchase Orders to create a new purchase order.

  2. In the Custom Form field, select Standard Purchase Order.

  3. Fill in all required fields on the purchase order.

  4. Click Save.

  5. View the newly created purchase order and verify that the custom fields, Custom Currency PO Amount and Custom Currency Exchange Rate, have the correct values.

Related Topics

General Notices