Help With Some Calculations

[sdutton]

First Calculation:

I have a calculation that gives me a family’s entire home address in vertical fashion. It works great. The address ultimately looks like this:

Steve Dutton

123 Main St.

1st Floor

City, State 11111

USA

That’s the result of this calculation:

Home Address 1 &Case(not IsEmpty(Home Address 2);

"¶" & Home Address 2) & "¶" & Home City &Case(not IsEmpty(Home State);

", " & Home State) & " " & Home Postal Code & "¶" & Home Country

My problem is, some people don’t have a home address; they only give a work address. What do I have to add to the above calculation to have the calculation result completely blank (so that if there’s no “home address 1”, the calculation stops right there). For my merge document in Microsoft Word to work properly, I need this calculated field to be totally blank if there’s no home address.

Second Calculation:

I want to combine one’s entire home address and one’s entire work address in a vertical fashion. If I have both addresses in my database, it works great. The combined addresses look like this:

Home Address:

123 Main St.

1st Floor

City, State 11111

USA

Work Address:

456 Main St.

2nd Floor

Work City, Work State 22222

USA

That’s the result of this calculation:

Case(not IsEmpty(Entire Home Address Vertical);

"Home Address:" & "¶" & Entire Home Address Vertical) & Case(not IsEmpty(Entire Work Address Vertical);

"¶¶" & "Work Address:" & "¶" & Entire Work Address Vertical)

My problem here is similar to the first calculation problem. Some people have just a home address, and some people have just a work address. With my calculation, I get this:

Home Address:

Work Address:

456 Main St.

2nd Floor

Work City, Work State 22222

USA

Or vice versa.

Home Address:

123 Main St.

1st Floor

City, State 11111

USA

Work Address:

What I need is for the calculation to evaluate if there’s just a home address or just a work address or both or neither.

It should ultimately look like this:

Home Address:

123 Main St.

1st Floor

City, State 11111

USA

OR

Work Address:

456 Main St.

2nd Floor

Work City, Work State 22222

USA

OR

Home Address:

123 Main St.

1st Floor

City, State 11111

USA

Work Address:

456 Main St.

2nd Floor

Work City, Work State 22222

USA

OR

(nothing – empty field)

Hope this all makes sense,

Steve

[coconutt2000]

Try writing your calculation to check to make sure the necessary fields contain data, and the correct data at that. If the fields contain data and that data is kosher, then have the calc proceed to putting that data together in the format you require. Else, have it output nothing, or a default.

By using a function to evaluate the fields first to make sure they have valid data, you can avoid printing addresses missing a city, state, zip or street address.

[coconutt2000]

One technique I've used in the past was an auto-entry calc number field that held either a 1 or a 0. The calc would sit around doing nothing but evaluating the data entry fields every time they were changed. If the data was kosher, the validation field would contain "0". If something was wrong, the field would contain "1".

All I would have to do then was have my scripts that worked with the data in those fields check the validation field, and if the field was "0", the script/calculation would go ahead. If not, the script would escape with an error, or continue along another logic branch until it output something else altogether.

A dark whisper in my head would repeat silently whenever I used that particular trick, "One calc to rule them all, and in the darkness bind them."

[comment]

You gave the key to the solution in your second calc, you just need to use it:

First calculation =

Let (

homeAddress =

Home Address 1 &Case(not IsEmpty(Home Address 2);

"¶" & Home Address 2) & "¶" & Home City &Case(not IsEmpty(Home State);

", " & Home State) & " " & Home Postal Code & "¶" & Home Country

;

Case ( not IsEmpty ( homeAddress ) ; homeAddress )

)

Second calculation =

Let ( [

homeAddress = << same as above >> ;

workAddress = << a similar calculation here >>

] ;

Case ( not IsEmpty ( homeAddress ) ; homeAddress ) &

Case ( not IsEmpty ( homeAddress & workAddress ) ; ¶ ) &

Case ( not IsEmpty ( workAddress ) ; workAddress )

)

See also these two threads for some alternative methods:

Link

Link

[sdutton]

Thank you for all your help.

Steve