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
Microsoft Dynamics GP GPUG Summit 2016 Tampa USA - Part 4 I got invited to be interviewed by the Enterprise Software Podcast. As a listener for some time now, I couldn’t turn the offer down… We talked about the GPUG Summit and that more UK engagement is required, I did my best to promote GPUG in the interview. While at
