REQUEST A DEMO

Create a view with a calculated column to be used in Clarify CRM and Dovetail Agent.

Static image

Our customer would like to query all the cases including a column with a calculated number of “open” subcases related to each case record.  They want to query for all of the cases that have any open subcases. A normal view would not work because it cannot calculate the count of related object with specified condition.

 

Steps taken.

1. We showed it could be done in SQL (See SQL below).  Note the nested SQL statement to calculate the number of open subcases.

select c.objid, c.id_number, cond.title, cond.title,
  (SELECT count(*)
   FROM table_subcase sc
   INNER JOIN table_condition sc_cond ON sc_cond.objid = sc.subc_state2condition
   WHERE sc.subcase2case = c.objid AND sc_cond.title LIKE 'OPEN%') 
      as open_subcase_count 
FROM table_case c
INNER JOIN table_condition cond ON cond.objid = c.case_state2condition

2. Created the following SchemaEditor SchemaScript.



  
    Case identifiers and condition and the count open subcases for the case
    select c.objid, c.id_number, cond.title, cond.s_title,
(SELECT count(*)
FROM table_subcase sc
INNER JOIN table_condition sc_cond ON sc_cond.objid = sc.subc_state2condition
WHERE sc.subcase2case = c.objid AND sc_cond.s_title LIKE 'OPEN%') as open_subcase_count 
FROM table_case c
INNER JOIN table_condition cond ON cond.objid = c.case_state2condition

   

  
  
  
    255
    false
  

  
    80
    true
  
  
  
    80

3. Previewed and applied the schema changes against the Amdocs/Clarify database using the Dovetail SchemaEditor.

  • Preview schema changes: SchemaEditor.exe -p
  • Apply schema changes: SchemaEditor.exe -a

4. Here is the code that using the new view within the Dovetail Agent application.



<%
  var caseObjid = Request.QueryString('case_objid') + '';
  var boCase = FCSession.CreateGeneric('cases_with_open_subcase_count');
  boCase.AppendFilter('open_subcase_count', '>', 0);
  boCase.Query();
    
  while(!boCase.EOF) {
  %>
         
<%
         boCase.MoveNext();
  }
%>
5. Here are the results. The Dovetail Agent with a “Testing” tab – the grid on the tab is filled with the all of the cases that have open subcases.
    

  • 2 cases are displayed in the grid.

      

    • Case 48 has 1 open subcase.
      • 

    • Case 53 has 3 open subcases.
      • 

    

    • 

  • This solution works against both MSSQL and Oracle databases.
    • 

ODYxLVJlc3VsdDNfdGh1bWIucG5n

 
6. Here are a few links for more information about the Dovetail SchemaEditor
 
***Please note that I paired with Kevin Miller (http://blogs.dovetailsoftware.com/blogs/kmiller/default.aspx) to come up with this solution.
· We hope that you find this information useful!

					

						
				

	

  

    
    
    
  
 <% =boCase('case_id_number') + "" %>           
          <% =boCase('case_cond_title') + "" %>             
          <% =boCase('open_subcase_count') + "" %>