Archive

Archive for the ‘Calculated Fields’ Category

How to get data in separate columns rather than rows in SmartList Builders (Aging, Sales by Months, etc)

November 1, 2010 2 comments

This has been on my list of things to blog about – but when I saw this post in the Forums, I knew its time had come:

I have a client who is attempting to create a Sales report which shows sales by Customer.  They want the report to include a separate column for each month – based on Document Date.  I have attempted to create a calculation for each month (If/Then) but either don’t have the syntax correct or it is truly not going to work.

CASE
WHEN {{Sales Transaction History:Document Date} >1/1/2017…
 
Any ideas?????

This is something that seems to have become a very popular request in the last few years and I have always coded it in SQL. Since it requires a ‘group by’ in SQL code, I can’t imagine being able to do this in SmartList Builder. I would recommend coding this in a SQL view and basing the SmartList on that view.

I would differ :-) … This is easily accomplished in SmartList Builder:

First – Add a calculated field for each column of data 

  • In this scenario, one for each month – use the Month function to test:
    for January:
    CASE WHEN MONTH({Sales Transaction History:Document Date})=1 then {Sales Transaction History:Originating Document Amount} ELSE 0 END
    and so on…
  • For a list with aging in columns (inventory, AR or AP) 
    for example – the calculation for the “31-60 days” bucket for AR by Document Date:
    CASE WHEN
    DATEADD ( d , -30, GETDATE() ) > {RM Open File:Document Date}  AND
    DATEADD ( d , -60, GETDATE() ) <= {RM Open File:Document Date}
    THEN
    (CASE WHEN {RM Open File:RM Document Type-All} > 6
    THEN -{RM Open File:Current Trx Amount}
    ELSE
    {RM Open File:Current Trx Amount}
    END)
    ELSE 0 END
  • For other types of data – like quantities on hand – with Sites in columns, here is the calculation for the North site column:
    CASE WHEN {Item Quantity Master:Location Code}=’NORTH’ then {Item Quantity Master:QTY On Hand}
    ELSE 0 END

Next, unselect as Display any fields you don’t want to group by (or sum).  

In the scenario above we would want Year to be a group by field (or we will get all sales in January in history!).  So add a calculated field for year (Users can do a search filter on this field when running the list):

YEAR({Sales Transaction History:Document Date})

Now use the Options icon on the top to make this into a Summary SmartList – the way to “group by” in Builders.   Select all of your column calculated fields as Summary type “Sum” , your other fields as “Group by” and, in the words of my daughter, “There you go!”  

Learn more about summary SmartLists in my post: Summary SmartLists in Builder (Sales by Item for a date range, etc)

Happy Building!
Devon

PS – My apologies and thanks to the ladies on the original forum post!

Date & Time fields in GP Builders

July 26, 2010 3 comments

Typically – most fields in GP are Date fields.   GP doesn’t have many fields where time is stored.  

Side note – Many of you know that GP added a time stamp field into many GP tables for version GP10 (field – DEX_ROW_TS in SQL).  But unfortunately – this field in NOT available in SmartList builders (hint, hint, GP programmers – that might be nice….) Unless you connect to the GP table like it is another SQL table…

A little background is necessary…  Actually in SQL all GP date fields are actually date/time fields.  It is just that the time is always 12:00 am.   This assumption is built into SmartList and the other builders.  In the field formatting you can only select formats with dates, not with times. 

This can cause issues when you are working with data in the Builders from other SQL data sources.  SmartList builder assumes that the field is just a date field and only gives you those formats.  That seems OK, until your users try to do searches on these fields like they are used to in GP.  Such as equal to 07/26/2010 … if there is actually a time in this field then the users will not get what they expect (chances are no records will be returned – because the search is actually looking for 07/26/2010 12:00 am !)

To prevent this and make the date/time field work like date field in GP, make a new calculated field with a Field Type of Date and the following calculation:
CAST(FLOOR( CAST( {table.field} AS FLOAT ) ) AS DATETIME)

If you want the users to see the time from this orginal field, you will need to add another calculated field use as Field Type of String and the following calculation:
CONVERT(CHAR(8), {table.field}  , 108)

Now, if you SQL people have better functions, please let me know (I don’t begin to claim much of a SQL background) – but these have worked just fine for me.

Hopefully, this trick will come in handy the next time you are using the Builders to view data from other SQL sources.  (Which is a great feature of the Builders!)  If you have other SQL data that your people need and use, why aren’t you leveraging the Builders to see that data in GP?  But that is whole other subject entirely.

Happy Building,
Devon

Transaction Amounts with correct signs (What you want Credit memos to be negative?)

Isn’t it irritating that for most Transactions (all in Payables Management & Receivables Management) are a “positive” number even Credit Memos and Payments?   Don’t you wish you could export GP SmartList to Excel, run the Subtotal feature and get a REAL total?  

Here is how to “fix” this in the Builders (SmartList, Excel & Navigational Lists):

  • Determine what field you can use to decide if the value should be negative or positive – your “type field” (Document Type for Payables; RM Document Type-ALL for Receivables; SOP Type for Sales Order Processing) 
  • Verify the field data type   (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 the Data Type is not Dropdown list (or Yes/No)  you will need to do a calculated field – scroll down… for Option #2
    • If the Date Type is Dropdown list (or Yes/No), then this field will be available in the next step.  But only if you have set the dropdown field options. See my post Drop Down Lists in SmartList Builder for details
  • Select the amount field that you want to correct the sign by clicking on the Display Name of that 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.)
  • Press the Negative values tab
  • Select your “type field” from above
  • Check Mark the boxes for the type values that should be negative

Note – these values are only used for displaying in your List.  If you want to do a Summary list – these sign corrections will not be used – you will need to do a Calculated Field.

Option #2 – A Calculated Field to correct the sign of a field:

Here we are going to use our handy-dandy Case Statement:  (For more info see Try CASE )

CASE {type field}
WHEN 4 THEN – {amount field}
WHEN 5 THEN – {amount field}
ELSE {amount field}
END

or

CASE
WHEN {type field} >=7 THEN – {amount field}
ELSE {amount field}
END

Uncheck the Default & Display boxes for the original amount field and check the Default & Display boxes for your new Calculated field so that users see this new field instead.

Works like a charm.

Happy building,
Devon

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

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

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

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

CASE
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
END

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):
CASE
WHEN {ShippingWeight} < 10 THEN 5.40
WHEN {ShippingWeight} < 25 THEN 7.76
WHEN {ShippingWeight} < 40 THEN 15.75
ELSE 35.25
END

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
END

Example:
CASE {SOPTYPE}
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’
END

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

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.

Example:

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!

Follow

Get every new post delivered to your Inbox.

Join 25 other followers