Skip to Content

Omitting records from one table depending on data from another table

I have a client's table and a charges table:

If a client has a felony charge then I don't want to include that client in my report at all.

Currently, I have the following code in my record selection:
not ({charges.chargesType} = "Felony")
so it will still include client 1 and client 3 just not the felony rows.
I don't want client 1 nor client 3 in my report at all since they have a felony record. How can I omit them from my report?

clients.png (7.0 kB)
charges.png (8.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 17, 2018 at 08:40 AM

    Hi Dee,

    You can create a Custom SQL query and use that as the report's datasource - this would be more efficient too. However, if you wish to do this without a query, please try the following:

    1. Remove the Record Selection Formula

    2. Insert a Group on the Client ID field

    3. Suppress the Group Header and Group Footer

    4. Create a formula (@Felony) with this code:

    If  {charges.chargesType} = "Felony" then 1 else 0

    5. Go to the Report Menu > Selection Formulas > Group and use this code:

    Sum({@Felony}, {ClientID}) = 0

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 17, 2018 at 07:25 PM

    Thank you... it totally worked.

    Add comment
    10|10000 characters needed characters exceeded