Restrict the range of the Number data type

Quentinlebg
Participant II

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.

Solved Solved
0 9 262
1 ACCEPTED SOLUTION

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.

View solution in original post

9 REPLIES 9

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])

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

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


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"))

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.

Works perfectly, thank you guys!

Top Labels in this Space