Enabling AutoComplete for all GP users

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.org/licenses/by-nc-sa/3.0/legalcode
--
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.

-- 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
--
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, MVP
IntellPartners, LLC
http://www.IntellPartners.com/