Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trouble selecting records in crystal reports 9

Posted on 2011-02-19
26
Medium Priority
?
430 Views
Last Modified: 2012-05-11
I am using the following selection in CR9 and it will not return the 3rd selection ( {Sales.Salesman}= {?Emp}) I have tried moving the code so that quote is last and then it will not selects quotes

{Sales.InvDate} in {?date} to {?edate} and {SalesEmp.Emp} = {?Emp} or
{Sales.Salesman} = {?Emp} and uppercase({Sales.InvType}) = "QUOTE" and {Sales.Salesman}= {?Emp}

Thanks
George
0
Comment
Question by:GeorgeSalet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
  • 5
26 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34934243
(
{Sales.InvDate} in {?date} to {?edate}
and {SalesEmp.Emp} = {?Emp}
)

or

(
{Sales.Salesman} = {?Emp}
and uppercase({Sales.InvType}) = "QUOTE"
)


The parentheses are necessary in order to enforce criteria being evaluated together.

On a side note, your uppercase({Sales.InvType}) won't be passed to the database for processing, since it's basically a runtime formula.  If you are connected to a SQL-based database (SQL Server, Oracle, etc..) then I'd recommend you replace it with a SQL Expression field.  If you list the database you're using, we can help with the syntax.

~Kurt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34934297
How about trying it this way

{Sales.Salesman} = {?Emp}
and
(
   uppercase({Sales.InvType}) = "QUOTE"
   OR
   {Sales.InvDate} in {?date} to {?edate}
)

Can any of the fields be NULL?
If so you have to handle that case by testing for it before you test the field.

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34934355
That was my first thought, too, until I saw he was actually using two different fields against the {?emp} parameter.

~Kurt
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:GeorgeSalet
ID: 34934489
I made those changes but I still can't get the second group to show, here is my code
(({Sales.InvDate} in {?date} to {?edate}) and ({SalesEmp.Emp} = {?Emp})) or
(({Sales.InvDate} in {?date} to {?edate}) and ({Sales.Salesman} = {?Emp}) and ({Sales.InvType} = "Quote"))
Thanks
george
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34934534
You have way too  many parentheses - - you don't need to encapsulate a single filter in parentheses.  This is much easier to read:

(
  {Sales.InvDate} in {?date} to {?edate}
  and {SalesEmp.Emp} = {?Emp}
)

or
(
  {Sales.InvDate} in {?date} to {?edate}
  and {Sales.Salesman} = {?Emp}
  and {Sales.InvType} = "Quote"
)

That being said, try this:

{Sales.InvDate} in {?date} to {?edate}

and

(
{SalesEmp.Emp} = {?Emp}

or
  (
  {Sales.Salesman} = {?Emp}
  and {Sales.InvType} = "Quote"
  )
)

As for the second condition not evaluating, are there any circumstances in which both the {SalesEmp.Emp} and {Sales.Salesman} will ever be the same?  That could cause problems.

~Kurt
0
 

Author Comment

by:GeorgeSalet
ID: 34934557
Yes in most cases the salesemp.emp and sales.salesman would be the same except in quotes
0
 

Author Comment

by:GeorgeSalet
ID: 34934621
I have two main tables in this report 1, is the invoice table that contains the salesman number and the 2 is the salesemp table that keeps track of the time spent on the invoice by multiple employees.
When a quote is completed no records are in the salesemp table just a employee number in the sales.salesman field.  I can't do a query on just the sales.salesman field because multiple employees will wrk on the on under salesemp. I am open to other options
Thanks
George
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34934712
It seems like you might benefit from a UNION statement - query each of the two tables independently of each other and append their recordsets to each other.  Do you have any experience with SQL?

Also, what do your table joins look like?

~Kurt
0
 

Author Comment

by:GeorgeSalet
ID: 34934789
Sales links to salesemp by invoice number.
Not much experience in sql

George
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34934914
Missed the different fields also.

Try them separately
First try the first part.  Do youget the records you expect
{Sales.InvDate} in {?date} to {?edate} and  {SalesEmp.Emp} = {?Emp}

Then try the second part.  Do you get the records you expect?
{Sales.InvDate} in {?date} to {?edate} and {Sales.Salesman} = {?Emp} and {Sales.InvType} = "Quote"

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34935140
Well Something is a miss
I selected the file by period (month) no other qualifiers and every quote invoice is skipped, I am stumped
George

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34935744
Do the quote invoices have dates or are they NULL?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34935939
The quote dates have both invoice dates and period dates, nothing null in the search perimeters, has to be something basic??
Thanks
George
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34937806
You are saying you used

    {Sales.InvDate} in {?date} to {?edate}

and got no sales of type QUOTE?

Did you try just
   {Sales.InvType} = "Quote"

What do you get?
Do the records have {Sales.InvDate} dates in the range?

If we take a step back -
What records are you trying to pull in words.

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34937909
because I was having trouble getting quotes to show, I used" sales.period =201101" which should show every invoice in the sales table for the month of January. All invoices were shown except quotes. Something else must be conflicting??

George
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34937961
Do quote records have a value in sales.period?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34937992
I didn't answer your question. I am trying to list all the invoices and quotes for a date range for a specific employee. Quotes only are listed in the sales table and the employee is the sales.salesman field. when the quote becomes a job the employee time is in a table called Salesemp the employee is listed in salesemp.emp.

The propose of the report is to quantify how much a employee brought in, how many quotes, callbacks,warranty and how many hours they got paid for, how many hours worked, traveled, lost etc and of course closing rate.

Sorry about being so long winded
George
0
 

Author Comment

by:GeorgeSalet
ID: 34938001
Yes I have approximately 6 records, checked it with utility that views tables amd allows you to see all fields in record
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34938234
Do quotes have a date in the InvDate field?
If not then do they have a date in some field or do quotes that don't go final just get deleted?

If I understand this, the SalesEmprecord isn't built until the quote changes to a job.  If that is the case the SalesEmp.Emp is NULL for quotes.

Try
{Sales.InvDate} in {?date} to {?edate}

and
(
    (
    Not IsNull({SalesEmp.Emp})
    AND
    {SalesEmp.Emp} = {?Emp}
    )
    or
    (
    {Sales.Salesman} = {?Emp}
    and
    {Sales.InvType} = "Quote"
    )
)

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34945201
I have started a new report to find out what is happening, when I add anything from "salesemp" table it removes the quotes from the report (a quote does not have a record in "salesemp")

I have tried this and it does not work but I think I am on the right track
if isnull({SalesEmp.Credit})then 0 else {SalesEmp.Credit}

Thanks
George
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34945342
How are the tables joined?

You need to use a LEFT OUTER JOIN

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34945562
I looked up left outer join in the manual but it doesn't explain how to do it??  I think we are getting to the bottom of this.
ALL your help is appreciated
George
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 34946333
Are you joining tables in Crystal?
If so
Open the report
Click DATABASE --> DATABASE EXPERT
Click the LINK tab
Right click the join between the tables
CLick CHANGE JOIN
Choose LEFT OUTER

mlmcc
0
 

Author Closing Comment

by:GeorgeSalet
ID: 34946663
Thanks For All the help
George
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34947593
Glad you got it working! Good catch on the joins, mlmcc!

~Kurt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34947704
Finally got the right clues.

mlmcc
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question