Surpress blank rows in a formula 1

[jeffm777]

I have the following formula but there won't always be data in each of the 6 fields. What I want is to remove the rows in the formula where there is no data.

{WOSummary.AcctNumtoShip} + ChrW(13) +
{WOSummary.CustomerShipTo} + ChrW(13) +
{WOSummary.ShipAddress1} + ChrW(13) +
{WOSummary.ShipAddress2} + ChrW(13) +
{WOSummary.ShipAddress3} + ChrW(13) +
{WOSummary.ShipAddress4} + ChrW(13)

This formula, for example, will result in...

12345
Any Company
1234 Anywhere Street
Suite. C
Anytown, USA 35645
Attn: Bob

Currently, if there is no data, in say, {WOSummary.ShipAddress2}, it results in this...

12345
Any Company
1234 Anywhere Street

Anytown, USA 35645
Attn: Bob

But I want it to result in this...

12345
Any Company
1234 Anywhere Street
Anytown, USA 35645
Attn: Bob

 

[fisheromacse]

You would need to add logic to each step to determine if it has a value and what to display if not, like below:

{WOSummary.AcctNumtoShip} + ChrW(13) +
{WOSummary.CustomerShipTo} + ChrW(13) +
+
(If (isnull({WOSummary.ShipAddress1}) or trim({WOSummary.ShipAddress1})="")
then ""
else {WOSummary.ShipAddress1} + CHR(13))
+
(If (isnull({WOSummary.ShipAddress2}) or trim({WOSummary.ShipAddress2})="")
then ""
else {WOSummary.ShipAddress2} + CHR(13))
+
(If (isnull({WOSummary.ShipAddress3}) or trim({WOSummary.ShipAddress3})="")
then ""
else {WOSummary.ShipAddress3} + CHR(13))
+
(If (isnull({WOSummary.ShipAddress4}) or trim({WOSummary.ShipAddress4})="")
then ""
else {WOSummary.ShipAddress4} + CHR(13))


i think you could also user variables, then use the results of the variable calculations as a display:

stringvar addr1;
stringvar addr2;
stringvar addr3;
stringvar addr4;

IF (isnull({WOSummary.ShipAddress1}) or trim({WOSummary.ShipAddress1})="")
then addr1 := ""
else addr1 := {WOSummary.ShipAddress1} + CHR(13));

IF (isnull({WOSummary.ShipAddress2}) or trim({WOSummary.ShipAddress2})="")
then addr2 := ""
else addr2 := {WOSummary.ShipAddress2} + CHR(13));

IF (isnull({WOSummary.ShipAddress3}) or trim({WOSummary.ShipAddress3})="")
then addr3 := ""
else addr3 := {WOSummary.ShipAddress3} + CHR(13));

IF (isnull({WOSummary.ShipAddress4}) or trim({WOSummary.ShipAddress4})="")
then addr4 := ""
else addr4 := {WOSummary.ShipAddress4} + CHR(13));

{WOSummary.AcctNumtoShip} + ChrW(13) +
{WOSummary.CustomerShipTo} + ChrW(13) +
addr1 +
addr2 +
addr3 +
addr4

 

[lbass]

A simpler solution is to place each field in its own section a, b, c, and then format each section to "suppress blank section".

-LB

 

[fisheromacse]

yes, placing each address part into it's own details section also works nicely. As long as there is no other data that needs to be displayed on that row/section.

 

[jeffm777]

Tks lbass - I thought about that but I have two formulas - one for shipping and one for billing that are both going in the same group header so fisherman's solution worked out better. Thanks to both of you!!!

I used...

(If (isnull({WOSummary.AcctNumtoShip}) or trim({WOSummary.AcctNumtoShip})="")
then ""
else {WOSummary.AcctNumtoShip} + CHR(13))
+
(If (isnull({WOSummary.CustomerShipTo}) or trim({WOSummary.CustomerShipTo})="")
then ""
else {WOSummary.CustomerShipTo} + CHR(13))
+
(If (isnull({WOSummary.ShipAddress1}) or trim({WOSummary.ShipAddress1})="")
then ""
else {WOSummary.ShipAddress1} + CHR(13))
+
(If (isnull({WOSummary.ShipAddress2}) or trim({WOSummary.ShipAddress2})="")
then ""
else {WOSummary.ShipAddress2} + CHR(13))
+
(If (isnull({WOSummary.ShipAddress3}) or trim({WOSummary.ShipAddress3})="")
then ""
else {WOSummary.ShipAddress3} + CHR(13))
+
(If (isnull({WOSummary.ShipAddress4}) or trim({WOSummary.ShipAddress4})="")
then ""
else {WOSummary.ShipAddress4} + CHR(13))