How to lock a record while updating its value to have consistency for the value on NetSuite?

Question

There is a Sales Order having many Purchase Orders, and the Sales Order has an item which sums up all the shipping cost in each PO.

I develop 2 functions as follows to calculate all the shipping cost in each PO:

function calcuTotalShippingCostByAllPOs(.....) {
        ......
    // var created_pos is an array which records all the unique PO id in SO
    for (i = 0; i < created_pos.length; i++) {
        var po_amountValue = po_record.getSublistValue({
             sublistId: 'item',
             fieldId: 'amount',
             line: ship_line
        });
        total_shipping += parseFloat(po_amountValue);
    }
    so_record.setSublistValue({
        sublistId: "item",
        fieldId: "amount",
        line: so_ship_line,
        // ignoreFieldChange: false,
        value: total_shipping
    });
}

function calcuTotalShippingCostByCurrentPO(.....) {
        ......
    switch (context.type) {
        case context.UserEventType.CREATE:
            var difference = new_po_ship_cost;
            break;
        case context.UserEventType.DELETE:
            var difference = -old_po_ship_cost;
            break;
        default:
            var difference = new_po_ship_cost - old_po_ship_cost;
            break;
        }

     // Get the old GST-Shipping cost on SO
         var old_so_ship_cost = so_record.getSublistValue({
         sublistId: 'item',
         fieldId: 'custcol_po_actual_cost',
         line: so_ship_line
     });

     // if PO UNIT COST on ship line is a finite number
     if (isNumber(old_so_ship_cost)) {
         var new_so_ship_cost = old_so_ship_cost + difference;
     } else {
         var new_so_ship_cost = difference;
     }

     so_record.setSublistValue({
         sublistId: "item",
         fieldId: "amount",
         line: so_ship_line,
         // ignoreFieldChange: false,
         value: total_shipping
     });
}

But if someone updates the shipping cost in a specific PO during the calculation, then the total shipping cost may be not correct.

Is it possible to lock the POs while executing the function to sum up all shipping cost?

For example, if there are 100000 POs in a SO, during the calculation of total shipping cost, if someone modify one of the shipping cost in a PO, then the total cost may be not be correct if the cost has been added in the total shipping cost.

How to prevent data inconsistency from happening on NetSuite.

So you want to update CostA while CostB and CostC are locked ? what about CostA, is it locked too ? — B. Assem
If one user is opening a record and updating the CostA, before the user submit the record, the hereafter users who open the record can not update the CostA. — skyclouder
Sorry, I can't understand what you want to do, please update your question to be more clear and add the full code (your code gets costA and then, without using that costA, it saves a newPORecord). — B. Assem
Perfect, now it is clear : I just have some questions : what is 5 minutes after the calculation, one of the POs changes, there will be inconsistency in this case too ? Or do you have some mechanism that will detect the modification of a PO and will trigger the recalculation of the SO ? — B. Assem

B. Assem B. Assem · Accepted Answer · 2019-02-28T20:39:26

So if you want to lock the POs records you can do that using the Lock Record workflow action : if you apply that action on the BeforeLoad of a record, it will prevent its edition.

So one way to do this : you add a new checkbox Custom Body field (example: locked for calculation) : in your script, before starting the calculation, you update all the POs so that custom field is TRUE. After calculation, you reset it to FALSE.

You create a Transaction workflow with one non finishing state, and you add the Lock action with a condition : locked for calculation = TRUE.

Theoretically, this will solve your problem BUT be careful of the used Governance in your script: because you will be Updating Twice a number of POs : if it is not more than 10, it must be ok, but keep that in mind.

Good luck :)

How to lock a record while updating its value to have consistency for the value on NetSuite?

1 Answers