Getting data from ISV tables onto reports without needing to create alternate reports

As a Dexterity developer, I have written a number of customisations to Microsoft Dynamics GP which add additional fields to the system. These additional fields are often have a relationship to existing data in Dynamics GP. It is quite common to store the additional data in a parallel table (sometimes called companion or shadow table)….

4

Cross Linked Companies, Pathnames and Everything

A long time ago, in an ERP system far far away, data was stored in tables which in turn were stored as files in folders. Yes, before Great Plains Dynamics became Great Plains Dynamics C/S+ for SQL (version 3.15/3.17), we had to specify pathnames to dictate where the Ctree or Btrieve files for each series…

1

Quick Tip: Copying larger amounts of data in SQL Server quickly

I recently posted the Quick Tip: Copying small amounts of data in SQL Server quickly. This quick tip used the clipboard to copy data between tables as an alternative to using DTS (Data Transformation Services), SSIS (SQL Server Integration Services) or Transact-SQL (T-SQL). In this post, I thought I would show a fairly simple way to…

2

Quick Tip: Copying small amounts of data in SQL Server quickly

Last week, I was working on a Microsoft Dynamics GP update case where we had to re-upgrade one company from the previous service pack to the current service pack. To allow Dynamics GP Utilities to re-upgrade one company there are 3 things we need to do. Restore the previous version of the Company database. Restore…

5

Quick Tip: Solving Dexterity Trigger Clashes

In a recent support case, I had the rare situation where Dexterity Triggers clashed.  Dexterity allows third party developers to create triggers against various events and scripts in a Dexterity application (like Microsoft Dynamics GP). There are times where more than one third party product registers a trigger against the same event or script. Most…

6

Failed eConnect document leaves orphaned records

I assisted on an eConnect incident today that was quite interesting and I thought I’d share the case and the solution. The issue: The ISV was integrating a Payables Invoice using eConnect 10.0. The XML document had 2 distributions and the invoice data. What the ISV found was that there was an issue in the…

3

Quick Tip: Working with VBA, Extender and Sales Transaction Entry

I have completed the development project I was working last week and have another Quick Tip that came out of the project. For the previous Quick Tips related to this project have a look at Quick Tip: Passing Variables to SQL Server and Quick Tip: Passing Date and Time Variables to SQL Server. The project involved…

1

Quick Tip: Passing Date and Time Variables to SQL Server

As a follow on from yesterday’s post, Quick Tip: Passing Variables to SQL Server, I wanted to discuss the related topic about passing of date and time variables. There are two main issues we see with passing Dates and Times and both can cause SQL Server to throw the following exception: /*  Date: 12/15/2011  Time:…

4

Quick Tip: Passing Variables to SQL Server

Wow, the week is almost over and I am yet to post something. This week I was working on a small development project using Visual Basic for Applications (VBA) to execute some commands at the SQL Server level after some user interface events. The code to be executed needed to have values from the windows passed through…

7

SQL Server Error: Cannot resolve the collation conflict

Today I have a post on the solution to a tricky SQL issue. When working with SQL Server and creating a join that links tables in two databases, all goes well when the two databases have the same collation. However, if the databases have different collations the query it will fail in a writhing blubbery gelatinous…

9

Capturing Images from a Microsoft Word 2007/2010 Document

Here is a neat trick that I just used to copy images from a Microsoft Word 2007/2010 *.docx document. I was given a Word document of a blog article to be posted on the blog. To get the text into the blog post editor (without extra html) is easy…. Just cut & paste into Notepad.exe…

4

Do not use the DEX_ROW_ID table column in your Customizations and Integrations

Today, I responded on a post on the Partner Forum asking “Can the DEX_ROW_ID change for a record over time?”. In this case the partner had seen the values for DEX_ROW_ID change on the GL20000 (GL_YTD_TRX_OPEN) and GL30000 (GL_Account_TRX_HIST) tables. This caused problems for an integration to an external system that used the DEX_ROW_ID as a…

11

Quick Tip: Using SQL to return the number of records in a Table

Now I am sure there are people out there that will be thinking that this is a silly topic for a blog article. Surely everyone knows how to get the number of records from a SQL table using the COUNT(*) function? Well, you would be correct that most people do know the COUNT(*) method as shown…

11

Quick Tip: Hidden Forms, Command Forms and Dictionary Context issues

This quick tip follows on from my previous post: Quick Tip: Updating Dexterity customizations to work with Security Tasks and Security Roles. There are times when there is a requirement to have some fields on a hidden form (a form with non of the windows set to AutoOpen). One example is when you want code…

4

Quick Tip: Updating Dexterity customizations to work with Security Tasks and Security Roles

Another Quick Tip for Dexterity developers. This one discusses what changes need to be made to an integrating Dexterity application when upgrading from pre version 10.0 code to version 10.0 or GP 2010 to handle the changes in the security model. When version 10.0 was released it changed the security model from the old user and…

3

Quick Tip: Using substring() with a text field datatype

An interesting tip today. Over the last few months there has been a thread on the Partner Forum (GP Notes Lockdown) with the request to prevent changes to the existing text on the Record Notes feature of Microsoft Dynamics GP.  The functionality request has two parts: Prevent existing notes from being deleted. Prevent existing text…

5

Quick Tip: Best practice for using sum range table command

I came across an interesting issue yesterday relating to the Dexterity sum range command. We had a SQL datetime conversion error caused by a sum range command. This was the error message: A sum range operation on table ‘SVC_Serial_Lot_Work_HIST’ failed accessing SQL data. More Info: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date…

4

21 Reasons why every workstation should have the Support Debugging Tool installed

As I perform my role in the Asia Pacific Microsoft Dynamics GP support team I am finding that more and more sites already have the Support Debugging Tool for Microsoft Dynamics GP installed. This makes my job much easier as there are many features of the tool that help a support engineer diagnose and resolve technical…

3

Public Service Announcement: Don’t Trust Hard Drives

A couple of weeks ago, a good friend of mine posted on his Facebook account that his 1TB external hard drive had failed and that it contained a lot of data that was not stored anywhere else. In particular, there was 10 years worth of digital photos. All now lost. I did have a look at…

1

Quick Tip: Using eConnect to update Customer or Vendor Addresses with a blank Address Line 3

I had a case this week that was asking a fairly commonly asked question about integrating address information using eConnect. The scenario is that we want to update a customer or vendor address and the previous address had three lines in the address and the new address only has two lines. When the update is processed, Address Lines…

5