Selecting one value from a field that allows multiple values

[campsb]

I need to select one value from a field that allows multiple values in the field for each record. For example, the field contain, A,B,HR,FIN. I need only the records that contain HR. Which functions works for this?

 

[IdoMillet]

--------------------
"HR" IN {your_field}
--------------------

hth,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

It's inefficient, but in the record selection formula use:

instr('HR', {table.Field}) > 0

If you're using a SQL Server database (or any SQL compliant database and connectivity), you can create a SQL Expression to create a test of the condition and filter accordingly in the record selection formula, offloading the work to the server, as in the following SQL Expression which I named instrtest:

CHARINDEX('HR', Table."multifield&quot

Then in the record selection formula use:

{%instrtest} > 0

This will limit the rows from the database.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ido offered a nice alternative to my first suggestion, but again, this won't be passed to the database, it will be processed by Crystal (slower performance).

But if you opt for that approach, use his.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[campsb]

Thanks, now for an added feature. If I want to only show the one code I am pulling how do I suppress the remaining codes in that field?

 

[synapsevampire]

Rather than suppress the others, just use "HR" in a text object.

Since the record selection has already qualified them all as HRs, just use HR as text or in a formula.

The means to determine if something contains HR in it would be something like:

if "HR" IN {your_field} then
"HR"
else
"Nope..."

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com