Subform Validation

[ricklincs]

I have a form called Pick with a subform called pick_items.

On the subform I have a lookup called unit_id, this selects records in table stock WHERE stock.aval_qty > 0 after browse I use php:

nuSetFormValue('unit_id' , nuLookUpRecord()->item_id);
nuSetFormValue('product_code' , nuLookUpRecord()->item_code);
nuSetFormValue('aval_qty', nuLookUpRecord()->aval_qty);

This Sets the values for Item_id, Item_code and qty.

I then enter a value for the subform field qty, this value needs to be = or less than the aval_qty, how on saving the record can th subform rows be validated and if this condition is not meet an error message appear? Or should this be done when the value for qty on each row changes?
I also need the validation to make sure that the unit_id is only selected once on the subform, if I use properties validation no duplicates the next time I create a new Pick and choose a unit_id that was on a prevoius pick it says Unit ID on row 1 has a duplicate (pick_items).

[kev1n]

I'd do a validation (with JS) as soon as a quantity is entered and also a validation with PHP before the form/subform is saved.

Under "Form Properties", Button "Style", add an error class. This is used to highlight cells that don't meet the criteria qty <= aval_qty

Code: Select all

.error {
  background-color: #F5CBA7
}

In the form's Custom Code, add an onQtyChanged() function that is triggered, when the qty is changed.

Code: Select all

function onQtyChanged(qty, event) {

      const avalQty = nuSubformRowObject(event.target.id, 'aval_qty'); // retrieve the aval_qty object in the same row
	
	if (Number(qty.val()) > Number(avalQty.val())) {
		qty.addClass('error'); // add an error class to the qty cell to highlight the erroneous data
                nuMessage('aval_qty must be <= qty'); // replace with a meaningful message.
	} else {
		qty.removeClass('error'); // if no error, remove the error class
	}

}

Add an onchange event for the qty object:

qty_onchange.png

Code: Select all

onQtyChanged($(this), event);

And finally, add some PHP code in the form's BS (Before Save) event:

Code: Select all

$arr = nuSubformObject('pick_items')->rows; // pick_items: subform object id

$arrUnitIds = array_column($arr, 1); // Unit Ids ( first colum) - change column number if different

// Duplicate check
if (count($arrUnitIds) != count(array_unique($arrUnitIds))) {
    nuDisplayError("unit_id is selected more than once"); // replace with a meaningful message
    return;
}

// loop through rows and verify that qty <= aval_qty

for ($row = 0;$row < count($arr) - 1;$row++) {

    // $unitId = $arr[$row][1]; // first colum
    $avalQty = (int)$arr[$row][3]; // change column number if different
    $qty = (int)$arr[$row][4]; // change column number if different

    if ($qty > $avalQty) {
        nuDisplayError("aval_qty must be <= qty"); // replace with a meaningful message
        return;
    }

}

I hope that helps. Let me know if there are any questions.

[ricklincs]

Thanks Kev1n for the quick reply, I will look at this now.