Hey team, apologies for the longish post but am struggling to figure out the best approach to construct a map/reduce script and think probably need a fair amount of context to make sense of of things! The goal is populating an item field with a date field sourced from open (pending receipt) Purchase Orders for the given item/locations. The items can have anywhere from 1 - 5~ of these open POs making up the On Order quantity, and the crux of things is to shift that date forward as the On Order amount from each PO is exhausted by back orders. So an item might have say POs A, B & C, each with quantity of 50, while the back ordered quantity is less than 50 the date field should be pulled from PO A, once 51 units backordered then the date from PO B, and once 101 then PO C. I can source the required data either with a single Item search with joins on Transactions, whereby Item and backordered quantity is repeated for each associated inbound PO, or two searches - Item Search to get the ID and back ordered quantity per location, Transaction search to get all POs pending receipt at location. Both searches will often exceed 4000 rows (ballpark 5k results for items, 30k results/items on POs) so map/reduce seems the sensible option. I’m struggling to work out the most efficient/best approach for what goes into what stage of the map/reduce, I’m thinking one of: 1. Pass the single Item search with joins to transactions (ie Item repeated once per open PO) to getInput, then in map assign item id as key and push each subsequent line for same item id into values object, in reduce do the logic to iterate through open POs adding On Order quantities until exceeds the backordered quantity and write date to field. (Or maybe just pass the PO date field and item id to reduce and set the field value there?). 2. Pass the item search for only Items and back ordered quantities into getinput, then in map pass Item Id as key, backordered quantity in values and then in reduce call the PO search filtered to the item id each time, then iterate through results doing the logic to work out which PO arriving date to populate on the item field. 3. Run both searches in getinput building own results array keyed by item id, do nothing in map, run the logic in reduce (I suspect this is not the best idea since somewhat lose the value of just passing search objects between stages). Hopefully that makes some vague kind of sense to some kind soul! 😄

I’d use #2 but instead of just passing back order qty, run the search on POs and map PO, Location, Quantity and Date as Array and pass on value then o Reduce you make the calculation and update item record.

Not 100% I’m following - are you saying run item search in get input, run the PO search filtered by item id in map and append PO data, then logic in reduce? Appreciate the help 🙂

that’s right…

i would vote #1, except you dont implement map and only use reduce

Hey fellas, much appreciate the input, I got this working in the end though not exactly following either recommendation 😄 I would love a bit of peer review so pasting the whole thing below, it feels like a reasonable approach and executes without taking too long, but no doubt there’s better approaches….

/**
 * @NApiVersion 2.x
 * @NScriptType MapReduceScript
 */

define(['N/runtime', 'N/search', 'N/record', 'N/log'], function (runtime, search, record, log) {
    var scriptObj = runtime.getCurrentScript();
    const LOCATIONS_IDS = scriptObj.getParameter({ name: 'custscript_location_ids' }).split(',');
    const WIP_DATE_FIELD_ID = scriptObj.getParameter({ name: 'custscript_wip_date_item_field' });  
    function getInputData() {
        log.audit({
            title: 'Script Started',
            details: 'Script Started'
        });
        var purchaseorderSearchObj = search.create({
            type: "purchaseorder",
            filters:
                [
                    ["type", "anyof", "PurchOrd"],
                    "AND",
                    ["mainline", "is", "F"],
                    "AND",
                    ["item.type", "anyof", "InvtPart"],
                    "AND",
                    ["location", "anyof", LOCATIONS_IDS],
                    "AND",
                    ["item.inventorylocation", "anyof", LOCATIONS_IDS],
                    "AND",
                    ["custbody_estimated_arrival_ocean", "isnotempty", ""],
                    "AND",
                    ["status", "anyof", "PurchOrd:B"],
                    "AND",
                    ["item.internalid", "anyof", "208199"],
                    "AND",
                    ["sum(item.locationquantityavailable)", "notgreaterthan", "0"],
                    "AND",
                    ["sum(item.locationquantityonorder)", "greaterthan", "0"]
                ],
            columns:
                [
                    search.createColumn({
                        name: "internalid",
                        join: "item",
                        summary: "GROUP",
                        sort: search.Sort.ASC,
                        label: "item_id"
                    }),
                    search.createColumn({
                        name: "locationquantitybackordered",
                        join: "item",
                        summary: "SUM",
                        label: "backordered"
                    }),
                    search.createColumn({
                        name: "internalid",
                        summary: "GROUP",
                        label: "tran_id"
                    }),
                    search.createColumn({
                        name: "custbody_estimated_arrival_ocean",
                        summary: "GROUP",
                        sort: search.Sort.ASC,
                        label: "wip_date"
                    }),
                    search.createColumn({
                        name: "quantity",
                        summary: "MAX",
                        label: "po_qty"
                    })
                ]
        });
        return purchaseorderSearchObj;
    }
    function map(context) {
        searchResult = JSON.parse(context.value);
        log.debug({
            title: 'map:Key',
            details: context.key
        });
        log.debug({
            title: 'map:Item ID',
            details: searchResult.values['GROUP(internalid.item)'].value
        });
        var yearNum = parseInt(searchResult.values['GROUP(custbody_estimated_arrival_ocean)'].split('/')[0]);
        var monthNum = parseInt(searchResult.values['GROUP(custbody_estimated_arrival_ocean)'].split('/')[1]);
        var index = yearNum + monthNum;
        context.write({
            key: searchResult.values['GROUP(internalid.item)'].value + ':' + searchResult.values['SUM(locationquantitybackordered.item)'],
            value: index + ':' + searchResult.values['GROUP(custbody_estimated_arrival_ocean)'] + ':' + searchResult.values['MAX(quantity)']
        })

    }

    function reduce(context) {

        log.debug({
            title: 'reduce:Key',
            details: context.key
        });
        log.debug({
            title: 'reduce:Values',
            details: context.values
        });
        var itemId = context.key.split(":")[0];
        var backOrderedQty = context.key.split(":")[1];
        log.debug({
            title: 'reduce:itemId',
            details: itemId
        });
        log.debug({
            title: 'reduce:backOrderedQty',
            details: backOrderedQty
        });
        var wipDate = 'soldout';
        var runningOnOrderQty = 0;
        for (var i = 0; i < context.values.length; i++) {
            var poWipDate = context.values[i].split(":")[1];
            var poQty = parseInt(context.values[i].split(":")[2]);
            log.debug({
                title: 'reduce:poQty',
                details: poQty
            });
            log.debug({
                title: 'reduce:poWipDate',
                details: poWipDate
            });
            runningOnOrderQty = runningOnOrderQty + poQty;
            if (runningOnOrderQty > backOrderedQty) {
                wipDate = poWipDate;
                break;
            }
        }
        log.debug({
            title: 'reduce:wipDate',
            details: wipDate
        });
        var poIndex = i + 1;
        log.audit({
            title: 'reduce:wipDate result',
            details: 'item: ' + itemId + ' will have wip date of ' + wipDate + ' from PO ' + poIndex + ' of ' + context.values.length + ' POs found.'
        });
        context.write({
            key: itemId,
            value: wipDate
        });

    }

    function summarize(context) {

        var itemsProcessed = 0;
        var itemsUpdated = 0;
        context.output.iterator().each(function (key, value) {
            itemsProcessed++;
            log.debug({
                title: 'summarize:Key',
                details: key
            });
            log.debug({
                title: 'summarize:Value',
                details: value
            });
            var itemObj = record.load({
                type: record.Type.INVENTORY_ITEM,
                id: key
            });
            var currentWipDate = itemObj.getText({
                fieldId: 'custitem_wip_us'
            });
            log.debug({
                title: 'summarize:currentWipDate',
                details: currentWipDate
            });
            if (value != 'soldout' && currentWipDate != value) {
                itemObj.setValue({
                    fieldId: WIP_DATE_FIELD_ID,
                    value: new Date(value)
                });
                itemObj.save({
                    enableSourcing: false,
                    ignoreMandatoryFields: true
                });
                itemsUpdated++;
            } else if (currentWipDate && value == 'soldout') {
                itemObj.setValue({
                    fieldId: WIP_DATE_FIELD_ID,
                    value: null
                });
                itemObj.save({
                    enableSourcing: false,
                    ignoreMandatoryFields: true
                });
                itemsUpdated++;
            }
            return true;
        });

        log.audit({
            title: 'Total Items Evaluated',
            details: itemsProcessed
        });
        log.audit({
            title: 'Total Items Updated',
            details: itemsUpdated
        });
        log.audit({
            title: 'Usage units consumed',
            details: context.usage
        });
        log.audit({
            title: 'Concurrency',
            details: context.concurrency
        });
        log.audit({
            title: 'Number of yields',
            details: context.yields
        });

    }

    return {
        getInputData: getInputData,
        map: map,
        reduce: reduce,
        summarize: summarize
    };
});

its more normal to use json to pass values back and forth

Thanks @battk might do some refactoring