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;
}