This is a follow up post to my earlier post, which demonstrated how Report Writer when combined with the power of Visual Basic for Applications (VBA) was able to create a customisation that would not have been possible with Report Writer alone.
In fact, I would be fairly surprised if any report writer would have been able to achieve what was required without some sort of scripting.
So, why the follow up post. Well four reasons really:
The Updated Situation
The original requirements for this customisation stated that on a single SOP Invoice there could be a single "Pack" item which would have its Item Description field prefixed with "(P) " and that would be followed by a number of "Line" items with the prefix "(L) ".
The customer has come back and said that it is now possible for multiple "Packs" with multiple sets of "Lines" to be included in a single document. The Item Description field would now be prefixed using "(P1) ", "(P2) ", "(P3) ", ... etc. and the associated lines would be prefixed "(L1) ", "(L2) ", "(L3) ", ... etc.
The Updated Solution
The code developed previously would need a few tweaks to make it work for the now updated requirements. However, as you will see the changes were minimal and the new code was up and running in only a few minutes.
We had to change how we identified "Pack" and "Line" items so that we only looked for "(P' and "(L" and adjust the code for removing the prefixes from the Item Description field.
Then to get the "Pack" items to total correctly, we needed to obtain the pack number from the description and then adjust the SQL query to only sum lines from the same pack number.
Everything else would work without changes.
The Updated Code
Below is the updated code used for the modified report. The packages exported from Customisation Maintenance are also included as attachments to the bottom of this post.
Note: If Multi-currency support was required, we would have to adjust the query to either sum originating or functional amounts depending on the currency view being printed. We would also need to change the method used to return the currency fields to the report (as per the articles below).
The previous post can be found here:
The solution described in this article used techniques from the following articles:
I hope this continues to demonstrate how VBA can be used to go beyond what is possible with Report Writer alone, and how flexible and easy it is to make changes to handle updated requirements.
Ahh, you have again provoked Mark with RW rambling. I'm eagerly awaiting his reply. May be a post which explains why RW is NOT world's great reporting tool. :-D
Jokes apart, this one really is great in terms of teaching us how to utilize RW & VBA for even seemingly complex scenario.
Posting from Mariano Gomez, The Dynamics GP Blogster
Posting from Mark Polino at DynamicAccounting.net
Posting from Michael Johnson, The MBS Guru
Posting from Mohammad Daoud
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.