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 currency associated with the vendor. The converted purchase order total is stored in a custom field on the purchase order record so that it can be used for reporting without changing the standard Total field.

Note:

This use case solution will update the custom fields every time the purchase order is edited. If you prefer to have this field only set when the purchase order is created, you must convert this to 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 and include 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

• Step 1: Create the Custom Fields

• Step 2: Write the Script

• Step 3: Create the Script Record

• Step 4: Deploy the Script

• Step 5: Create and Set the Script Parameter

• Step 6: Test the Solution

Before You Begin

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

Required Features	The following features must be enabled in your account: Client SuiteScript - This feature allows you to apply client scripts to records and forms. File Cabinet – This feature allows you to store your script files in the NetSuite File Cabinet. Multiple Currencies - This feature enables currency selections on transactions and exchange rates. Purchase Orders - This feature allows you to create and track the orders for items you are buying from vendors. A/P - This feature allows you to create and track bills based on vendor terms. For more information, see Enabling Features.
Required Permissions	You will 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 purchase 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 prepends ‘custbody’ to this ID.
   Description	This custom field stores the purchase amount in the currency for the user saving the order (creating or editing).
   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 prepends ‘custbody’ to this ID.
   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:

• Creating a Custom Field

• Creating Custom Transaction Body Fields

Step 2: Write the Script

This script converts the transaction total on a purchase order to a user-specific currency rather than use the currency associated with the vendor. The converted purchase order total is stored in a custom field on the purchase order record so that it can be used 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 do not need to explicitly load this module as a dependency in your define or require statement, however, you may 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	saveRecord(scriptContext)

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 and paste it into your text editor and save the script as a .js file (for example, cs_poExchangeRate.js).

If you would rather create your script by adding code in logical sections, 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). You must use the require function if you want to copy the script into the SuiteScript Debugger and test it. 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:

• Start with required opening lines

• Create the entry point function

• Get the currency script parameter

• Get the purchase order data

• Convert the purchase order total to a decimal value

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

• Calculate the custom currency amount

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

• Create the return statement

• Save your script file

Note:

The code snippets included below do not account for indentation. Refer to The Complete Script for suggested indentation.

Start with required opening lines

JSDoc comments and a define function are required at the top of the script file. The JSDoc comments in this script indicate that it is 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 – allows you to log execution details

Start a new script file using any text editor and place 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). You must use the require function if you want to copy the script into the SuiteScript Debugger and test it. 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 used to log any errors that might occur during script execution. Most of the script code will be placed 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 will end.

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 total amount of the purchase order value to a decimal number. A decimal number is required to calculate the purchase order amount total to the user’s custom currency.

Add the following code within 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 within 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

And you want to set the custom fields on the purchase order to display the custom currency’s exchange rate and the calculated purchase amount total with the custom currency.

Add the following code within 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 load it to the NetSuite File Cabinet. Before you save your script file, you may want to adjust the indentation so that the script is more readable. Refer to The Complete Script for suggested indentation.

When you are happy with how your script file reads, 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 the script file 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 prepends ‘customscript’ to this ID.
   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. Optionally set any other fields on the script record as desired.

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 for your script, 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 prepends 'customdeploy' to this ID.
   Status	Testing The Testing status allows the script owner to test the script without affecting other users in the account.
   Log Level	Debug The Debug level will write all log.debug statements in a script to the Execution Log tab of the script deployment record as well as all errors.
   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. Locate your script in the list of scripts and click Edit next to the script name.

3. On the Script page, click the Parameters tab 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 have entered the values for each parameter.

   Label	ID	Type	List/Record	Description	Preference
   Custom Currency	_custom_currency NetSuite prepends ‘custscript’ to this ID. The value ‘custscript_custom_currency’ is used in the script.	List/Record	Currency	This parameter specifies the user’s custom currency.	User This setting allows each user to set their own value for the script parameter using the Set Preferences page. The user preference will override the value specified for the script parameter (if any) on the script deployment page.

5. Click Save to save the script parameter. The Script page appears.

6. Click Save to save the script record with the updated script parameter.

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. Under the Custom Preferences tab, 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 your solution by creating a new purchase order and reviewing after it is saved to verify the currency and exchange rate.

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, are populated with the correct values.

Related Topics

SuiteCloud Customization Tutorials

Add Custom Button to Execute a Suitelet

Calculate Commission on Sales Orders

Copy a Value to the Item Column

Disable Tax Fields

Hide a Column in a Sublist

Populate Item Substitution

Set a Default Posting Period in a Custom Field

Set the Item Amount to Zero for Marketing Orders

Set Default Values in a Sublist

Track Customer Deposit Balances

Validate Order on Entry

General Notices