AnsweredAssumed Answered

Using a range of numbers between two fields to and

Question asked by j_swart on Dec 15, 2017
Latest reply on Dec 15, 2017 by PSI


New FM16 user here and I hope someone can help with an issue I am having.


I have two tables. The first table, MasterList, has every single number within the field FactoryNumber that I need to categorize and fill into the Category field that is also within the MasterList table.


The second table, CategoryList, contains Upper Limit, Lower Limit, and Category fields.


I need all the numbers in the FactoryNumber field, see what the range its in (between upper limit and lower limit), and then assign labels from Category field within CategoryList table so that all the labels now appear in the MasterList table under the Category field.


For example:

I want to automatically assign 165.621 from the FactoryNumber field


This information is in the CategoryList table

Lower Limit                    Upper Limit               Category

     165.555                       165.789                      Y231

     165.790                       165.800                      Y232

     165.801                       165.900                      Y233


It fits between the 165.555 and 165.789 limits so the field would populate with Y231.

In the end, I would like it to look like this in MasterList

FactoryNumber                 Category

165.621                              Y231

165.625                              Y231

165.794                              Y232

165.801                              Y233


I can do this in Excel with VLOOKUP but I am having trouble here in FM16.

Thank you for any help, I greatly appreciate it.