AIT has tables that are designed to make cross-company lookups and verifications faster and simpler by collecting information from your many companies, and storing it in the DYNAMICS database. When you add new companies, particularly when you add new Internal companies, you will need to run the check links process to update these tables. If you have AIT installed on all workstations, you do not need to run this process during your daily routines of adding new accounts to your companies, as long as the accounts are added through the Great Plains interface, such as the Account Maintenance or Mass Modify window, or through Integration Manager. If you have special SQL routines that add accounts directly to SQL tables (e.g. via stored procedures or triggers) you must run a separate routine to update these tables. You can either run the Check Links process from the AIT Maintenance window (File => Maintenance => AIT Maintenance) or you can use this document to guide you through creating a SQL Agent process that keeps the AIT lookup tables properlymaintained.
When you add a new company, make sure to review the steps in the AIT manual, under the section "Adding a New Company". When you follow the steps outlined in the manual, do not mark the AIT Setup Wizard option "Build AIT Tables". After you have completed the AIT Setup Wizard, you may need to run check links on the AIT Account Format table (File/Maintenance/AIT Maintenance). This check links procedure can be run safely while users are logged into your system. Do not run check links on the AIT Account Master: use the steps below to complete that check links process.
If you have a large number of companies, or do not want to have to log all users out of Dynamics GP, you can use the "Update AIT Account Master" process on the AIT Utilities window. This will update the AIT Account Master with all accounts in the current login company only, so you will have to log into each company to run this process on the new companies.
Automating AIT Account Master Check Links
This document contains the steps required to run or schedule the AIT Account Master check links process outside of the normal Great Plains interface. This applies only to systems that are using the MSSQL database option for Dynamics GP. This process is not necessary if you are using AIT 9.0 or higher, as these versions use triggers on the GL Account Master to self-maintain the AIT Account Master.
This process will help you create a SQL Job that you can schedule this process to run overnight or on weekends without user intervention, on a one-time or recurring schedule. The job takes into account current system activity, so that it will not run if users are logged into the accounting system. The job handles only basic SQL Job features, so it will not return any error code if it does not run to completion. Please refer to SQL Server documentation if you want to add additional features, such as error logging, notifications, etc., or if you need more assistance with SQL Jobs.
- Under the Server\Management\SQL Server Agent branch, select Jobs
- Right-click on Jobs and select New Job…
- Fill out the General with a Name and Description of your choice.
- Click on the Steps option, and click New.
- Fill out the New Job Step as follows, using the following code for the Command (you can copy and paste this into the Command window):
/* Declare variable to indicate any error */
declare @intResult int
/* Make sure there are no users logged into GP and then run the AIT stored procedure*/
select * from DYNAMICS.dbo.ACTIVITY
if @@rowcount = 0
exec itBuildITAccountMSTR 'sa', @intResult
- For the Database, choose ANY one company database. DO NOT CHOOSE THE DYNAMICS DATABASE, or the procedure will fail. You do not need to create a job for more than one database, as the procedure will loop through all company databases.
- Click OK
- Click OK again.
You can now run or schedule the job as required for your accounting system by creating a new schedule under the Schedules section.