How to Use SmartFill to Lookup Intercompany General Ledger Accounts

Subject:

The steps shown below describe how to set up a SmartFill Custom SQL Object to allow you to lookup Intercompany General Ledger accounts.

Resolution:

Create a new SmartFill Object following these steps:

  1. Click Microsoft Dynamics GP | Tools | Setup | SmartFill | Objects and then click the plus sign (+)
  2. Enter an Object Name of Intercompany Accounts
  3. Select the Search Type of Custom SQL
  4. Set the Object Definition section as follows:
    1. Product is Microsoft Dynamics GP
    2. Lookup Form is Account_Lookup
    3. Table Name is GL_Account_MSTR
    4. Return Field is User Defined String 1
    5. Return Datatype is String
  5. Enter the following Lookup Fields under Display Name:
    1. Company
    2. Account Number
    3. Account Description
    4. Account Unformatted
  6. Check Search and Display for all four Lookup Fields
  7. Click Save

NOTE:  You will not be able to use the Test Search feature under the Preview section in the SmartFill Object Maintenance Details window as it will not properly display the account numbers from other companies.

Create or edit the SmartFill stored procedure in SQL Server Management Studio following these steps:

  1. Find your Maximum Segments in Microsoft Dynamics GP under Microsoft Dynamics GP | Tools | Setup | Company | Account Format.
  2. Edit the number of Account Segments in the following scripts to match your environment.
    1. Edit the first part of the INSERT statement, by changing the SF_Return_Field_Account_1 through _10 lines to match your Maximum Segments found in step one above.   If your maximum is less than 10, you would need to remove the lines for SF_Return_Field_Account_X through SF_Return_Field_Account_10.
    2. Edit the SELECT clause of the INSERT statement, making the same change to the number of Account Segment lines.
  3. If you have other existing Custom SQL Objects in SmartFill
    1. Add the following code to your existing stored procedure in all company databases with the edits mentioned above:
IF @SmartFillObjectName = 'Intercompany Accounts' BEGIN
DECLARE @SQL VARCHAR(5000)
SELECT @SQL =
'INSERT INTO DYNAMICS.dbo.SF001
(USERID,
CMPANYID,
SF_Return_Field_String,
SF_Return_Field_Account_1,
SF_Return_Field_Account_2,
SF_Return_Field_Account_3,
SF_Return_Field_Account_4,
SF_Return_Field_Account_5,
SF_Return_Field_Account_6,
SF_Return_Field_Account_7,
SF_Return_Field_Account_8,
SF_Return_Field_Account_9,
SF_Return_Field_Account_10,
SF_Return_Field_Integer,
SF_Return_Field_Currency,
SF_Field_Value_1,
SF_Field_Value_2,
SF_Field_Value_3,
SF_Field_Value_4,
SF_Field_Value_5,
SF_Field_Value_6,
SF_Field_Value_7,
SF_Field_Value_8,
SF_Field_Value_9,
SF_Field_Value_10)
SELECT
''' + RTRIM(@UserID) + ''',
''' + LTRIM(STR(@CompanyID)) + ''',
ACTUNFORMAT,
'''', --Account Segment 1
'''', --Account Segment 2
'''', --Account Segment 3
'''', --Account Segment 4
'''', --Account Segment 5
'''', --Account Segment 6
'''', --Account Segment 7
'''', --Account Segment 8
'''', --Account Segment 9
'''', --Account Segment 10
0, --Integer Placeholder
0, --Currency Placeholder
COID,
ACTNUMST, --Display Field 2
ACTDESCR, --Display Field 3
ACTUNFORMAT, --Display Field 4
'''', --Display Field 5
'''', --Display Field 6
'''', --Display Field 7
'''', --Display Field 8
'''', --Display Field 9
'''' --Display Field 10
FROM
(SELECT COID = ''' + RTRIM(@SearchValue2) + ''', ACTNUMST, ACTDESCR,
ACTUNFORMAT = RTRIM(AM.ACTNUMBR_1)+RTRIM(AM.ACTNUMBR_2)+RTRIM(AM.ACTNUMBR_3)
+RTRIM(AM.ACTNUMBR_4)+RTRIM(AM.ACTNUMBR_5)+RTRIM(AM.ACTNUMBR_6)+RTRIM(AM.ACTNUMBR_7)+RTRIM(AM.ACTNUMBR_8)+RTRIM(AM.ACTNUMBR_9)+RTRIM(AM.ACTNUMBR_10)
FROM [' + RTRIM(@SearchValue2) + ']..GL00100 AM
LEFT JOIN [' + RTRIM(@SearchValue2) + ']..GL00105 AIM
ON AIM.ACTINDX = AM.ACTINDX) ALLCOACCTS
WHERE (ACTUNFORMAT LIKE ''' + '%' + LTRIM(RTRIM(@SearchValue)) + '%''' + -- contains search
' OR ACTDESCR LIKE ''' + LTRIM(RTRIM(@SearchValue)) + '%''' + ')' --begins with

EXEC (@SQL)
RETURN @@ROWCOUNT
END
  1.  If you have never used the Custom SQL stored procedure for SmartFill
    1. Run the following SQL script to create the spSmartFill stored procedure in all company databases after you make the necessary edits to your maximum segments:
--VERSION DATE 06-10-2021
--This SmartFill proc is for Intercompany Account Lookups
--This assumes 2nd Search Value is the Company ID
--Run this procedure against all company databases
--EXEC spSmartFill '16','sa', -1, 'E', 'CNTSO', '', '', '', '', '', '', ''
--SELECT * FROM DYNAMICS.DBO.SF001
--DELETE DYNAMICS.DBO.SF001
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'spSmartFill') DROP PROC spSmartFill
GO

CREATE PROC spSmartFill
@SmartFillObjectID INT,
@UserID CHAR(15),
@CompanyID INT,
@SearchValue CHAR(50),
@SearchValue2 CHAR(50),
@SearchValue3 CHAR(50),
@SearchValue4 CHAR(50),
@SearchValue5 CHAR(50),
@SearchValue6 CHAR(50),
@SearchValue7 CHAR(50),
@SearchValue8 CHAR(50),
@SearchValue9 CHAR(50)
AS
DECLARE @SmartFillObjectName CHAR(50)
SET @SmartFillObjectName = (SELECT SF_Object_Name FROM DYNAMICS.dbo.SF002 WHERE SF_Object_ID = @SmartFillObjectID)

IF @SmartFillObjectName = 'Intercompany Accounts' BEGIN
DECLARE @SQL VARCHAR(5000)
SELECT @SQL =
'INSERT INTO DYNAMICS.dbo.SF001
(USERID,
CMPANYID,
SF_Return_Field_String,
SF_Return_Field_Account_1,
SF_Return_Field_Account_2,
SF_Return_Field_Account_3,
SF_Return_Field_Account_4,
SF_Return_Field_Account_5,
SF_Return_Field_Account_6,
SF_Return_Field_Account_7,
SF_Return_Field_Account_8,
SF_Return_Field_Account_9,
SF_Return_Field_Account_10,
SF_Return_Field_Integer,
SF_Return_Field_Currency,
SF_Field_Value_1,
SF_Field_Value_2,
SF_Field_Value_3,
SF_Field_Value_4,
SF_Field_Value_5,
SF_Field_Value_6,
SF_Field_Value_7,
SF_Field_Value_8,
SF_Field_Value_9,
SF_Field_Value_10)
SELECT
''' + RTRIM(@UserID) + ''',
''' + LTRIM(STR(@CompanyID)) + ''',
ACTUNFORMAT,
'''', --Account Segment 1
'''', --Account Segment 2
'''', --Account Segment 3
'''', --Account Segment 4
'''', --Account Segment 5
'''', --Account Segment 6
'''', --Account Segment 7
'''', --Account Segment 8
'''', --Account Segment 9
'''', --Account Segment 10
0, --Integer Placeholder
0, --Currency Placeholder
COID,
ACTNUMST, --Display Field 2
ACTDESCR, --Display Field 3
ACTUNFORMAT, --Display Field 4
'''', --Display Field 5
'''', --Display Field 6
'''', --Display Field 7
'''', --Display Field 8
'''', --Display Field 9
'''' --Display Field 10
FROM
(SELECT COID = ''' + RTRIM(@SearchValue2) + ''', ACTNUMST, ACTDESCR,
ACTUNFORMAT = RTRIM(AM.ACTNUMBR_1)+RTRIM(AM.ACTNUMBR_2)+RTRIM(AM.ACTNUMBR_3)
+RTRIM(AM.ACTNUMBR_4)+RTRIM(AM.ACTNUMBR_5)+RTRIM(AM.ACTNUMBR_6)+RTRIM(AM.ACTNUMBR_7)+RTRIM(AM.ACTNUMBR_8)+RTRIM(AM.ACTNUMBR_9)+RTRIM(AM.ACTNUMBR_10)
FROM [' + RTRIM(@SearchValue2) + ']..GL00100 AM
LEFT JOIN [' + RTRIM(@SearchValue2) + ']..GL00105 AIM
ON AIM.ACTINDX = AM.ACTINDX) ALLCOACCTS
WHERE (ACTUNFORMAT LIKE ''' + '%' + LTRIM(RTRIM(@SearchValue)) + '%''' + -- contains search
' OR ACTDESCR LIKE ''' + LTRIM(RTRIM(@SearchValue)) + '%''' + ')' --begins with

EXEC (@SQL)
RETURN @@ROWCOUNT
END
GO

GRANT EXECUTE ON spSmartFill TO DYNGRP
GO

Once you have either edited or created the spSmartFill stored procedure, you will need to create a Repository record for the field and window where you want to be able to use SmartFill to lookup Intercompany GL Accounts.  The steps for creating the Repository record in the Transaction Entry window in General Ledger are:

  1. Log into Microsoft Dynamics GP as sa or a SmartFill Administrator.
  2. Go to Microsoft Dynamics GP | Tools | Setup | SmartFill | Repository.
  3. In the Repository Maintenance window, click the New button.
  4. In the Lookup Creation Wizard:
    1. Click Next.
    2. Select the Intercompany Accounts SmartFill Object.
    3. Click Next.
    4. Open the window under Transactions | Financial | General.
    5. Tab through to the Account field.
    6. Click Add Lookup.
    7. Click Done.

You can use the steps above to add this SmartFill Object to any other field where you might need to look up an Intercompany GL account number.   Simply open the appropriate window in step four above.

All KB Articles

Related Content

More Resources