if then issues
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20924
Printed Date: 02 May 2024 at 1:05pm
Topic: if then issues
Posted By: hjorrdis
Subject: if then issues
Date Posted: 29 Jul 2014 at 7:13am
I am trying to set up a predictive date sort - so certain scenarios have different due dates.
I've set up a formula field as follows:
if isnull({@ApptDate})
then if isnull({@ScheduledDate})
then if isnull({order_.apptTimeframe})
then cdate(dateadd("d",14,{@OrderedDate}))
else {@FinalTimeFrame}
else cdate(dateadd("d",14,{@ScheduledDate}))
else cdate(dateadd("d",7,{@ApptDate}))
It is returning all of the correct dates for the scenarios, except for @ScheduledDate, which comes back null. At first I thought maybe there was something wrong with @ScheduledDate, but it is formatted exactly like @ApptDate which does return correctly. On these records, @ApptDate and order_.apptTimeframe are both null.
Any ideas for why it would be blank? You would be my hero.
|
Replies:
Posted By: kevlray
Date Posted: 29 Jul 2014 at 8:30am
|
Of course the first question. What does the formula for @ScheduledDate look like? Which of course is dependent on the data.
|
Posted By: hjorrdis
Date Posted: 29 Jul 2014 at 8:34am
It is:
if isnumeric({order_.scheduledDate})
then date(tonumber(left({order_.scheduledDate},4)),tonumber(mid({order_.scheduledDate},5,2)),tonumber(right({order_.scheduledDate},2)))
And it does display on the same record returned by the report
|
Posted By: kevlray
Date Posted: 29 Jul 2014 at 12:04pm
I am not sure why it is a null (of course the @ScheduledDate should return a Null if order_.scheduledDate is not numeric). I did notice something odd (even though I do not think it would make a difference) is that you are converting dates to dates. The logic of the if statement looks correct, but nested if's logic can be deceiving.
I do not have any suggestions.
|
Posted By: hjorrdis
Date Posted: 04 Aug 2014 at 6:40am
|
I have to convert all of the dates to dates because in the database they are actually string fields (It's an electronic health record). I am going to play around and if I figure out where the problem is, I'll post my solution here. Thanks for your help!
|
Posted By: hjorrdis
Date Posted: 04 Aug 2014 at 9:03am
|
It looks like it was something weird with @ApptDate. If I assigned it a dummy value when it was null (date(0,0,0)), I could add that to the isnull condition. It then worked just fine.
|
Posted By: DBlank
Date Posted: 04 Aug 2014 at 9:05am
|
inside the formula editor try changing the option for how to handle NULLS to 'use default values for nulls'
|
|