Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Need AND & OR formula for report Post Reply Post New Topic
Author Message
DrewEC
Newbie
Newbie


Joined: 02 Sep 2020
Online Status: Offline
Posts: 7
Quote DrewEC Replybullet Topic: Need AND & OR formula for report
    Posted: 21 Dec 2022 at 9:14am
Hello there,

Thank you for your assisting. I'm trying to create a AND & OR formula that filters for unitcodes that starts with ("10" or "TH" or "CO") AND starts with ("WE").

The logic statement is, show me all incidents that have a unitcode dispatched (all of this is filtered for already) with a unitcode that starts with either "10" or "TH" or "CO" ALONG with any unitcode that starts with "WE".

I currently have this formula which is not working:

({incilog.unitcode} startswith ["10"] or {incilog.unitcode} startswith ["TH"] or {incilog.unitcode} startswith ["CO"]) and
({incilog.unitcode} startswith ["WE"])


Any help is appreciated.


Edited by DrewEC - 21 Dec 2022 at 9:14am
IP IP Logged
DrewEC
Newbie
Newbie


Joined: 02 Sep 2020
Online Status: Offline
Posts: 7
Quote DrewEC Replybullet Posted: 21 Dec 2022 at 10:50am
I'm also attempting this from a suppression standpoint as well which is something like:

If
{incilog.unitcode} like ["10*", "CO*", "TH*" and "WE*"]
then false
else if {incilog.unitcode} like ["WE*"]
then true
else if {incilog.unitcode} like ["10*", "CO*", "TH*"]
then true
else true

The logic for the above is, if the {incolog.unitcode} field contains BOTH anything that starts with 10*, etc etc, AND anything that starts with WE, don't suppress , otherwise suppress everything else (including anything that only has a unitcode that starts with 10*, etc. or WE* by itself.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 03 Jan 2023 at 8:35am
You can't have a single unitcode that starts with (10 or CO or TH) and also starts with WE. So really what you need is starts with 10 or CO or TH or WE. This will get you records with unit codes that start with any of the four options.

Try this for a formula if you just need to identify the records:

Left({incilog.unitcode}, 2) in ["10", "CO", "TH", "WE"]


If you're using this in the Select Expert to filter the records coming into the report, you don't want to do it that way, though, because Crystal can't pass its own formulas to the database for processing and will pull ALL of the data into memory and filter it there. Instead, you'll do this in the Select Expert formula:

(
{incilog.unitcode} StartsWith "10" OR
{incilog.unitcode} StartsWith "CO" OR
{incilog.unitcode} StartsWith "TH" OR
{incilog.unitcode} StartsWith "WE"
)

Note the use of parentheses - if you have any other selection criteria, you MUST have them in order for the filter to work correctly.

-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.