Select Expert Formula question 2

[marlow1208]

I have members in a table that are assigned a HMP Code, but a member can show up in the table several times with differnt HMP Codes. I would like to extract members who have a record with HMP code "fchfen", but exclude those members who also have a record with an HMP Code of "fepedb". I am currently using the following formula in the Select Expert, and the output does not exlude members who have a record with "fepedb" as its HMP Code.

{CME_CASE_MANAGEMENT_EPISODE.CME_HMP_CODE} = "fchfen" and
{CME_CASE_MANAGEMENT_EPISODE.CME_HMP_CODE} <> "fepedb"

Can anyone help with this? I am fairly new to Crystal and can't figure this out.

 

[jmd0252]

so the only code you want is fchfen?? or do you want all codes excluding fepedb??

 

[marlow1208]

I want members who have the code fchfen, but do not have the code fepedb. A member can several records with several different codes, but I only want members who have a record with fchfen and do not have a record with fepedb.

For example, if the records below existed in the table, Member1 should be the only member output because Member1 has a record with fchfen, but does not have a record with febedb. Member2 would not be output because, although Member2 has a record with fchfen, they also have a record with febedb.

Member1, fchfen
Member1, fcaden
Member1, fasten
Member2, fchfen
Member2, fedben
Member2, fcaden
Member2, fasten

 

[lbass]

Use a record selection formula like this:

{CME_CASE_MANAGEMENT_EPISODE.CME_HMP_CODE} in ["fchfen","fepedb"]

Then create a formula {@hasfepedb}:

if {CME_CASE_MANAGEMENT_EPISODE.CME_HMP_CODE}= "fepedb" then 1

Insert a group on {table.member} and then go to report->selection formula->GROUP and enter:

sum({@hasfepedb},{table.member}) = 0

You will need to use running totals if you need to summarize across members, since non-group selected records would still contribute to the more usual inserted summaries.

-LB

 

[marlow1208]

Thank you! This worked for me.