Macro to provide access to Transaction Dimension Codes to Users

Hi all

In this article, I am going to publish a SQL script which will be helpful to many using Analytical Accounting module.

I have come across many clients who have multiple companies and they have hundreds of users spread across each of these companies. Assume the case when a new dimension code value has to be added to all these companies, you can add these codes using the UI. But the main part which is difficult here is to grant access to all the users in that company (assuming all users need access to the newly created dimension code). This task can be tiring if you need to login to each company and granting access to the specific dimension code to all the users in that company.

Note: My script below assumes that all users in a specific company has access to all the dimension codes that are setup in that company. And my script is based on the fact that there is only one dimension which has been setup in the company.

DECLARE @COMPANYID INT
DECLARE @COMPANYNAME CHAR(65)
DECLARE @RECORDCOUNT INT
DECLARE @USERID CHAR(15)

BEGIN
    SET @RECORDCOUNT = 0
    PRINT '# DEXVERSION=10.0.324.0 2 2'
    DECLARE cr_Companies CURSOR
        FOR SELECT  CMPANYID,
                    CMPNYNAM
            FROM    DYNAMICS.dbo.SY01500
            ORDER BY INTERID

    OPEN cr_Companies

    FETCH NEXT FROM cr_Companies INTO @COMPANYID, @COMPANYNAME

    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @RECORDCOUNT = @RECORDCOUNT + 1
            IF @RECORDCOUNT > 1
                BEGIN
                    PRINT 'CloseWindow dictionary ''Analytical Accounting''  form aaUserAccToCodes window aaUserAccToCodes '
                    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL'
                    PRINT 'CommandExec dictionary ''default''  form ''Command_System'' command CompanyName '
                    PRINT 'NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' '
                    PRINT '  ClickHit field ''(L) Company Names'' item '
                        + LTRIM(RTRIM(STR(@COMPANYID))) + '  # '''
                        + LTRIM(RTRIM(@COMPANYNAME)) + ''' '
                    PRINT '  MoveTo field ''OK Button'' '
                    PRINT '  ClickHit field ''OK Button'''
                    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL '
                    PRINT '  CommandExec dictionary ''Analytical Accounting''  form ''Command_AA'' command aaUserAccess '
                    PRINT 'NewActiveWin dictionary ''Analytical Accounting''  form aaUserAccToCodes window aaUserAccToCodes'
                END
            ELSE
                BEGIN
                    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL'
                    PRINT 'CommandExec dictionary ''default''  form ''Command_System'' command CompanyName '
                    PRINT 'NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' '
                    PRINT '  ClickHit field ''(L) Company Names'' item '
                        + LTRIM(RTRIM(STR(@COMPANYID))) + '  # '''
                        + LTRIM(RTRIM(@COMPANYNAME)) + ''' '
                    PRINT '  MoveTo field ''OK Button'' '
                    PRINT '  ClickHit field ''OK Button'''
                    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL '
                    PRINT '  CommandExec dictionary ''Analytical Accounting''  form ''Command_AA'' command aaUserAccess '
                    PRINT 'NewActiveWin dictionary ''Analytical Accounting''  form aaUserAccToCodes window aaUserAccToCodes'
                END
            DECLARE cr_Users CURSOR
                FOR SELECT  USERID
                    FROM    DYNAMICS.dbo.SY60100
                    WHERE   CMPANYID = @COMPANYID
                    ORDER BY USERID
            OPEN cr_Users
            FETCH NEXT FROM cr_Users INTO @USERID
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    PRINT '  ClickHit field ''(L) UserOption'' item 2  # ''User ID'' '
                    PRINT '  MoveTo field ''User ID'' '
                    PRINT '  TypeTo field ''User ID'' , '''
                        + LTRIM(RTRIM(@USERID)) + ''''
                    PRINT '  MoveTo field aaTrxDimDescr2 '
                    PRINT '  TypeTo field aaTrxDimDescr2 , ''TESTDIMCODE'''
                    PRINT '  MoveTo field aaDistribute  # ''FALSE'''
                    PRINT '  ClickHit field aaDistribute  # ''TRUE'''
                    PRINT '  MoveTo field aaAdjust  # ''FALSE'''
                    PRINT '  ClickHit field aaAdjust  # ''TRUE'''
                    PRINT '  MoveTo field ''Save Button'' '
                    PRINT '  ClickHit field ''Save Button'''
                    FETCH NEXT FROM cr_Users INTO @USERID
                END
            CLOSE cr_Users
            DEALLOCATE cr_Users
            FETCH NEXT FROM cr_Companies INTO @COMPANYID, @COMPANYNAME
        END
    PRINT 'CloseWindow dictionary ''Analytical Accounting''  form aaUserAccToCodes window aaUserAccToCodes '
    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL'
    CLOSE cr_Companies
    DEALLOCATE cr_Companies
END

This script is written for GP v10 SP4, and it generates the contents of the macro, which logs into all the companies that have been setup in DYNAMICS and grants access to the dimension code added in all the companies to all the users belonging to that company. Once the script generates the macro code, copy the code and save it into a notepad file and save it as a file with a .MAC extension and open the macro file from GP, and sit back and relax. The macro would do all for you…

This macro code can be modified to suit individual business requirement and I am sure this will be a good starter for many…

Note:

GP 2010 has got a slight enhancement to this functionality but not what exactly what the script above achieves. A little write up on this below.

In Dynamics GP 2010, when a user tries to add a dimension code on the fly, the system automatically grants access to that particular user and not to all users in that company. And also, this happens only when the dimension code is added on the fly from a transaction window and not when a dimension code is created from the Transaction Dimension Code Maintenance window. Even in GP 2010, if the user creates a dimension code from the Maintenance window it does NOT grant access to that code to any users in that company.

The only enhancement is that when a user tries to add a new dimension code from the transaction on the fly, earlier versions did not give access by default even to the user who created it from the transaction level, which prevented the user from tabbing off the field on the AA transaction window, and the user has to go exclusively to the User Access window to grant him access so that he can tab off. This was a problem since not all users would have access to the User Access window in AA.

So this script of mine, would still be useful in the event of users starting to use GP 2010 and AA, since this script grants access to all dimension codes to all users in that company.

Hope this information is helpful.

Until next post…