Convert transaction currency to foreign currency.

Jira Code: PROF-9 Singapore Quote and Singapore Sales Order

The rate and amount of items in transactions are shown in the base currency even though we have a different transaction currency. In order to show the item rate and the amount in the Singapore dollar, two custom columns are added. A client script is used which fetches the current exchange rate and calculate item rate and the amount in the Singapore dollar and set to the respective columns.

/**
 * @NApiVersion 2.x
 * @NScriptType ClientScript
 * @NModuleScope SameAccount
 */
define(['N/record','N/search'],

function(record, search) {
    
   

    /**
     * Validation function to be executed when record is saved.
     *
     * @param {Object} scriptContext
     * @param {Record} scriptContext.currentRecord - Current form record
     * @returns {boolean} Return true if record is valid
     *
     * @since 2015.2
     */
    function saveRecord(scriptContext) {
    	var record=scriptContext.currentRecord;
    	var record_id=record.id;
    	try{
    		
    	
  		// get subsidiary of customer

           var sub = record.getValue({
        	    fieldId: 'subsidiary'
        	});
           console.log(sub);
           // only if subsidiary is singapore
           if(sub==5){
        	  
        	
        		var rate_sgd;
        		var amount_sgd;
        		
        			// search to get latest usd to sgd exchange rate
        	    	 var currencyrateSearch = search.create({
        	  		   type: "currencyrate",
        	  		   filters: [
        	  		      ["basecurrency","anyof","5"], 
        	  		      "AND", 
        	  		      ["transactioncurrency","anyof","1"]
        	  		   ],
        	  		   columns: ["exchangerate",
        	  		      search.createColumn({
        	  		         name: "effectivedate",
        	  		         sort: search.Sort.DESC
        	  		      })
        	  		   ]
        	  		});
        	  		var searchResult=currencyrateSearch.run().getRange({
        					start : 0,
        					end : 1
        				});
        	  		var exchangerate=searchResult[0].getValue({
        					name : 'exchangerate'
        				});
        	  		exchangerate=parseFloat(exchangerate).toFixed(2);
        	  		console.log(exchangerate);
        	  		
        	  		
        	  		//search to get sgd to usd exchange rate
        	  		
        	  		var currencyrateSearch1 = search.create({
         	  		   type: "currencyrate",
         	  		   filters: [
         	  		      ["basecurrency","anyof","1"], 
         	  		      "AND", 
         	  		      ["transactioncurrency","anyof","5"]
         	  		   ],
         	  		   columns: ["exchangerate",
         	  		      search.createColumn({
         	  		         name: "effectivedate",
         	  		         sort: search.Sort.DESC
         	  		      })
         	  		   ]
         	  		});
         	  		var searchResult1=currencyrateSearch1.run().getRange({
         					start : 0,
         					end : 1
         				});
         	  		var exchangerate1=searchResult1[0].getValue({
         					name : 'exchangerate'
         				});
         	  		exchangerate1=parseFloat(exchangerate1).toFixed(2);
         	  		console.log(exchangerate1);
        	  		
         	  		// set exchange rates to record
         	  		scriptContext.currentRecord.setValue({
         	  		    fieldId: 'custbody_jj_usd_to_sgd',
         	  		    value: exchangerate,
         	  		    ignoreFieldChange: true
         	  		});
        	  		
         	  		scriptContext.currentRecord.setValue({
         	  		    fieldId: 'custbody_jj_sgd_to_usd',
         	  		    value: exchangerate1,
         	  		    ignoreFieldChange: true
         	  		});
        	  		
        	  		
        	  		var numLines = scriptContext.currentRecord.getLineCount({
        	  		    sublistId: 'item'
        	  		});
        	  		
        	  		for(var i=0;i<numLines;i++){
        	  			var lineNum = scriptContext.currentRecord.selectLine({
        	  			    sublistId: 'item',
        	  			    line: i
        	  			});
        	  			var rate = scriptContext.currentRecord.getCurrentSublistValue({
            			    sublistId: 'item',
            			    fieldId: 'rate'
            			   
            			});
            			console.log("rate",rate);
            			rate_sgd=parseFloat(rate*exchangerate).toFixed(2);
            			console.log("rate_sgd",rate_sgd);
            			
            			scriptContext.currentRecord.setCurrentSublistValue({
            			    sublistId: 'item',
            			    fieldId: 'custcol_jj_rate_sgd',
            			    value: rate_sgd,
            			    ignoreFieldChange: true
            			});
            			
            			var amount = scriptContext.currentRecord.getCurrentSublistValue({
            			    sublistId: 'item',
            			    fieldId: 'amount'
            			});
            			console.log("amount",amount);
            			amount_sgd=parseFloat(amount*exchangerate).toFixed(2);
            			console.log("amount_sgd",amount_sgd);
            			scriptContext.currentRecord.setCurrentSublistValue({
            			    sublistId: 'item',
            			    fieldId: 'custcol_jj_amount_sgd',
            			    value: amount_sgd,
            			    ignoreFieldChange: true
            			});
            			scriptContext.currentRecord.commitLine({
            			    sublistId: 'item'
            			});
        	  		}
        			

        			// convert total, subtotal, frieght, tax to sgd
        	  		
        	  		var total =scriptContext.currentRecord.getValue({
         	  		    fieldId: 'total'
         	  			});
        		    var total_sgd=parseFloat(total*exchangerate).toFixed(2);
        		    var subtotal =scriptContext.currentRecord.getValue({
     	  		    fieldId: 'subtotal'
     	  			});
    		        var subtotal_sgd=parseFloat(subtotal*exchangerate).toFixed(2);
    		        var tax =scriptContext.currentRecord.getValue({
 	  		        fieldId: 'taxtotal'
 	  			    });
		            var tax_sgd=parseFloat(tax*exchangerate).toFixed(2);
		            var freight =scriptContext.currentRecord.getValue({
	 	  		        fieldId: 'shippingcost'
	 	  			    });
		            
			        
			        
			        console.log(total_sgd);
			        console.log(subtotal_sgd);
			        console.log(tax_sgd);
			        console.log(freight);
			        
			        scriptContext.currentRecord.setValue({
         	  		    fieldId: 'custbody_jj_total_sgd',
         	  		    value: total_sgd,
         	  		    ignoreFieldChange: true
         	  		});
			        scriptContext.currentRecord.setValue({
         	  		    fieldId: 'custbody_jj_subtotal_sgd',
         	  		    value: subtotal_sgd,
         	  		    ignoreFieldChange: true
         	  		});
			        scriptContext.currentRecord.setValue({
         	  		    fieldId: 'custbody_jj_taxtotal_sgd',
         	  		    value: tax_sgd,
         	  		    ignoreFieldChange: true
         	  		});
			        if((freight!=null)&&(freight!=undefined)&&(freight!="")){
		            	var freight_sgd=parseFloat(freight*exchangerate).toFixed(2);
		             }
			        else{
			        	var freight_sgd=0.00;
			        }
			        scriptContext.currentRecord.setValue({
         	  		    fieldId: 'custbody_jj_freight_sgd',
         	  		    value: freight_sgd,
         	  		    ignoreFieldChange: true
         	  		});
           }
           return true;
    	}
           catch(err){
        	   console.log(err);
       	}

    }

    return {
        
        saveRecord: saveRecord
    };
    
});