REQUEST A DEMO

Can I created a view that includes a calculated view column to be used in Dovetail Agent (Amdocs/Clarify CRM)?

Static image

Summary: This question was asked by one of our Dovetail customers.  In this post I will explain the steps that we used to understand and implement a solution to satisfy the customers requirements.

Question: Can I created a view that includes a calculated column? If so, can I query all the cases with the calculated number of open subcases related to each case, in one view?

Requirement: Our customer needed a view to query for  1) all of the open cases and 2) all of the closed cases that have any open subcases – to be displayed in the same view.

Solution:  See steps taken below.

 

Steps taken to get to our Solution.

1. Read and discussed the customers requirements.

2. Tested our understanding of the requirements and the existing views using SQL against the following databases: MSSQL and Oracle SQL.

  • Researched the existing view, the objects involved, the relationships, cardinality.

3. Confirmed that we could perform SQL queries and get the customers desired results using SQL.  Please 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

4. Created the casesWithOpenSubCaseCount.schemascript.xml file (see file below).



  
    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

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

  • Preview: SchemaEditor.exe -p
  • Preview: SchemaEditor.exe -a

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






using System;

using System.Collections.Generic;

using System.Text;


using FChoice.Foundation;

using FChoice.Foundation.Clarify;

using FChoice.Foundation.Filters;


namespace TheCust.XYZ.Core

{

    public class QueryCasesQuery

    {

        private ClarifyDataSet queryDataSet;


        private ClarifyDataRow[] caseRows;


        public QueryCasesQuery(ClarifyDataSet dataSet, string condition)

        {

            this.queryDataSet = dataSet;


            ClarifyGeneric caseGen = this.queryDataSet.CreateGeneric("cases_w_open_scase_cnt");

            caseGen.DataFields.AddRange("case_objid", "case_id");


            switch (Convert.ToString(condition).Trim().ToLowerInvariant())

            {

                case "open":

                    caseGen.AppendFilter("condition", StringOps.StartsWith, condition);

                    break;

                case "closed":

                    caseGen.AppendFilter("condition", StringOps.StartsWith, condition);

                    break;

                default:


                    caseGen.Filter.AddFilter(

                        FilterType.Or(

                            FilterType.StartsWith("condition", "Open"),

                        //  - OR -

                            FilterType.And(

                                FilterType.StartsWith("condition", "Closed"),

                        //      - AND -

                                FilterType.And(

                                    FilterType.GreaterThan("open_subcase_count", 0),

                                )

                            )

                        )

                    );


                    break;

            }


            caseGen.Query();


            if (caseGen.Rows.Count > 0)

            {

                this.caseRows = new ClarifyDataRow[caseGen.Rows.Count];

                caseGen.Rows.CopyTo(this.caseRows, 0);

            }

        }


        public ClarifyDataRow[] CaseRows { get { return this.caseRows; } set { this.caseRows = value; } }

    }

}

7. 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.

  • Kevin did all of the heavy lifting to come up with this solution. 
  • We hope that you find this information useful!