Archive

Archive for November, 2010

Make a GREAT list – checklist for Field Formating (common things to check)

November 30, 2010 1 comment

I started this post 4 months ago, but never finished it.  In doing some Builder research for another post I stumbled across a blog from another SmartList Lover, Sara Corbett.  Her post SmartList Builder Etiquette is in the same vein as this post, but, interestingly, she lists different items than I do.  Compare the two posts and comment on what you think is important for a Great list…

What is the difference between a good SmartList and a great SmartList?  In my opinion, it is often in the details – mostly the Formating details. Here is a handy checklist of common things to check:

  • Field formating — press the blue arrow in the header left of Display Name to launch the field settings:
    • Note Index fields – check the box to pull the Note text
    • All Account Index fields – check the box to pull the Account Number
    • Phone & Fax numbers – select the dropdown for the Phone number formating
    • Social Security numbers – select the dropdown for Social Security formating
    • Date fields – check the box to display 1/1/1900 as a blank  (also can change the date formating, too)
    • Fields from SQL Views – make sure you turn off the $ on quantity fields and add % for percentage fields
    • Set Decimal places – especially for fields stored in GP as integers but displayed with decimals in GP (like the Shipping Weight on the Item master is stored as 100 where it is really 1.00, HR module stores most hour totals this way.)
  • Field Display Names – Add field names from the setup for user defined fields. On most Maintenance (“Card”) tables (yes, you have to type these EVERY time you add these tables, sorry!)   Look for “User Defined”
  • Dropdown Fields – make sure you enter values – See Drop Down Lists in SmartList Builder
  • Look for fields that will appear more than one on the final list, especially fields that will have different data from the various tables.  Consider turning off the Display checkbox, or at least, changing the Display name for some fields.

Also don’t forget to add useful Go To’s!

Happy Building!
Devon

Navigation List Builder – Let’s add some Actions!

November 5, 2010 2 comments

OK so you have built your Navigation List (as per my prior post: Making a new Navigation List in Builder for GP 2010
Now let’s add some Actions:

Default Actions
Open your new Navigation List in your Navigation Pane and you will see two Action categories: Reports and Go To. 
Reports will contain the default action “Print This List”
Go To will contain the default action “Send to Excel”
(Both options are enabled when list items are selected and will print only selected items.)

Adding to the Go To action group
This group should open forms pre-loaded with the data related to the list item

  • Press the Actions Icon on the top bar for the Actions window
  • Press the Add Action button on the middle right > Select Open Form on the dropdown
  • Give it a description (name), Group of Go To, button size & priority
  • Rest of this window is identical to adding a SmartList Go To

If you want to add a Go To to the SOP Inquiry, RM Sales Inquiry, RM Cash Inquiry, PM Transaction Entry Zoom or PM Manual Payments Zoom windows, these are included in a different section (kind-of like the Advanced Go To’s on SmartList Builder):

  • Press the Actions Icon on the top bar for the Actions window
  • Press the Add Action button on the middle right > Select Execute Procedure on the dropdown
  • Give it a description (name), Group of Go To, button size & priority
  • Product is Microsoft Dynamics GP
  • Procedure – select from list of forms above
  • Bottom of window populates with the Parameters required to open this form.  Click on each line, Press Edit button on right and add Table and Field to find that value on your list.

Adding to the New action group
This group should open forms so that users can enter new transactions and/or cards

  • Press the Actions Icon on the top bar for the Actions window
  • Press the Add Action button on the middle right > Select Open Form on the dropdown
  • Give it a description (name), Group of New, button size & priority
  • Start like a SmartList GoTo with your Product, Series and Form, but typically you won’t want to add any tasks  (You won’t want to pre-load any data on the form.)

Adding to the Actions or Modify action groups
Actions group is for processes to the transaction or card on your list.  Modify is for changes to the transaction or card.  (For “Edit” under Modify use the Go To instructions above – unless that transaction is listed below)

  • Press the Actions Icon on the top bar for the Actions window
  • Press the Add Action button on the middle right > Select Defined Action on the dropdown
  • Give it a description (name), Group of Actions or Modify, button size & priority
  • Product is Microsoft Dynamics GP
  • Select the appropriate Series and Action from the list
  • Bottom of window populates with the Parameters required for this action. Click on each line, Press Edit button on right and add Table and Field to find that value on your list.

Here is an organized list of the Defined Actions available by Series (and the Group they are typically listed under in Navigation Lists):

HUGE NOTE – the Defined Actions of Print and Email are in Navigation List Builder but do not work.  Apparently, this is a known bug – they haven’t been programmed yet.  MS expects these to be fixed in GP 2010 SP2.  This is true for all document types.

  • System – Modify – Edit Note
  • Financial
    • Journal Entry
      • Actions – Back out, correct, copy, post
      • Modify – Delete
    • Batch – Actions – post, delete
    • Account – Modify – Open maintenance form
  • Inventory
    • Items – Modify – delete;  Site assign / unassign;  Currency assign / unassign
    • Item Price– Modify – adjust, copy, delete 
    • Inventory Transaction – Modify – Open transaction
  • Purchasing
    • Vendors – Modify –  apply / remove hold, delete, email settings
    • Purchase Orders
      • Modify  – delete, apply / remove process hold
      • Actions – print, email (see note above)
    • Payable Batch – delete, post
    • Payables transaction
      • Modify  – delete, post
      • Actions – print (see note above)
  • Sales
    • Customer
      • Modify – apply / remove hold, email settings, delete
      • Actions – Age account, assess finance charge ,print statement, NSF, waive FC
    • Prospect – Modify – delete
    • Salesperson – Modify – delete, inactivate, reactivate
    • Receivables Batch – delete, post
    • Receivables transaction
      • Modify  – delete, post
      • Actions – print, email (see note above)
    • SOP  Fulfillment Order – Actions – confirm pack, pick, ship
    • Sales Order Processing
      • Modify – apply & remove hold, delete, post, open entry (Edit)
      • Actions – allocate/fulfill, purchase, transfer
      • Actions (not working, see note above) – print Document, email, print Packing slip, print Picking ticket

Under the product Extender actions are avaliable for Delete, Duplicate, Open, Update value 

Let me know if you see any other issues with these actions and Happy Building!
Devon

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!

Follow

Get every new post delivered to your Inbox.