Calculated Remaining Inventory Quantity

[IVAN88]

Hi,

I have a table that record In-Out inventory transaction by batch number.

My case :

On Out transaction I want, user can only select product that still have a remaining quantity, I want to hide a zero quantity product.

the remaining quantity will be group by batch number

How can I do that ?

Rgds,

[Barry]

Hello Ivan,

In the "Configure Filtering Fields" of the Report you can use comparisons with values. You could add the quantity there with the "Greater Than" type.

Something like this:

[ParkLoey]

Hi! Just wanted to share a reference where we can check what comparison types in report pages Caspio supports:

https://howto.caspio.com/datapages/reports/search-and-filter-comparison-types/#:~:text=gmail.com”)-,Comparison Types,-The following is

[IVAN88]

Hi Barry and ParkLoey,

What I meant, When user input Out Transaction,  insert product Name and Quantity, If they input quantity larger than available quantity, they can't submit the record.

Rgds,

[cheonsa]

Hi @IVAN88,

You can hide the button to prevent the users from submitting the record if the value they enter is greater than the available quantity. 

First, you need a formula to compare the available quantity and the requested quantity. You can use a Virtual Field > Calculated Value.

For example:

CASE WHEN [@field:Quantity] > (SELECT (Product_Quantity) FROM TABLE WHERE Product_Name = target.[@Product_Name]) 
THEN 'Available' 
ELSE 'Not Available' 
END

Then, add this script in the Footer of the DataPage:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {
document.querySelector("input[name='cbParamVirtual1']").addEventListener('change', function() {
if (document.querySelector("input[name='cbParamVirtual1']").value == 'Not Available') {
document.getElementsByClassName('cbSubmitButtonContainer ')[0].style.display = 'none';
} else {
document.getElementsByClassName('cbSubmitButtonContainer ')[0].style.display = 'inline';
}
});
});
</script>

 

NOTE: The cbParamVirtual1 refers to the Virtual Field 1.

 

Hope this helps.