Print Page | Close Window

This should be simple...

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12999
Printed Date: 06 May 2024 at 1:06am


Topic: This should be simple...
Posted By: a58strod
Subject: This should be simple...
Date Posted: 22 Apr 2011 at 3:10am
I swear this should be a straight forward formula but I'm hitting a wall.
 
I have 2 SQL tables; tblServiceOrders & tblReps
 
tblSerivceOrders.TechAssigned is linked with tblReps.RepID (numbers)
 
When you mark a service order as "unassigned" the TechAssigned field is 0
 
There is NOT a record in tblReps with a RepID of 0.
 
My goal is to group service orders by the assigned tech and also list the unassigned service orders on the report.  So far everything I've tried will not display service orders that are unassigned.
 
Here are the formulas I've tried:
 
If isnull({tblServiceOrders.TechAssigned}) then "Unassigned" else {tblReps.FirstName}
 
or
 
If {tblServiceOrders.TechAssigned} = 0 then "Unassigned" else {tblReps.FirstName}
 
or
 
If ToText({tblServiceOrders.TechAssigned}) = "0" then "Unassigned" else {tblReps.FirstName}
 
 
All of these group service orders by the tech's first name but unassigned service orders are MIA.
 
However,
 
If {tblServiceOrders.TechAssigned} = 0 then "Unassigned"
 
results in correctly marking unassigned service orders and leaving the rest of the service orders without anything in the field.
 
What am I missing?
 
Thanks!!!


Replies:
Posted By: lockwelle
Date Posted: 22 Apr 2011 at 3:39am

how are the tables linked?  if they are inner joined, there won't be any unassigned records.

isnull doesn't work, as the value is 0, not null.
totext doesn't work as the value returned is probably something like "0.00", try totext({table.field}, 0, "")
 
since the last one works but only gives unassigned and the middle one only gives the first name...it would seem that the middle one should work.  There is something about enforcing the join that might be at play. 
 
I've never really concerned myself with this as it doesn't affect my reports(they come from stored procs and only result in 1 wide table).  Between the enforce join and the inner / outer join, they might be clues .
 
HTH
 

Posted By: a58strod
Date Posted: 22 Apr 2011 at 4:06am
Genius!
 
Full Outer Join fixed the problem along with using the middle formula.
 
Thank you!


Print Page | Close Window