Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Data Selection Post Reply Post New Topic
Page  of 2 Next >>
Author Message
bjfils
Newbie
Newbie


Joined: 03 Apr 2016
Online Status: Offline
Posts: 31
Quote bjfils Replybullet Topic: Data Selection
    Posted: 02 Nov 2021 at 3:27am
There's a field in our job ticket table where we indicate if a job has been moved to another location, which is a new process for us. To generate a report on that, I duplicated one that was for all jobs at my location and added a selection record showing {status} = "Anaheim" which works fine for the new report. However, when I add in {status} <> "Anaheim" to the original report, those records are still included in it.

There's also a subreport that looks at quallity cases related to the job tickets. On the new report, adding {status} = "Anaheim" works fine. When I put {status} <> "Anaheim" in that subreport, I'm now getting job tickets listed with anywhere from 1 to over 1,300 lines when they should only be listed once.

I'm a a loss as to why these things aren't working with the <> "Anaheim" selection criteria. Any ideas?
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 02 Nov 2021 at 6:31am
Could the <> "Anaheim" be an OR condition?

The way that I would troubleshoot would be to display all the parts of you selection criteria as a formulas...
put each IF section or part of an AND into its own formula and display it on the report...If everything looks as expected, I would start adding formulas that combine elements. I have found that what I thought was happening in my head, was not what Crystal was seeing.

HTH
IP IP Logged
bjfils
Newbie
Newbie


Joined: 03 Apr 2016
Online Status: Offline
Posts: 31
Quote bjfils Replybullet Posted: 02 Nov 2021 at 7:14am
Here's the selection criteria that is working as expected:
{Ticket.DateShipped} in LastFullMonth and
{Ticket.CustomerNum} startswith ["W"] and
{Ticket.Ink_Status} = "Anaheim"

And here's the criteria that doesn't work:
{Ticket.DateShipped} in LastFullMonth and
{Ticket.CustomerNum} startswith ["W"] and
{Ticket.Ink_Status} <> "Anaheim"
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 02 Nov 2021 at 10:26am
Looking at the code, I agree with you, it doesn't make sense.


I would try creating formulas for line1 like {Ticket.DateShipped} in LastFullMonth. do the same for lines 2 and 3. Put them on the report, run the report.
I would also display the 3 fields. You would expect that all would return true. Then you can start to see why.

My thought was that Anaheim is cased differently, like all caps.

This is how I debug formulas, since Crystal is pretty much a black box and you cannot see its reasoning as to why it does stuff.

HTH

IP IP Logged
bjfils
Newbie
Newbie


Joined: 03 Apr 2016
Online Status: Offline
Posts: 31
Quote bjfils Replybullet Posted: 03 Nov 2021 at 3:50am
I'm not sure what you mean by creating a formula and putting them on the report. Isn't that already the case? My Crystal knowledge is all self taught and pretty basic.

When I display the Ink Status field on the subreport, it doesn properly show the contents of that field. The issue there becomes some records end up repeating hundreds of times when there's only one actual record in the related table.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 03 Nov 2021 at 6:36am
we know that something is going wrong with the report. we don't know why. we cannot 'see' why Crystal is displaying stuff that it should be.

I am self taught as well. the way that I 'see' what Crystal is doing to break a larger formula into smaller formulas so that I can see how part of the larger formula is interacting.

In your formula there are 3 lines/conditions. I would see what each line looks like. Does Crystal return a true or a false as expected. If Crystal is returing true and expect a false, that will give you an idea of what to look at to make Crystal return the correct value.

Let's say that you find some entries as Anaheim, some as anaheim and some as ANAHEIM. We know that the are all the same, but Crystal doesn't. The solution would be to use UCASE or UPPER, I don't remember which on off hand so that he comparison is always the same and correct.

Hope this is clearer than mud...
IP IP Logged
bjfils
Newbie
Newbie


Joined: 03 Apr 2016
Online Status: Offline
Posts: 31
Quote bjfils Replybullet Posted: 04 Nov 2021 at 10:26am
It's thin mud. How do I get a formula to show a true or false in this instance? I did try creating a report from scratch using each statement one at a time. Everything was fine until I added {Ticket.Ink_Status} <> "Anaheim". Then I get a lot of records repeating on the report dozens of times, even though there's only one record for each.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 08 Nov 2021 at 4:52am
There has to be something that we are missing.  I have not seen this kind of behavior in Crystal.  You can always create a formula with the condition, i.e., if {Ticket.Ink_Status} <> "Anaheim" then 1 else 0.
Then see if it is working as expected.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 10 Nov 2021 at 8:54am
This is probably a data issue. What other tables are you linking to? Also, have you tried turning on "Select Distinct Records" in the database menu? If that doesn't work, we may need to see your actual report design as I suspect there is a one-to-many link somewhere in the data that is causing your issues.

-Dell
IP IP Logged
bjfils
Newbie
Newbie


Joined: 03 Apr 2016
Online Status: Offline
Posts: 31
Quote bjfils Replybullet Posted: 16 Nov 2021 at 9:40am
Sorry for the delay in responding. I had something else come up that was more pressing.

I tried changing the one condition to if {Ticket.Ink_Status} <> "Anaheim" then 1 else 0. That kicked out an error about needing a boolean.

There are only two tables involved and only one link between them, that being customer number. I tried adding another link for the ticket number. Same result.

The report works fine when = "Anaheim" is the criteria. Change that to <> "Anaheim and is gets messed up.
IP IP Logged
Page  of 2 Next >>
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.