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
Microsoft Dynamics GP GPUG Summit 2016 Tampa USA - Part 3 The Microsoft show at the arena on the previous day (day zero), was a controversial late add-on to Summit. It was now day one of "proper" summit, after intros from Bob, Errol Shoenfish took to the stage and showed us power apps and talked Dynamics GP road maps
Microsoft Dynamics GP GPUG Summit 2016 Tampa USA - Part 2 Microsoft had booked the Ice Hockey Arena booked for 2pm to give a keynote and launch Dynamics 365. That meant I had the morning to myself. I headed out of the hotel first thing (well perhaps 2nd thing haha), to discover the the hotel provides a efficient fleet of free
Microsoft Dynamics GP GPUG Summit 2016 Tampa USA - Part 1 To Tampa, 22hrs door – door I printed my plastic luggage tag for GPUG Summit 2016 with my 3D printer and was ready to travel. The flight itinerary involved a flight from Newcastle in United Kingdom -> to London -> Miami -> Tampa. In all it took twenty
General Technology Interest Kill hung IIS windows FTP sessions During some integration development some FTP sessions got created with keep alive on them, but how to remove them? List active FTP sessions in IIS Sounds simple, but was not so easy. The live sessions are listed in IIS under the ftp site in question. Select the site in IIS
