Comparing tables In Dynamics (GP)

While we are on the subject of SQL tables, we often need to find records that are in one table but not another. This usually happens after there has been some kind of interruption during a posting process.

This can be done fairly easily with a nested SQL query.

The first thing you need to do is determine which tables you need to look in (Take a look at our previous article on Integration Basics). It is also worth remembering the process flow through Dynamics( GP) (Work>Open>History).

Work tables will start with a 1 after the two alphabetic characters that represent the module/series.

Open tables start with a 2

And you guessed it, history tables start with a 3.

Keys tables etc. start with a 0.

Once you have determined which tables you need to determine which field value you are going to compare. As a rule of thumb, its best to use primary key constraints. You can determine the primary key by using sp_helpindex TABLENAME (Replace TABLENAME with the table i.e. GL10000).

Also it worth remembering that vouchernumber is a unique reference in payables and document number is a unique reference in receivables.

Journal number is unique in GL but only for that year as it is possible to start again with journal numbers after year end in Dynamics (GP) if you so require.

So we now have our tables and which field we are going to compare.

Now we create a nested SQL query

Select VCHRNMBR from PM20000


where VCHRNMBR not in


( Select VCHRNMBR from PM30200)


go

The above statement will return all the records from the PM20000 where they are not in the PM30200. If you wanted to return records in both just drop the not from the statement.

It gets slightly more complex if you are comparing to the master keys table as the Vouchernumber is the control number in this table so I would select into a temp table first and change the field name during the process to match that of the other table. The temptable can be called anything you like as long as it is unique and starts with a # to designate it as a temp table. Mine is #TCLCONTROLNUMB. The temp table will remain in existence as long as the query window is open. Once you close the query the temp table is removed.

Select VCHRNMBR as CNTRLNUM into #TCLCONTROLNUMB from PM20000


go


Select CNTRLNUM from #TCLCONTROLNUMB


where CNTRLNUM not in


(Select CNTRLNUM from PM00400)


go