Restrict the range of the Number data type

AppSheet AppSheet Q&A
, ,
1

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.

2

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
3

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

4

Quentinlebg:

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

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
5

3X_9_c_9cf37024da8f1627427b029c1ce1b9ad03c452ef.png
3X_9_c_9cf37024da8f1627427b029c1ce1b9ad03c452ef.png764×298 26 KB

Would this help? Do you need other tables?

6

Quentinlebg:

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
7

3X_9_2_92a2d1533df4afe0e39f34725c491917b274646e.png
3X_9_2_92a2d1533df4afe0e39f34725c491917b274646e.png1542×415 33.8 KB

3X_8_7_8742f10e3b854087b934e36018a89c2bf55f70e4.png
3X_8_7_8742f10e3b854087b934e36018a89c2bf55f70e4.png1542×670 69.2 KB

1 Like
8

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
9

Rafael_ANEIC-PY:

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

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
10

Works perfectly, thank you guys!

1 Like