Dynamics GP Inventory Transaction History Quantities

It took some doing but I think I've figured out how to properly account for inventory transaction history in Dynamics GP.

The starting point for this was my client's request for a point-in-time inventory report.  The way they use inventory does not allow for the few available GP reports to serve any useful purpose.  So I had to hit the drawing board to come up with a reasonable facsimile.

I started with the Historical Stock Status report in GP as the model for this.  It works fairly well in regards to quantities on-hand as of a prior date.  The detail version of this report actually shows how to roll back the quantity from the current balance to the prior date by backing out transactions since that date.  This was extremely helpful to me to adjust my logic to do - essentially - the same thing.

To establish the point-in-time quantity balance start with the current quantities in the IV00102 Item Quantity Master.  Then back out transactions in the IV30300 Inventory Transaction Amounts History table that happened since the target report date.  When backing out the IV30300 transactions keep in mind the following:

• Returns (DOCTYPE=5) are excluded.  At this customer returns do not go directly to on-hand quantity.
• Transfers (DOCTYPE=3) between different locations are reduced at the FROM location and increased in the TO location.  These transfers are in a single transaction that has both the from (TRXLOCTN) and to (TRNSTLOC) locations.
• Transfers at the same location are considered if they affect the on-hand quantities.  The from and to location will be the same on these and you must examine the quantity type in either the from (TRFQTYTY) or to (TRTQTYTY) location.  Quantity type 1 is "On-hand".

This should give a quantity on-hand as of any desired report date.

Account Hierarchy table

To add to my previous posts regarding the Tree View (TV) control to maintain an account hierarchy I will add the following to explain the underlying table structure supporting it. All of my table updates are done via stored procedure - Dex only reads from this table for display purposes.

Here is the basic layout of my table:

• ID integer
• Level integer
• Parent integer
• Order integer
• Description string 30
• Segment ID string 67
• IsCredit Boolean

Since Dex does not support a SQL Identity field I maintain the ID column in code. To add a new record I simply add one to the highest record on file. This is the primary key field to the table. I never expect two people to add to this table at once so concurrency is not a concern. Note that the ID field is stored in the data element of the TV node.

Level starts at one for the root level of the hierarchy and increments by one for all the levels below. I suppose this could be a tinyint field but I didn't bother. When a child is added to the hierarchy it is added with a level one above the parent level. A sibling uses the same level.

Parent is the ID of the parent. This provides the ability to walk up and down the hierarchy, which is required to display and maintain the tree. When a child is added the ID of the parent is used for this. When a sibling is added its parent level is used.

Order is incremented for the same level. I am not using this right now but could, at a future time, add this to the context menu to allow users to change the order of the items on any level.

Description is the label for the node. For a hierarchy level it is the level name itself, i.e. Assets or Current Assets. For account nodes it's simply the same as the Segment ID.

Segment ID is one or more digits of the account number. For example if all the cash accounts start with 10 then the Segment ID for cash is 10. My code checks this column and if there is a value in this field it knows this is an account level and thus it gets the State Image and there can be no children allowed.

Finally IsCredit is true when the account balance is normally a credit amount. This allows flipping the sign bringing Accounts Payable balances (normally credit) into the warehouse as positive amounts. We use this instead of the account flag in GP for more control.

The next entry regarding TV will cover the code that supports the context menu that does the heavy lifting for this.

Using the Tree View control

Using the Tree View control

 

 

You can add a Tree View control to your window from the Dex Toolbox.  It’s the control on the bottom 2^nd from the right.

The control has a number of properties that are self-evident.  I will cover the ones I used in my instance.

• Object - Field:  Of course give your control a unique name, but open the Field Definition to access the Static Values for the images that are displayed in your tree.  There are two kinds of images:
  • Item Images – These are the ones that sit beside your text on the tree.  I use the
    • TreeView_FolderCollapsed – this is of course a closed folder image.
    • TreeView_FolderExpanded – this is the open or expanded folder image.
    • ToolBarCards_PBD_Up_Small – this represents the account or lowest level of my tree.
  • State Images – These are optional images that I used to designate DR or CR for my typical account balance.  I created my images with Paint using a simple 20x20 pixel image with the letters DR or CR.

Scripts: I used the following:

• Collapse:  Dex documentation recommends removing data when the tree is collapsed.  I do not.  I don’t have THAT much data in my tree.  When my tree is collapsed the data (underneath) stays put.  Dexterity handles this well and I haven’t noticed any performance issues.  What I do in this script is change the Item Image to the closed folder image (has to be done for both the Selected and Normal image.)
• Expand:  On this script I do have a script and I only add data to the tree as the user requests it.  This helps maintain performance and seems to work so far.  I also display the open folder image.
• StateClick:  As I mentioned I use the State Image to signify DR or CR of the account balance.  These images combined with the StateClick script allows the user to simply toggle the DR/CR setting by clicking on it.  I use the TreeView_SetNodeStateImage function after looking up the setting of the node they click on then change it to the other and update the underlying data.  So if the user clicks the DR image I lookup the value, change it to the other value, update the State Image then save the new value to the database.
• ContextMenu:  This is what really makes this tree shine!  I have not previously used this feature in Dexterity but it works well and I will use it more.  I will write a separate article on this but for now this allows the user to add or remove nodes as needed.

Note that to support the ContextMenu script I’ve added Commands to the Form that run when the user selects a context menu action.

This form is so simple there are no other controls on the form at this point.  The entire content of the form is the Tree View control.

There is one other action take and that is on the Window Pre script I fill the root level of the hierarchy.  I look up the root level from my table (identified there as level 1) and use the TreeView_AddNode function to populate the initial state of the hierarchy.  One side-effect of this is that if there is no data in the table the tree is blank.  There are a few actions that can be implemented to allow this type of thing, but my implementation does not involve this event so it truly represents an exception that should not happen.

Refer to the Dexterity Function Library Reference, available from the Help screen, for further information regarding the required TreeView functions you will need to implement the control.  The Programmer’s Guide Volume 2 includes information regarding implementing the control.

Here is my Hierarchy TreeView in action:

Account Hierarchy editor

In order to provide my client with the ability to edit a hierarchy of GL accounts I used the Tree View control in Dexterity in a very simple fashion.

I created a Hierarchy form and in that form I created a Hierarchy window.  I attached the Hierarchy table to that.  I also created the following 5 commands in that form to provide the ability to manipulate the hierarchy as follows.

1. AddAccountChild - add an account as a child to the currently selected node.
2. AddAccountSibling - add an account as a sibling to the currently selected node.
3. AddHierarchyChild - add a hierarchy node as a child to the current node.
4. AddHierarchySibling - add a hierarchy node as a sibling to the current node.
5. RemoveNode - remove the current node and any children of that node.

The hierarchy window contains one control - a Tree View control.  Nothing more is required.

The Tree View will be populated with the root-level hierarchy, which must be present in the table.  From a strict Accounting standpoint a root-level hierarchy can correspond to the following:

• Assets
• Liabilities
• Equity
• Revenue
• Expenses
• Statistics

Note the last entry.  This will allow statistical accounts to pass through to the data warehouse but keep them separate from posting accounts.

Note these levels are just starting points to the rest of the hierarchy.  In other words they are the top hierarchy level.  Or another way to say it they are the highest level summary of accounts.

In the data warehouse any BI tool should allow the totals of the root level hierarchy to equate to the following Accounting 101 proof:

Assets - Liabilities - Equity = Revenue - Expenses.

Starting from the root level further sub-levels can be added down to the account level.  Levels that are not accounts I call Hierarchy nodes.  Account nodes are just that - they represent one or more accounts directly.

The basic idea is you provide structure from the root down to the account level.  This allows summary analysis and reporting at levels above the account, always keeping the Accounting 101 proof in view.

Another example of a level of hierarchy could appear as follows:

• Assets
• Current Assets
• Cash and Equivalents
• Cash (10)

The lowest level of the hierarchy is the account, as indicated by the account number prefix.  No levels may be defined below the account level.

Accounts are defined with the starting digits of the number range included.  In the example above all the accounts that start with 10 are considered cash accounts.  This would include, for example, the following accounts:

• 1001 Checking
• 1002 Savings
• 1005 Money Market

Inside the Tree View control is a Context Menu that includes the options corresponding to the Commands defined in the form.  These allow alteration of the hierarchy to suit the needs of the client's chart of accounts.

Further posts will define each of the form elements in more detail.

Dexterity Tree View control

Just wrapping up some development of a Dexterity enhancement to GP that uses the TreeView control.  I would like to share some of my findings in this and future posts.

We create data warehouses for clients for Business Intelligence (BI) analysis of financial data.  One aspect of this is the need to summarize data for presentation.  Many times this can be done using existing data attributes, i.e. sales by customer.  But there are other levels of summary that are not available in the data contained in GP.

A simple example of this is a summary of financial data by GL account.  The typical GL contains hundreds, sometimes thousands of accounts.  While GP does contain a table that summarizes that data by period, many times clients want to see that data summarized by the components of the account itself.  Even more common is a summary by what we call the "Natural" or main account number.

An example of that is as follows.

Customer wants to analyze expense data according to the main account in the example below.  The numbers in parenthesis are the starting digits of the accounts in range for the classification shown.

Expenses:

• Selling Expenses
• Salaries & Commissions (61)
• Travel (624)
• Meals and Entertainment (625)
• General and Administrative
• Salaries (71)
• Communications (721)

Let's say it's a perfect world for them and their accounts are arranged in the proper sequence to support this structure.  And let's call this a hierarchy, which is the term we use.

It would be nice if we could set this up once and not have to worry about it again.  But charts of accounts are fluid things and can change, sometimes frequently.

Regrettably there really is no way in GP to represent this type of summary.  This is often times an exercise that is required for financial presentation in FRx or Management Reporter, and those tools have a Tree component in addition to versatile Row format options that allow for this type of thing.

But moving the financial data to a warehouse then out to an analysis tool can present difficulties.  Enter the Tree View control in Dexterity.

I will show how that tool can be utilized to allow the client to maintain a hierarchy for any data in GP so that the above is not only possible, but it's simple to operate.

If you want some advance information about the Tree View control check out the Dexterity Programmer's Guide Vol. 2 as well as the Function Reference.  Both cover substantial detail regarding this useful, but seemingly underused control.

Dynamics GP tables are what?

Having come from the GP DOS world back in the day, the table structure in Dynamics GP makes sense to me.  Others may not be so lucky in understanding these things.  Furthermore GP doesn't exactly give you a one-stop-shop for this.

There are lots of internet resources you can turn to for GP table descriptions.  The most useful is Victoria Yudin's blog.  It's not comprehensive but it is invaluable as she defines many of the key fields that you would search hard to find the defined value for (i.e. What are all the SOPStatus values?)  Joseph Spaur also hosts a very nice, organized list that includes field type information as well as references to other tables where a particular column is used.

But most people don't realize GP comes with its own internal table descriptions as well as an SDK that has even MORE descriptive table information.  These are the sources I use most.

To get to the GP table list inside Dynamics from the menu system drop the Microsoft Dynamics GP menu then Tools > Resource Descriptions > Tables.  From the Table Descriptions window click the ellipse beside the Table: field to open the Table Names window.


In the Table Names window I change the View By: to "by Table Physical Name" and it presents a nicely sorted list of tables in the "Series" for that "Product".  Note that it is helpful to understand what a Product and Series are before starting out with this.


My trick in using the Table Names window is to stay on it and not make a selection unless required.  Use this window to scrounge all the tables you seek then select them if you must.  You will be returned to the Table Descriptions window where all the columns in that table are defined.  This can be a helpful window at times as some column values are defined there.  But you will have to start over if you want to go back to Table Names to find a different table.


The SDK is a separate install from the GP DVD.  You will find it in the Tools\SDK\Dynamics GP folder.  Once installed and opened you get a Windows Help-like window with lots of technical information.  One nugget to extract you will find under Table Integration > Database Diagrams and is the Table List.  Open that and you will get an Excel spreadsheet with all the GP tables organized by "Product" in the worksheet tabs.  I save this to my desktop and refer to it frequently.

Netsuite HAS your data

I've been working with a client over the last several weeks trying to get their data OUT of NetSuite (NS) and into an Azure data warehouse.  All I can say is What a chore!

Regrettably this client has extended their customer file in NS beyond the bounds of what the NS ODBC connector is able to handle.  That means more than 1000 columns!!

According to NS this is a limitation of Oracle, their database platform.  All we get are errors in ODBC.

Regardless that puts a serious crimp on what we can do.  Here's why.

Even though it's only the customer file that is extended so widely the effect of this is the employee and vendor files is also blocked.  These are the critical master files that feed the dimension data in the warehouse.  I can only speculate that their underlying views in ODBC somehow extend the problem from one table to all the other master file tables.  I have no idea why this is - it simply makes no sense.

Fortunately there is a Plan B for this.  In NS you can create Saved Searches (SS) and those can be downloaded via email on a schedule.  We were lucky enough at this point to be able to grab just the needed data from the customer, employee and vendor files in SS downloads and that is working for now.

What we've found, though, is that while we can get the transactional data from ODBC it does not include all the attributes available.  So we have to go back to SS to grab the missing data elements to fill out the Fact tables.

There is one other odd fact I can't get over.  I can find a Project entity in SS that I can download, but I can find no similar item in ODBC.  So even if ODBC worked for the files it does not it is still missing the project file.  Go figure!

There are serious limitations for SS that make this entire project a real kludge.  Any SS download is limited to either 2MB of data or 100,000 records, whichever comes first.  This ruled out grabbing transactional data from SS, as the transactions far overrun both those limitations.  In addition we hit the limit on the SS transaction supplements at the 100k record mark and thus had to break those into bite-size chunks and work from a "Last 90-day" window in our warehouse updates.

If you are planning any data extract projects with NS keep these limitations in mind.

Dynamics GP SOP Next Master Number malfunction

A client has been struggling with this issue for some time.  They use Sales Order Processing (SOP) in GP and they also use the Master Number feature to tie quotes, orders and invoices.

GP has a dark side with this where it does not honor the next master number sequence.  This is a very difficult problem to pin down as it does not correlate to any noticeable user task.  But the master number will suddenly reset itself at random times.  This can cause crossed master numbers in the SOP file causing unrelated documents to link together.

Turns out this is a known problem.  There is something wrong with the way GP doles out numbers.  This is something I've observed happen with Purchase Orders also.  In fact there is a tool available for setting the next PO number.  But nothing for the SOP master number.

What is available is an updated script from Microsoft that adds code to the SOPGetMasterNumber stored procedure to keep the next master number in SOP Setup from dropping below the next highest number in the master number table.

I've installed this script at this client and will report back if any problems arise or continue.

As an interesting side-note this problem has existed since I've been visiting this client in 2009 with GP 9.  They are now on GP 2010 and I've looked at that stored proc in GP 2013 and it's exactly the same as GP 2010.  Apparently if the problem is fixed in GP 2013 they fixed it via some means other than this stored proc.  We shall see.

Update 8/27/2014:
Since applying this update we have had exactly ONE incident report where the SOP next master number dropped below the actual next number.  This is proof positive of the solution found IMHO.  Normally we'd receive several notices per week.

In addition we've had no reported duplicate master numbers since.  Again normally those would happen at least a couple times a month.  So far so good!