Add data in AutoComplete list from third party application

Suppose you need to enter data in a field in GP which already exists in some other application. You want users to add exact same values without any minor difference or spelling mistakes. For this kind of situation you can use the following code to add data in autocomplete list of a particular field in window in GP.

I am using the dexterity method AutoComplete_AddStringValue for this which  Dave described in his post Adding AutoComplete Items with Dexterity

For adding autocomplete data based on third party application, you will need a stored procedure get the data form that application. Grant access to DYNGRP to the stored procedure to avoid any privilege  issues.

Option Explicit

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String

Dim cn As New ADODB.Connection

Dim rstProfiles As New ADODB.Recordset
Dim cmdProfiles As New ADODB.Command

Set cn = UserInfoGet.CreateADOConnection()
With cn
.CursorLocation = adUseClient
.DefaultDatabase = UserInfoGet.IntercompanyID
End With
Set CompilerApp = CreateObject(“Dynamics.Application”)
Commands = “local boolean result;” & vbCrLf
cmdProfiles.ActiveConnection = cn
cmdProfiles.CommandType = adCmdStoredProc
cmdProfiles.CommandText = “_sp_getProfiles”
Set rstProfiles = cmdProfiles.Execute
Dim profile As String
Do While Not rstProfiles.EOF
profile = rstProfiles!MyField
Commands = Commands & “result = AutoComplete_AddStringValue(‘User Defined 1′ of window ‘GL_Account_Maintenance’ of form ‘GL_Account_Maintenance’, “”" & profile & “”");” & vbCrLf
rstProfiles.MoveNext
Loop

CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)

If CompilerError <> 0 Then
MsgBox CompilerMessage
End If

If rstProfiles.State = adStateOpen Then rstProfiles.Close
If cn.State = adStateOpen Then cn.Close

End Sub

 

In this code, I am using the stored procedure “_sp_getProfiles” to get data from third party application. I compiled dexterity sanscript string by looping through the records returned by stored procedure. MyField is the column returned by stored procedure. I am adding it in Autocomplete list of User Defined 1 field of Account Maintenance Window using following code.

“result = AutoComplete_AddStringValue(‘User Defined 1′ of window ‘GL_Account_Maintenance’ of form ‘GL_Account_Maintenance’, “”" & profile & “”");” & vbCrLf

 

Data returned by the SQL query

Data added in  AutoComplete list of User Defined 1 field of Account Maintenance Window

For enabling AutComplete for User Defined 1 field of Account Maintenance Window refer to my previous post Adding autocomplete to a field

One Response to “Add data in AutoComplete list from third party application”

1. Rick Thomas says:

   May 13, 2011 at 1:00 pm

   I can see how this would be useful. Would be great in reducing user errors. Nice article!