How to update most recent record group by a specific field using transform map?

[Ankita Kolhe]

Hi Community,

I want to import data to Exchange Rates table. Need to update only recent records group by 'Currency' field. I have created the import set and trying to utilise transform map script so that only required records updated and other records can be ignored.

However, I'm not sure how to return the sys ids to target object. Having below script handy:

(function transformRow(source, target, map, log, isUpdate) {

    // Add your code here

    var exchangeRatesSysIDs = [];

    var rate = new GlideAggregate('fx_rate');

    rate.addAggregate('COUNT');

    rate.groupBy('currency');

    rate.setLimit(10);

    rate.query();

    while (rate.next()) {

        var currency = rate.currency;

        var rateLatest = new GlideRecord('fx_rate');

        rateLatest.addQuery('currency', currency);

        rateLatest.setLimit(1);

        rateLatest.orderByDesc('sys_updated_on');

        rateLatest.query();

        if (rateLatest.next()) {

            gs.info(rateLatest.getUniqueValue() + ' ' + rateLatest.currency.getDisplayValue());

            exchangeRatesSysIDs.push(rateLatest.getUniqueValue());

        }

    }

    return exchangeRatesSysIDs;

})(source, target, map, log, action === "update");

Could someone please help on this?

Thanks

[Ankur Bawiskar]

@Ankita Kolhe 

don't have field maps and handle it completely using onBefore transform script

something like this

(function transformRow(source, target, map, log, isUpdate) {
    // Array to store the sys_ids of the most recent exchange rates
    var exchangeRatesSysIDs = [];

    // GlideAggregate to group by 'currency' and get the count
    var rate = new GlideAggregate('fx_rate');
    rate.addAggregate('COUNT');
    rate.groupBy('currency');
    rate.query();

    while (rate.next()) {
        var currency = rate.currency;

        // GlideRecord to get the most recent record for each currency
        var rateLatest = new GlideRecord('fx_rate');
        rateLatest.addQuery('currency', currency);
        rateLatest.orderByDesc('sys_updated_on');
        rateLatest.setLimit(1);
        rateLatest.query();

        if (rateLatest.next()) {
            gs.info(rateLatest.getUniqueValue() + ' ' + rateLatest.currency.getDisplayValue());
            exchangeRatesSysIDs.push(rateLatest.getUniqueValue());
        }
    }

    // Check if the current record's sys_id is in the list of recent records
    if (exchangeRatesSysIDs.indexOf(target.sys_id.toString()) === -1) {
        // If not, skip the update
        log.info('Skipping record with sys_id: ' + target.sys_id);
        return false;
    }

    // Proceed with the update for the recent records
    return true;

})(source, target, map, log, action === "update");

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@Ankita Kolhe 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.