How to edit VBA when the client only has customization site licence

Well you learn something new every day and today our tame Dex developer is sat next to me in the office (some say he writes code in his sleep, others say he has dexterity tattooed on his heart, all we know is, he is called Jon) so picking up all sorts of things.


This one however is really simple and very useful. Often we will develop modifications for clients on our systems then import these using customization maintenance onto the client systems. This means that the client does not need to have modifier etc, just the customization site licence to run the mods.

However if the VBA on the mods need editing you need to export it back out onto your system to do this then re import it.

Well here is a simple tip. Export the modification out into a package file. Then open the package file up with notepad.

In the VBA code add in the word STOP. This command will tell the VBA to debug.

Re import the package file then open the screen or run the report. The STOP in the VBA code will then open the VBA window and allow you to edit the VBA.

Next handy tip DONT CLOSE THE VBA WINDOW if you do you will have to go through all this again to open the window so leave it open till you have finished and remember to remove the STOP from the code.

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

Integrating to Dynamics (GP) The Basics

Its only Monday and I have already been asked by two clients about integer values in tables and the best way to integrate to them without using Integration Manager. Add to this the couple of calls I had last week on the same subject I thought it would be worthy of a blog article.


The first question that gets asked is “what is the best way of integrating?”.

Well that depends on what you’re integrating. If you’re integrating static data (addresses, debtors, accounts) then SSIS and other such tools are fine. However if you are integrating transactional data I would suggest it is best to use a tool that will use the business logic of Dynamics (GP) and thus prevent you pumping rubbish into the system. These tools will usually use eConnect to access that business logic, tools like Scribe, Integration Manager and Smartconnect can all use eConnect.

The next question tends to be “what tables do I need to integrate to?”

Well there are several ways of finding this out and a combination of them will provide you with the best results.

• Install the SDK from additional products on your Dynamics (GP) CD. This gives excellent help on integrations and will advise you on tables and the values required for integer fields etc.

• Use the Direct to table import. Open the window in which you would normally enter the information manually, then in that window select Tools > Integrate > Table Import. This will then display a list of tables that this window accesses and therefore would be updated if you entered information.

• Resource descriptions. These can be found under Tools > Resource descriptions > table definitions. Here you can find all the tables within Dynamics (GP) and their structure. It will also show you their physical name (The one used in SQL) and their display name as well as their technical name (Usually the one used in error messages).

Using a combination of these you should be able to get all the info you need. This is a very basic look at integrations and does not cover all the options, it is more of a pointer in the right direction.

And always integrate into a test database first when you’re developing your integration.