Author |
Message |
charlese
Newbie
Joined: 21 Apr 2011
Online Status: Offline
Posts: 33
|
Topic: Sorting on a custom with multiple columns Posted: 21 Oct 2013 at 7:17am |
Hi, I have got a report which is displaying fee detail information and I need to create a sort. My condition is as follows i.e. if there is no sort string I want to sort by work date and the sort string else I want to sort by work date and timecard orginal index. I created a formulae like this If IsNull ({ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}) then toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex}) else toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.SortString}) Then in from the Report menu selected Record Sort Expert and choose my new custom field i.e. the formulae. However this does not work, report is not sorting Can anyone please help me. The datatype for Work date is datetime and the datatype for timecard orignal index is integer. Hence that is why I am using ToText to convert it to a string. However if I just have workDate in the formulae it works but as soon as I add the time card orignal index, it does nto work. Kind Regards Emma
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Oct 2013 at 8:33am |
if you place the formula field in your details section does it properly display for every row?
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 21 Oct 2013 at 9:44am |
could there be a null in the time card original index?
as we all know, adding a NULL to anything is a NULL, and CR won't display an error, which is probably what DBlank was getting at by asking if the formula returns the correct information for every line of detail.
HTH
|
IP Logged |
|
charlese
Newbie
Joined: 21 Apr 2011
Online Status: Offline
Posts: 33
|
Posted: 21 Oct 2013 at 10:24am |
Hi, I place the formulae in the detail section and the concatenation of work date and timecard orginal index is not working, it is printing nothing. I have put the timecard original index on the detail to see it's value. I can see it is displaying it correctly, there is no null values but the concatenation is not working. In the report it is something like this : Work Date TimeCare Orignal Index Concatenation 23/04/2013 7,208,97 Nothing 29/04/2013 7,212,353 Nothing If IsNull ({ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}) then toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex}) else toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.SortString}) The first part of this if statement is not working but the second part is working as I can see the concatenation of the formula on the report. Is something wrong with this toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex}) please ?
|
IP Logged |
|
charlese
Newbie
Joined: 21 Apr 2011
Online Status: Offline
Posts: 33
|
Posted: 21 Oct 2013 at 10:29am |
Why would toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex}) return nothing ? Is something wrong with this concatenation. Work date is datetime and TimeCard1__OrigTimeIndex is an integer However the second part i.e. Text({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.SortString}) works fine and I can see the actual value properly concatenated.
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 21 Oct 2013 at 10:36am |
I know that it will sound redundant, but have tried a formula like:
toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex})
perhaps, the concatenation is ok, perhaps it the the field {ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}...maybe it is not null, but an empty string.
debugging CR can be hard because you think the data looks one way, and it doesn't.
HTH
|
IP Logged |
|
charlese
Newbie
Joined: 21 Apr 2011
Online Status: Offline
Posts: 33
|
Posted: 21 Oct 2013 at 10:49am |
Ok I have tried this ... but still nothing... the first part of the if statement i.e. the concatenation of work date and timecard index is nothing.. If IsNull ({ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}) OR {ListofProfDetailTime.SortString} = '' then CStr({ListofProfDetailTime.WorkDate} & Cstr({ListofProfDetailTime.Timecard1__OrigTimeIndex})) else CStr({ListofProfDetailTime.WorkDate} & Cstr({ListofProfDetailTime.SortString}))
|
IP Logged |
|
charlese
Newbie
Joined: 21 Apr 2011
Online Status: Offline
Posts: 33
|
Posted: 21 Oct 2013 at 10:52am |
Don't understand why concatenation of a datetime and integer is not working !! Work Date TimeCard Orignal Index Concatenation 23/04/2013 7,208,97 Nothing 29/04/2013 7,212,353 Nothing Why would this above not concatenate ????
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 21 Oct 2013 at 10:52am |
hmmm...
just create a formula that would display the concatenation that we think is not working. see if that displays correctly. If it does, then the next step is to display the value in the IF...and see what that value is
|
IP Logged |
|
charlese
Newbie
Joined: 21 Apr 2011
Online Status: Offline
Posts: 33
|
Posted: 21 Oct 2013 at 10:55am |
The second part of the statement is getting concatenated properly. I can see these values on the report where 23/04/2013 00:00:00 is work date and 1 is the sort string23/04/2013 00:00:001 23/04/2013 00:00:002 23/04/2013 00:00:003
|
IP Logged |
|
|