Dynamic Code Blocks

Microsoft Dynamics GP

Empty Matched To Shipment in Purchasing Invoice Entry of Dynamics GP

About from time to time on Euro transactions we get a yellow triangle and empty “Matched to Shipment” field in the Purchasing Invoice Entry window of GP. The problem is always the same, the fields * CURNCYID * CURRNIDX * XCHGRATE * RATECALC Are all either default or missing for the item row in

Microsoft Dynamics GP

OLE Notes Migration

It was time to get our old OLE notes migrated. GP has stopped using OLE notes containers for attachments to notes in the system, mostly due to the need to work with the web browser hosting of GP.  I didn’t want to import the attachments back into the new

SQL Server

SSMS SQL Hazard

Let me point out a hazard using SSMS, when developing a SQL delete query, especially more complex ones that take time to build up. I also think about some of the ways I work and how they minimise the chances of damage to data. Let us say we are developing

Microsoft Dynamics GP

Disable Enhanced Intrastat Dynamics GP

I have fallen out of friends with the GP Intrastat module, it has caused me problems with side effects in different parts of GP over the years. After another such incident, I decided it had to go, but I found it was not so obvious how to switch it off…

Microsoft Dynamics GP

Writing off stock from Qty In Service from Dynamics GP using SQL

I have 9,791 items with QTY in service >0 that I want to "adjust" out of stock. All these items have been financially accounted for, written off over the years, the IV account manually cleared by journal each month. However the items have been left in

SQL Server

Uppercase SQL script using SSMS

I just realised I use this functionality a lot and thought that others might not know it was possible. Highlight a lower case bit of text in the SQL editor window of SSMS and press CTRL+SHIFT+U and the text will go to upper case (L for lower case)

SQL Server

Using SQL UNPIVOT operator to reconcile Dynamics GP inventory items

Working with the Dynamics GP inventory tables you will encounter the field named QTYTYPE a lot. This is usually seen with its partner LOCNCODE. Inventory in GP can reside in different locations (for example depots New York, London, Sidney). That location then is broken down further into five item types.

Microsoft Dynamics GP

Inventory Year End, Remove Sold Receipts and Cost Change History

FIFO has to be maintained somewhere so goods receipts are added to table IV10200, as the inventory is consumed the QTYSOLD field is incremented against the receipt, until the QTYRECVD = QTYSOLD and at this point the field RCPTSOLD is set from 0 to 1, to indicate that the receipt has

Microsoft Dynamics GP

Updating Dynamics GP Comment ID in Sales Order Entry

A library of pre-written comments can be defined in Dynamics GP and then stored for later quick recall. A name known as the Comment ID is used for quick recall of the stored comments. The lookup table that stores the library of pre-written comments is SY04200. This table stores comments

Microsoft Dynamics GP

Dynamics GP Item Stock Enquiry Window taking too long to display data

If it takes a long time for the Item Sock Enquiry window to display data after entering an item number, this is a sign of large amounts of data in GP slowing things down. In my example, entering an item number into the stock enquiry window, it then takes nine

Microsoft Dynamics GP

Document attach feature Dynamics GP database and BusObjKey formats

I’ve been working on an archive project recently where I’ve been archiving records from GP into another archive database. This meant looking at how attachments are handled in Dynamics GP, in order to take any attachments related to records being moved into the archive. The Table CO00102 is

Microsoft Dynamics GP

SQL server FILESTREAM for Dynamics GP file attachments in coattachitems -is it possible?

In previous post (Managing document attachments  in Dynamics GP) I looked at the attachments feature in Dynamics GP 2013-R2 and above. I talked about how files attached to GP in this manner are stored in the database, specifically in the coAttachmentItems table. This table can get to be a huge

Microsoft Dynamics GP

Dynamics GP Document attachments “i” and “d” check box columns

A question came up on the GP forums  today regarding the "i" and "d" column check boxes in the Dynamics GP document attachment management window and specifically what they are there for. Luckily I got to have a good play with this window when it was

SQL Server

Trying to update locked view

Trying to update a view with ALTER VIEW and it does not happen immediately, then there is most likely a contention for the table with another process (user). To find out what is blocking, start the alter statement again, then execute the following SQL, for SQL server, sp_who2 active

Microsoft Dynamics GP

Puzzle of the negative bin quantities in Dynamics GP

Since going live with multiple binning there have been niggles with with bin quantity errors. Until today I had assumed it was our bespoke fulfilment or other bespoke processes causing the irregularities in bin quantities. Today I had the time to analyse it and find the root cause which was

Microsoft Dynamics GP

Lookup Dynamics GP install location from registry key

You should never assume that Dynamics GP has been installed in the default location. Almost always it will be, but sooner or later you will find a site or development machine where it is different. It is a good idea to check the machine registry for the application folder(s)

SQL Server

Old SQL habits

With each new version of SQL server some old SQL habits have to die. In Dynamics GP we often find document numbering sequences that are padded with zeros to facilitate sorting/ordering and to “look normal” on printed documentation. For example website orders may be imported via econnect into GP

Microsoft Dynamics GP

Financial Elementz export to Excel crashing Dynamics GP

User reported that Microsoft GP crashed with “Microsoft Dynamics GP has stopped working”. This occurred when attempting to export the Financial Elementz window Receivables Transaction Analysis to Excel. By closing all instances of Excel on the machine and then examining the user’s Windows Task Manager, it was revealed that

Microsoft Dynamics GP

Extender–you do not have access to open this window

You might have this problem where Dynamics GP claims “you do not have access to open this window”. The extender manual tells the basics of setting up security for extender to allow access to windows, it does not mention that the inquiry windows have their own security section or my

SQL Server

Beware diacritic characters where integrating with SQL

I’m certain you all know the above and practice it regularly. First a little background… In Dynamics GP we wrote a very basic “CRM like” system using a .NET GP Addin, that lays over the top of the SOP module. It introduces the concept of contact records, with many-many

Microsoft Dynamics GP

Enforcing field level security in Dynamics GP

Field level security is a useful Dynamics GP feature. It used for easily securing a field or form, however I have seen the admin user interface form confuse IT admins who are not used to the way GP does things, or simply mistakes being made setting up security, leading some

Microsoft Dynamics GP

Dynamics 365 Financials - Dynamics GP Migration Extension

Had a quick play with the migration tooling for Dynamics GP to Dynamics 365 Financials today, but failed pretty quickly with the lack of a data export option in GP. Here is where I got to: In Dynamics 365 Financials go to: Services & Extensions –> Extensions –> Extension Marketplace,

Microsoft Dynamics GP

Dynamics GP - Arithmetic overflow error converting IDENTITY to data type int

Anyone who looks at raw Dynamics GP tables is familiar with this column DEX_ROW_ID that occurs on most of them. It is an identity column, it auto increments an integer for each new record inserted. In GP it is an of integer data type, this means if the

General Technology Interest

Visualise regular expressions with “REGEXPER”

I have just been shown this is a really cool tool (thanks Pete). Enter a regular expression into the box at the top and it will generate a visual flow diagram of what the regular expression is doing. I think this the presentation is spot on. The site is at

Microsoft Dynamics GP

GPUG Summit 2016 Tampa USA - Part 5

Sadly Last Summit Day started with a last walk into the conference centre along the river walk with its art works, miniature trams and other artefacts along the way. Today Bob starts the day off announcing next year’s summit details, Nashville October 2017. He also lets us know what