Archive for May, 2010

Item Quantities, Unit of Measure and Base U of M

In MS Dynamics GP, items can have several units of measure and any of these units can be used in transactions.  So how do you make sure you are looking at the same units and quantities on transactions and reports?  Especially if you are trying to do a report with totals for a period? (sales, receipts, etc)

Let me introduce you to a very important field that can be seen on many transaction tables –  “Qty in Base U of M”   This field is the number of the base unit in the unit on the transaction.  An example will help:

Item 100XLG   Base unit is Each   Transaction unit is Case    Since, if you look on the Unit of Measure Schedule for this item you will discover there are 10 Each in Case,  the value in the field Qty in Base U of  M is 10.

If for the same item the Transaction unit was Each, then the value in the field Qty in Base U of  M is 1.  (There is one Each in Each)

To come up with the total quantity of the Transaction in the Base unit, you multiply the transaction quantity times the field Qty in Base U of  M. 

Another important point is that if the item changes (such as the item is now packed in a case of 12) this field was stored at the time of the transaction, so – as long as you haven’t changed the Base unit – the data will be accurate.   (Unfortunately, GP doesn’t store the Base unit on the transactions.)

Now, how do you add the actual name for this Base Unit on your report? 

  • Add the Item Master table with a link on Item number (use a Left Outer join/link if you want non-stock items to show)
  • Add the Inventory U of M Schedule Setup with a link on the U of M Schedule from the Item Master
  • Now add the field Base U Of  M

Couple more notes on what quantity fields to use for several typical tranactions:

  • Sales Order Processing (Sales Transactions Amounts Work or History)- these tables have lots of quantities and it can be tricky to find the correct one.  Here are my suggestions – these will work no matter how you have SOP setup:
    • Invoice – use Qty to Invoice
    • Return – use Quantity
    • Back Orders & Orders – depends on what you really want – but Qty Remaining may be best is you want what is left to ship (Work – Open orders)
    • Quote – generally use Quantity
  • PO Receipts – Use Purchasing Receipt Line Quantities table.  QTY Shipped minus QTY Rejected. (Our field is called U Of M QTY in Base, here) 

    (Note – this is a work & history table, but Purchasing Receipt Line History doesn’t have a Quantity field!)  Link to Purchasing Receipt History table to get just the posted transactions.

For instructions on how to add a calculated field refer to my prior post: Adding a Calculated field with Basic Math

Happy Building,


Using a SQL view in SmartList Builder

May 17, 2010 1 comment

Although this blog is dedicated to the features within Builder itself, I have to admit that about 1/3 of the Builder lists I do have at least one SQL view.  Either I want to filter a table before adding it, group by certain fields first, or some other data manipulation before I use the data in Builder.   In that spirit, here are links to other blogs and post about using SQL views:

Victoria Yudin – How to use a sql view in Smartlist Builder  Pay special attention to the “Use Company” checkbox.  This is VERY important, and generally can’t be changed after adding your view to your List.

Also note that all of her SQL views end with:   
This is critical if you want the SmartList data to be loaded by any user other than sa.

Mariano Gomez – SmartList Builder Security for SQL Server Views   Key on this is the two steps at the bottom, you will need add this Task and assign it to all your users so that they can use your new SmartList with a SQL view or table.  (In GP9 or GP8, these rights are listed under SmartList Builder section of the Tools menu – User Security)

Mariano Gomez – Changing SQL Server views for SmartList Builder SmartLists  A trick I use all the time, your new fields will appear at the end of the field list, but you can live with that!

Here are various blogs that will have sample SQL views that you can use:

Victoria Yudin –
She also has a great section on GP Tables, see the menus at the top.

Real Life Dynamics User –

Let me know if you come across any other resources and I will add them.

Happy Building,

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

May 12, 2010 1 comment

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!

If then else in Calculated Field in SmartList Builder? Try CASE

OK, so you want to use an If …then…else in a Calculated field on your SmartList…  Sorry, ‘If …then…else’ is not a SQL function.   But, have no fear, let me show you the CASE function.  Case can do everything a if statement can do and more! 

Here is the most basic way to use the CASE function (line breaks are for clarity – not needed in the Calculated field screen):

WHEN statement to evaluate* (see below)
THEN what value to use when statement true 
ELSE what value to use when statement is false

Such as:  CASE WHEN {Voided} = 1 THEN ‘Yes’ ELSE ‘No’ END 
OR: CASE WHEN {DocumentType} = ‘CreditMemo’  THEN -{DocumentAmount} ELSE {DocumentAmount} END

But you can also have additional When sections (which is ugly with If, then – you need to nest your statements!)

WHEN statement to evaluate THEN what value to return when statement true 
WHEN next statement to evaluate THEN what value to return when this statement true 
ELSE what value to return when none of the statements are true

It will only look at the second WHEN if the first WHEN is false and so on… (you could have as many as you need…)

Such as a quick calculation for Shipping rates based upon a weight field (a Shipping Weight of 30 would return 15.75):
WHEN {ShippingWeight} < 10 THEN 5.40
WHEN {ShippingWeight} < 25 THEN 7.76
WHEN {ShippingWeight} < 40 THEN 15.75
ELSE 35.25

Now if you are going to evaluate the same field in all of your statements (just have different WHEN for each value your field is equal to) there is an easier way to write that:

CASE field to look at
WHEN first value THEN value to return when field is equal to first value
WHEN second value THEN value to return when field is equal to second value
WHEN third value THEN value to return when field is third value  …
ELSE value to return when field is not any of the values above

WHEN 1 THEN ‘Quote’
WHEN 2 THEN ‘Order’
WHEN 3 THEN ‘Invoice’
WHEN 4 THEN ‘Back Order’
WHEN 5 THEN ‘Return’
WHEN 6 THEN ‘Fulfillment Order’
ELSE ‘Unknown’

A important note is that when your field is an integer, long integer or currency you just put in your values.  All string data must be included in quotes !),  whether that data is part of your statements to evaluate or the values you wish to return.  These are the quotes by your Enter key – NOT the fancy slanted quotes used by MS Word and my blog…sorry.  Both single or double quotes seem to work (but I have had some feedback from MS support that single quotes are preferable – since SQL really only uses single quotes). 

Also note that fields from your tables (in {} in my examples) can be included in all parts of the functions.

*Statement to evaluate – generally statements are:

  • a field then
  • = (equal to),  < (less than), >(greater than), <= (less than or equal to), >= (greater than or equal to)  
  • a fixed value to compare or another field

Your statement to evaluate can be several statement separated with AND  (all must be true)  or with OR (only one must be true).   (Example:  WHEN {DocumentType} = ‘CreditMemo’  AND {Voided} = 0 THEN …)  

Again, if you need additional information, do a web search and look at other examples on this blog, you will get the hang of it!  

Try it, you will learn to love the CASE statement.

Happy Building!

Adding a Calculated Field – Basic Math (Margin, Qty Available, etc)

OK,  So you need a field for your SmartList and it isn’t on the table, but you know how to calculate it from fields that are on the table(s).    Before Builder, you would export your list into Excel and add a calculated column, but now that you are using Builder, you want to add the field for your users.   Here is how:

  • Press the Calculation button on the top bar to launch the Calculated Field screen.
  • Press the + sign on the left side to add a new field (remember the – sign is to delete a field and the paper/pen icon is to change an existing field).  
  • Enter a name for your field (this doesn’t need to be unique, but should make sense to users)
  • For data type, since we are talking about fields with math you will use either:
    • Integer – just whole numbers, no decimals  (positive or negative)
    • Long Integer – value greater than the max amount on Integer  – just whole numbers, no decimals  (positive or negative)
    • Currency – any number with decimals (0 to 5).  Remember that all quantities in GP are actually Currency type fields (without a $ sign), this is the only way to get decimals.   Also percentage fields are currency fields too. (also can be positive or negative)
  • In the lower area add your calculation:
    • To view the fields to add, find the table listing on the right part of the screen.  Press the + in front of the desired table to view the field names. (NOTE – sometimes SLB prints fields multiple times on this list, this is just a display problem and doesn’t appear to cause any issues in your calculated field.)  Double-click on your desired field and it will add it into the window where your cursor is.
    • Now add your desired math function:
      • +   to add
      • –  to subtract
      • *  to multiply
      • /  to divide
      • (  )   as needed to have the math work properly
    • Use the field listing to add additional fields.  All fields you add must be either integers or currency type fields.  (If the aren’t, you will need to convert the data, see a future post on Advanced Math with the CAST function)
  • Press the Validate button to check your field  (If you are on GP9 or GP8, this button is not available, see my post Help, my SmartList loaded no data on how to check Calculated Fields in these versions.)
  • Press Save
  • Back on your main SmartList builder screen, press the + in front of Calculated Fields on the left table section to view your fields on the right side of the screen.
    • Check the box in the Default column to have the fields appear on the Default list (the * favorite)
    • Don’t forget to press the Blue Arrow by Display Name to change the display settings (remove the $, add a % sign or change number of decimals displayed), if needed.


A typical field you may want to add is to calculate Qty Available on the Item Quantities table, here is how this will look:

Here is Margin on the Sales Transaction Posted table:

Here is Margin % on the Sales Transaction Posted table:

NOTE – you can’t use a Calculated field in a calculated field… you need to use your copy (Ctrl-c) and paste (Ctrl-v) to repeat the formula text from the first field into the desired section on the second field.

One more important point – If you are including fields from a table with the Link Method of Left Outer you will need to make adjustments to your calculated field, or just adding a calculated field will effectively make your Link Method into an Equals…  but that is a subject for a future post… (sorry, I didn’t say it was always easy!)

Happy Building!