RW – Working with POSTNET barcodes for US zip codes

The Postal Numeric Encoding Technique (POSTNET) is a barcoding system developed by the United States Postal Service to assist with automatic mail sorting and routing. The POSTNET barcode uses a combination of half-bars and full-bars to encode the zip code that appears on a mail piece. The barcode starts and ends with a full bar (often called a guard rail or frame bar and represented as the letter “S” in the USPS TrueType Font).

In the United States, zip codes can be of 5 digits, 9 digits (also known as Zip+4), or 11 digits in the case of a specific delivery point. Each digit in the zip code is represented by 5 bars. Therefore a 5-digit zip code is represented by 25 bars. In addition, to ensurePOSTNET accuracy during mail processing, a check digit, which is five bars, is calculated and added to the zip code. Hence a 5-digit zip code would render a total of 32 bars: 25 bars for the 5-digit zip code + 5 bars for the check digit + the 2 enclosing guard rail bars. Hence, Zip+4 uses 52 bars, and a delivery point uses 62 bars.

To calculate the check digit, each digit in the zip code is added. The result is then subtracted from the nearest multiple of 10. For example, if each digit in the zip code 33076 is added, 3+3+0+7+6 = 19, and we subtract from the nearest multiple of 10, number 20, then the check digit is 1. Mathematically speaking 10 – (digit-sum mod 10). This will yield the following S330761S. This number is then represented by the following barcode (including the guard rails, represented by “S”):

While based on a binary system, the weight of each bar is different than in standard binary. The weight of each bar is as follows:

The following illustration shows are representation of all numbers:

To add zip code barcoding capabilities to a Report Writer report, for example an Invoice or a Check, you must first download and install the fonts. You can get a set of fonts from here. To install the fonts (the TTF files in the zipped file), extract the fonts to your My Documents folder, then copy them to your Windows\Fonts folder.

1) Open Report Writer and select the SOP Blank Invoice Form. Click the Layout button to open the report in the Layout window.

2) Add the RM_Customer_MSTR_ADDR.Zip field to both Report Header and Page Header sections. Set the fields properties to Invisible.

3) Create a string calculated field called (C) Postnet. Assign the RM_Customer_MSTR_ADDR.Zip field to this calculated field as shown in the picture:

4) Add the (C) Postnet calculated field to the Report Header and Page Header sections. Change the font to IDAutomationSPOSTNET. By now, your report layout should look like this:

5) Add the report to VBA. Go to Tools > Add Report to VBA.

6) Select all 4 fields, the 2 on the report header and the 2 on the page header and add them to VBA. Go to Tools > Add Fields to VBA.

7) Open the VBA Editor, locate the SOPBlankInvoiceForm (Report) object in the Project Explorer , double-click and add the following code:

Public Function EncodeZip(pZip As String) As String    Dim i As Integer    Dim sZip As String    Dim digitSum As Integer    Dim checkDigit As Integer

    sZip = "S"    digitSum = 0

    For i = 1 To Len(pZip)        If IsNumeric(Mid(pZip, i, 1)) Then            ' Concat the digit            sZip = sZip & Mid(pZip, i, 1)

            ' Add up the digits            digitSum = digitSum + CInt(Mid(pZip, i, 1))        End If    Next i

    ' Calculate check digit    checkDigit = 10 - (digitSum Mod 10)    sZip = sZip & CStr(checkDigit) & "S"

    EncodeZip = sZipEnd Function

Private Sub Report_BeforePH(SuppressBand As Boolean)    Dim sZip As String

    sZip = Zip    sZip = EncodeZip(sZip)    CPostnet = sZipEnd Sub

Private Sub Report_BeforeRH(SuppressBand As Boolean)    Dim sZip As String

    sZip = Zip    sZip = EncodeZip(sZip)    CPostnet = sZipEnd Sub

Compile the code.

Return to Microsoft Dynamics GP saving all changes as you exit.

9) Grant security to the modified report. Print an invoice to test.

I hope you found this article interesting and the implementation very simple. You can find the fonts and package file for this project at the bottom of this article.

Downloads

Postnet Fonts – Click here
v10 SOP Blank Invoice Form report – Click here

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

How many Report Options can I create for any given report?

I fielded this question just recently in the newsgroup, and have to admit, I had to dust off my old training manuals and do some inquiries in my Dexterity help file. Bottom line, this could well be a question worthy of a certification exam.

Straight to the answer: you can have up to 32,767 report options for any given report. The number comes from the size limitations imposed to the Dexterity drop-down list control.

You may be saying, “but, I was told that you can have 32, where does the number 32 come from?“. 32 is the number of report options that can be assigned to any given report group. Now this limitation stood up to version 8 (see KB article 849505) and I am not sure it has changed for v9 and v10, but if you test it out and find a different value, I would love to hear from you.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

SQL Server Management Studio Standard Reports

If you have worked with SQL Server Management Studio (SSMS) in either Microsoft SQL Server 2005 or Microsoft SQL Server 2008, you may have inadvertly overlooked one of its key features: Standard Reports.

Standard Reports are Reporting Services (SSRS) reports that can provide all sort of statuses and and information about the database engine and its management components and well as the databases themselves in real time — the reports are refreshable! When executed, the reports are embedded in tabs within a new SSMS tab. As a consultant, I find these reports particularly useful when attempting to establish the health of a Microsoft Dynamics GP SQL Server installation. I can immediately relay critical SQL Server performance information to my clients and suggest preventative or corrective actions to mitigate the issues, saving them money in the process.

So lets take a look at the available reports by node…

Node	Report
Server	Server Dashboard
Server	Configuration Changes History
Server	Schema Changes History
Server	Scheduler Health
Server	Memory Consumption
Server	Activity – All Blocking Transactions
Server	Activity – All Cursors
Server	Activity – Top Cursors
Server	Activity – All Sessions
Server	Activity – Top Sessions
Server	Activity – Dormant Sessions
Server	Activity – Top Connections
Server	Top Transactions by Age
Server	Top Transactions by Blocked Transactions Count
Server	Top Transactions by Locks Count
Server	Performance – Batch Execution Statistics
Server	Performance – Object Execution Statistics
Server	Performance – Top Queries by Average CPU Time
Server	Performance – Top Queries by Average IO
Server	Performance – Top Queries by Total CPU Time
Server	Performance – Top Queries by Total IO
Server	Server Broker Statistics
Server	Transaction Log Shipping Status
Database	Disk Usage
Database	Disk Usage by Top Tables
Database	Disk Usage by Table
Database	Disk Usage by Partition
Database	Backup and Restore Events
Database	All Transactions
Database	All Blocking Transactions
Database	Top Transactions by Age
Database	Top Transactions by Blocked Transactions Count
Database	Top Transactions by Locks Count
Database	Resource Locking Statistics by Object
Database	Object Execution Statistics
Database	Database Consistency History
Database	Index Usage Statistics
Database	Index Physical Statistics
Database	Schema Changes History
Database	User Statistics
Database	Active Full-Text Catalogs
Logins	Login Statistics
Logins	Login Failures
Logins	Resource Locking Statistics by Logins
Management	Tasks
Management	Number of Errors
Notification	Services General
SQL Server Agent	Job Steps Execution History
SQL Server Agent	Top Jobs

To access a specific report, just right-click on the desired node then choose Reports > Standard Reports, select the desired report. The following is an example of the navigation to the Server node reports.

Lets take a look at some sample standard reports…

Server Dashboard report (server > Reports > Standard Reports > Server Dashboard)

This report provides detailed configuration information including, but not limited to the SQL Server startup time, product version and edition, server collation, the number of processors in used by the SQL Server instance, CPU usage by database, and number of active databases.

Disk Usage by Top Tables report (database > Reports > Standard Reports > Disk Usage by Top Tables)

This reports shows vital table information such as the number of records in the table, the amount of disk space reserved for the table, spaced occupied by data and indexes, and the unused space. This information can be used to plan for disk space optimization and establish whether it will be necessary to increment the number of partitions or relocate the databases.

Backup and Restore Events report

This one is got to be one of the most important database level reports as it shows statistics about the backups completed on a specific database: average time, size of backups, whether the database backup was complete or differential, etc.

I hope you like this SQL Server gem and start to explore these reports. There is valuable information that can be used to administer your Dynamics GP and overall SQL Server environment.

Until next post!

MG.-
Mariano Gomez, MIS, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Developing for Dynamics GP Weekly Summary

After a long absence, my friend David Musgrave is slowly regaining his writing pulse. Please check out some of his cool articles this week, but first and foremost let him know what you think about them. I always encourage everyone to test and play with some of the code we provide in these articles. They are a good way to get acquainted with Microsoft Dynamics GP functionality. So let’s get on with the articles:

1. Microsoft Dynamics GP 10.0 Service Packs, Hotfixes and Payroll Compliance/Year End Updates May Damage the Modified Reports and Forms. After a few of these errors circulating on the newsgroups, it has been confirmed by Microsoft development team. The payroll year end update will in fact damage your modified forms and reports. I have a theory on this one: since the YE update is also inclusive of service pack 3, it will be necessary to take all the precautions layed out when applying a service pack, that is, export ALL your customizations to a package file BEFORE applying any patches. Read more here.

2. Microsoft Dynamics™ GP 10.0 Bootcamp (Australia). Now, if you ever wanted to go to a Dynamics GP training class, won’t it be a dream to do so in the land down under? Now, that’s what I call a Dynamics GP training with style. Read more here.

3. eOne.Dynamics.GP.ExcelBuilder.Engine.dll Exception. If you have received this error after applying Microsoft Dynamics GP 10 Service Pack 2 and above and you are currently using SmartList Builder and Excel Builder, make sure you take a look at this article and the fix by reading more here.

4. Hybrid – Adding Named Printers control to Reports using VBA. Wondering how to get Named Printers to work with unsupported reports? David answers this one with a cool VBA customization. Best of all, you can download sample code! To play with this code, click here.

5. WorldMaps Tracking Added. As if my uber friend wasn’t all that uber geek already, he adds a cool hits tracker on his blog with the cool WorldMaps beta product from Structure Too Big. I have to confess, I will be adding this one pretty soon to my blog! I find it fascinating to know where your readers are concentrated, which helps when developing content for the blog. For more info, click here.

6. Modifier – Reading and Writing Data with ADO Example. It does not matter how many of these we do, I always find Modifier with VBA fascinating and a very good alternative to Dexterity customizations when possible. Now, don’t get me wrong. I love Dexterity, but I also love the portability offered by VBA customizations too. To read more, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to determine if a report or a form has been modified

Business Situation

How many times have you walked into a GP implementation done by a previous VAR and cannot establish what changes have been done on a report, if any? Or how many times have you come across forms and reports dictionaries with tons of objects and cannot tell by simply looking at these if they have been changed or not? Or have you made some changes to a report a few years aback and now cannot remember what these changes were? I get this question every once in a while and finally someone was keen enough to post it on the Dynamics GP public newsgroup.

Solution

Let me start by saying that ALL modified forms and reports should ALWAYS be backed up in the form of package files, and that ALL these package files should be stored in a source code control repository — for example, Visual Source Safe — and versioned if all possible, with notes on all changes done from version to version. However, this is not always possible, especially if the company happens to be a small company with limited technical and software development resources — unless of course, the business happens to do software development ).

In order to establish what changes have been done to a form or a report, without having a source control repository, you can use old fashioned Microsoft Word… well, I will be using Microsoft Word 2007 for this example. In addition, we will use a slightly modified version of the SOP Blank Invoice report.

*Click on image to enlarge

1) Export the modified report to a package file. Go to Microsoft Dynamics GP > Tools > Customize > Customization Maintenance. Highlight the SOP Blank Invoice report and click on Export. Save the file as SOPBlankInvoice_Modified.package

*Click on image to enlarge

2) From a workstation not pointing to the REPORTS.DIC dictionary file containing the modified report or from a standalone copy of GP, say for example the one you carry on your laptop, print the SOP Blank Invoice report to screen. Go to Transactions > Sales > Sales Transactions and choose an invoice. Print to screen. Click on Modify to open the report with Report Writer. Once the report is shown in the Report Layout window, return to Dynamics GP.

*Report with no customizations. Click on image to enlarge

3) Repeat step 1 on your standalone environment, this time saving the file as SOPBlankInvoice_Original.package. Move the SOPBlankInvoice_Modified.package file to the same directory with the SOPBlankInvoice_Original.package file. This will both files easily accessible.

*Click on image to enlarge

4) Open Microsoft Word 2007. Click on the Review menu item.

*Click on image to enlarge

5) Click on the Compare option. Open your SOPBlankInvoice_Original.package in the Original document column, open SOPBlankInvoice_Modified.Package in the Revised document. Changes can be labeled with markers to allow for easy identification. Word will run the comparison and highlight any changes between the two documents

*Click on image to enlarge

Remember: it’s not about the tools, it’s about how you use these tools to meet your needs. I hope this article provides a mechanism to quickly and accurately identify changes between original reports and customized reports and help you get a headstart when working at customers and customizations you have not created — or may have and don’t recall .

Until next post!

MG.-
Mariano Gomez, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Deck the halls with Report Writer

Business Situation

The holidays are around the corner and your organization is looking to send season greetings on each invoice to all customers. However, the traditional Microsoft Dynamics GP text comment is all too dull and does not reflect the image or the character of your organization. Your marketing team has spent some good hours designing a holiday banner and would like to see it on the invoices at all cost.

Lets take a look at some cool ways to deck the halls with Report Writer.

Solution

For this example, we will work with the SOP Blank Invoice Form report.

1) Open the Sales Transaction Entry window, select an invoice and click on the print button. When the options window is displayed, select the Blank form type and then print to screen.

* Click on image to enlarge

2) Press CTRL + F9 on your keyboard to modify the current report. This will open Report Writer with the report in the layout window.

3) We will now add a picture to the Picture library to include on our invoice. In Report Writer, go to the Resources menu and select Pictures. This will open the Picture Definition window.

NOTE: Due to Dexterity limitations in color management, you will need to reduce the number of colors of your graphic image to 16 colors (4 bits per pixel). The file size must be less than 32KB which corresponds to the size limitation imposed by Dexterity.

4) Click the New button to enter a picture name, say Season_Greetings. Click inside the gray area, and paste the image from the clipboard with CTRL+V. Click OK to save the image.

NOTE: The picture must be loaded with a graphics editor, like Paint for example, and have been captured with the Copy feature of that editor. This will send the image to the clipboard.

5) To add the image to the report layout, click the Picture tool button in the Toolbox window. This will activate the visual cue indicator.

6) Click on the area of the report where the picture will be placed, for example on the footer. Size according to your display needs.

7) Return to Dynamics GP and grant security to your modified report. If the report has been previously modified, you are done!

Execute the report and verify it meets the visual requirements and complies with your company expectations.

RELATED ARTICLES:

How to get large amounts of text to look good on a report – by David Musgrave @ Developing for Dynamics GP.

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to automatically send all posting reports to screen in Microsoft Dynamics GP

My buddy and fellow MVP Frank Hamelly had been excited about a trick he did for one of his clients, so I figured I would materialize his trick in this article.

Business Situation

In this day and age where the economy seems to be at the center piece of all business decisions, not even Dynamics GP has escaped the turmoil. Customers are demanding more efficient use of all business resources and paper is certainly one of those elements that lend themselves for true money saving. Frank’s customer had wondered if all posting reports could automatically be directed to screen without manually having to go into each series posting option to change the output. In addition, he requested the system not ask for the destination of the report each time it was going to be printed.

Background

The Posting Setup window (Microsoft Dynamics GP > Tools > Setup > Posting > Posting) displays all posting reports based on the series and transaction origin. These settings are then stored in the Posting Journal Destinations table (SY02200) under the company database.

In order to understand how data is stored in the SY02200 table, run the following query against your company database:

select PRNTJRNL, SERIES, TRXSOURC, PTGRPTNM, ASECTMNT, PRTOPRNT, PRTOSCNT, PRTOFLNT from sy02200GO

In turn, the query will return the following result set:

PRNTJRNL SERIES TRXSOURC                  PTGRPTNM                        ASECTMNT PRTOPRNT PRTOSCNT PRTOFLNT-------- ------ ------------------------- ------------------------------- -------- -------- -------- --------1        6      Manual Checks             Benefit Register                1        0        0        01        6      Manual Checks             State Tax Register              1        0        0        01        6      Manual Checks             Local Tax Register              1        0        0        01        6      Manual Checks             Tip Allocation Posting Register 1        0        0        00        6      Manual Checks             Shift Code Register             1        0        0        01        6      Period-End Reports        FUTA Posting Register           1        0        0        01        6      Period-End Reports        SUTA Posting Register           1        0        0        01        6      Period-End Reports        Workers' Comp Posting Register  1        0        0        01        6      Void Checks               Check Register                  1        0        0        01        6      Void Checks               Check Posting Register          1        0        0        00        6      Void Checks               Vacation/Sick Time Acc Register 1        0        0        01        6      Void Checks               Pay Type Register               1        0        0        01        6      Void Checks               Department Register             1        0        0        01        6      Void Checks               Position Register               1        0        0        01        6      Void Checks               Deduction Register              1        0        0        01        6      Void Checks               Benefit Register                1        0        0        01        6      Void Checks               State Tax Register              1        0        0        01        6      Void Checks               Local Tax Register              1        0        0        01        6      Void Checks               Tip Allocation Posting Register 1        0        0        00        6      Void Checks               Shift Code Register             1        0        0        01        6      Computer Checks           Checkbook Posting Journal       1        0        0        01        6      Manual Checks             Checkbook Posting Journal       1        0        0        01        6      Void Checks               Checkbook Posting Journal       1        0        0        01        5      Transaction Entry         Inventory Transaction Journal   1        0        0        01        5      Transfer Entry            Inventory Transfer Journal      1        0        0        01        5      Transaction Entry         Inventory Transaction Edit List 1        0        0        01        5      Transfer Entry            Inventory Transfer Edit List    1        0        0        01        5      Transaction Entry         Transaction GL Register         1        0        0        01        5      Transfer Entry            Transfer GL Register            1        0        0        01        5      Transaction Entry         Cost Variance Journal           1        0        0        01        5      Transfer Entry            Cost Variance Journal           1        0        0        01        3      Invoice Entry             Invoice Posting Journal         1        0        0        01        3      Invoice Entry             Dist Breakdown - Detail         1        0        0        01        3      Invoice Entry             Dist Breakdown - Summary        1        0        0        01        3      Invoice Entry             Inventory Sales Register        1        0        0        01        3      Invoice Entry             Salesperson Register            1        0        0        01        3      Invoice Entry             Cost Variance Journal           1        0        0        01        3      Invoice Entry             Checkbook Posting Journal       1        0        0        01        3      Sales Transaction Entry   Sales Posting Journal           1        0        0        01        3      Sales Transaction Entry   Dist Breakdown - Detail         1        0        0        00        3      Sales Transaction Entry   Dist Breakdown - Summary        1        0        0        00        3      Sales Transaction Entry   Inventory Sales Register        1        0        0        00        3      Sales Transaction Entry   Salesperson Register            1        0        0        01        3      Sales Transaction Entry   Cost Variance Journal           1        0        0        01        3      Sales Voided Transactions Sales Voided Posting Journal    1        0        0        01        3      Sales Deposits            Sales Deposits Journal          1        0        0        01        3      Sales Transaction Entry   Checkbook Posting Journal       1        0        0        01        3      Sales Deposits            Checkbook Posting Journal       1        0        0        01        2      Bank Deposit Entry        Bank Deposit Posting Journal    1        0        0        01        2      Bank Transaction Entry    Bank Trx Posting Journal        1        0        0        01        2      Reconcile Bank Statement  Bank Adj Posting Journal        1        0        0        01        2      Reconcile Bank Statement  Cleared Transactions Journal    1        0        0        01        2      Reconcile Bank Statement  Reconciliation Posting Journal  1        0        0        01        2      Bank Transfer Entry       Bank Transfer Posting Journal   1        0        0        01        4      Purchasing Voided Trx     Purchasing Voided Journal       1        0        0        01        4      Receivings Trx Entry      Receivings Posting Journal      1        0        0        01        4      Receivings Trx Entry      Receivings Distribution Detail  1        0        0        01        4      Receivings Trx Entry      Cost Variance Journal           1        0        0        01        4      Receivings Trx Entry      Back-Ordered Items Received     1        0        0        01        4      Receivings Voided Trx     Receivings Voided Journal       1        0        0        01        4      Edit PO Status            Edit PO Status Distributions    1        0        0        01        4      Edit PO Status            Edit PO Variance Journal        1        0        0        01        4      Purchasing Invoice Entry  POP Invoice Posting Journal     1        0        0        01        4      Purchasing Invoice Entry  Invoice Distribution Detail     1        0        0        01        4      Purchasing Invoice Entry  Invoice Cost Variance Journal   1        0        0        01        4      Purchasing Invoice Entry  Back-Ordered Items Received     1        0        0        01        4      Voided Purchase Invoice   Voided Purchase Invoice Journal 1        0        0        01        7      Equipment Log Entry       Dist Breakdown - Detail         1        0        0        01        7      Equipment Log Entry       Dist Breakdown - Summary        1        0        0        01        7      Misc. Log Entry           Misc. Log Posting Journal       1        0        0        01        5      Assembly Entry            Assembly Posting Journal        1        0        0        01        5      Assembly Entry            Assembly Distribution Detail    1        0        0        01        5      Depot Adjustment          Transaction GL Register         1        0        0        01        5      Depot Adjustment          Cost Variance Journal           1        0        0        01        5      RMA Adjustment            Inventory Transaction Journal   1        0        0        01        5      RMA Adjustment            Transaction GL Register         1        0        0        01        5      RMA Adjustment            Cost Variance Journal           1        0        0        01        5      RTV Adjustment            Inventory Transaction Journal   1        0        0        01        5      RTV Adjustment            Transaction GL Register         1        0        0        01        5      RTV Adjustment            Cost Variance Journal           1        0        0        01        5      SC C-Line Adjustment      Inventory Transaction Journal   1        0        0        01        5      SC C-Line Adjustment      Transaction GL Register         1        0        0        01        5      SC C-Line Adjustment      Cost Variance Journal           1        0        0        01        5      SC Inventory Adjustment   Inventory Transaction Journal   1        0        0        01        5      SC Inventory Adjustment   Transaction GL Register         1        0        0        01        5      SC Inventory Adjustment   Cost Variance Journal           1        0        0        01        5      SC R-Line Adjustment      Inventory Transaction Journal   1        0        0        01        5      SC R-Line Adjustment      Transaction GL Register         1        0        0        01        5      SC R-Line Adjustment      Cost Variance Journal           1        0        0        01        4      Returns Trx Entry         Returns Posting Journal         1        0        0        01        4      Returns Trx Entry         Returns Distribution Detail     1        0        0        01        4      Returns Trx Entry         Returns Cost Variance Journal   1        0        0        01        7      Timesheet Entry           Timesheet Posting Journal       1        0        0        01        7      Timesheet Entry           Dist Breakdown - Detail         1        0        0        01        7      Timesheet Entry           Dist Breakdown - Summary        1        0        0        01        7      Equipment Log Entry       Equipment Log Posting Journal   1        0        0        01        5      Assembly Entry            Cost Variance Journal           1        0        0        01        2      Currency Revaluation      Revaluation Register-Detail     1        0        0        01        2      Currency Revaluation      Revaluation Register-Summary    1        0        0        01        2      Currency Revaluation      Revaluation Journal             1        0        0        01        2      Euro Conversion           GL Euro Conv. Journal           1        0        0        01        2      Euro Conversion           GL Euro Conv. Register-Detail   1        0        0        01        2      Euro Conversion           GL Euro Conv. Register-Summary  1        0        0        01        2      Reconcile Bank Statement  Outstanding Transaction Report  1        0        0        01        5      SC Inventory Transfer     Inventory Transfer Journal      1        0        0        01        5      SC Inventory Transfer     Transfer GL Register            1        0        0        01        5      SC Inventory Transfer     Cost Variance Journal           1        0        0        01        5      Depot Transfer            Inventory Transfer Journal      1        0        0        01        5      Depot Transfer            Transfer GL Register            1        0        0        01        5      Depot Transfer            Cost Variance Journal           1        0        0        01        5      RMA Transfer              Inventory Transfer Journal      1        0        0        01        5      RMA Transfer              Transfer GL Register            1        0        0        01        5      RMA Transfer              Cost Variance Journal           1        0        0        01        5      RTV Transfer              Inventory Transfer Journal      1        0        0        01        5      RTV Transfer              Transfer GL Register            1        0        0        01        5      RTV Transfer              Cost Variance Journal           1        0        0        01        5      SC R-Line Transfer        Inventory Transfer Journal      1        0        0        01        5      SC R-Line Transfer        Transfer GL Register            1        0        0        01        5      SC R-Line Transfer        Cost Variance Journal           1        0        0        01        5      Depot Adjustment          Inventory Transaction Journal   1        0        0        01        4      Currency Revaluation      Revaluation Register-Summary    1        0        0        01        4      Euro Conversion           PM Euro Conv. Journal           1        0        0        01        4      Euro Conversion           PM Euro Conv. Register-Detail   1        0        0        01        4      Euro Conversion           PM Euro Conv. Register-Summary  1        0        0        01        3      Receivables Sales Entry   Sales Entry Posting Journal     1        0        0        01        3      Receivables Sales Entry   Sales Detail GL Register        1        0        0        01        3      Receivables Sales Entry   Sales Summary GL Register       1        0        0        01        3      Receivables Cash Receipts Cash Receipts Posting Journal   1        0        0        01        3      Receivables Cash Receipts Cash Detail GL Register         1        0        0        01        3      Receivables Cash Receipts Cash Summary GL Register        1        0        0        01        3      Receivables Apply Doc.    Discounts/Writeoffs Journal     1        0        0        01        3      Receivables Apply Doc.    Apply Detail GL Register        1        0        0        01        3      Receivables Apply Doc.    Apply Summary GL Register       1        0        0        01        3      Voided Trx Maintenance    Voided Trx Posting Journal      1        0        0        01        3      Voided Trx Maintenance    Voided Trx Detail GL Register   1        0        0        01        3      Voided Trx Maintenance    Voided Trx Summary GL Register  1        0        0        01        3      Transfer Commission       Commissions Posting Journal     1        0        0        01        3      Receivables Sales Entry   Checkbook Posting Journal       1        0        0        01        3      Receivables Cash Receipts Checkbook Posting Journal       1        0        0        01        3      Voided Trx Maintenance    Checkbook Posting Journal       1        0        0        01        3      Currency Revaluation      Revaluation Journal             1        0        0        01        3      Currency Revaluation      Revaluation Register-Detail     1        0        0        01        3      Currency Revaluation      Revaluation Register-Summary    1        0        0        01        3      Euro Conversion           RM Euro Conv. Journal           1        0        0        01        3      Euro Conversion           RM Euro Conv. Register-Detail   1        0        0        01        3      Euro Conversion           RM Euro Conv. Register-Summary  1        0        0        01        6      Computer Checks           Check Register                  1        0        0        01        6      Computer Checks           Check Posting Register          1        0        0        00        6      Computer Checks           Vacation/Sick Time Acc Register 1        0        0        01        6      Computer Checks           Pay Type Register               1        0        0        01        6      Computer Checks           Department Register             1        0        0        01        6      Computer Checks           Direct Deposit Check Register   1        0        0        01        6      Computer Checks           Direct Deposit Register         1        0        0        01        6      Computer Checks           Direct Deposit Trxs Register    1        0        0        01        6      Computer Checks           Position Register               1        0        0        01        6      Computer Checks           Deduction Register              1        0        0        01        6      Computer Checks           Benefit Register                1        0        0        01        6      Computer Checks           State Tax Register              1        0        0        01        6      Computer Checks           Local Tax Register              1        0        0        01        6      Computer Checks           Tip Allocation Posting Register 1        0        0        00        6      Computer Checks           Shift Code Register             1        0        0        01        6      Manual Checks             Check Register                  1        0        0        01        6      Manual Checks             Check Posting Register          1        0        0        00        6      Manual Checks             Vacation/Sick Time Acc Register 1        0        0        01        6      Manual Checks             Pay Type Register               1        0        0        01        6      Manual Checks             Department Register             1        0        0        01        6      Manual Checks             Position Register               1        0        0        01        6      Manual Checks             Deduction Register              1        0        0        01        2      Clearing Entry            Clearing Posting Journal        1        0        0        01        2      General Entry             General Posting Journal         1        0        0        01        2      Quick Entry               Quick Posting Journal           1        0        0        01        4      Payables Trx Entry        Trx Entry Posting Journal       1        0        0        01        4      Payables Trx Entry        Trx Distribution Detail         1        0        0        01        4      Payables Trx Entry        Trx Distribution Summary        1        0        0        01        4      Payables Trx Entry        Trx Entry Check Register        1        0        0        01        4      Payment Entry             Payment Entry Posting Journal   1        0        0        01        4      Payment Entry             Payment Distribution Detail     1        0        0        01        4      Payment Entry             Payment Distribution Summary    1        0        0        01        4      Computer Checks           Computer Check Posting Journal  1        0        0        01        4      Computer Checks           Comp Chk Distribution Detail    1        0        0        01        4      Computer Checks           Comp Chk Distribution Summary   1        0        0        01        4      Computer Checks           Check Register                  1        0        0        01        4      Apply To                  Apply To Posting Journal        1        0        0        01        4      Void Open Trx             Void Open Posting Journal       1        0        0        01        4      Void Historical Trx       Void Hist. Posting Journal      1        0        0        01        4      Payment Entry             Checkbook Posting Journal       1        0        0        01        4      Computer Checks           Checkbook Posting Journal       1        0        0        01        4      Payables Trx Entry        Checkbook Posting Journal       1        0        0        01        4      Void Historical Trx       Checkbook Posting Journal       1        0        0        01        4      Void Open Trx             Checkbook Posting Journal       1        0        0        01        4      Currency Revaluation      Revaluation Journal             1        0        0        01        4      Currency Revaluation      Revaluation Register-Detail     1        0        0        01        7      Misc. Log Entry           Dist Breakdown - Detail         1        0        0        01        7      Misc. Log Entry           Dist Breakdown - Summary        1        0        0        01        7      Employee Expense Entry    Employee Expense Posting Jrnl   1        0        0        01        7      Employee Expense Entry    Dist Breakdown - Detail         1        0        0        01        7      Employee Expense Entry    Dist Breakdown - Summary        1        0        0        01        7      Employee Expense Entry    Check Register                  1        0        0        01        7      Employee Expense Entry    Checkbook Posting Journal       1        0        0        01        7      Billing Entry             Billing Posting Journal         1        0        0        01        7      Billing Entry             Dist Breakdown - Detail         1        0        0        01        7      Billing Entry             Dist Breakdown - Summary        1        0        0        01        7      Billing Entry             Checkbook Posting Journal       1        0        0        01        7      Inventory Transfer Entry  Inventory Xfer Posting Journal  1        0        0        01        7      Inventory Transfer Entry  Dist Breakdown - Detail         1        0        0        01        7      Inventory Transfer Entry  Dist Breakdown - Summary        1        0        0        01        7      Revenue Recognition Entry Rev Recognition Posting Jrnl    1        0        0        01        7      Revenue Recognition Entry Dist Breakdown - Detail         1        0        0        01        7      Revenue Recognition Entry Dist Breakdown - Summary        1        0        0        01        7      Project Closing           Project Closing Posting Journal 1        0        0        01        7      Project Closing           Dist Breakdown - Summary        1        0        0        01        7      Project Closing           Dist Breakdown - Detail         1        0        0        01        7      PA Returns Trx Entry      Project Posting Journal         1        0        0        01        7      PA Returns Trx Entry      Project Dist Breakdown - Detail 1        0        0        01        7      PA Returns Trx Entry      Project Dist Breakdown - Summar 1        0        0        00        3      Receivables Cash Receipts Sales Entry Posting Journal     1        0        0        00        3      Receivables Apply Doc.    Sales Entry Posting Journal     1        0        0        01        4      Edit PO Status            Encumbrance Summary Edit List   1        0        0        01        4      Edit PO Status            Encumbrance Audit Report        1        0        0        01        4      Computer Checks           EFT Payment Register            1        0        0        0

(220 row(s) affected)

The Table Columns

PRNTJRNL: It’s a flag that will determine whether a posting journal will be printed at all. 0 – Don’t Print, 1 – Print.

ASECTMNT: Ask Each Time – will force Dynamics GP to bring up the Report Destination window.

PRTOPRNT: Print to Printer – 0 – Don’t Print, 1 – Print

PRTOSCNT: Print to Screen – 0 – Don’t Print, 1 – Print

PRTOFLNT: Print to File – 0 – Don’t Print, 1 – Print

Solution

To force all posting reports to screen, we can then run a query that will change the status of the PRTOSCNT column to 1 – Print, while changing the status of all the other output destination flag columns to 0, as follows:

UPDATE SY02200 SET ASECTMNT = 0, PRTOPRNT = 0, PRTOSCNT = 1, PRTOFLNT = 0GO

Remember: You can always filter this UPDATE statement by product series in order to control the posting jounals output more granularly.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Length of Report Writer Calculated String Fields

I spent a good time writing my previous blog article on adding trailing asterisks to the check amount in words. If you followed the article, you will noticed that at some point I created two conditional calculated string fields to fill in the amount in words with trailing asterisks based on a condition. However, it appears that calculated string fields are still affected by the 80 characters limitation, rendering the remaining characters truncated from the report.

Needless to say this limitation is not a limitation with any of the Report Writer user-defined functions. In the case of the RW_PadWithStars function used in the previous article, it accepts any string and returns the padded version with asterisks up to the limit specified. However, when the value is passed to the Report Writer calculated field, it is further truncated to 80 characters.

This issue has been on the table since Dexterity 6.0 (and perhaps earlier) and I am surprised it has not gained traction with the Dexterity development teams at Microsoft. We definately no longer live in the world of 80 x 25 screens and much less in the dot-matrix printer era.

Related Articles

KB article 856435 – Length of Calculated String Fields

Developing for Dynamics GP – Cheque Amount in Words

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to add trailing asterisks after check amount in words

This one came up in the Microsoft Dynamics GP newsgroup today and I thought it would make for a good blog article.

Business Situation

It is common business practice to suffix the check amount in words with asterisks to prevent words being extended to reflect larger check amounts than what the check was originally written for.

Challenges

The trailing asterisks need to be dynamic, not static, to accomodate for variable lengths in the amount in words. Asterisks should be able to change relative to the length of the amount in words.

Solution

Microsoft Dynamics GP check stubs reports generated in the Purchasing series use String A 255 and String B 255 directly from the PM_Payment_Work table. In order to add the asterisks at the end of the strings, two conditional calculated fields will need to be created. To add the asterisks we can use the Report Writer user-defined function RW_PadWithStars found in the financial series.

Steps

1) Open Report Writer.

v9. Go to Tools > Customize > Report Writer.
v10. Go to Microsoft Dynamics GP > Tools > Customize > Report Writer

Select Microsoft Dynamics GP and click on OK to continue.

2) Select the desired check stub form from the available reports. For our example we will use Check With Stub on Top and Bottom – Text. Click on Insert to continue.

3) Highlight the inserted stub and click the Open button.

4) Click on the Layout button to continue.

Adding the Calculated Fields

We will create two conditional calculated fields: (C) String A 255 and (C) String B 255. These fields will add the asterisks at the end of the amount in words based on whether PM_Payment_WORK.String B 255 is empty or not.

(C) String A 255

(C) String A 255Returns: StringCondition: PM_Payment_Work.String B 255  ""True Case: PM_Payment_Work.String A 255False Case: FUNCTION_SCRIPT(RW_PadWithStars PM_Payment_Work.String A 255 255)

Click OK to save the changes when finished

(C) String B 255

(C) String B 255Returns: StringCondition: PM_Payment_Work.String B 255  ""True Case: FUNCTION_SCRIPT(RW_PadWithStars PM_Payment_Work.String B 255 255)False Case: PM_Payment_Work.String B 255

Click OK to save the changes when finished.

Changing the Report Layout

1) Select String A 255 and press Delete on your keyboard to remove from the report layout.

2) Drag (C) String A 255 from the Calculated Fields list in the Toolbox window onto the report layout. Set in place of String A 255.

1) Select String B 255 and press Delete on your keyboard to remove from the report layout.

2) Drag (C) String B 255 from the Calculated Fields list in the Toolbox window onto the report layout. Set in place of String B 255.

Returning to GP and Setting Up Security

1) Exit the Report Layout window saving all changes.

2) Select Microsoft Dynamics GP from the File menu.

3) Grant security to the modified report

In Dynamics GP 9, open Advanced Security and located the Check With Stub on Top and Bottom – Text report in the Purchasing series. Click on the radio button for the modifed version.

In Dynamics GP 10, go to Microsoft Dynamics GP > Tools > Setup > System > Alternate/Modified Forms and Reports. Select the DEFAULTUSER role ID, select Microsoft Dynamics GP from the Products drop-down, then select Reports from the Type drop-down.

Click on the Purchasing folder and locate the Check With Stub on Top and Bottom – Text object. Click on the modified version.

4) Click Save to continue.

Testing it out!

In your test environment, create a new check batch and print the checks selecting the format you modified. Print to screen or printer and verify the outcome.

Related Articles

Developing for Dynamics GP – Cheque Amount in Words
Developing for Dynamics GP – Using the built-in Report Writer Functions

Downloads

Click here to download package file for this customization. Use the Customization Maintenance window to import the package file provided.

Warning: Importing this package file may destroy any previous modifications done to the Check With Stub on Top and Bottom – Text report.

Until next post!

MG.-
Mariano Gomez, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Developing for Dynamics GP – Weekly Summary

This was a very active week over at Developing for Dynamics GP. David Musgrave brings 6 excellent articles that, once again, cover a number of interesting topics from ways to execute scripts across multiple GP company databases, all the way to the impact Microsoft’s new Statement of Direction for Dynamics GP will have on everyday product features. So let’s get started!

Article 1: David first article — Running SQL scripts against all GP company databases — explores a batch file he had developed in conjunction with his friend Robert Cavill in the past. This batch file makes use of the OSQL command line utility application (OSQL.EXE) provided with SQL Server to execute a query against all Dynamics GP company databases. In summary, a T-SQL SELECT statement is executed against the Company Master table (DYNAMICS..SY01500) to retrieve the INTERID column values. These values are then used to execute a script repetitively for each company.

Remember, you can always achieve the same thing via T-SQL in Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). However, this approach involves the use of cursors, as follows:

-- retrieves a list of customers from all companiesdeclare @companyID char(6)declare c_company cursor forselect INTERID from SY01500


open c_companyfetch next from c_company into @companyIDwhile @@fetch_status = 0begin  EXEC ("select * FROM " + @companyID + "..RM00101")  fetch next from c_company into @company IDend


close c_companydeallocate c_company

Article 2: How many times have you wished you could just record a macro and just execute it with a large dataset? Most of us, who have been in the trenches for a while, have known of a little dirty secret for quite some time now involving the use of Microsoft Office Word’s Mail Merge functionality to merge datasets into a macro file. In fact, macros are used to stress-test Dynamics GP and you can leverage this feature to do your own stress test.

David previously wrote KB article 953437, but have decided to bring it to the light on his blog site under the title How to Use Word Mail Merge and Macros to Import Data. Be sure to check this out as it is a unique chance to explore another interesting way of importing data and or stress testing your system.

If you are still looking for other ways of importing data into GP, please see my previous article on Table Import.

Article 3: Using VBA with Report Writer comes pack with a complete explanation of the Report Writer bands and how these are associated to Visual Basic for Applications (VBA) events. As David pointed out, “Most people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP forms and the Modifier, but not everyone is aware that VBA can be used with the Report Writer as well.“, and it’s a shame because Report Writer, while very ‘primitive’ in it’s behaviour and architecture, still offers a wealth of possibilities over the more commercial reporting tools. Be sure to check out (as in try) the sample code and evaluate how this can be implemented in your future Report Writer projects.
Don’t forget to check other links posted by David in this same article with tons of examples on how to access data and expose that data onto Report Writer reports.

Article 4: Using ADO with VBA with Report Writer showcases a sample on accessing data stored in tables that cannot be easily linked using standard Report Writer table relationships. By now, many of you probably know that Report Writer only support one 1-to-Many table relationship on a report, which can be a serious limitation for more complex reports. However, the use of old fashioned calculated fields, little VBA, and the new UserInfo connection object can turn Report Writer into a very dangerous tool — just kidding about the dangerous piece .

If you are not to engraned with the terminology, be sure to check Microsoft’ ActiveX Data Objects (ADO) frequently asked questions page, or you can download the latest copy from here.

Article 5: I ran across this page last Saturday when working on my Table Import article, trying to dig up all SDKs and did not think of posting a blog about it. However, David was clever enough to put together blog with links to the Developer Documentation for Microsoft Dynamics GP page for releases 9.0 and 10.0.

Article 6: “Should I continue to develop in Dexterity?” That is the eternal question that customers and frankly speaking, developers around the world continue to ask as GP evolves more to a collaborative environment. David answers this by pointing out interesting features highlighted in the latest Statement of Directions for Microsoft Dynamics GP release, as it relates to developers.

From personal experience, let me tell you: Dexterity developers are in high demand! Even Microsoft is looking for a few good ones. So don’t get discouraged — but don’t fall asleep either — if you see everyone else shooting to learn Visual Studio. Dexterity is not going away anytime soon. I promise!

Hope you enjoy this explosion of articles and let David or myself know what you would like to see on our sites.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/