Table Range Up and Down

Navigation:  Formulas > How to create formulas using data from tables >

Table Range Up and Down

Previous pageReturn to chapter overviewNext page

Each 'Key' column specified in the Table Definition (with a datatype of Int, Long, Decimal or DateTime) now has a check box to allow the user to indicate if the column is ranged or not.  (See Creating a table definition).

What this means is that when you perform a table range lookup, instead of only finding the exact match for the ranged key, it will attempt to find the next matching (or exact) key value in the specified direction (RangeUp/RangeDown).

The RangeUp or RangeDown mode is defined within the formula.(See Create Multiple Table Range Formula.
 
For example, let's say you have the following Keys and Data:

Table Contents:        

RangeKey

Data

5

a

10

b

15

c

20

d

Instead of finding nothing in the table, a table ranged lookup with a key of 3 (with RangeUp as the mode),  would "look upwards" and match against 5, retrieving the result of "a".

If you used the RangeDown mode instead, the lookup would return nothing as there is no value equal or lower to 3.

So going with further examples:

•[RangeUp] Key = 5, Result = "a"

•[RangeDown] Key = 5, Result = "a"

•[RangeDown] Key = 14, Result = "b"

•[RangeUp] Key = 21, No result

When determining whether to range up or down, you just need to keep in mind that

oUP means you are looking for a key EQUAL to or GREATER than the value you specify,

oDOWN means you are looking for a key EQUAL to or LESS than the value you specify.

When using the dateTime as your range key, UP matches dateTimes that are EQUAL to or LATER, while DOWN matches dateTimes that are EQUAL to or EARLIER.