Omit 1 but keep 2 results

[pectin232]

Name: Jane Bell
Role: Supervisor
Phone: 712-419-2031
Address: 123 Main Street Armadillo Texas

Name: Jane Bell
Role: Therapist
Phone: janebell@gmail.com
Address: 234 North Hill St, Armadillo Texas


Name: Jane Bell
Role: Primary Worker
Phone: 712-419-2031
Address: 123 Main Street Armadillo Texas

How do I omit the Therapist position and only to show the supervisor role and primary worker role. Do I do this in SQL or can this be done in Crystal. I tried Section Expert but they kept showing. maybe I am not doing this correctly

 

[IanWaterman]

In select expert

{RoleField} <> 'Therapist'

or you can try

{RoleField} in ['Supervisor', 'Primary Worker']

Ian

 

[Madawc]

It's not clear what you're after: your example is a case where you want to omit example 2 but keep 1 and 3.

If it is the role you want to select on, then you need a line in the selection command that might say

{role} <> "Therapist"

Or perhaps

{role} in ["Supervisor", "Primary Worker"]

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

Please do not start new threads on the same topic. This appears to be continued from your earlier thread: thread767-1648570. You should instead clarify what happened when you tried suggestions so that responses can build on previous attempts at solutions.

-LB

 

[pectin232]

A person can sometimes have several roles and telephone.. but the telephone is sometimes an email address and that is what I want to omit. I tried this {Role} in ['Supervisor', 'Primary Worker'] but it did not work.

 

[pectin232]

My bad Linda... I forgot about that. I appreciate it. It will not happen again.

 

[lbass]

If you selected only those two roles by using it in the record selection formula like this:

{Role} in ['Supervisor', 'Primary Worker']

... the record with role = therapist could not have been returned. But if your goal was not related to the role, but to the telephone field, you could have used this in your selection formula:

instr({table.telephone},"@") = 0

-LB

 

[pectin232]

Problems I get is... some users have phone nos as an email address as well as a phone, thus making them unique and appear 2x or more. How do I show the user as only once?

 

[lbass]

Are there any cases wheere there is ONLY an email and no phone? You could group on name and then on role (assuming you are still only allowing those two roles into the report), and sort ascending on the formula:

instr({table.telephone},"@")

Then place the fields in the role group section and suppress the details. Then emails would only show if there is no phone number.

-LB

 

[pectin232]

Another problem is like say. Because of the unique nos of this she appears twice. This is just an exp of cases like this.. there are other variations of it. The grouping by name and then role works fine but how do I suppress if the person appears twice or even three times and just show it as once?

Jennifer Taylor
Role: Primary Worker Tel: (865) 376-3411

Jennifer Taylor
Role: Primary Worker Tel: (865) 376-4124

 

[pectin232]

In the Group Selection Formula it is

instr({Command.TELEPHONE},"@") = 0 or
or
GroupName ({Command.NAME}) < 1?

 

[lbass]

If you place the fields in the role group, the name and info will only appear once per role. The issue is what you want displayed for phones/email. Did you try my last suggestion? What was wrong with that approach? It would not have resulted in duplicates per role.

You could collect multiple instances and display them in one section, if that is the issue.

You need to clarify your ultimate goal here. You initially wanted the same person to appear once per role. Then you were concerned about emails versus phones. I'm not sure what the end product is that you want anymore.

-LB

 

[pectin232]

Thanks so much LB I got it working using a combination of different methods above. Correcting the query to get the base result sets and then using formulas to suppress it. I am wondering if I use this formula

if instr({Command.PHONE},"@")>0
then "Not Available"
elseif {Command.PHONE} = Null or 0 then ---[Tried isNull{Command.PHONE}] but did not work either---
{Command.PHONE} = "Not Available"
Elseif {Command.PHONE}.value = instr({Command.PHONE},"xxx")>0 then
"Database Error"
else {Command.PHONE}
......

How to I add more parameters to encompass more it to increase the logic?

 

[lbass]

You have to always test for nulls first, so:

if isnull({Command.PHONE}) or
val({command.phone}) = 0 or //assuming phone is a string
trim({command.phone}) = "" or
instr({Command.PHONE},"@")>0 then
"Not Available" else
if instr({Command.PHONE},"xxx")>0 then
"Database Error" else
{Command.PHONE}

Not sure what you meant by "{command.phone}.value =".

-LB

 

[pectin232]

I get most of those to show as 'Not Available' or Database error but I still get a few with so called 'Blanks'... but I also did this in the SQL portion and in the Format Editor >>> Display String

 

[lbass]

Not following what you did. Please post your actual formulas and identify where you are placing these. I don't see how you could get any blank fields.

-LB