1 Reply Latest reply on Sep 21, 2014 6:18 AM by philmodjunk

    lookup table

    PhilShaffer

      Title

      lookup table

      Post

      I am stumped 

      In each record, I have multiple fields, call them Field 1, Field 2, and field 3.

      I need to put a value in another field based on the values in these fields :

      Field 1 = 4, then Field A should contain 1.05

      Field 2 = 8, then field B should contain 1.50

      Etc

      In a spreadsheet, this is a simple lookup table, of course

      (There are 90 possible values for Field 1..... 18, each with a unique value that Field A....ZZ should contain.)

      I could, I suppose create 90 new fields, essentially the lookup table, in each record, but that is way too ugly. 

      I tried the function "lookup", but that is only for related records, 

      I tried creating a new table with 90 records, 1...90, and tried the function GetNthRecord, but got null results in the calculation for some reason I don't understand. 

       

      Is there an elegant solution for this?

       

        • 1. Re: lookup table
          philmodjunk

          Sounds like you need that related table. A "look up" table is simply a related table that stores values that can be copied over to records in the linked table. How that works and which values get copied over depends on the values in your match fields and how you define the relationship. 

          The details in how you do that depend how the values in your current record will determine what values get looked up. And you may find that you are setting up a record with multiple fields that should be divided up into separate records.