N/Query module FieldContext.CONVERTED based on transaction date

Hi All,

I am running a query on purchase orders, and want to convert the transaction currency amount to a specific currency on the date of the transaction (note this currency is different to the base currency of the subsidiary raising the PO).

Starting from the local currency amount and using FieldContext.CONVERTED seems to be the most logical way to go about this, but I am only able to use a specific date or a relative date, rather than a date from the resultset.

My code is below; I have marked the area where I want to insert the transaction date.




function getlastpurchasedata (itemarray) {
    let lastpurchasedata = query.create({
        type: 'TRANSACTION'
    });

    let transactionlinejoin = lastpurchasedata.autoJoin({
        fieldId: 'transactionLines'
    });

    let accountinglinejoin = transactionlinejoin.autoJoin({
        fieldId: 'accountingImpact'
    });

    let itemjoin = transactionlinejoin.autoJoin({
        fieldId: 'item'
    })

    var myDatesAgo = query.createRelativeDate({
        dateId: query.DateId.MONTHS_AGO,
        value: 12
    });
    lastpurchasedata.condition = lastpurchasedata.and(
        lastpurchasedata.createCondition({
            fieldId: 'type',
            operator: 'ANY_OF',
            values: ['PurchOrd']
        }),lastpurchasedata.createCondition({
            fieldId: 'trandate',
            operator: 'WITHIN',
            values: myDatesAgo
        }),transactionlinejoin.createCondition({
            fieldId: 'mainline',
            operator: 'IS',
            values: false
        }),transactionlinejoin.createCondition({
            fieldId: 'item',
            operator: 'ANY_OF',
            values: itemarray
        }),transactionlinejoin.createCondition({
            fieldId: 'subsidiary',
            operator: 'ANY_OF',
            values: 9
        })
    );

    lastpurchasedata.columns = [
        lastpurchasedata.createColumn({
            fieldId: 'trandate',
            context: query.FieldContext.RAW
        }),
        lastpurchasedata.createColumn({
            fieldId: 'entity',
            context: query.FieldContext.DISPLAY
        }),
        lastpurchasedata.createColumn({
            fieldId: 'tranid',
            context: query.FieldContext.RAW
        }),
        transactionlinejoin.createColumn({
            fieldId: 'item',
            context: query.FieldContext.DISPLAY
        }),
        lastpurchasedata.createColumn({
            fieldId: 'type',
            context: query.FieldContext.RAW
        }),
        lastpurchasedata.createColumn({
            fieldId: 'currency',
            context: query.FieldContext.DISPLAY
        }),
        transactionlinejoin.createColumn({
            fieldId: 'foreignamount',
            context: {
                name: query.FieldContext.CONVERTED,
                params: {
                    currencyId: 2,
                    date: <<< I WANT TO GRAB THE TRANSACTION DATE HERE >>>
                }
            }
        }),
        transactionlinejoin.createColumn({
            fieldId: 'foreignamount',
            context: {
                name: query.FieldContext.RAW
            },
            alias: 'localamount'
        }),
        transactionlinejoin.createColumn({
            fieldId: 'quantity',
            context: query.FieldContext.RAW
        }),
        lastpurchasedata.createColumn({
            fieldId: 'exchangerate',
            context: query.FieldContext.RAW,
            alias: 'poexchangerate'
        }),
    ];

    lastpurchasedata.sort = [
        lastpurchasedata.createSort({
            column: lastpurchasedata.columns[0],
            ascending: false
        })
    ]

    var resultset = lastpurchasedata.run().asMappedResults();

    return resultset;
}
Rookie Asked on February 22, 2022 in SuiteScript.
Add Comment
1 Answer(s)

i believe, you can’t do what you want directly cuz they have limited what is availabe in that narrow method.

So if you are trying to dynamically join in the date from the transaction then you have two choices.

Before either choice, notice that if you export the query using toSuiteQL you’ll find that the function being used is `BUILTIN.CURRENCY_CONVERT` and it only accepts certain values.  You can use this function manually yourself so:

    1. use a formula instead. in your formula you can reference  {trandate} and manually build in the function call yourself 
    2. jump through their hoops and then export/manipulate the SQL after it is generated.
        1. use an obviously-wrong-date such as new Date(0) (this is always 1969-12-30 ) 1969-12-30
        2. dump the query toSuiteQL
        3. Search and replace the date 1969-12-30 — subsituting in "TRANSACTION".trandate instead
          1.  
      1.  

 

 

Rookie Answered on February 22, 2022.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor