Joining Multiple Fields in Formula

[MollyH]

I am attempting to join multiple fields in a formula, but am getting blank lines when there is no data in one field. This is for an address and the formula created is pretty simple:

{POPPPR.PB_ADR_STNO}+ " " + {POPPPR.PB_ADR_DIR1} + " " + {POPPPR.PB_ADR_STR1}+ " " + ({POPPPR.PB_ADR_TYP1} + " " + "APT" + " " + {POPPPR.PB_ADR_APT}

But only when there is data in all fields (which is rare), does it print - otherwise it is blank on the form.

Any suggestions?

 

[Ngolem]

you must test for the nulls since failure to do that causes the formula to crash when it hits one

Try this

//@formulaResult

WhilePrintingRecords;
StringVar Result := "";

if not isnull({POPPPR.PB_ADR_STNO}) or
length(trim({POPPPR.PB_ADR_STNO})) <> 0 then
result := result + {POPPPR.PB_ADR_STNO} + &quot; &quot;;
if not isnull({POPPPR.PB_ADR_DIR1}) or
length(trim({POPPPR.PB_ADR_DIR1})) <> 0 then
result := result + {POPPPR.PB_ADR_DIR1} + &quot; &quot;;
if not isnull({POPPPR.PB_ADR_STR1}) or
length(trim({POPPPR.PB_ADR_STR1})) <> 0 then
result := result + {POPPPR.PB_ADR_STR1} + &quot; &quot;;
if not isnull({POPPPR.PB_ADR_TYP1}) or
length(trim({POPPPR.PB_ADR_TYP1})) <> 0 then
result := result + {POPPPR.PB_ADR_TYP1} + &quot; &quot; +
&quot;APT&quot; + &quot; &quot; ;
if not isnull({POPPPR.PB_ADR_APT}) or
length(trim({POPPPR.PB_ADR_APT})) <> 0 then
result := result + {POPPPR.PB_ADR_TYP1} ;

Result;

Now this may be one long string so make certain you allocate enough space for it and/or enable the &quot;Can Grow&quot; for the field.

If you want this address on separate lines just insert
+ chr(13)
at the spot where you want it to create a new line.


Jim Broadbent

 

[MollyH]

Thank you - I'm getting some records, at least, but still getting plenty of blank lines where the address should be.

 

[Ngolem]

the formula works though yes?

Try this....beside the formula in Design...place the fields that are giving you blank data....run the report and see if they are blank themselves as a test.

Is the whole line blank or are you missing pieces?

Jim Broadbent

 

[MollyH]

There are blank fields (not every street is N, S, E, W, and not everyone lives in an apt, etc.). It appears that if one field in the formula is blank, nothing prints. The whole line is blank. I don't know if this makes a difference, but the formula is in the page header.

 

[Ngolem]

is this formula working sometimes?

Ahhhh it is in the page header.....the data has come and gone..

How is your report set up??

Is there grouping after the page header....do you have more than one group/page...more than 1 page /group

Layout the structure of your report...the problem is that the data is there and then disappears somehow but I must see the structure to figure out why.

Jim Broadbent

 

[MollyH]

Okay, here is the formula now. The address prints now, except that if a field is blank, the last record is read and the data pulled and added to each record until it comes upon a field with a new value in it. For example:
125 Main #105 (correct address)
456 Oak #105 (although no apt number in data field)
789 Cedar #105 (continues...)
101 State #A (correct address)
505 Main #A (no apt in data)

FORMULA:
stringvar apt = &quot; &quot;;
stringvar stno = &quot; &quot;;
stringvar dir = &quot; &quot;;
stringvar street = &quot; &quot;;
stringvar typ = &quot; &quot;;

If not(isnull({POPPPR.PB_ADR_STNO})) then stringvar stno := {POPPPR.PB_ADR_STNO}
else ' ';
If not(isnull({POPPPR.PB_ADR_DIR1})) then stringvar dir := {POPPPR.PB_ADR_DIR1}
else ' ';
If not(isnull({POPPPR.PB_ADR_STR1})) then stringvar street := (LeadingCaps ({POPPPR.PB_ADR_STR1}))
else ' ';
If not(isnull({POPPPR.PB_ADR_TYP1})) then stringvar typ := {POPPPR.PB_ADR_TYP1}
else ' ';
If not(isnull({POPPPR.PB_ADR_APT})) then stringvar apt := {POPPPR.PB_ADR_APT}
else ' ';

stringvar address := stno + ' ' + dir + ' ' + street + ' ' + typ + ' ' + apt

This seems to be getting worse...

 

[MollyH]

Forgot to add - there is only one group, but one letter per group, using a date range to print letters.
Tried moving the formula to the group header instead, but no go.

 

[synapsevampire]

This shouldn't be in the page header.

Anyway, try these few adjustments:

FORMULA:
stringvar apt := &quot;&quot;;
stringvar stno := &quot;&quot;;
stringvar dir := &quot;&quot;;
stringvar street := &quot;&quot;;
stringvar typ := &quot;&quot;;
stringvar address := &quot;&quot;
If not(isnull({POPPPR.PB_ADR_STNO})) then stno := {POPPPR.PB_ADR_STNO}
else '';
If not(isnull({POPPPR.PB_ADR_DIR1})) then dir := {POPPPR.PB_ADR_DIR1}
else '';
If not(isnull({POPPPR.PB_ADR_STR1})) then street := (LeadingCaps ({POPPPR.PB_ADR_STR1}))
else '';
If not(isnull({POPPPR.PB_ADR_TYP1})) then typ := {POPPPR.PB_ADR_TYP1}
else '';
If not(isnull({POPPPR.PB_ADR_APT})) then apt := {POPPPR.PB_ADR_APT}
else '';

address := stno + ' ' + dir + ' ' + street + ' ' + typ + ' ' + apt

If you supply example data and expected output you'll get better results here.

The whole thing could use a rewrite, but this should get you going.

-k

 

[MollyH]

Thanks!!