Retrieve Data from a Non Dynamics GP Server

Hands pointing at charts and a computer

A Dexterity developer may need to create a customization to retrieve data from a non-Dynamics GP server. In this example, we will retrieve the next vendor ID from a server where Dynamics GP is not installed.

A new push button control will need to be created. In order to accomplish this, open Dexterity, click the Forms folder, then open the PM_Vendor_Maintenance form. Select the PM_Vendor_Maintenance window and click the Open button. Next, click the New button on the Layout tab.

Create the new field and the data type for the new field.

Next, click and drag the new field onto the PM_Vendor_Maintenance window.

Next, we will need to add new code to the Get Next Vendor push button.

PM_Vendor_Maintenance Get Next Vendor_CHG

local reference recordset, cmd, Debug;

local string query;

local integer adOpenDynamic, adLockOptimistic;

local string l_returned_vendor_num;

{These are the constants from CursorTypeEnum (adOpenDynamic) and LockTypeEnum (adLockOptimistic) for the .Open method}

adLockOptimistic = 3;

adOpenDynamic = 2;

{Create a COM object reference to ADO Recordset – Remember we are taking the .Connection off the dummy window.}

recordset = COM_CreateObject(“ADODB.Recordset”);

query = “Declare @NextVendorID integer exec  GetNextVendorID @NextVendorID”;

recordset.Open(query, ‘(L) connection’ of window ADO of form ADO_Connection_Form, adOpenDynamic,adLockOptimistic);

query = “select max(VENDOR_ID) from PAYABLES_NEXT_VENDOR_ID”;

recordset.Open(query, ‘(L) connection’ of window ADO of form ADO_Connection_Form, adOpenDynamic,adLockOptimistic);

if not recordset.BOF or not recordset.EOF then

{We found the vendor in our custom table, update the vendor id field on the PM Vendor Maintenace window}

recordset.MoveFirst();

enable field ‘Vendor ID’ of window PM_Vendor_Maintenance of form PM_Vendor_Maintenance;

l_returned_vendor_num = recordset.Fields.Item[0].Value;

{set ‘Vendor ID’ of window PM_Vendor_Maintenance of form PM_Vendor_Maintenance to str(l_returned_vendor_num);}

set ‘Vendor ID’ of window PM_Vendor_Maintenance of form PM_Vendor_Maintenance to l_returned_vendor_num;

run script ‘Vendor ID’ of window PM_Vendor_Maintenance of form PM_Vendor_Maintenance;

{disable field ‘Vendor ID’ of window PM_Vendor_Maintenance of form PM_Vendor_Maintenance;}

focus field ‘Vendor Name’ of window PM_Vendor_Maintenance of form PM_Vendor_Maintenance;

end if;

{**** End Call to Stored Proc ****}

{Close the recordset.}

recordset.Close();

{Clear the variables.}

clear recordset;

 

Next, you will need to

create a window to open the connection to the non-Dynamics GP server.

Create the new form ADO_Connection_Form and window called ADO.

Add the pre and post window scripts.

ADO_WIN_PRE

local reference connection;

local string conString;

{Create a COM object reference to ADO Connection.}

connection = COM_CreateObject(“ADODB.Connection”);

{Define the ADO connection string.}

{*** Production Connection String FQDYN01-SQL Server Name ***}

conString = “Provider=MSDASQL;Data Source=PRODUCTION;UID=FQPROD;PWD=perform621;Initial Catalog=fq”; {LIVE }

{Create and open a connection.}

connection.Open(conString);

{Set the window field equal to the connection.  The window field is a local field that is defined as a reference.

Now we can refer to this window field from other scripts in other places.}

‘(L) connection’ = connection;

ADO_WIN_POST

{Close the connection.}

‘(L) connection’.Close();

{Clear the field.}

clear ‘(L) connection’;

*** You will notice that you need to create an ODBC connection to connect with. The ODBC name in this example is called PRODUCTION.

Once you have completed these steps, you will need to create a chunk file and test your new customization.

For more information on how to create a customization to retrieve data from a non-Dynamics GP server, contact us.

Kate & Ben — How can we help you? Contact Us!

How can we help you?