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

• 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	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:

• 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 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	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 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.

Note

This sample uses 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 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

• 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