Author |
Message |
hjorrdis
Newbie
Joined: 13 Sep 2013
Location: United States
Online Status: Offline
Posts: 8
|
Topic: if then issues 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.
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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.
|
IP Logged |
|
hjorrdis
Newbie
Joined: 13 Sep 2013
Location: United States
Online Status: Offline
Posts: 8
|
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
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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.
|
IP Logged |
|
hjorrdis
Newbie
Joined: 13 Sep 2013
Location: United States
Online Status: Offline
Posts: 8
|
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!
|
IP Logged |
|
hjorrdis
Newbie
Joined: 13 Sep 2013
Location: United States
Online Status: Offline
Posts: 8
|
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.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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'
|
IP Logged |
|
|