Use 'FETCH' to pull dates/no. of Client Contact

Solved a problem in an elegant manner and want to show off your code? Know a hard-to-find feature? Post it here for the benefit of others. Questions don't belong here.
Post Reply
Scott_Evans
Posts: 13
Joined: Thu Aug 30, 2012 4:24 pm

Use 'FETCH' to pull dates/no. of Client Contact

Post by Scott_Evans » Wed Apr 17, 2013 6:00 pm

Recently had a request from our management team to create a report that pulls of frequency of Client contact.

It is useful MI so thought I would share a way of doing this was creating the following maths fields:-

#Total Tel Calls to Client
result = FETCH( "COUNT" , "History" , "action_code" , "FOR EACH History NO-LOCK WHERE History.case_no = '" + {case.key} + "' AND (action_type = 'MC' AND corr_type = 'CL' )" )

#Total Tel Calls from Client
result = FETCH( "COUNT" , "History" , "action_code" , "FOR EACH History NO-LOCK WHERE History.case_no = '" + {case.key} + "' AND (action_type = 'TC' AND corr_type = 'CL' )" )

#Date of Last Call to Client
result = FETCH( "CONTENT" , "History" , "completed_date" , "FOR LAST History NO-LOCK WHERE History.case_no = '" + {case.key} + "' AND (action_type = 'MC' AND corr_type = 'CL' )" )

#Date of Last Call from Client
result = FETCH( "CONTENT" , "History" , "completed_date" , "FOR LAST History NO-LOCK WHERE History.case_no = '" + {case.key} + "' AND (action_type = 'TC' AND corr_type = 'CL' )" )

#Date of Most Recent client contant
IF {Total Tel Calls from Client.Number} = "0" AND {Total Tel Calls to Client.Number} = "0" THEN
result = "?"
ELSE
IF {Date of Last Call from Client.Date} > {Date of Last Call to Client.Date} THEN
result = {Date of Last Call from Client.Date}
ELSE
result = {Date of Last Call to Client.Date}
END
END

proclaimadmin
Site Admin
Posts: 39
Joined: Wed Nov 30, 2011 9:37 pm

Re: Use 'FETCH' to pull dates/no. of Client Contact

Post by proclaimadmin » Wed Apr 24, 2013 11:53 am

Thanks for your post Scott! A great reference for future.

- ProclaimForum.co.uk -

barryfee
Posts: 1
Joined: Thu Jun 19, 2014 10:00 am

Re: Use 'FETCH' to pull dates/no. of Client Contact

Post by barryfee » Thu Jun 19, 2014 10:25 am

Nice maths, just missing LF & PR details I use this code to help control which correspondent I am looking at

###############################################
#
# Decide on who the correspondent is
#
CL-Case = TRUE
LF-Case = FALSE
PR-Case = FALSE
IF {Litigation Friend.Full Name/Company Name} <> "" THEN
LF-Case = TRUE
CL-Case = FALSE
PR-Case = FALSE
END
IF {Personal Representative.Full Name/Company Name} <> "" THEN
LF-Case = FALSE
CL-Case = FALSE
PR-Case = TRUE
END
###############################################
IF CL-CASE = TRUE THEN
(Add your own logic)
ELSEIF LF-CASE = TRUE THEN
(Add your own logic)
ELSEIF PR-CASE = TRUE THEN
(Add your own logic)
END

Post Reply