Add an address block without blank lines

Friday 15 February 2019 @ 12:21 am

<< for a simpler approach, see this later article on the same topic >>

I often find one or more “address blocks” at the top of form reports, like invoices or purchase orders. These are blocks of text that typically show a customer name, two or three lines for address info, and a last line for City/State/Zip. The simple approach is to arrange the individual fields on the report, but if some address lines are blank you get empty rows in the block. Here is the approach I use to make sure that there are no empy lines in the block.

First you write a formula that combines the city/state/zip into one row like this:

//{@City, State & Zip}
{Cust.CITY} & ", " & {Cust.STATE} & "  " & {Cust.ZIP}

Then you write a formula that combines the first formula with the other potential rows of the address, like this:

{CONTACT1.Company} &
(if {Cust.ADDRESS1} > "" then CHR(13) & {Cust.ADDRESS1} else "") &
(if {Cust.ADDRESS2} > "" then CHR(13) & {Cust.ADDRESS2} else "") &
(if {Cust.ADDRESS3} > "" then CHR(13) & {Cust.ADDRESS3} else "") &
CHR(13) & {@City, State & Zip}

The formula above assumes that every record will have a Company value and a City/State/Zip value. The address lines are added if they have a value. And when they are added a carriage return is also added, using CHR(13). This way each line appears on its own row, but only when it has data. There are no blank lines when a field has no data.

Two things to watch for.
1) You should set both of the formulas above to use “Default Values for Nulls”. Otherwise a Null value for one of these fields will cause a blank address block.
2) Make sure you format the address block with “Can Grow” and then don’t put anything right below it in the same section.  Otherwise the address field might grow right over the object below it.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Recrystallize Pro