Last record of the second table. 1

[MarsianStar]

Hello All,
CRXI & Oracle,
Call Center Enviroment. I would like to show the last journal entered in each ticket selected.
want it to look like this

Incident#(Num) Account(Num) Name(Str) ---LastJournal(memo)

Each incident could have a lot of journals, I am only interested in the very last one or two. Any Ideas?

 

[satinsilhouette]

Get the max date of the journal entry should give you the last journal entry.

Thanks so much!
satinsilhouette

 

[MarsianStar]

Hello satinsilhouette

thanks for your help, but that got me the last entry of all journals in all of the tickets.

 

[PatriciaObreja]

It should be grouped by incident.

 

[MarsianStar]

Thanks patriciaobreja

I looked over that thread

http://www.tek-tips.com/viewthread.cfm?qid=1443723

and now, I have another problem. Tickets with no journal entries do not show up.

My joint is set as left outer. The sum shows 3 but one of those 3 incidents does not have journal entries so it does not show at all.

 

[PatriciaObreja]

Can you show me the Select statement please?

 

[lbass]

Which method did you use from the thread you referenced?

-LB

 

[MarsianStar]

I used yours
{command.timestamp} = maximum({command.timestamp},{command.ticketnumber})

 

[MarsianStar]

SELECT "CALLLOG"."CALLID", "CALLLOG"."RECVDDATE", "CALLLOG"."NATURE",
"CALLLOG"."ITEM", "CALLLOG"."CALLSTATUS", "JOURNAL"."ENTRYDATE",
"JOURNAL"."ENTRYTIME", "CALLLOG"."CALLDESC", "JOURNAL"."ENTRYTEXT"
FROM "HEAT"."CALLLOG" "CALLLOG" LEFT OUTER JOIN "HEAT"."JOURNAL" "JOURNAL"
ON "CALLLOG"."CALLID"="JOURNAL"."CALLID"
WHERE "CALLLOG"."CALLSTATUS"<>'Closed' AND "CALLLOG"."NATURE"='MODEM'
ORDER BY "CALLLOG"."ITEM", "CALLLOG"."CALLID

 

[PatriciaObreja]

And in Crystal you group by a field that has null values.

The null values maybe should be replaced with a generic text:
if isnull({command.ticketnumber}) then
"No Ticket"
else
{command.ticketnumber}

 

[MarsianStar]

CALLLOG"."CALLID" can not be null,
I just checked
//@NullJournal
StringVar ticketid;
if isnull ({JOURNAL.ENTRYTEXT}) then (ticketid:= "Does not Have Any Journals")
else ticketid:= "Entered";
ticketid

they all show "Entered" and the ones that are not showing are do not have. They are in the selection though, because they are counted in the summary.

 

[MarsianStar]

if isnull ({JOURNAL.ENTRYTEXT})
Forgot to mention that I tried Jorurnal.callid, entrytime, entrydate. Still does not work.

 

[PatriciaObreja]

Instead of "Entered" leave the actual value of the TicketID field, because we are grouping by it.

 

[MarsianStar]

I think it has to do with the statement in the bottom.
I think it is being supressed because it does not meet the condition below.

{command.timestamp} = maximum({command.timestamp},{command.ticketnumber})

 

[lbass]

You cannot use the exact language from the other post, since those are not the names of your fields. Nor are you using a command, so the table names are wrong, too.

-LB

 

[MarsianStar]

I changed them to meet my field names otherwise it would not have worked at all.
{@JournalDateEntered} = maximum ({@JournalDateEntered},{CALLLOG.CALLID})

I am wondering if I can make an IF statement that will look like this


If ( There is at lease one journal) then {@JournalDateEntered} = maximum ({@JournalDateEntered},{CALLLOG.CALLID})

How can I accomplish that?

 

[lbass]

What is the current problem? If you placed the formula:

{@JournalDateEntered} = maximum ({@JournalDateEntered},{CALLLOG.CALLID})

in report->selection formula->GROUP, only the most recent record per call ID would be displayed.

-LB

 

[MarsianStar]

The problem is that there are tickets with out any journals at all. So I have to find a way to have either the last journal show up, or a statement such as "No Entries" added instead of the journal entry. That is why I have a left outer joint.

Is there another approach, where I can capture the last journal entry in a variable?

 

[lbass]

Try a group selection like this:

isnull({journal.ID}) or //replace with your linking field
{@JournalDateEntered} = maximum ({@JournalDateEntered},{CALLLOG.CALLID})

-LB

 

[MarsianStar]

That was it, Thanks A LOT lbass.