RIP Apture - I Will Miss You

How many of my blog readers remember about my blog being enabled with Apture Highlights?

It's very sad that this beautiful feature is now no more. I got to know this long back, but the feature that I added on my blog still worked; until this morning.

You check out Apture's Site. All you got to see is a message from Apture Team as below:

I am not sure what to express, but only one thing which I feel right now is this: Google, you have successfully killed one more beautiful product.

RIP Apture. I loved your features. I loved the way you simplified my vocabulary learning. I loved the way you just simply popped out of the web page without taking me to any other site. I loved the way you were just a simple yet catchy product called Apture. I was so in awe that I put your logo on my blog and proudly claimed that my blog got "Aptured".

Not any more.

I will surely miss you.

VAIDY

Test Company Posting Journal File Destinations

One thing we all must remember while restoring a Live Company DB onto a Test Company DB is, that several places in GP tables, Company ID is stored. We have a SQL Script that will search ALL tables and ALL columns that contain the Inter ID (SQL ID for each GP database) and replace the live DB ID with test DB ID.

This post explains some specific cases where the Posting Journals of all transaction types are destined to a text file. Typically, in a multi-company environment, consultants would setup the path for all Posting Journals with the respective company Inter ID (or any folder name that uniquely identify each company).

I will explain you an ideal scenario, where this poses an issue.

I would restore live backup onto a Test Company and run the SQL script that will replace all live company ID references to test company ID. But my Posting Journals File destination is a simple string value, something like below (I have taken Purchase as a sample series):

:C:Journals/[CompanyID]/Purchase/[JournalReportName].txt

So if my live company ID is, for instance, VMLIVE, then the above path would like this:

:C:Journals/VMLIVE/Purchase/JournalReportName.txt

Whenever I post a purchase transaction, my Posting Journal detail would go and get append on this file, which I can audit at any point of time.

Now, consider that my Test Company ID is VMTEST. When I restore my live backup onto my test company and run the SQL script which replace live company reference with test company ID, everything would get fixed except this. Since the value stored in File Destination field in the Posting Journal Destinations table (SY02200) is NOT JUST the company ID, but the above Filename with Path.

After restoration and I post a purchase transaction on my test company, the posting information get appended on the file JournalReportName.txt on the path C:\Journals\VMLIVE\Purchase, which is WRONG. It's not just wrong. Your posting journal file gets dumped with test entries as well as live entries. If any client audits the Posting Journal files as part of their internal process(es), then it's a big trouble.

In such scenarios, where all posting journals are destined to a text file on a path identified specifically by a company ID or name, the GP Administrator must make sure that the field FILEXPNM (File Export Name) on table SY02200 (Posting Journal Destinations) must be properly updated before we post any test entires. Below is the simple UPDATE statement which would fix this:

UPDATE SY02200
SET FILEXPNM = REPLACE(FILEXPNM, '[LiveCompanyID/Name]', '[TestCompanyID/Name]')

Where [LiveCompanyID/Name] denotes the value which identifies your Live Company and [TestCompanyID/Name] denotes the value which identifies your Test Company.

VAIDY

To all Budding GP developers - Screen Resolution Does Matter

This is very silly point, but quite a huge dampener when it comes to Customer satisfaction.

I had given a simple customization as a trial to a budding developer as part of an exercise. I received the chunk to test it out.

What I could see was a huge window containing almost 50-75 fields (including labels, several text boxes, etc.) with lot screen real estate being wasted between fields. Secondly, this window went out of my test machine's screen space. The only possibility for that, in my opinion, is the resolution of the developer's machine was way too higher than my test machine's screen resolution.

Always, keep in mind, that when you develop a customization on a computer with higher resolution than that of the customers', it's going to be an issue. Customer would have to scroll horizontally each time to enter or view data on each field. And trust me, that's very irritable than a bad and buggy customization itself.

Always, try to limit the window size that would fit inside a 1024 X 768 screen resolution (that's the lowest that I feel is still existing in this universe), so it would fit in almost all resolutions.

This possibly be a non-issue soon with technology being so advanced nowadays and customers are willing to shed out some bucks on higher resolution & wide screen monitors.

But you never know.

VAIDY

Support Debugging Tool (SDT) v11.0 Build 16 is Released

We have a bundle of features and enhancements added to SDT and the new version v11.0 Build 16 is released.

David has a write up on this release with exhaustive details.

Sure this is going to be more useful than previous versions.

VAIDY

Welcome 2012...!

My heartfelt wishes to entire GP Community... Happy, Prosperous, Successful & Peaceful 2012...!!!

Most part of 2011 was quite dull, especially the later period, due to various professional and personal commitments. I would like to change it this year and be active as much as possible.

Wishes again for a wonderful 2012.

VAIDY

SQL Tip: Find Table(s) Having A Particular Column

This SQL query is my savior for a long time now. Thought I would just share it with all budding SQL developers.

Q: How to find the table(s) which contain a particular column which I know?
A: Below is the query that would do the trick.

SELECT name 
FROM sys.objects 
WHERE object_id IN 
(
SELECT object_id 
FROM sys.columns 
WHERE name = [columnname] --Column which you would want to locate
)

This query will return all tables which contains the specific column. And this query also would save your time immensely.

VAIDY

Capturing Images from a Microsoft Word Document (.docx) - David

That was awesome. Sheer awesome.

David shares with us about capturing images from a Microsoft Word Document (only .docx) file.

Honestly, I never even imagined that any .DOCX/.XLSX/.PPTX file is actually zip archived file in disguise.

Thank you so much, David. Another tip that's quite interesting and awesome.

VAIDY

Analytical Accounting Class Maintenance Error: This record has been created since your attempt to create it. Changes won't be saved.

WARNING: This post explains a method which is carried out in SQL and also affects an active table record. Consultants / Developers / End Users, who are not aware of GP & SQL processes, MUST NOT carry out this without an experienced consultant's / developer's supervision or assistance.

This one's another nightmare. I have no idea about how this happened, but now I know how to rectify this without affecting any of the system's normal behavior.

When a user tried creating an Analytical Accounting Class, the following error message was thrown at and the record did not get saved:

Analytical Accounting Class Master table is AAG00201 in SQL Server. It is also aaAccountClassMstr in AA dictionary. When I queried this table, it did not give me any indication that something could be wrong there.

Upon taking a SQL Profiler (I trust SQL Profiling to my life), I found that the flow stops at a table DYNAMICS..AAG00102, which is  (table AAG00102, database DYNAMICS) and technical name of this table is aaSyTableRow. When I queried this table, it was quite interesting. Below is a snapshot of the table records:

Logically, the table, DYNAMICS..AAG00102, stores the ID information for each type of record that is stored in Master tables. For instance, in our case, Account Class Master table is AAG00201. See the highlighted record in the above snapshot.

aaTableID: 201 (denotes Account Class Master table)
CMPANYID: 1 (denotes my Production Company ID)
aaRowID: 29 (denotes Last Class ID that was generated when I created Account Classes)

ISSUE: My last Account Class ID at the moment is 30. This is NOT the same as it is in my AA System Table Row (DYNAMICS..AAG00102) table. When the user tried creating the new AA Account Class, it took the aaRowID from AAG00102 table, which is 29, and that clashed with my Account Class Master table, since I already have one record with Class ID 29.

FIX: I realized that I must reset this aaRowID to the last generated Class ID, which I can get from Account Class Master table.

I took a backup of the table DYNAMICS..AAG00102 (SELECT * INTO ...) as a precautionary method. Then I executed the below query to reset it:


UPDATE DYNAMICS..AAG00102 --AA System Table Row Table
SET aaRowID = 30 --Last generated class ID in my case
WHERE aaTableID = 201 --Denotes Account Class Master table
AND CMPANYID = 1 --Denote my production company ID

Above query reset it to correct *Next Class ID*. User is now able to create new class IDs.

Issue is resolved, but how aaRowID on DYNAMICS..AAG00102 was not incremented when last Account Class ID got created, remains a mystery.

I am unable to recreate that scenario so far.

VAIDY

ERROR: A valid exchange rate could not be found. Choose Continue to open the Exchange Rate Entry window, where you can enter or select a valid date.

The following was the message one of the users received when she tried entering a Receiving Entry.

The currency was GBP and there was indeed a valid exchange rate that's been uploaded on a daily basis. The rate validity duration is set to "Week". That means, I have a rate which is valid till 23-Nov-2011.

The user mentioned to me that when she uses a particular batch, this message is thrown up. If any other batch is used, it picks up a rate without any issues.

When I checked the batch, I found the reason behind the issue.

Posting date of that batch was in future. This user had created a batch with the last day of this month as posting date, for which I don't have a valid rate in the system. When I select this batch and then enter the currency, GP does not find a valid rate and throws above error message.

By default, the exchange rate for a transaction is taken from either the transaction date OR the batch posting date, if a batch is assigned before the currency is entered.

The user do not post the batch itself and always post individual transaction. Keeping this on mind, I had to recommend her in changing the posting date of that batch as first date of the month, so we always have a valid exchange rate for all currencies.

It's quite a simple concept, but is very VERY critical.

VAIDY

Microsoft Dynamics GP Roadmap - GP "15" Added

Mariano has just published the updated Microsoft Dynamics GP Roadmap on his blog, with GP "15" added to the exciting list of GP versions to come in future.

I am absolutely delighted to see Microsoft's commitment to this wonderful product.

VAIDY