The Dynamics GP Blogster

Autocomplete has been a very valuable feature in Microsoft Dynamics GP since version 8.0, however, little is known as to the method of storing this data within the application tables. In addition, one of the commonly requested features by application administrators in sizeable environments is to enable Autocomplete for all users created in the system. Autocomplete for each user -- along with many other user preferences -- is stored in the DYNAMICS.dbo.SY01402 table, in a very cryptic way. Each preference is stored as a series of concatenated characters representing the selections made by the user during setup, in the SYUSERDFSTR column. In addition, the feature has been assigned a system default type a(syDefaultType) of 30. The following query demonstrates the content of SY01402 for the 'sa' user. -- Created by Mariano Gomez, MVP -- This code is licensed under the Creative Commons -- Attribution-NonCommercial-ShareAlike 3.0 Unported License. -- http://creativecommons.o...

Hi and welcome to another edition of The Dynamics GP Blogster! Someone, please explain something to me? Why is it that after 20 years of existance, we still don't have a Reconcile SOP Batches utility in Dynamics GP? Well, I figured I would try to solve this puzzle by creating my own SQL server stored procedure and provide a few ideas on expanding the Remove-Reconcile Sales Documents window (Microsoft Dynamics GP | Tools | Utilities | Sales | Reconcile-Remove Sales Documents) to execute the stored procedure, all seamlessly integrated! First the stored procedure: dbo.spReconcileSOPBatches -- Created by Mariano Gomez, MVP -- This code is licensed under the Creative Commons -- Attribution-NonCommercial-ShareAlike 3.0 Unported License. -- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode -- CREATE PROCEDURE dbo.spReconcileSOPBatches AS WITH SOP_BACHES (BACHNUMB, NUMOFTRX, BCHTOTAL) AS ( SELECT BACHNUMB, ISNULL(COUNT(SOPNUMBE), 0) AS N...

Welcome to another edition of my blog! This time around, I want to talk -- not literally -- about a common occurrence experimented in many Dynamics GP environments. When the pressure amounts, some company buyers may find themselves accidentally overriding the PO number field, a common mishap that may cause wasted time or the need to void and re-enter the document. Now, think for an instance, if you are using the Manufacturing module or any third-party product how cumbersome the task can become. Fortunately, there is help on the way! I have developed a script based on a previous post , that will scan for the PONUMBER field in all tables in the company database. The script will automatically produce another script in the Results pane that can be copied and pasted into a new Query window and be executed against the company database. The following example shows the script with the new PO number (@newponumber) and the old PO number (@oldponumber) variables being used to facilitate the i...

It is no secret that SOP allows users to transfer transactions from Orders to Invoices (or any other valid transfer type). In the process, the exceptions, that is, transaction records that did not get transferred from one type to another, are comingled with the transactions that did get transferred. In this case, the SOP batch will contain, say for example, orders and invoices making it difficult for the user to distinguish exceptions from final transactions. Yes, batch edit lists do exist, but are not quite an effective tool in sorting out the good, from the the bad, from the ugly. The following SQL script allows you to split the transferred records from the exceptions in two batches. The original batch name will be used for the transactions that got successfully transferred and an exception batch will be created for those that were left behind during the transfer. Lets assume that batch contains orders 'SHIPTODAY' that need to be invoiced, this is, transferred. -- Cre...