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.
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:
For more information, see Enabling Features. |
Required Permissions |
You need a role with access to the following:
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:
-
Go to Customization > Lists, Records & Forms > Transaction Body Fields > New
-
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
-
Click Save.
To create the Custom Currency Exchange Rate field:
-
Go to Customization > Lists, Records & Forms > Transaction Body Fields > New.
-
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
-
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 |
|
Modules Used |
|
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.
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.
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:
-
Get the exchange rate between the transaction currency and the user’s custom currency
-
Set the custom currency exchange rate and custom currency PO amount custom fields
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:
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:
-
Upload your script to the NetSuite File Cabinet.
-
Go to Customization > Scripting > Scripts > New.
-
Select your script from the Script File list and click Create Script Record. The Script page is displayed.
-
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.
-
Set any other fields on the script record as required.
-
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:
-
Complete the steps in Step 3: Create the Script Record.
-
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
-
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:
-
Go to Customization > Scripting > Scripts.
-
Find your script in the list and click Edit next to the script name.
-
On the Script page, click the Parameters subtab and click New Parameter. The Script Field page appears.
-
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.
-
To save the script parameter, click Save. The Script page appears.
-
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:
-
Go to Home > Set Preferences to set your parameter value as a user preference.
-
On the Custom Preferences subtab, in the Custom Currency field, select the currency to use for the custom exchange rate.
-
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:
-
Go to Transactions > Purchases > Enter Purchase Orders to create a new purchase order.
-
In the Custom Form field, select Standard Purchase Order.
-
Fill in all required fields on the purchase order.
-
Click Save.
-
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