Work with Option Values in the Jet Data Manager

Overview

This document walks you through the process of using the Jet Data Manager to replace option values with their descriptive text values.

What is an Option Value?

An option value is a number that corresponds to a text description. For example 1 corresponds to G/L Account, 2 Item, etc.

The option value doesn't mean much on its own and our goal is to bring in the descriptive text so that we can properly identify our data.

When working with your data you may notice option values. For example in the Sales Invoice table we have a field called Type that corresponds to a set of option values.

Process

1. First we must find the table that contains the text descriptions. This is most often the name of the table followed by the word Type. For example the Sales Invoice Line table has an associated table called Sales Invoice Line.Type

   

2. Select the Option Value field and the Option Text field.

   

3. In the Staging database move the table containing your option values above the table that contains only the Type field. In this example Sales Invoice Line contains our Type field and Sales Invoice Line.Type contains our descriptive text.

   

   If tables are not placed in the proper order you will receive Null values and not the desired text descriptions

   The table containing the descriptions must be placed above the table containing our Type field.

   

4. Expand the table that contains your Option Text Field and drag the Option Text field down to the table below. This will create a red look-up table with an identical name.

   

5. Expand the newly created look-up field and assign a more descriptive name. In this example we chose Item Description.

   

6. Right click Joins and click Add Join. The Add Join window is displayed.

   

7. In the Lookup field drop down, select the Option Value from the Sales Invoice Line.Type table. In the Field drop down select Type from the Sales Invoice Line table.

   

   We have now successfully joined our Sales Invoice Line table with our Sales Invoice Line.Type table.

8. Right click the table and select Deploy and Execute. Click Start.

   

9. Now you can right click the table select Preview Table and verify that the field looks proper.

   

   Your newly created look-up field should now be populated with the descriptive text values.