Dynamic Code Blocks

Microsoft Dynamics GP & .NET technical Blog by Tim Wappat

Developing against Dynamics GP Multiple Bins, tables used

Quick guide to data flow when working with Dynamics GP and multiple bins.

Inventory levels

As is normally the case, stock levels are maintained by IV00102, in fields QTYONHND (quantity on hand) and ATYALLOC (quantity allocated).

When multiple bins is enabled (see post: TSQL determine inventory MultiBinning is enabled in Dynamics GP), then a layer is added on top of the standard stock levels with an additional table IV00112. This table holds the bin detail break down of the stock levels held in the IV00102 table. This bin detail table is keyed by item, location and quantity type where quantity type is “on hand”, “in service” etc. It adds a break down of the quantities of stock held at each Bin in the field IV00112.QUANTITY.

Bin Allocations vs. Site Allocations

It is important to understand that multiple binning also introduces another tier of stock allocations, at a bin level. Transactions such as SOP documents can have stock allocated to them at a site level (shown in the quantity allocated in item enquiry windows by site) but now with multiple binning enabled, they must also be allocated at the bin level of detail. The quantity of stock in bin detail that is allocated to sales, manufacturing or inventory transactions is held in the field IV00112.ATYALLOC. The value in ATYALLOC should be the total of all the different allocations over all the different types of transaction that can demand stock for that item.

When the transaction document is posted (or invoice posted in case of sales documents), the value for both ATYALLOC and QUANITTY of table IV00112 will be decreased by the quantity posted. There is an example later on in this document.

To put this another way, the total of the QUANTITY field in IV00112 for a item and location over all the bins should equal the QTYONHND in IV00102 for that item location (site).

Stock Level and Bin Stock Level Tables

Note That the quantity changes in IV00112.ATYALLOC in real time as quantities are selected in transactions documents, even before the lines are “saved” with the save button.

Default bins for transaction types

Default bins (some of which are highlighted by coloured box in screen shot) can be specified to be used for fulfilment , returns, receipts etc. These default bins are held against each item site combination by the IV00102. If defaults are specified, the application will not prompt the user for bin choices in these types of transactions, for that site and item. Instead the default bin will be automatically selected however should insufficient stock be available from that bin, the window will open prompting for the user to select where the stock should come from.

Default bins and priorites by item site

When this prompt window opens, the order of the bins shown in the lookup can be forced, by specifying a priority order, by item, in the table IV00117.  In native GP, this priority is merely used for the order in the lookup window. Some ISVs offer products that will allow the bins specified in the priority table to be used also until all stock is exhausted – this is how I’ve programmed our implementation too, I think this should be an option check box to make GP behave this way.

Bin Priority Table

In the next screen shot it can be seen how the priority is provided as a sort column in selecting bins. Further options are made available by clicking on the blue bar.

Bin Priority shown in Bin selection lookup

Serial Number Selection window

Table IV40700, site definition table holds default bins by site – shown by box below. This operates at the site level in the same way as the item default bins, allowing for items, for example to be placed into a put away floor grid by default when GRN is complete.



Sales document bin allocations

In the same way that stock is allocated from inventory at both site and bin levels, so too sales orders can be allocated.

Stock allocations from bins to sales order lines is recorded against a sales order line using two tables, depending upon if the item is a serial numbered item or a standard inventory item;

  • Non serial numbered/lot items Table: SOP10203
  • Serial/lot items Table: SOP10201

When viewing a sales order line in the detail window, either the serial/lot button will be enabled or the bins button depending on the type of item, this also indicates the table responsible for recording that bin allocation.

The total quantity allocated from a bin to documents is an aggregate sum of the values in SOP10203.QUANITTY and SOP10201.QUANTITY fields for all sales order lines and the equivalent fields for other transaction types that consume stock, for example inventory transactions.

Sales Order bin allocation tables

When the order is transferred to invoice, the SOPTYPE and SOPNUMBE are updated in SOP10201/SOP10203 to those of the newly created invoice, thus transferring the bin allocation records from the order to invoice. On posting that invoice, the value for both ATYALLOC and QUANITTY of table IV00112 will be decreased by the quantity posted, as the stock is removed from inventory. It is at this point that the rows corresponding to the sale are marked as posted in the order tables SOP10201/SOP10203, using  POSTED=1 in the POSTED field. Thus records with POSTED=1 should not be treated as live bin allocations.

Sales order example:

When the sales order is entered and a quantity entered for the order, then assuming stock availability, the order line will cause a site allocation of the stock. The IV00112 and SOP10201 tables will not show any ATYALLOC value for the order at this point. The Bin button or the Serial/Lot button can be pressed from within the sales order line detail window. This lets the user see what bins the stock resides in and allows the user to select which bins they want to fulfil the order from. Note that immediately the user selects the bin or serial number and quantity, the stock becomes allocated by a value in IV00112.ATYALLOC and associated quantity in SOP10203.QUANTITY or SOP10201.SERLTQTY. Note that the order line also then becomes fulfilled by the bin quantity allocated! With standard GP, it is not possible to direct pickers to stock by selecting bins within the order line as this will result in premature fulfilment of the sales order quantities for that line! To work around this in my solution we introduced another table to hold our own bin allocations for serial and non-serial items during picking, the values in this table are transferred to the order at fulfilment of the order lines allowing the pickers to be directed to bins holding the stock for the order.

As noted earlier, once the order is transferred to invoice the order allocations will be marked POSTED and the inventory values decremented by the value posted.

Inventory transactions

Other transaction types can hold bin allocations, such as inventory and manufacturing orders.
IV10004 holds the bin details for inventory transactions.


Installing/using/finding Professional Services Tools Library (PSTL)

I just couldn’t find the Professional Services Tools on Dynamics GP today & I admit that this is not the first time, so I’m blogging it to remember for next time.


Couldn’t find any reference to a PSTL DIC in Dynamics.SET -so assumed it had been missed during a previous upgrade on this install.

Installing PSTL (GP2013R2)

I know in the past that the PSTL was downloaded from Partner Source (website that your GP partner has access to), thus I raised a support ticket to get the installer from partner for me. Then was told it is now part of the standard GP installer from GP2013R2 – excellent, I like that more of these extras are turning up in mainstream media!

Ran the installer and selected to alter existing install and there it is in the list!


Ran through the installer but couldn’t see any changes to the list in .DIC, no prompt to import any chk files on running GP, so got puzzled.

Went to have a quick look at what was on the machine, I often use the “add shortcut” as a fast way to browse the dictionaries on an client install. I then realised my oversight. The Professional Services Tools Library is actually in the Technical Services Tools Dictionary. I’d been checking under “P” rather than “T” with tunnel vision as I was in a hurry and under pressure. Below you can see the “main window” under Technical Service Tools>>Project>>Professional Services Tools Library.


Back to the SET file

The Professional Services Tool Library for Dynamics GP has .SET number of 1838, another fact that is useful for Google to learn.


After all that…

Anyway it happens that the “Shipment Notification” window that I thought was part of the PSTL is not and requires a Partner Source download, back to raising a new support ticket!


Raspberry Pi Zero Octoprint / OctoPi setup

Written on Dec 2015

I expect this post to age quickly but will hopefully find people setting up OctoPi for the newly released Pi Zero.

OctoPi is a Raspbian image with Octoprint installed and configured on it. Octoprint allows network access and control of a USB connected 3D printer. It is the best thing since sliced bread, in my opinion and it has allowed the whole household to access the printer. The software has also created freedom to control the printer from multiple devices in the house. The webcam monitoring allows progress of prints to be checked too. There is even a mobile phone app now that allows control of the printer from an Android smart phone.

Get the case

House your Pi Zero in a case to protect it or use self adhesive posts to mount it somewhere on or in your 3D printer, remembering to allow access to SD slot and USB sockets. I’d recommend this case I made: http://www.thingiverse.com/thing:1167846

Get the image

If the current release version of Octopi is placed into a Pi Zero, then the Pi Zero will flash the on-board LED repeatedly 8 times and the Pi Zero will refuse to show any output to HDMI and will fail to boot.

This is because the current (Dec 2015) release version of OctoPi (2015-07-02_2015-05-05-octopi-wheezy-0.12.0) is built on the wheezy version of Raspbian. Raspbian is the flavour of Linux operating system developed for the Raspberry Pi devices. The Pi Zero requires the jessie version of Raspbian as a minimum to boot. Luckily the nightly builds of OctoPi are now using Jessie, although the nightly builds are not official releases and may have some issues yet to be ironed out, I’ve actually found them reliable in practice. Download the latest nightly build from the nightly build folder, for example today it is https://storage.googleapis.com/octoprint/2015-12-04_2015-11-21-octopi-jessie-0.13.0.zip, the 04th of Dec being today.

Write the image

Uncompress the zip file and use the resulting disk image to write to your micro SD card that will be placed into the Pi Zero. Follow the standard instructions for writing Raspbian to SD cards. Follow the instructions on “how to use it” here https://github.com/guysoft/OctoPi. Remember to configure the network settings in the network text file located in the root directory of the written SD card.

Plug in the Pi Zero and 3D printer

Use a micro-B USB on the go to USB A female cable adapter, to plug in a (powered preferably) USB hub, into which you plug a WIFI dongle and the printer. Next plug in the micro-B USB power to the right hand socket, this is the dedicated power socket, does not do data and also note the data USB socket next to it cannot be used to power the Pi Zero.

Boot and using a web browser browse to http://octopi.local or find the IP address your network has assigned to the Pi by checking the router or DHCP services.

At this point you should be up and running.


Printrbot, Pi Zero & USB 3.0 Hubs

Trying to connect Printrbot Simple Metal Printrboard to the Pi Zero resulted in no serial port being listed in OctoPi. The message on trying to auto connect was:

Failed to autodetect serial port, please set it manually

Connecting to the OctoPi via SSH and looking at the $ dmesg output revealed issues with the Pi identifying the Printrboard, which presents itself as a serial device.

device descriptor read/64 error -71 dwc_otg

This normally indicates a power issue, but the hub is a Amazon Basics USB 3.0 powered hub, so this seems unlikely. From the previous Pi to the Pi Zero, the printer has moved from being directly plugged into the Pi to going via the hub, due to the Pi having no internal USB hub.

After some research it turns out that support for USB 3.0 hubs might not be very good on the Raspberry Pi. I tested this by getting a simple, old unpowered USB 2.0 hub out the drawer, much to my delight this worked allowing the printer to be detected by OctoPi.

Please comment if this was helpful as it motivates me to write more!

Dynamics GP Power Tools–what will it not do?


If you are developing on top of Dynamics GP, or perhaps go to the many Dynamics GP conferences, or maybe just using search engines to find answers to your common GP problems, then you will have encountered David Musgrave (MVP).

His work is pervasive in the Dynamics GP world and he has saved my bacon, more times than I can count via technical blogs revealing the obscure corners of the GP product that I too often find myself despairing in.

Until a year ago David was working for Microsoft. You may have experienced that there are major world news events that you remember exactly where and when you heard that big news. I remember the moment I was told by a MS developer that David was having to leave Microsoft, news that made the group groan. I had been waxing lyrical about his work and suddenly felt deflated. Not long after, that same news shook the partner community as it propagated out to all corners of the world. You may ask, Why the drama? Well David has given a lot to the GP community both user and partner side of it and he is someone that I think we all respect and hold in highest regard and esteem. I also think that we were being a bit selfish in worrying that this news might herald his exit from that community. Thankfully this was not the case! In fact it turns out that it may have been one of the best things that could have happened for us and time will tell for David. For now all us GP developers have our code warrior, bridging the chasm that lies between Dexterity, the barren dry scripting language GP is written in and the world of .NET development, the lush palace where the rest of us live (yes I mean palace)..

A year ago, the dust settled, David went quiet for a little while and surfaced with a product, Power Tools published by his company Winthrop Development Consultants, whose logo has an uncanny resemblance to GP Dynamics sails log (although it predates his MS days). David has released the product and is continuously developing it a high rate of knots. It is a product that helps the rest of us developers be more productive, and in many cases enables us access to do things with some aspects of GP that otherwise we would be barred from! The product is called GP Power Tools, for indeed it is powerful. The product provides not only developer APIs and helpers to develop solutions with Dynamics GP, it also provides facilities for administrators to perform admin tasks and access to specialised debugging diagnostic tools together with exposing so many GP configuration switches. Power Tools has sprung from a product from Microsoft named The Support Debugging Tool, something that David actually wrote in the first place and got the rights back from Microsoft for.


Find below the extensive feature list, as of today Build 20, from the user guide: GP Power Tools User Guide



Manual Logging Mode

Manually turn on SQL Logging and Dexterity Logging and Profiling

Individual Logging

Control Individually control SQL Logging and Dexterity Logging and Profiling

Dex.ini Settings

Change System and Debugger Dex.ini Settings

Resource Information

Obtain Details of any Table, Form, Window, Field or Report resource

Security Profiler

Monitor all Security check activity

Security Information

Display Security settings for specific resources for a user and company

Security Log

Security activity tracking for users, companies and the entire system.


Capture and either email or save Screenshots and System Status information

Send Email

Send Email messages from within the application.

Automatic Debugger Mode

Automatically starts logging and watches for specified events

Dictionary Control

Enable and Disable third party products

XML Table Export

Export any table(s) to an XML file

XML Table Import

Import previously exported tables

Runtime Execute

Run Dexterity sanScript scripts

SQL Execute

Run Transact SQL scripts

.Net Execute

Run C# and VB.Net scripts

Configuration Export/Import

Export and Import settings

Configuration Maintenance

Clear Debugging Tool settings

Administrator Settings

Change system wide Administrator Settings controlling the behavior of the tool

Dex.ini Configuration

Automatically update Dex.ini settings across multiple workstations.

Database Validation

Validate SQL user and database information and table structures.

About GP Power Tools

Display version and build information or un-install GP Power Tools


Tools & Utils for all

toolboxPower tools has some very dangerous, in an exciting way and powerful facilities that only power users, developers or system administrators should use and offers other features that are end user friendly. End user features such as emailing a screenshot & snapshot of the Dynamics GP configuration to the support people in your organisation on the press of a button in GP is really useful.

Indeed Power Tools is much like one of those concertina tool boxes, where you can dig deeper and deeper inside to find just the right fitting tool.

One more thing, David has a sponge like special power, he soaks up any ideas in the atmosphere from users, partners and I can only guess where else. He then refines those ideas into feature sets for products. His pace of development is astounding, so I have no doubt that by this time next year there will be little Power Tools can not do! I am also expecting to see some other products emerging into the GP market, long may it last.

Now, go read the product pages, get it ordered and start benefiting from David’s hard work.