Restrict the range of the Number data type

So I have been trying to change the range of the number data type. I changed it manually at first, but as you can see with the table below, the hardwares don’t have the same limit.

Table Name: Hardware Discount
Hardware Minimum Maximum
Hardware#1 10,000 100,000
Hardware#2 10,000 100,000
Hardware#3 2,500 10,000

I tried using this:
IF([_THISROW].[Hardware] = “Hardware#1”,
ANY(SELECT(Hardware Discount[Minimum], [Hardware] = [_THISROW].[Hardware])),
ANY(SELECT(Hardware Discount[Maximum], [Hardware] = [_THISROW].[Hardware])))

But I keep getting “The expression is valid but its result type ‘Number’ is not one of the expected types: Yes/No, List”
I feel like even if I was not getting that error message, it would still not work.

Hello @Quentinlebg, i’m guessing you have a table where you want to input your hardware discounts, using a reference to a hardware discount table, if that’s the case please try this expression in your valid_if() field in your discount column.

AND([_THIS]>=[Hardware_REF].[Minimum],[_THIS]<=[Hardware_REF].[Maximum])

1 Like

I tried using your expression, but it didn’t work. It said it couldn’t find the column

I have a bunch of tables. Quotes, Hardware, Pricing, Hardware Discount.

This expression would be used for Quotes. The pricing depends on the hardware discount. The hardware discount depends on the hardware.

My goal is to make a form that is pretty much like:
Hardware
Quantity
Pricing

Well, it’s not going to be an exact fit, i have no idea how your column structure looks like, you have to change the [Hardware_REF] to the actual column that works as a reference to your discounts table, and that is IF that reference exists hehe

If you’re looking for an exact answer you’re gonna have to share more information about your data structure

3 Likes

Would this help? Do you need other tables?

I could use a screenshot of your columns as seen from appsheet’s app editor of both your Hardware Discount and Quotes tables

1 Like


1 Like

I have no idea on which column of your Quotes table you’re gonna type in your desired discount, but you can use this expression on its valid_if() expression:

AND([_THIS]>=LOOKUP([Hardware],"Hardware Discount Volume","Hardware","Lower"),[_THIS]<=LOOKUP([Hardware],"Hardware Discount Volume","Hardware","Higher"))

3 Likes

Better:

ISNOTBLANK(
  FILTER(
    "Hardware Discount Volume",
    AND(
      ([Hardware] = [_THISROW].[Hardware]),
      ([_THIS] >= [Lower]),
      ([_THIS] <= [Higher])
    )
  )
)

Your expression uses LOOKUP() twice to find the same row, which is two scans of the table. Mine scans the table only once.

4 Likes

Works perfectly, thank you guys!

1 Like