| Author |
Message |
bjfils
Newbie
Joined: 03 Apr 2016
Online Status: Offline
Posts: 32
|
   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 Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
bjfils
Newbie
Joined: 03 Apr 2016
Online Status: Offline
Posts: 32
|
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 Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
bjfils
Newbie
Joined: 03 Apr 2016
Online Status: Offline
Posts: 32
|
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 Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
bjfils
Newbie
Joined: 03 Apr 2016
Online Status: Offline
Posts: 32
|
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 Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
bjfils
Newbie
Joined: 03 Apr 2016
Online Status: Offline
Posts: 32
|
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 Logged |
|
|
|
