Drop Down Lists in SmartList Builder (Document Type on Transactions, etc)

Have you had users ask you to give them a Dropdown list of the set values in a certain SmartList field for easier searching? Like this:

Have you loaded your list and got values of 1,2,3 rather than values of Quote, Order, etc?

Or worse, your users say that they are unable to search on a particular field?

This is how to resolve either or both these situations.

Adding a Drop Down List in Builder:

• First, an important note, Drop Down List is a Data Type in GP, Dexterity (what GP is written in) and SmartList Builder.  It actually contains integer data.  The programmers for GP didn’t always select the Data type of Drop Down List where we think they should have. (RM Open the field for Documents Type “RM Document Type-All” is a good example.) But don’t worry, we can still make it work.
• Find out the Data Type for your field
  • Click on the desired table on the left table listing
  • Press the Show Details (down chevrons) on the far left of the field header line
  • Now you should be seeing the Data Type on the second row under each field name
    
• If your field has a Data Type of Drop Down List:
  • Click on the Display Name to select your field
  • Press the Blue Arrow between Display Name and Display on the Field header line to launch the Set Field Options window. (This window changes its appearance based upon the data type for the field.)
    
  • If you are in GP10 or higher, many tables and fields will come in with each integer value defined (as shown above).
  • If your screen comes in with only 0 listed, build your list as per this sample.Hint #1 – if possible, use an existing SmartLists with the same data to determine GP Descriptions: look up example records on your list for the integer value then on the “stock” SmartList. If you are missing certain types, enter sample data in your test company to verify all possible values.
    Hint #2 – Remember that sometimes 0 is No and 1 is Yes
    Hint #3 –  for table RM Open File, field RM Document Type-All: 1 = ‘Sales/Invoice’
    2=’Schedule Payments’, 3 = ‘Debit Memo’ ; 4 = ‘Finance Charges’; 5 = ‘Service/Repairs’
    6 =’Warranty’;  7 = ‘Credit Memo’; 8 = ‘Returns’; 9 = ‘Payments’
  • Values that you don’t list will display as their integer values, but will not be able to be selected in the Drop down on a Search window.
• If your field has a Data Type of Integer:
  • Press the Calculations Button
  • On the Calculated Fields window, press the + button to add a new calculated field
  • Give your new field a Name and Data Type of Drop Down List
  • Insert the field in the lower window (Press the + in front of the table name on the right, find the field and double-click to insert into the text window)
    
  • Now, follow the steps above on your new Calculated Field (listed under the “table” of Calculated Fields) to define each integer values.
• If your field has a Data Type of Yes/No you don’t need to do anything.  This is appear as a Drop down on the Search or Restrictions.
• If your field has a Data Type of String and your users want a Drop Down field for searching, you will need to make a Calculated Field to convert each string value into an integer value, then use the Set Field Options window to set each integer value back into string values (Silly, but it will work.) You can use a Case statement for this as per the example below:

Note – make sure you map 0 to ‘Unknown’ or something like that. 

That’s it!

Now, another hint that may be useful:  Get into the habit of checking that you have values for all fields with a Data Type of Drop Down List.   There is an easy way to do this:

• Press the word Tables above the table section on the left side of the screen.  This will open a Dropdown menu with Table or Field Types as options. 
• Select Field Types. 
• In the listing of Field (Data) Types now in the left window section, Click on Drop Down List
• Press your Show details (down chevrons on far right of line headers)
• On the second line, it will state “Warning – No List items specified”  for fields that you may want to add values.

Happy Building!
Devon