Select with search for a string

[WANguy2k]

I'm trying to do a select based on the value of a text field. I want to exclude records that contain the words "damaged" or "out of stock" in the item description. It could contain other text as well, so I need to search the string for those words or phrases. I've tried "is not like" and tried using wild cards, but the report is not excluding those records. Thanks in advance for your help.

 

[Turkbear]

Hi,
Try this in your record seelction formula:

InStr("damaged",{Table.TextField}) < 1
and
Instr("out of stock",{Table.TextField}) < 1

[i]If Case sensitivity is an issue use:[/i]

InStr("damaged",{Table.TextField},1) < 1
and
Instr("out of stock",{Table.TextField},1) < 1

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[WANguy2k]

It didn't work. Here's what I have:

A field @Dc1 calculated by adding two item description fields together: trim({VINITEM.ICDSC1})+trim({VINITEM.ICDSC2})

Here's a portion of the selection criteria:

InStr("damaged",{@Dc1 },1) < 1 and
Instr("out of stock",{@Dc1 },1) <1

Here's one of the records that should have been excluded:
10980905,LF-SHALIMR-ET1DM,746480980905,GUERLAIN,SHALIMAR by GUERLAIN - EDT SPRAY (DAMAGED BOX),1 OZ,1,45.00,13.75,58,

Thanks again.

 

[Turkbear]

Hi,
If @Dc1 is part of the report, the selection criteria will not apply to it. Either use it as a supression formula or test the database fields for those strings.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[WANguy2k]

This ended up working, I got it from another report. Problem is, I see no reference to using % as a wildcard in the XI book, so I have no idea where this came from.

not ({@Dc1 } like ["%DAMAGED BOX%", "%DAMAGED%", "%UNBOXED%", "*Damaged Box*", "OUT OF STOCK"])

 

[Turkbear]

Hi,
Wildcards can be database specific, so your dataabse must use % ( like Oracle, for instance).

Glad you got it sorted out.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I think the following would have worked, as it appears case was the issue (and the sequence of the function arguments):

InStr(ucase({@Dc1 }),"DAMAGED") = 0 AND
Instr(ucase({@Dc1 }),"OUT OF STOCK") = 0

-LB

 

[Turkbear]

Hi,
Good spotting LB, I missed the order needed in the function, but shouldn't the 1 make the comparison case-insensitive?

InStr (start, str1, str2, compare)
Arguments
start is the character in str1 where the search is to begin. This is a 1 based index. (This argument is optional.)
str1 is the text string to be searched.
str2 is the text string being sought within str1.

compare is an optional number value indicating which type of string comparison should be used. 0 indicates case-sensitive comparison, and 1 indicates case-insensitive comparison. If this argument is not used, a case-sensitive comparison is performed.

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Thanks for clarifying. I did look at the function, but didn't take the extra step to check out the meaning of the third argument, so yes, you are right, that should do the trick.

-LB