Wednesday, October 15, 2008

Understanding the rw_SelectAddrLine Address Line function in Microsoft Dynamics GP Report Writer

The Report Writer user-defined rw_SelectAddrLine function script is a very handy function that allows most Sales Order Processing documents to display addresses without those pesky blank lines in between say the first address line and the city, state, and zip code, especially when the second address line is empty.

For this explanation I will use the Bill To Address Lines on the Sales Blank Invoice Form, which is one of the most commonly modified reports throughout the system.

The current address lines on this report are predicated on 3 calculated fields (Bill To Address Line 1, Bill To Address Line 2, and Bill To Address Line 3). In turn, these calculated fields call a Dexterity script -- the rw_SelectAddrLine script -- that displays the address lines based on the parameters that are passed in. The implementation of this script is as follows:


function returns string rw_SelectAddrLine;
in integer nLineNum;
in string sLine1;
in string sLine2;
in string sLine3;
in string sLine4;
in string sLine5;
in string sLine6_City;
in string sLine6_State;
in string sLine6_PostalCode;
in string sLine7;

This script returns the address line for the specified position wherein blank lines are suppressed. Parameters are passed in the same order as the address lines to be printed. City/State/Postal Code are combined to form a single line. The line returned is selected from the parameter list by position but only non-blank lines are counted when calculating the position. This effectively suppresses the printing of blank address lines, which could be seen in earlier versions on Microsoft Dynamics GP.

Example: sLine1 through sLine5 are typically used for the contact person and fields Address 1, Address 2 and Address 3. sLine7 could be used for Country. But there are no restrictions on the values that sLine1-sLine5 and sLine7 actually contain. However, the line6 parameters of City/State/Postal Code are passed to the standard formatting method and thus should conform accordingly (or all left blank). If no line exists on the report for a given input line, pass an empty string for that parameter.

What this means to you as a user is, if you want to add, say the Country, you could potentially modify Bill To Address Line 1, Bill To Address Line 2, and Bill To Address Line 3, as follows:


Bill To Address Line 1 = FUNCTION_SCRIPT(rw_SelectAddrLine 1, "", RM_Customer_MSTR_ADDR.Address1, RM_Customer_MSTR.Address2, "", "", RM_Customer_Master_ADDR.City, RM_Customer_Master_ADDR.State, RM_Customer_Master_ADDR.Zip, RM_Customer_Master_ADDR.Country)


Bill To Address Line 2 = FUNCTION_SCRIPT(rw_SelectAddrLine 2, "", RM_Customer_MSTR_ADDR.Address1, RM_Customer_MSTR.Address2, "", "", RM_Customer_Master_ADDR.City, RM_Customer_Master_ADDR.State, RM_Customer_Master_ADDR.Zip, RM_Customer_Master_ADDR.Country)


Bill To Address Line 3 = FUNCTION_SCRIPT(rw_SelectAddrLine 3, "", RM_Customer_MSTR_ADDR.Address1, RM_Customer_MSTR.Address2, "", "", RM_Customer_Master_ADDR.City, RM_Customer_Master_ADDR.State, RM_Customer_Master_ADDR.Zip, RM_Customer_Master_ADDR.Country)

In addition you will need to create a Bill To Address Line 4 to account for the Country. Let me explain. As is, the line addresses are only designed to account for an Address 1, Address 2, and City, State and Zip on a third line. If there is no Address 2 line, then only 2 lines get displayed. However, if you include Country, then in the cases where there are 2 address lines then the Country will not be displayed. With this said, Bill To Address Line 4 will look something like this:


Bill To Address Line 4 = FUNCTION_SCRIPT(rw_SelectAddrLine 4, "", RM_Customer_MSTR_ADDR.Address1, RM_Customer_MSTR.Address2, "", "", RM_Customer_Master_ADDR.City, RM_Customer_Master_ADDR.State, RM_Customer_Master_ADDR.Zip, RM_Customer_Master_ADDR.Country)

NOTE: The commas in this article are for readability sake, but in Report Writer these must not be included when calling a FUNCTION_SCRIPT.

Finally, to include the RM_Customer_Master_ADDR.Country field on the existing Bill To Address Line 1, Bill To Address Line 2, and Bill To Address Line 3, you will need to click on the last set of double quotes ("") then click the Remove button then add the Country field by selecting the Customer Master Address File from the Resources drop-down. Select the Country field then click on Add.

For more information on this and other Report Writer functions, please visit David Musgrave's blog and read up.

Until next post!

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

9 comments:

Chris Mast said...

Important to note that all sline positions must be included either as a data element or a double-quote. There should be 9 positions in total as listed above. The double-quotes are markers for empty sLine positions.

Also the double-quote positions are NOT added as double quotes. Rather, they are added as blanks of type String. The Report Writer application will translate these blanks into double-quotes.

Mariano Gomez said...

Excellent point Chris. Thanks for your contribution and keep up the readership.

MG.-
Mariano Gomez, MVP

ian richardson said...

now that I have it figured out, the 1, 2, 3 etc is the identification of the position being evaluated. Just going into report writer it sure looked like it was address 1 address 2 sequential so when you added city state would be 4 and country 5. City state = 6 and country = 7

lep said...

I have successfully added country to the Bill To Address BUT cannot replicate to Ship To Address. Still getting error in equation error.

Client is on GP2013 and modifying the SOP Blank Packing Slip Form.

Any further suggestions would be greatly appreciated.

Thank you.

lep said...

I tried to insert the SOP Header Doc Temp Country field into the last field in Add Line 1 thru 5 but that errored out. I created a sCountry calculation and insert that in Line 1 to 5 and it errored.

Finally, I removed the last field sPhone(default on form) and replace with my sCountry and the form works.

Why - I don't know and not sure that is the best solution but needed to move forward. Perhaps this may help someone else!!

Thank you.

Paul said...

I wonder, is it possible to add the country in this way but only if it is international (non-US)?

Steve Simon said...

Thanks, Mariano. After reading this blog 5 times i FINALLY understood it. Works like a charm!!

Hats off to David Musgrave for giving us the system function to add parts of the address without having blank lines show.

Sincerely,
Steve Simon
PDG Consultants

Mariano Gomez said...

Steve,

Great I was able to help and definitely, I will give your shout out to David. It was great seeing you in Fargo.

MG.-

Muhammad Amir said...

It works perfectly with table fields but when I tried using it with Global address fields it throws an error. Any idea which function works with Global fields?

Thanks

Amir