Very Informative Articles across Blogosphere

It's been a really hectic month for me, doing all sorts of data corrections, troubleshooting, fixes, testing and what not?

But I was still able to read thru' some of the informative articles across the blogosphere. Here are the list that enlightened me:

1. Preventing Backdated Transactions using VBA - David Musgrave.
2. Report Writer Function to convert Amount in Numbers to Words - David Musgrave.
3. Accessing Third Party Table Buffer on a Cross Dictionary Trigger - Dave Dusek.
4. Report Writer: Making One to Many Relationship to behave like One to One - David Musgrave.
5. Development White Papers Link - David Musgrave.
6. Sampling Data from Extender Tables - Mariano Gomez.
7. Transferring Extender Customizations from One Company to Other - Mariano Gomez.
8. How to find Line Number of an Item : Part I - Mariano Gomez.
9. How to find Line Number of an Item : Part II - Mariano Gomez.
10. Is there a Maximum Number of Lines that can be entered on a Scrolling Window? - Mariano Gomez.
11. Sales Order Processing Posting Statuses - Mariano Gomez.
12. Securing VBA Projects - Mariano Gomez.
13. Accessing Active Directory From SQL Server - Steve Endow.
14. Sending Emails to Business Portal Users using SQL Server - Steve Endow.
15. Landed Costs : Part I - Christina Phillips.
16. Adding Watermarks to a Crystal Report - Victoria Yudin.
17. SQL View for all SOP Line Items - Victoria Yudin.

Yeah, that's a quite big list, but you would certainly feel enlightened and contended on learning some really important and interesting concepts.

VAIDY

String overflow during concatenation

This one is pretty simple error message (really simple), but sometimes people tend to not keep this on mind when coding. I am one among them and I did face this issue today.


I had to pass a SQL query from a custom dex code to get some value based on a complex calculation. It involved so many fields and an intricate JOIN. I decided to go against PURE DEX code, which would have ideally got RANGEs and GET TABLEs.


I did something like this:


local text l_tQuery;
...
...

l_tQuery = "SELECT [an expression of calculation which involves more fields] FROM [custom table] JOIN... ... ..."

The above query is really long. Once done, I compiled this script and faced no errors. When I installed the chunk and ran the process, the following errors greeted me:

Initially I was surprised to see this, as I had declared the variable as TEXT and it should ideally hold upto 32000 characters. My query was also pretty less than 32000 characters. And when I referred to the Dexterity Help Manual on this error, this is what it tells us:


--
String overflow during concatenation.
Situation
A concatenation of two or more strings resulted in a string longer than 255 characters.
Solution
Check the script where strings are concatenated (using the + operator) to determine whether the 255-character limit for strings is being exceeded.
--


Well, it actually means: We cannot concatenate and assign a string value at one stretch that would exceed 255 characters.


For instance, my SQL query would have got nearly 1000 characters and I had written it in one single assignment. When I pass this SQL Statement from Dex, I have to first construct it as a typical text value and then execute it using SQL_Execute().


When I construct the text value, I should have done like below, if my SQL statement is more than 255 characters long:


l_tQuery = "";
l_tQuery = l_tQuery + "SELECT [expression] ";
l_tQuery = l_tQuery + "FROM [custom table] ";
... ... and so on.


Thereby, at one stretch we would not have concatenated more than 255 characters length string.


I thought this would be useful for developers out there wondering with this error.


VAIDY

Office Palooza Novice Challenges - My Solutions

It was certainly a learning curve for me to learn such interesting and new concepts. There are lots more to learn, but I have certainly taken a big step on the right direction.

I was able to clear all 10 challenges and have also got selected for a sweepstake. What's more that I can ask for?

Now it's time to share my learning and the knowledge that I have acquired based on the challenges. Please do visit this link to download the solutions: Office Palooza - Novice Challenges - Vaidy's Approach. There can be (are) much better solutions to all these challenges, but by sharing my solutions, I could probably start a healthy discussion on my approach to each challenge and the respective solution. To more about each challenge and refer my solution, visit the site: Office Palooza Challenges List.

It may seem to be repetitive, but it's worth to be. I am Thankful to David for his article and leading me (probably more GP consultants out there) to know a contest like this and learn new things.

VAIDY

A Query on FRx

To all FRx Consultants available, kindly read the below query from one of my colleagues and please send in your inputs as comments to this post. Any help is highly appreciated:

--
In a row format, I have a calculated row which joins the values from 6 non printing row to it like below.

220 NP
250 NP
280 NP
310 NP
340 NP
370 NP
400 CAL A = A220, B TO X = B220 TO X220, Y TO AR = Y250 TO
AR250, AS TO BK = AS280 TO BK280, BL TO CE = BL310 TO CE310, CF TO CY = CF340
TO CY340, CZ TO DP = CZ370 TO DP370.

If i print the report seperately using this row format, it prints fine.

But when i add that row format(as a reporting unit) to an existing report
tree, while printing it shows the "Error in calculated formula in [400]".

Any idea of why its happening only in reporting tree?
--

VAIDY