Dynamics GP Inventory – Item History Window

Let’s talk about the Dynamics GP Inventory Item History Window. This is a “hidden” window that not many folks know about. It is accessed not from a menu, but only from the GoTo on Item Maintenance or Item Inquiry. *Edit – David Musgrave pinged me after posting that he used the GP Power Tools and this window is actually on a menu but not in an expected spot – it is under Inventory > CARDS! – see note at the end for more discussion on this.

There are many posts on where the data comes from – but sadly most are not accurate.
I had a client asking questions about this window – so I figured I would post where the data really comes from. First, all of this is controlled by the “keep history” checkboxes on EACH item maintenance card (options button bottom right – Keep History: Calendar or Fiscal). No history checkboxes, no records in this window.

The top section with Current year and Last Year is SALES numbers from the SQL table IV30101 (Inventory Sales Summary History). During the inventory year end close routine, the current year numbers are moved to Last year and then cleared out.  Since this is NOT date sensitive, but based upon when the routine is run – these numbers might not be accurate.

The radio button in the middle left “Year: Calendar/Fiscal” changes the display in the bottom grid.
Further to the right you can select a specific site filter for this grid, or leave it to display the record for all sites.

Annual Historical Usage uses your GP system date to total up the prior period history records for BOTH sales and demand. Help says it “Displays the annual usage quantity for the selected item, calculated from sales and dependent demand history for the last year, starting with the month or period prior to the current month or period”  The Projected Usage is interesting, but I have never seen anyone use it – Check out the help > about this window for more information. 

The Lower grid contains summary data updated by transaction posting from SQL table IV30102 (Inventory Sales Summary Period History).

The first row displays sales totals, additionally the second row shows item usage in Inventory assemblies or Manufacturing Orders as “Dependent Quantity”  (press the down chevrons on the far right of the title bar to see the second row.)

I have had clients use this data in SmartLists and/or other analysis tools besides using it in this screen.

Hopefully this is helpful.

Best, Devon

** Edit – post discussion — I find that GP programmers put this window under Cards and not Inquiry fascinating, probably related to the “Projected usage” feature that I mentioned above. But it brings up an VERY important point that I missed in the original post — the fields are able to be changed (editable) by users.  The top annual fields are editable all the time and the lower grid fields are editable when a specific site is selected. 

Building Form GoTo’s for Receivables & Customers

Here are Steps for building the most common GoTo’s for the Receivables module:

Basic Steps (Unless listed differently below):

1. Press the Go To … button on the middle of the top bar in SmartList Builder (Or Open Drilldown Builder.)
2. Enter your Description
3. Product is Microsoft Dynamics GP
4. Series is Sales
5. Select Form listed below
6. Press + on the middle right of the blue bar to add a task
7. Add task(s) as below, press Save on Add Task window
8. Press Save on Add Go To – Open Form window

Specific Windows (Forms):

• Customer Maintenance – “Set a field value and run the field script” on field Customer Number
• Address Maintenance
  
  .
• Receivables Transactions Inquiry Zoom — this is accessible via an Advance Go Tos rather than a Form Go To – use the Receivable Transaction Inquiry (Also will take you to a Cash Receipt Inquiry Zoom for a payment.):   **See note below

 

• Customer Payment Summary Inquiry  – “Set a field value and run the field script” on field Customer Number
• Customer Yearly Summary Inquiry  – “Set a field value and run the field script” on field Customer Number  Will open by default to year of your GP date
   
• Customer Period Summary Inquiry  – “Set a field value and run the field script” on field Customer Number  Will open by default to current period (based upon GP date)
   
• Customer Inquiry – this uses a calculated field called Integer3 (Type integer, in the calculation area enter the number 3) to select the “Bill to” address (Third value in the dropdown)
  
  
• Customer Transaction Inquiry – this Go To loads the customer, but also sorts by date, unchecks to display History (at least for Active customers) and Shows Details (second line for each transaction) See example screen below:
  
  
  .
• Collections Management Main — Note that the Product and Series is different than the basic steps:
  
  .
• Transaction entry (For transactions in Work status)  **See note below
  
• Cash Receipts Entry (For transactions in Work status)  **See note below
  
  .
• Apply Sales Documents (For Open Transactions, Payment, Return or Credit Memo types only)
  
  .
• Edit Transaction (Open only)  (The dropdown for Type doesn’t have #2 Scheduled Payments.  A Calculated field is needed.)
  
• Posted Transaction (Open only)  (The dropdown for Type doesn’t have #2 Scheduled Payments.  A Calculated field is needed. See above.)
  
  .
• Receivable Batches this uses a calculated field called Integer2 (Type integer, in the calculation area enter the number 2) to select the “Cash receipts entry” (Second value in the dropdown) – Use 1 for Receivable Transaction batches
  

** Unlike the SmartLists that come from GP – we are not able to build a Go To that will open the Entry window for transactions in Work status and the Inquiry window for transactions in Open or History status.

Hopefully this helps!   Let me know if I have missed a Receivable window.  Next post, Payables!

Happy Building,
Devon

Adding Tasks for a Open Form Go To in the Builders

If you want your Go To in SmartList Builder or your Drilldown Builder to open the form AND load related data, you will need to add Tasks to your Go To.  Your Go to will need to reproduce the same steps, in the same order, that users do to pull up data on that form. 

Your Go To needs to enter data, leave fields, check boxes, select radio button options and pick options from dropdown fields just like a user would.  Each of these tasks has its own tricks:

• Fields that you enter data into:
  • Trickiest part is figuring out the name of the field that you enter the data into.  They will appear on the Field dropdown in the order that you tab thru in on the window.  But the name may not be what you expect.  You may need to try a couple fields or if you have access to Dexterity, Modifier or the Support Debugging tool you can find the exact name of the field on the window. 

  

  • Use the “Set value of a field and run the field script” task type
• Dropdown fields:
  • Each option on the list is represented with an integer (1,2,3, etc). Typically they are numbered in the order they appear in the dropdown, but you may not be seeing all the options. 
  • Note – Some fields may have a Dexterity feature selected to display the options in alphabetical order – which means that you will have to use another method to determine what integer represent each value. (There is always trial and error!)
  • Create a calculated field to hold your desired integer value to pass to the Go To.
  • Use the “Set value of a field and run the field script” task type
• Radio button options – these are the little circles that you can only pick one of the several circles:
  • Each circle is numbered in the order that you tab thru them, starting with zero.
  • You need to find the name for the Radio Group field and pass the correct number for your option to that field
  • Again create a calculated field to hold your desired integer value to pass to the Go To.
  • Use the “Set value of a field and run the field script” task type
• Selecting or un-selecting Checkboxes – These are a bit problematic. 
  • Better minds than I have been stumped by this in SmartList Builder.  One solution is to use VBA to check a Checkbox to make your GoTo’s work (Mariano)  In this discussion, macros were discarded as a solution because it will toggle the field, first time it will uncheck, but the next time it will check.   I have tried using every type of calculated field and SQL table field, etc with no luck, except:
  • I have found is that you can use a GP Yes/No field to trigger a checkbox! … now since Yes/No is, unfortunately, NOT a calculated field type, you must have a Yes/No field on your GP tables that always stores the correct value.   Try “Voided” or “Inactive” for No and, maybe, various “Keep History” fields for Yes.  It is a little odd, but try it – it might just work for you!    (And sorry – a SQL yes/no or bit field won’t work.)
  • Use the “Set value of a field and run the field script” task type
• Show/Hide Details
  • Use the “Run field script” task type
  • Field will be named something like:
    • “Scrolling Window Expand button” or  “Scrolling Window Shrink Button”  or sometimes just  “Scrolling Window Shrink”
    • “Show Summary Button” or “Show Detail Button”
    • “(L) Expand Button” or “(L) Shrink Button”
    • Typically will NOT be the field with Switch in the name – this is just an image field  (unless there is no Button field – then the programmer may have attached the field script to the image field.  A field “(L) ShrinkExpandSwitch” with scripting is part of the Dexterity samples)
• Buttons
  •  Use the “Run field script” task type
  • Button names typically have the words on the button and “Button” in the field name like “Save Button”  or “Hold Button” or “Redisplay Button”
  • Blue Arrows are buttons called “Expansion Button” with a number.  You may need to use Trial and Error to determine the correct one to trigger (unless you have access to Modifier or Dexterity to see the exact field name).

As always – remember to build your Go To step by step and to test after each step – if you make a mistake nothing will happen when you select your Go To.

Hopefully, this list will help you add Go To’s for many parts of GP.  Let me know if you discover any more Tricks and I will update this post.  In the next posts of the series I will be using these steps to build sample Go To’s for many of the common GP screens.  

Until then – Happy Building!
Devon

Tricks for building an Open Form GoTo in the Builders

GoTo’s are one of the best features of SmartList Builder, Navigation List Builder and now, via the Drilldown Builder, Excel reports and SSRS reports.  Here are some points to remember when building your Go To:

• You can only open forms that have fields that users can enter the key data into.   Sometimes this isn’t obvious.   At first look the Item Purchasing Options would seem like it wouldn’t work – the key field Item Number is greyed out.
   .
  But if you hit the Save button the item number field becomes accessible.
  
  In contrast, there are no buttons on the Manufacture’s Item Number Maintenance that will make the Item Number accessible.  You can’t build a GoTo to open this form.
  

 

NOTE – some of the most popular forms that can’t be accessed with a GoTo’s are available as Advanced GoTo’s (or Execute Procedure in Navigation List Builder.) 
SmartList Advanced GoTo’s – Payables Transaction Inquiry, Purchase Order Inquiry, Receivable Transaction Inquiry, Sales Inquiry

Navigation List – Execute Procedure – SOP Inquiry, RM Sales Inquiry, RM Cash Inquiry, PM Transaction Entry Zoom or PM Manual Payments Zoom ** In GP2010 these are also available in SmartList Builder 

• The name of the Window is the Name on the Top bar of the screen (This is the “Window Display Name”)
• To determine the Product & Series:
  • use the Support Debugging Tool (I like to got to the Security Information, select by Menu and on the Menu Explorer on the Dictonary is listed on the right) For the series you will need to use trial & error
  • GP’s  Tools | Resource Descriptions | Window
• To open the form with the related record(s), you need to reproduce the same steps, in the same order, that users do to pull up data on that form.  (So go do just that!)
• Always build your Go To in steps and test for EACH step.  First open the form without any tasks, test.   Then add your first task, test.  And so on.  If you make a mistake you will know exactly where your problem is — nothing will happen when you select your Go To.
• On SmartLists – I always test by selecting the Go To in the SmartList Go To dropdown (selecting the upper right window icon and clicking on my Go To name) rather than double-clicking on the list item to trigger the default GoTo.  This allows me to isolate problems with the Go To from problems with the Default Go To (Which, unfortunately has its own issues – to be discussed in a later post.)

For step-by-step screenshots of building a sample Go To in SmartList Builder, check out this post: http://janakirammp.blogspot.com/2009/07/smartlist-builder-building-goto.html

Sometime opening the form is easy but getting the form open with the data related to your SmartList data is a bit trickier.   That will be the subject of the next post in this series – Adding Tasks.

Until then, Happy building!
Devon

GoTo Series – Post 1 – screen shots on Building a GoTo

I have not been posting for a while, partially because I have been working (Stuck?) on a comprehensive series of posts about building Go To’s with advice and examples for most common screens… let’s just say that it has been a learning experience!

My first step was to google what was out in the blog sphere and newsgroups… and let me just say, many of you are rather frustrated by trying to build Go to’s. I have to agree… sometimes I have been perplexed too on how to make a Go To work.

One great post I found was a step-by-step look at building a pretty complex Go To – the Checkbook Register Inquiry by the Dynamics Blogger:

http://janakirammp.blogspot.com/2009/07/smartlist-builder-building-goto.html

Check it out! Next post I will dive into basic rules for adding Go To’s.

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

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!

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)

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!

Want to duplicate an GP Excel Report plus a few fields?

Want to duplicate an GP Excel Report plus a few fields?  Or wondering what these “Data connections” are in SmartList Builder? Or puzzled on where the “Data connection” went in Builders for GP 2010?  Wonder no more…

The GP Excel Reports are based off a series of SQL views added to the GP database. The “Default” ones are all the data straight from the view, others are the view with some restrictions added. (Same as the SmartList favorites they are modeled after.)

The programmers for GP Builders added these views as “Data Connection” in the table section for the Builders.  So you can use this data in anything you build. 

They have their own security (similar to the SQL tables/views) under MS Dynamics GP > Tools > SmartList Builders > Security > Data Connection Security.  

In GP2010, the “Data Connection” option disappears in the table section in Builders unless there are Data Connections enabled in security. (Same with the SQL table option!)

Next time you want to quickly add the data from an Excel Report (which you know are a lot like the SmartLists)… Try the Data Connection.   It might work for you.

Happy Building,
Devon

PS – for those of you familiar with SQL – check out the design of these views (names are the same as the name for the Data connection) you will learn all sorts of interesting things about the GP data and tables.  (All these neat SQL Functions “dbo.DYN_FUNC_XX”  like  DYN_FUNC_Account_Category_Number)

Making a new Navigation List in Builder for GP 2010

People have been searching for and asking how to use the new Navigation List builder in GP2010. So here are some basic steps for building a Navigation List:

1. You will find the Navigation List Builder under SmartList Builder (Microsoft Dynamics GP >> Tools >>SmartList Builder >> Navigation List Builder)
2. Add your tables, restrictions & calculated fields as normal.  Set the Field Display options and field names.  (Remember you can use the Copy/Duplicate option from an existing SmartList for this.  See my posts: Making a copy of a Builder SmartList and Copy a basic SmartList from GP into SmartList Builder? )
3. On the upper right, select what Navigation Pane you want this list to appear on.
4. Check Display fields – the columns on the main list (be sparing with these – but you can add more than will show on the screen – remember that users can unselect fields so they don’t show.)
5. Select Preview fields – the bottom section on the List.  Press the Preview icon on top to organize these fields on the Preview section.
   
6. If you want a Status image     for your records – press Options icon on the top to select the source field (only fields with data types of Dropdown List or Yes/No) and image for each value.
   
7. Now you want to add the items to appear on the top dark blue bar of your list:
    
   You do this via the Actions icon on the top
   • Actions are organized into the following Groups:  Modify, New, Reports, Go To, & Actions (similar to the groups in the screen-shot above)
   • Select Large or Small Icons (the image shown will be the stock image for the Group selected)
   • Select Button priority (Primary, Secondary, Force Overflow) – what to show when displaying GP with small screen sizes
   • Several types of Actions are available:
     • Defined Action – typically items found on stock Lists for Modify and special Groups.  These will be the subject of a follow-up post. Posted on 11/5/10:  Navigation List Builder – Let’s add some Actions!
     • Execute Procedure – under Product of Microsoft Dynamics GP there are 5 to open GP windows – SOP Inquiry, RM Sales Inquiry, RM Cash Inquiry, PM Transaction Entry & PM Manual Payments 
     • Items you are used to from SmartList Builder Goto’s : Open Form, Open File & Open Web Page
8. Save your List.  You will find it at the after the rest of the Lists on the selected Navigation Pane

Try building a new Navigation List today, I think you will find these Lists very useful!

Happy Building,
Devon