Quantity vs Current Stock

AppSheet AppSheet Q&A
1

Hello,

I’m trying to limit the sales quantity to less than the current stock. But I keep on getting an error that numbers cannot be compared to lists when I use a valid_if statement. Can anyone help? I have tried to convert the list to a number but have had no success.

Current Stock:

SUM(
SELECT(
Purchases[Quantity],
[Product Barcode] = [_THISROW].[Product Barcode]
)) -
SUM(
SELECT(
Sales[Quantity],
[Product Barcode] = [_THISROW].[Product Barcode]
)) +
[Initial Stock]

Valid_if for sales:[Quantity]<=Product[Current Stock]

Cannot compare Number with List in ([Quantity] <= Product[Current Stock])

2

This part of your expression returns a list. If it’s already a single-row list, then wrap it in INDEX or ANY to instead return the single value. You may also first need to wrap it in a SELECT or other function to return the single row you need.

2 Likes
3

So I tried ANY, but it returns a list of numbers that have sold in the past rather than the current stock value? A value over the current stock can still be valid for some reason.

4

If [Quantity] is in a different table than [Current Stock], then your Valid if expression probably needs to be something like:

[Quantity] <= {expression modeled on [How do I get a column value from the last row of this thing only?](https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-FILTER-LOOKUP-MAXROW-MINROW-REF-ROWS-and-SELECT/m-p/366402/highlight/true#M4507)}

If those columns are in the same table, then maybe all you need is:

[Quantity] <= [Current Stock]
1 Like