INSTR Function

[IronRocket]

A quick question. I have a cross-tab and I put a formula in the row section. The formula is supposed to grab all the database fields that begin w/ "HAM". Here is the following formula that I have:

if instr({Asgnmnt.GroupName},"HAM") then
{Asgnmnt.GroupName}

This gets a message of "boolean required here". I know this is something simple because I've came across this before and made it work, but I don't think it was w/ the instr function. I'm stuck on stupid since it's Friday...can anyone help?

 

[CoSpringsGuy]

if instr({Asgnmnt.GroupName},"HAM") = 1 then
{Asgnmnt.GroupName}


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[IronRocket]

CoSpringsGuy:

I went ahead and copied/pasted your formula in...I got "The formula result must be a boolean". Thank you for the help, just wondering if there is another way?

 

[beltmanjr]

Dont you just want

if instr({Asgnmnt.GroupName},"HAM") then 
  true
else
  false

 

[CoSpringsGuy]

belt,
instr should return the position that the second string was found in the first string. Thats why my suggestion was "=1".

iron,
Im not sure why there is still a boolean error though. I tested it on my side before I sent the suggestion and it worked fine. I even added it to a crosstab and it worked... Although, there were three rows included
HAM
empty
Null

not sure if that is your desired results or not.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[beltmanjr]

ah sorry,
I meant the outcome of the if then statement to be true or false.

I suspect this isnt used in a formula but in a formatting formula orso. Hence the result must be a boolean thing.

so whole thing

if instr({Asgnmnt.GroupName},"HAM")=1 then 
  true
else
  false

 

[CoSpringsGuy]

that sounds logical to me ... although the original post led me to believe otherwise ... can you clarify iron?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[IronRocket]

I'm not getting an error now but I'm trying to show the fields of anything that begins w/ "HAM" (for an example, "HAM IS" or "HAM Tech"). I'm getting for my rows as true and false.

So I put the formula as this:

if instr({Asgnmnt.GroupName},"HAM") =1 then
{Asgnmnt.GroupName}

Now the rows show me everything that shows HAM*. Thanks a lot - I appreciate your help!

 

[CoSpringsGuy]

ok .. I thought that was the same thing you said you pasted from my other post and still recieved an error?

never the less...

Im glad it is working!!

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[beltmanjr]

Lol. Same I was just about to write...
Ah well,. one problem less in the world

 

[IronRocket]

Again...thanks for everyone's help. I was going to start a new thread, but I'm really polishing up the same report, and I found a new problem. Again, my report is a cross-tab that has a parameter asking the user for what group. You guys helped me solve the problem. Now, I see another problem with the same report.

For an example, if I type in "HAM" or "HMA" on the parameter field prompts, everything works out fine except for the top row and column. Please see below:

Sev1 Sev2 Sev3 Total
4 5 3 3 15
HAM 701 SEC 0 2 1 2 5
HAM BAAN 0 3 0 2 5
HAM AEP 0 6 0 0 6
HAM ELP 2 2 1 1 6
______________________________________________________
Total 6 18 5 8 37

Remember, the above is a cross-tab. If you look at the first column and the first row, these numbers doen't have any fields that it belongs too. I'm thinking these are nulls, but maybe they are not. Tell me if I need to elaborate more and also, what is your advice on what to do in order to suppress these numbers. Thank you.

 

[lbass]

Those are the records that don't meet your criteria. You should use a record selection formula like:

{Asgnmnt.GroupName} startswith "HAM"

If you can't do that because the report needs to contain all records for other purposes, then insert s subreport for the crosstab where you use this record selection formula.

-LB

 

[IronRocket]

Lbass:

In the record selection, I put the formula:

{Asgnmnt.GroupName} startswith {?Group}

That did get rid of the unwanted first row but it still didn't get rid of the unwanted column.

At least we're progressing...the only thing to get rid of is the first (unwanted) column.

 

[lbass]

Well, you'd have to help us out here. What IS your column field? If it is a conditional formula, then use the condition in your record selection formula, as in:

and
{table.field} in ["Sev1","Sev2","Sev3"]

-LB

 

[IronRocket]

Thanks again Lbass.

I put the "{CallLog.Severity} is one of (Sev1, Sev2, Sev3...etc)" in the Select Expert - works just fine now.

 

[IronRocket]

I revisited this situation and I now need to put multiple values in the parameter field such as "HAM" and "HMA" to show both the HAM* & HMA* fields. So I went ahead and checked the box "Allow Multiple Values" on the Edit Parameter Field and then run the program. I typed in "HAM" and then add it to the Value field and then typed in "HMA" to add it to the value field as well. So far, so good. Then when I pressed "OK" - my formula editor box pops up with the formula inside it:

if instr({Asgnmnt.GroupName},{?Hello}) =1 then
{Asgnmnt.GroupName}
else
if {?Hello} = "" then
{Asgnmnt.GroupName}

THEN...I get the error box on top of it saying:
"This array must be subscripted. For example: Array ".

I'm assuming I have to create an array variable since I checkmarked multiple values on the parameter field?

 

[lbass]

What is the point of this formula? Because both clauses havea the same result, so it is the same as using:

{Asgnmnt.GroupName}

Where are you putting this formula?

-LB