Not in string operator

[jeffr1]

In a selection formula, I use the "IN" operator to select data based on text string existing in a field

<text variable> in <field>

but i also want to select the data when <text variable> does not exist in the field.

How do I incorporate Not into the formula so I only select data when a <text variable> is not in the <field>?

 

[Turkbear]

Hi,
In Crystal you can negate a test by using NOT:

NOT (<text variable> in <field>)
(The parens are optional if this is the only criteria specified, but I use them to clarify the scope of the NOT operator.)

the more intuitive
<text variable> NOT in <field>
does not work, however.





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

 

[jeffr1]

Thanks. I was trying to exclude data based on not existing in multiple fields and have now got it working.

 

[lbass]

You could use a command set up like this:

SELECT table.`ID`, table.`field`
FROM table
WHERE not exists (
select A.`ID`
from table A
where A.`ID` = table.`ID` and
A.`field` = 'ABC'
)

Or, you could allow all values of the field into your report, and then create a formula like this:

//{@hasABC}:
if {table.field} = "ABC" then
1

Insert a group on ID (in this example), and then go to report->selection formula->GROUP and enter:

sum({@hasABC},{table.ID}) = 0

If you need to do calculations across groups, you would need to use running totals, as the more usual inserted summaries would include values from groups that are not displayed. So, if you can use a command, it might be simpler
to work with the data for any calculations, but the command should be built so that it is your sole datasource.

-LB