SAP® Business One: Crystal Reports Address Fix

Working with the different fields for addresses can be a pain.  They have many different fields which will confuse users and not to mention most fields won’t even print.

Even with careful control of all of the form settings you are likely to have issues where a user tries to enter a piece of information and it doesn’t show correctly (typically there are gaps in your address when printed).

I had found a post on the SBO forum suggesting the following so I can’t take credit but I have summarized it here with additional steps for the newbie.  (Credit goes to Greg Stone, http://scn.sap.com/thread/3180284)

NOTE:  There is only one drawback to this method.  It will show blank rows in the logistics tab where the address is.  Just make this clear before implementing it.  The printout will work how they are requesting.

1. You have to setup the address format to include Address Line 1 and Address Line 2.

2. Navigate in SAP Business One to Administration >> Setup >> Business Partners >> Address Format.

3. Add the Street and Street No. on different lines.  We will relabel the fields Street to Address Line 1 and Street No. to Address Line 2 later.

4. Change the form settings in the BP Master Data.  Click the ‘Form Setting’ icon along the top of the screen in Business One (looks like a piece of paper with a gear) and set Street and Street No. in order and hide all the unused fields.  Keep in mind that you could do this with any of the existing fields in SBO.  If you needed to use one of the other fields for some specific reason you can do that as well (IE. block, etc.).  At this time it doesn’t appear that any UDF added to the BP address will show up in the format.  Therefore, if you wanted to add several fields you would have to pull it directly into the Crystal Report.

5.  You can see in my example above that the Address Line 1 and Address Line 2 are relabeled from Street and Street No.  (to relabel fields hold CTRL + double click the label and it will prompt you to modify the description).

6. In your marketing documents you are going to want to also modify the address fields to that everything is consistent.  Open up a new document in ‘Add’ mode (don’t add it just use it for modifying the form settings) and select any BP.  Go to the logistics tab and click the […] button next to the addresses.  Use your form settings and field labels to make things match the BP Master.

Now you will have a consistent set of fields and labels (you only have to do it once for all of the marketing documents which is nice).

7. Now you need to modify the Crystal Reports to not show the blank row if there is no data in the second row.  Unfortunately you have to do this for all of the marketing documents you use…

8. Navigate to an example marketing document (say an A/R Invoice for example).  Click the little pencil icon along the top for ‘Layout Designer.’  When the window pops up click ‘Manage Layout.’  This next section assumes you know the difference between the PLD and Crystal Reports and that you are reasonably familiar with Crystal Reports.  This is a fairly straightforward modification but I will not go into extreme detail so please ask another consultant for help with these steps.

9. Once Crystal Reports pops up you want to add a new formula field.  In this case we are going to call it @Billing and use the following formula:

REPLACE({OINV.Address}, ChrW(13)+ChrW(13), ChrW(13))

This basically takes the address and wherever there is two returns in a row replaces it with a single one.

SO:

Address Name {return}
Address Line 1 {return}
{return}
City, Province{return}
Postal {return}

This will format the blank row correctly so it looks like this:

Address Name
Address Line 1
City, Province
Postal

10.  Drag the formula field to where your address is and format it correctly.

11. Click Add-ins >> Save to save the layout.

Keep in mind you want to do a format painter in most cases since there is a conditional suppress on the addresses if they are identical.  Usually only one address is shown in that case.

Next time you do a print preview you will see the address printing properly.  Yay!!

Leave a Comment