Is it possible to do "looping" formulas in CR7? 1

[Storyteller]

Hello All,
I have a string field that contains single characters seperated by blank spaces
Example: {ado.AgenciesNotified} contains "F L C"
F = Forest Service
L = Fish and Wildlife
C = Public Lands

When I use an If-Then-Else statement like this

If "F" in {ado.AgenciesNotified}
Then "Forest Service"
Else If "L" in {ado.AgenciesNotified}
Then "Fish and Wildlife"
Else If "C" in {ado.AgenciesNotified}
Then "Public Lands"

The formula works fine if there is only one value in the {ado.AgenciesNotified} field. However, if there is more than one value for example: "C L" in the field. The formula stops after the first correct "Then".

What I would like to do is be able to loop through the field "x"number of times to extract the character values and assign them to variables. Then in another formula assign the variables to the proper full text values.

Is this possible in Crystal Reports 7?

I am also open to any other suggestions you may have.

Regards,
Michael Microsoft Office 2000 Master Instructor
Corel Certified Instructor WordPerfect 9

 

[dgillz]

You need Cr v8 or newer to do this. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[Naith]

I'm terrible with remembering what was or wasn't available in v7, and what was introduced in 8. That's my disclaimer.

If you have the 'Split' command in v7, you could try something along the lines of:

WhilePrintingRecords;
StringVar Array MyArray := [""];

MyArray := Split({ado.AgenciesNotified}," ");

If UBound(MyArray) < 1

//When was UBound introduced? 7 or 8? I have no idea. UBound isn't compulsory to the formula but it does stop you getting 'array longer than string' errors.

Then '' 
Else

//If you don't have UBound, start the formula after assignments here ->

StringVar Variable1 := MyArray[1];
If UBound(MyArray) > 1
Then StringVar Variable1 := MyArray[2];
If UBound(MyArray) > 2
Then StringVar Variable1 := MyArray[3];

...and so on and so forth. You get the picture. Quit when you reach the maximum field length. Hopefully, it isn't a varchar2(255) field! If you don't have split or ubound, and there're no ufls to achieve the same, then have a think about upgrading.

Good luck,

Naith

 

[synapsevampire]

Just create 3 formulas:

@Part1
left({MyTable.MyField},1)

@Part2
Mid({MyTable.MyField}, 3, 1)

@Part3
Mid({MyTable.MyField}, 5, 1)

There's your three formulas, have your way with them

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

BTW, yes you can do looping in Crystal, but it isn't appropriate here.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

SynapseVampire,

When Storyteller says: 'What I would like to do is be able to loop through the field "x"number of times', I think that he's saying that the string is variable in length or significantly longer than 5 characters.

However, if I've misassumed, Storyteller, then by all means use mid. As you can see, it's a simpler approach.

Naith

 

[mbarron]

I couldn't check this in version 7, so I'll apologize if it doesn't work for you.

Creat the formula below. It is currently set up to do a "carriage return" to seperate the occurances of the F, L, or C. If you want to add commas instead, replace the chr(13) with ", ". If you stick with the chr(13) make sure you format your field to "Can Grow"

The formula, no matter what order the letters( F, L, or C) are entered, will result as if they were entered as F L C.



stringvar flc:="";stringvar flc2;stringvar flc3;stringvar out;
stringvar carret;stringvar carret2;



If "F" in {?{ado.AgenciesNotified} Then flc:="Forest Service";
if flc="" then carret:="" else carret:=chr(13);
out:=flc+carret;

If "L" in {?{ado.AgenciesNotified} Then flc2:=out +"Fish and Wildlife";
if flc2="" then carret2:="" else carret2:=chr(13);
out:=flc2+carret2;

If "C" in {?{ado.AgenciesNotified} Then flc3:=out + "Public Lands" else
out:=out




Mike

 

[Storyteller]

Hello All,
Thank you everyone for your great responses. After I posted the original message I did the following:
1. Created individual formulas to extract the single characters. Ex:
{@F}
If "F" in {ado.AgenciesNotified}
Then "Forest Service"
2. I then created a second formula to link the text together. Ex:
{@LinkText}
{@F}+ chr(13)+
{@L}+ chr(13)+
{@C}+ chr(13)
The above formulas work. However, formula 2 has empty lines if there is only text for {@C}. So my next question would be how to remove the blank lines if there is only text for example {@C}?

Naith is correct, the {ado.AgenciesNotified} field contains a variable length of characters (the string field has a length of 17).

I will try all the other suggestions when I get back to my computer. I am in the classroom training today.

Regards,
Michael Microsoft Office 2000 Master Instructor
Corel Certified Instructor WordPerfect 9

 

[Naith]

Storyteller,

Using your formulas, you would omit empty lines by tweaking them to read:

//{@F}
If "F" in {ado.AgenciesNotified}
Then "Forest Service" + chr(13)
Else "";

//{@LinkText}
{@F}+ {@L}+ {@C}

Naith

 

[synapsevampire]

Omit the carriage return in the individual formulas and use the final display to insert them to avoid the extra carriage return:

join([{@F},{@D},{@C}],chr(13))

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

I like your original approach

There is no need to parse out individual values from the string through other formulas.

The problem, that you ran into, was using If-then-else instead of cascading if-then statements.

Try this:

@LinkText

WhilePrintingRecords;
StringVar result := "";

If "F" in {ado.AgenciesNotified} then
result := result + "Forest Service" + chr(13)+ chr(10);

If "L" in {ado.AgenciesNotified} then
result := result + "Fish and Wildlife" + chr(13)+ chr(10);

If "C" in {ado.AgenciesNotified} then
result := result + "Public Lands" + chr(13) + chr(10);

// add as many similar searches for letters as you want

result; // displays the final answer after all searches



Jim Broadbent

 

[Storyteller]

Hello All,
I spent the day teaching clients a Excel 2000 Level 1 class. I love to see the light bulb go off above peoples heads when they get it.

I printed out and tried the various suggestions people had. The difficulty in working with multiple versions of Crystal Reports is that it becomes harder and harder to remember what functions work with what version.

As it turns out Join(), Split(), UBound() are not available in Crystal Reports 7. I would love to upgrade to Crystal Reports 8/8.5/9 however for a variety of reasons I'm unable to.

I tried Ngolem's (Jim Broadbent) last suggestion and it works great. I just had to remember to turn on "Can Grow" for the field. I'm in the process of documenting how/why it worked (since I will forget the next time I run into this problem ).

Regards,
"Light On above my head"
Michael Microsoft Office 2000 Master Instructor
Corel Certified Instructor WordPerfect 9

 

[varavoorvishnu]

Hi All,
Is it possible to loop an array value..
For instance i have an array declared "a" which holds the values of string from
"Monday" to "Sunday" stored in the array..
Incase i need to print all the values from the array at a time,what should i do??
I dont want to give hard corded way like a[1],a[2]..and so on.
Please suggest me..

Vishnu

 

[synapsevampire]

I already answered this, vara.

Checkl your other ost.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[varavoorvishnu]

Hi synapsevampire ,
I would like to tell u that i am using Crystal Reports 7.0 and so the formula u gave did not work..Please suggest another alternative for looping the array value and display the values at a time.

Vishnu

 

[Ngolem]

StoryTeller - Glad it worked for you. Sometimes it just takes another set of eyes. Jim Broadbent