Brain Freeze moment---EASY FORMULA that is eluding me. 9

[agray123]

I am having a bear of a time doing what I think is an easy formula, so please feel free to fire away.

I want to creat a formula that will give me one address from an address1 and address2 fields.

I would like the fields to be separated by a comma in the new formula field if at all possible.


Thanks,

 

[mbarron]

For your formula use:
{address1}+" , "+{address2}

If you have version 8 or higher you can use &'s instead of +'S Mike

 

[rosemaryl]

Dear Array123:

Try this:

stringvar add1;
stringvar add2;
stringvar comma;

add1 := If not isnull({Table.Address1}) then ({Table.Address1}) else "";

add2 := If not isnull({Table.Address2}) then ({Table.Address2}) else "";

comma:= if not isnull({Table.Address1}) and not isnull({Table.Address2}) then ", " else "";

add1 & comma & add2


I did it this way, because if either is null in Crystal and you concatenate you get nulls. Also, I didn't want to see the comma if there was no address2 or if both were null.

Hope this helps,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

To check for NULL or blank, which helps if later they alter the nullable for a field:

(non variable version)

if not( isnull({address1}) )
and
trim({address1})<> &quot;&quot; //allows for spaces entered
then
{address1}+ &quot;, &quot; + {address2}
else
{address2}

May have a typoe in there, but the theory should hold.

Rosemary had the solution, just a lil creeping elegance here...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

wow...RoseMaryl...always a source of innovative code

I never saw statements such as below before:

Test := IF something THEN this ELSE that;

Actually I think I would find it a little difficult to read though but I always like reading your posts.

My 2 cents worth for this question is the following...a combination of SV and RoseM

WhilePrintingRecords;
StringVar result := &quot;&quot;;

if not isnull({table.Address1}) and
length({table.Address1}) <> 0 then
result := result + {table.Address1} ;

if not isnull({table.Address2}) and
length({table.Address2}) <> 0 then
result := result + &quot;, &quot; + {table.Address2};

result;

yes...it will provide an extra comma if Address1 is not present but I would expect that would be very rare.

And if you wanted each address portion on a different line
then I would do it as follows:

WhilePrintingRecords;
StringVar result := &quot;&quot;;

if not isnull({table.Address1}) and
length({table.Address1}) <> 0 then
result := result + {table.Address1} ;

if not isnull({table.Address2}) and
length({table.Address2}) <> 0 then
result := result + &quot;, &quot; + chr(13) + chr(10) +
{table.Address2};

result;

Jim Broadbent

 

[synapsevampire]

Ngolem: I tried to allow for spaces which yours won't when just checking the length of the field, you'd still have to do a trim(),rendering the length test meaningless because a space in address 1 will still produce a length of 1. And as you mention, you add in a ghost comma in *rare* occasions.

I did blunder though, kinda rushing things today, it's been a rough one:

if not( isnull({Testdate.Addr1}) )
and
trim({Testdate.Addr1})<> &quot;&quot; //allows for spaces entered
then

(if not( isnull({Testdate.Addr2}) )
and
trim({Testdate.Addr2})<> &quot;&quot; //allows for spaces entered
then
// Both have data
{Testdate.Addr1} +&quot;,&quot;+ {Testdate.Addr2}

else
//Addr2 is blank
{Testdate.Addr1} )
else
//Addr1 is blank
{Testdate.Addr2}

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[AndrewBaines]

Take a look at the join function for this - very tidy. Unfortunately, you still have to mess about for the nulls.

stringvar address1 :=&quot;&quot;;
stringvar address2 :=&quot;&quot;;
if not isnull({Customer.Address1}) then address1 := trim({Customer.Address1});
if not isnull({Customer.Address2}) then address2 := trim({Customer.Address2});
join([address1,address2],&quot;, &quot;
Andrew Baines
Chase International

 

[Ngolem]

SV- you are right...I forgot the trim function in the formula...it was late

my formula for Multi-line address SHOULD be

WhilePrintingRecords;
StringVar result := &quot;&quot;;

if not isnull({table.Address1}) and
length(trim({table.Address1})) <> 0 then
result := result + {table.Address1} ;

if not isnull({table.Address2}) and
length(trim({table.Address2})) <> 0 then
result := result + &quot;, &quot; + {table.Address2};

result;


Andrew - In this case I don't see much of a gain in the Join function...you will always get an extra comma if Address2 is null so I don't see much advantage

Jim Broadbent

 

[Ngolem]

argghh... that is the single line version...not the multi-line version.

This weather is getting me down. We had a snow storm last night and today...4 inches of the stuff...In MAY!!...and it isn't the only storm we have had this month!! Yeah it will melt by afternoon...but that's not the point!

[ranting and foam at the mouth] Jim Broadbent

 

[agray123]

Thanks!

Not only did you all answer my needs in multiple fashion, but you also gave me weather reports!

It is way too cool here in PA Jim, we have had frost warnings the last 3 nights... It is crazy, no snow in January and frost in May???

 

[rosemaryl]

Dear Agray123,

I think that you have just discovered, &quot;competitive altruism&quot; in action.

Doesn't really matter which formula you use - use the one that suits your needs best.

ro

***Weather Report***
(Paramus, NJ - 60 degrees, still too cold in May for this Floridian transplanted to NJ) Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Ngolem]

Thanks I am glad you like it....I live in Calgary, Alberta Canada and this has got to be the worst Spring EVER!

In the space of 3 weeks, we have had 2 snow storms with over 2 feet of snow combined, with periods where the temperature is +20C which is about 75F for you other guys!

THIS is supposed to be Global Warming....Bah! It is a myth! Jim Broadbent

 

[rosemaryl]

Dear Ngolem, (Jim);

I had meant to ask you earlier, what did you find wrong with my formula? It does work. I am not trying to be petty here - really asking.

Did you coin that phrase &quot;competitive altruism&quot;?

I saw it posted in the forum somewhere and started laughing because that actually hit the nail on the head as a description of how everyone bends over backwards to help and each other around here...
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

I'm avoiding formulas using variables at this contract because they destroy pass through SQL generation (not applicable here, but I try to be consistent for maintenance reasons), and unfortunately, this contract is using Views, not SP's. People seem to prefer formulas with variables aroun here, I think it makes them feel like *real* coders or sumpin...

Simple example of destroying pass through:

Formula Named SomeID
numbervar CNT_ID;
CNT_ID := 1;
CNT_ID

If you use this formula in a record selection criteria, it won't pass it through

If you just place 1, or use some math or Crystal functions, it will pass it through. Picky lil Crystal SQL generator...

ro: I like the phrase *competitive altruism* and I suppose either formula can work. The quality check and performance gain probably aren't factors here. I used to own a database outsourcing firm, and this was SOP for addresses (we cleaned the table if we were paid, but if we were just building a tape to hang on a print station, we'd just format accordingly).

*andrew*: Join is great. *tidy* is indeed a good word for it, that's the way to go, though the example seems to require a comma cleanup if address1 is empty. Join is a great way to display parameter choices from the array, thanks! <applause inserted here>

ngolem: Looks pretty good, but I still dislike not testing for blank in the address1 field, and why use a length check when you already have the field trimmed and can just check for = &quot;&quot;, I suggest it just degrades your performance...and...you're the second person who I've read mentioning the weather up there, a recently repatriated Canadian friend wrote today:

The roads are too long, and covered with snow, why did I move back to Canada again?

Sounds a bit nipply up there, time to arrange for a training class in California, methinks.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

SV - I have checked for a blank space using this

if not isnull({table.Address1}) and
length(trim({table.Address1})) <> 0 then
result := result + {table.Address1} ;

the purpose of the &quot;Trim&quot; is to remove any blank spaces before the length of the field is measured. I have run into several instances where users get around a &quot;NOT NULL&quot; oracle field by simply entering a blank space or two or three ...arghhh. This way I can catch that...and as for a performance hit...not noticeable at all.

Your point is well taken about the use of formulas in Record selection...

I find the use of variables much easier to read, easier to debug as time passes and generally it is my consistant style.

Rosemary - You are absolutely right this format does work.

Test := IF something THEN this ELSE that;

I have never seen this format before and tried it and it works fine. But I don't like the readability of it personally...not meant as an insult....hope you don't take it that way.

In reading, your posts I have discovered several interesting ways of approaching a problem...yours often seem novel to me. I am a formula person...love them...often I avoid simpler solutions such as running counts simply because I know my method will work and I am loath to change it.

But we are &quot;competitive&quot; here aren't we....but I must say it is the nicest form of competition that I have ever been involved in...most everyone here is trying to help...I like that. Jim Broadbent

 

[synapsevampire]

Ngolem: Sorry, I meant allowing the comma. I was typing quickly, I tend to errorrrr in these forums because I rarely read what I type, I just tap it out and click. I dislike leaving that sort of thing as a possibility.

Your original formula didn't have the trim, I suggested it, you put it in. But you don't need both a trim AND a length, it's overkill, you have 2 constructs when one will suffice, just the trim = &quot;&quot;.

No worries on the programming style, I explained why, I've been coding for 18 years, Crystal is just part of what I do now, I used to teach VB coding, worked as a coder in many languages, and we all approach things differently, but performance and accuracy should be the goal, style is secondary.

But then I'm olde skewl...

You offer lots of good advice in these forums, please don't mistake my candor for anything less than a desire to help or understand.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com