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!)