11/25/2024 9:38:58 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss reports for SalesLogix including Crystal, SQL Reports, etc. View the code of conduct for posting guidelines.
|
|
|
|
Querying Blank Fields
Posted: 12 Mar 08 10:39 AM
|
Can anyone help out. I'm building a Crystal Report (v8.5) and need to bring in all of the history items where the history.category field is marked as either "New Business" or "Existing Business", but sometimes they leave this field blank (even though I don't think it's null) and don't choose one of those options. As a fail safe I want to also capture all of these "blank" records and label them as "Other", how would I adjust this query to do this?
{HISTORY.TYPE} in [262154, 262147, 262146, 262145] and
IF {HISTORY.CATEGORY} in ["Existing Business", "New Business"] then
{HISTORY.CATEGORY} = {HISTORY.CATEGORY}
Else IF {HISTORY.CATEGORY} ???? then {HISTORY.CATEGORY} = 'Other' and
{HISTORY.COMPLETEDDATE} = {?Date} |
|
|
|
Re: Querying Blank Fields
Posted: 12 Mar 08 11:28 AM
|
Tom create a regular formula and drop that in the details field , and inside the formula check the category and if exiting or new , then formula = history.category else formula = "other"
change the recordselectionformula to accept everything from the db,
alternate method is to create a sql view and do a case statement in there. I'd prefer this as all processing is done in the db and not in the CR viewert component.
|
|
|
|
Re: Querying Blank Fields
Posted: 12 Mar 08 11:47 AM
|
| Thanks, but I'm not quite sure I understand. Can you send me an example of how to write that formula? |
|
|
|
Re: Querying Blank Fields
Posted: 12 Mar 08 12:30 PM
|
Dim dblFromYear as NUmber
Dim dblToYear as Number
If ISNULL({ASSOC.FROM_YEAR}) Then
dblFromYear = -1
Else
dblFromYear = {ASSOC.FROM_YEAR}
End If
If dblFromYear = -1 or dblToYear = -1 Then
Formula = -1
Else
Formula = (dblToYear - dblFromYear ) + 1
End If
Change the script type to basic script.
|
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|