Enabling AutoComplete for all GP users

Hi,

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.

SELECT * FROM DYNAMICS..SY01402 WHERE USERID = 'sa';

The results are as follow:

USERID          syDefaultType SYUSERDFSTR
--------------- ------------- ------------------------------
sa              15            0
sa              5             1
sa              3             251
sa              1             0
sa              2             0
sa              4             1
sa              7             2
sa              6             TRUE
sa              8             3
sa              30            FALSE-0-10000
sa              13            -1:-1:-1:-1:-1:-1:-1:-1:-1:-1
sa              42            4
sa              14            3
sa              31            TRUE

The following script has been designed to will change all user preferences to enable field auto-complete features throughout the application for every user.

UPDATE SY01402 SET SYUSERDFSTR = REPLACE(SYUSERDFSTR, 'TRUE', 'FALSE')
WHERE syDefaultType = 30

NOTE: If the user ID is not present within the table, the query can be modified to insert an autocomplete record based on the User Master table , dbo.SY01400.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Reconciling SOP Batches

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
CREATE PROCEDURE dbo.spReconcileSOPBatches AS

WITH SOP_BACHES (BACHNUMB, NUMOFTRX, BCHTOTAL) AS (
 SELECT BACHNUMB, ISNULL(COUNT(SOPNUMBE), 0) AS NUMOFTRX, ISNULL(SUM(DOCAMNT), 0) AS BCHTOTAL
 FROM SOP10100
 GROUP BY BACHNUMB
)
UPDATE A SET A.NUMOFTRX = B.NUMOFTRX, A.BCHTOTAL = B.BCHTOTAL
FROM SY00500 A INNER JOIN SOP_BACHES B ON (A.BACHNUMB = B.BACHNUMB)
WHERE (A.SERIES = 3) AND (A.BCHSOURC = 'Sales Entry')
GO

This stored procedure should be created against each company database. Once created, you must run the GRANT.SQL utility script against each company database to make sure each user in the DYNGRP database role gains execution rights to it.

Ok, now that we have the stored procedure out of the way, lets focus on ways to improve our already existing UI to perform the task at hand:

1. You could add the form to Modifier, add a push button control, then add the form to VBA and program the push button control to execute the stored procedure (complexity level: beginners, required knowledge ADODB, SQLOLEDB, DYNAMICS GP VBA object model). This is what the code may look like (at least to read the connection information, since the rest is history):

GetConnectionInfo()

Dim cn As New ADODB.connection
Dim rst As New ADODB.recordset
Dim cmd As New ADODB.Command

Private Sub GetConnectionInfo()

  Set cn = UserInfoGet.CreateADOConnection
  cn.DefaultDatabase = UserInfoGet.IntercompanyID
  cmd.ActiveConnection = cn
End Sub

2. You could use Dexterity to add a checkbox beneat the radio button group and set an event trigger on the Process button to fire up before or after the GP process is completed (complexity level: advanced, required knowledge SanScript). The trigger will need to be registered prior to being able to use it.

Startup

{ Created by Mariano Gomez, MVP }
{ global procedure: Startup     }

local integer l_result;
l_result = Trigger_RegisterFocus(anonymous('Process Button' of windowSOP_Reconcile of form SOP_Reconcile), TRIGGER_FOCUS_CHANGE, TRIGGER_BEFORE_ORIGINAL, script ReconcileSOPBatches);

if l_result  SY_NOERR then
  warning "Trigger registration failed.";
end if;

ReconcileSOPBatches

{ Created by Mariano Gomez, MVP         }
{ global procedure: ReconcileSOPBatches }
local long status;
call spReconcileSOPBatches, status;

spReconcileSOPBatches

{ Created by Mariano Gomez, MVP         }
{ global prototype procedure: spReconcileSOPBatches}
sproc returns long Ret_Code;

local long timedelay;
local integer i, n_loopcount;

if 'Reconcile Batches' of window SOP_Reconcile of form SOP_Reconcile = 1 then
  try
    call sproc "spReconcileSOPBatches", Ret_Code;
  catch [EXCEPTION_CLASS_DB_DEADLOCK]
    if i <= n_loopcount then
      timedelay = Timer_Sleep(200);
      increment i;
      restart try;
    else
      exit try;
    end if;
  else
    exit try;
  end try;
end if; 

I would like to hear from you. Please leave your comments!

Until then!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

UPDATES TO THIS ARTICLE:

12/15/2008 – Replaced cursor (below) for set-based approach (above). The set-based approach improves the stored procedure’s performance and yields a better query plan that the cursor.

CREATE PROCEDURE dbo.spReconcileSOPBatches AS
DECLARE @bachnumb char(25), @numtrx int, @batchamount numeric(19,5)
DECLARE c_sop CURSOR FOR
  SELECT BACHNUMB FROM SY00500
  WHERE (SERIES = 3) AND (BCHSOURC = 'Sales Entry')

OPEN c_sop
FETCH NEXT FROM c_sop INTO @bachnumb

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @numtrx = ISNULL(COUNT(SOPNUMBE),0), @batchamount = ISNULL(SUM(DOCAMNT),0)
  FROM SOP10100
  WHERE BACHNUMB = @bachnumb

  UPDATE SY00500 SET NUMOFTRX = @numtrx, BCHTOTAL = @batchamount
  WHERE BACHNUMB = @bachnumb

  FETCH NEXT FROM c_sop INTO @bachnumb
END
CLOSE c_sop
DEALLOCATE c_sop
GO

Wrong PO Number!

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 misshap 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 interfacing with the person executing the change.

DECLARE @newponumber char(25), @oldponumber char(25)
SET @newponumber = 'PO1023'
SET @oldponumber = 'PO1001'

SELECT DISTINCT 'UPDATE ' + RTRIM(objs.name) + ' SET PONUMBER = ''' + RTRIM(@newponumber) + ''' WHERE PONUMBER = ''' + RTRIM(@oldponumber) + ''''
FROM syscolumns cols
  INNER JOIN sysobjects objs ON (cols.id = objs.id)
  INNER JOIN sysindexes indx on (cols.id = indx.id)
WHERE (cols.name = 'PONUMBER') and (objs.xtype = 'U') and (indx.rowcnt  0)

When this script is executed against plain vanilla GP v10, it produces the following results:

UPDATE POP10100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP10110 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP10310 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP10500 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP30100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP30110 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP30310 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP40100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE SOP60100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
(9 row(s) affected)

The above results can be copied and pasted into a new results window to effect all the changes in the tables where the PONUMBER field is used.

NOTE: If using third party products that use a different table column name, you will need to replace accordingly in the script. Enjoy!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.com/

Moving SOP Transactions from One Batch to Another

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.

-- Created by Mariano Gomez, MVP
-- Maximum Global Business, LLC
declare @bachnumb char(15), @strpostdate char(20), @interid char(5)
declare @postdate datetime
declare @numtrx int, @batchamount numeric(19,5)
declare @noteindex numeric(19,5)
declare @l_result integer, @error_state integer

set @bachnumb = 'XCPTNS' + convert(char(4), year(getdate())) + '_' + convert(char(4), month(getdate())) + '_' + convert(char(4), day(getdate()))

-- drop timestamp
set @strpostdate = convert(char(20), getdate(), 101)
set @postdate = convert(datetime, @strpostdate)

-- move the left behind order transactions to
-- the new exception batch
update sop10100 set bachnumb = @bachnumb where bachnumb = 'SHIPTODAY' and soptype = 2

-- adjust the 'Ship Today' batch amounts for the
-- documents that did get transferred
select @numtrx = IsNull(count(sopnumbe), 0), @batchamount = IsNull(sum(docamnt), 0) from sop10100 where bachnumb = 'SHIPTODAY'
update SY00500 set NUMOFTRX = @numtrx, BCHTOTAL = @batchamount where bachnumb = 'SHIPTODAY'

-- get next note index to assign to the new batch
SELECT @interid = DB_NAME()
EXEC @l_result = DYNAMICS..smGetNextNoteIndex @interid, @@SPID, @noteindex output, @error_state output

-- get the number of transactions and amounts for the new batch
select @numtrx = IsNull(count(sopnumbe), 0), @batchamount = IsNull(sum(docamnt), 0) from sop10100 where bachnumb = @bachnumb

-- create the new batch in batch headers based on the
-- majority of fields in the old batch

INSERT INTO SY00500
( GLPOSTDT
, BCHSOURC
, BACHNUMB
, SERIES
, MKDTOPST
, NUMOFTRX
, RECPSTGS
, DELBACH
, MSCBDINC
, BACHFREQ
, RCLPSTDT
, NOFPSTGS
, BCHCOMNT
, BRKDNALL
, CHKSPRTD
, RVRSBACH
, USERID
, CHEKBKID
, BCHTOTAL
, BCHEMSG1
, BCHEMSG2
, BACHDATE
, BCHSTRG1
, BCHSTRG2
, POSTTOGL
, MODIFDT
, CREATDDT
, NOTEINDX
, CURNCYID
, BCHSTTUS
, CNTRLTRX
, CNTRLTOT
, PETRXCNT
, APPROVL
, APPRVLDT
, APRVLUSERID
, ORIGIN
, ERRSTATE
, GLBCHVAL
, Computer_Check_Doc_Date
, Sort_Checks_By
, SEPRMTNC
, REPRNTED
, CHKFRMTS
, TRXSORCE
, PmtMethod
, EFTFileFormat
, Workflow_Approval_Status
, Workflow_Priority
, TIME1)
SELECT
  glpostdt
 ,bchsourc
 ,@bachnumb
 ,series
 ,mkdtopst
 ,@numtrx
 ,recpstgs
 ,delbach
 ,mscbdinc
 ,bachfreq
 ,rclpstdt
 ,0
 ,bchcomnt
 ,brkdnall
 ,chksprtd
 ,rvrsbach
 ,userid
 ,chekbkid
 ,@batchamount
 ,BCHEMSG1
 ,BCHEMSG2
 ,BACHDATE
 ,BCHSTRG1
 ,BCHSTRG2
 ,POSTTOGL
 ,MODIFDT
 ,CREATDDT
 ,@noteindex
 ,CURNCYID
 ,0
 ,0
 ,0.00000
 ,PETRXCNT
 ,APPROVL
 ,APPRVLDT
 ,APRVLUSERID
 ,ORIGIN
 ,0
 ,GLBCHVAL
 ,Computer_Check_Doc_Date
 ,Sort_Checks_By
 ,SEPRMTNC
 ,REPRNTED
 ,CHKFRMTS
 ,TRXSORCE
 ,PmtMethod
 ,EFTFileFormat
 ,Workflow_Approval_Status
 ,Workflow_Priority
 ,TIME1
FROM SY00500
WHERE bachnumb = 'SHIPTODAY'

This batch of T-SQL statements can be automated in a SQL job to execute daily at certain time after the orders have been transferred by the AR department. This will automate the process and since the exception batch is created based on the current date, it is easy to distinguish one from the other on a daily basis, even if previous exceptions have not been processed. In addition, the ‘Ship Today’ batch totals (number of transactions and batch total) will be updated automatically as well, even if previous invoices remain unprocessed in the ‘Ship Today’ batch.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.com/