Sage CRM Developers Course Entities and the Data

Sage CRM Developers Course Entities and the Data Model (Part 2)

Looking ahead to the classes DP 01: Introduction to the Development Partner Program DP 02: Entities and the Data Model (Part 1 of 2) DP 03: Entities and the Data Model (Part 2 of 2) DP 04: Implementing Screen Based Rules (Part 1 of 2) DP 05: Implementing Screen Based Rules (Part 2 of 2) DP 06: Screen and User Independent Business Rules DP 07: Workflow (Part 1 of 2) DP 08: Workflow (Part 2 of 2) DP 09: Using the API Objects in ASP Pages (Part 1 of 2) DP 10 : Using the API Objects in ASP Pages (Part 2 of 2) DP 11: Using the Component Manager DP 12: Programming for the Advanced Email Manager DP 13: Using the Web Services API DP 14: Using the Web Services API (Part 2 of 2) DP 15: Coding the Web Self Service COM API (Part 1 of 2) DP 16: Coding the Web Self Service COM API (Part 2 of 2) DP 17: Using the. NET API (Part 1 of 2) DP 18: Using the. NET API (Part 2 of 2)

Agenda Use of SQL in Blocks Use of SQL in Interface Building Views Entities and the Security Model Tips for Building views with Derived Fields

Use of SQL in Blocks, Use of SQL in Interface

Using SQL & CRM must use SQL for all its database actions. Uses vendor specific SQL e. g. Oracle SYSDATE SQL Server GETDATE() SQL Trace (SQL Profiler) allows developer to identify actual data manipulation. Views Utilized Actual DML passed (c. f. Security Territories & mail merge –user info) Developer will need to establish requirement to support Databases within component View Syntax Differences

List Blocks in ASP pages var comp_companyid = CRM. Get. Context. Info('company', 'comp_companyid'); var projectlist = CRM. Get. Block('list'); with (projectlist) { //Table project is a custom added table //Select. SQL is property of CRM List. Block object Select. SQL = 'select * from project, opportunity where oppo_projectid = proj_projectid ' Select. SQL += ' and proj_companyid =' + comp_companyid; var proj_name = Add. Grid. Col('proj_name'); var proj_userid = Add. Grid. Col('proj_userid'); } CRM. Add. Content(projectlist. Execute()); Response. Write(CRM. Get. Page());

# Codes Code Meaning #U Current User ID #L #T #C #D #R #O #N Escalation Rules Dashboard Blocks Order. Quotes SQL Configuration P Current User Logon Name P Current System Date/Time P P Current User Team ID Current User Team Name P O Recent List Info O Current Opportunity ID Current Version O Order/Quote P P P O O P P

Tab SQL clause Provides simple control access to tabs U: 4, 5 – Only user with user_userid equal to 4 or 5 can use tab C: 4, 5 (user_primarychannelid) – Only user in team 4 or 5 can use tab

Tab SQL clause Where Clause only E. g. in My CRM opportunities tab only appears for users with opportunities assigned to them. – exists (select * from opportunity where oppo_assigneduserid = user_userid) Can reference either current user or entity in context NOT both

Groups –SQL feature Full control over SQL statement. Change automatic ‘and’ clauses to ‘or’ Groups and their access via Key Attributes allow Companies to be grouped.

Entities and the Security Model

Entity Relationships Entity Concept used in: Security Workflow Data Upload Reporting & Groups Coding – Context – Entity & Table level scripts Entity Definition Contingent on Context within CRM Can define own Entities to be managed by CRM VIEWS govern entity/context definition

Simple vs Complex Entities Relationships Consider Leads & Company & Opportunity Role of personlink table and Person and Company relationship Related Companies and the multipleentitylink table Communications and Comm_link

Person_link table Example Intersection Table Person_link Address_link Person and Company relationship Direct foreign key relationship between Parent Company and Child Person E. g. pers_companyid Direct Relationship used in most views, exceptions v. List. Person v. User. Contacts v. Report. User. Contacts Person_Link table used in Also Intersection Table used to allow peoplelist action called from company tab. recording of ‘role’ of person within company. Type of Person maintained via Translations

Userid, Createdby, Channelid, Sec. Terr Cases Communication case_assigneduserid cmli_comm_userid Company Lead Opportunity Person comp_primaryuserid lead_assigneduserid oppo_assigneduserid pers_primaryuserid case_channelid comm_channelid comp_channelid lead_channelid oppo_channelid pers_channelid case_secterr comm_secterr comp_secterr lead_secterr oppo_secterr pers_secterr case_createdby cmli_createdby comp_createdby lead_createdby oppo_createdby pers_createdby Intersection tables do not have these columns. person_link comm_link Multipleentitylink Security is cumulative User must have rights on all tables referenced in view before can access record. E. g. to see communication for a company and person, then user must have rights on communication, company and person.

Building Views, Tips for Building views with Derived Fields

Changing or Adding Views Add & edit database views using the CRM Interface. Custom_views Recommend creation of new views not editing of existing views where possible. Typical views changed Merge views Reports Target Lists

Mail Merge Views Sage CRM v 7. 1 sp 2 onwards Context View Company v. Mail. Merge. Company Person v. Mail. Merge Opportunity v. Mail. Merge. Opportunity Case v. Mail. Merge. Case Orders v. Mail. Merge. Children. Orders Quotes v. Mail. Merge. Children. Quotes Lead v. List. Lead

Expressions in Views Date Ranges Calculations Concatenations SELECT DATEDIFF(day, oppo_opened, getdate()) AS oppo_days FROM opportunity WHERE oppo_status = 'In Progress';

Example of Expression in View The Case List "caselist" uses the view "v. List. Cases". CREATE VIEW v. List. Cases AS SELECT RTRIM(ISNULL(Pers_First. Name, '')) + ' ' + RTRIM(ISNULL(Pers_Last. Name, '')) AS Pers_Full. Name, CASE WHEN Case_Status <> 'closed' AND Case_SLAClose. BY < GETDATE() THEN 'Red' WHEN Case_Status <> 'closed' AND Case_SLAAmber. Close. By < GETDATE() THEN 'Amber' ELSE 'Green' END AS Case_Color, Pers_Person. Id, Pers_Created. By, Cases. *, Comp_Name, Comp_Company. Id, Comp_Created. By, Pers_Sec. Terr, Comp_sec. Terr, Pers_Primary. User. Id, Comp_Primary. User. Id, Pers_Channel. Id, Comp_Channel. Id, Chan_Description, Comp_Email. Address, Pers_Email. Address FROM Cases LEFT OUTER JOIN Person ON Pers_Person. Id = Case_Primary. Person. Id LEFT OUTER JOIN Company ON Comp_Company. Id = Case_Primary. Company. Id LEFT OUTER JOIN Channel ON Comp_Channel. Id = Chan_Channel. Id WHERE Case_Deleted IS NULL CASE statement CASE WHEN Case_Status <> 'closed' AND Case_SLAClose. BY < GETDATE() THEN 'Red' WHEN Case_Status <> 'closed' AND Case_SLAAmber. Close. By < GETDATE() THEN 'Amber' ELSE 'Green' END AS Case_Color which evaluates whether the green, amber or red colour should be used.

Case_Color The column "case_color" DOES NOT exist in the database. It is an entirely derived "alias". In order for this derived column to look like it is part of CRM it must have meta data to control its properties. select * from custom_edits where colp_colname = 'case_color‘ select * from custom_captions where capt_family = 'colnames' and capt_code = 'case_color'

SQL on external tables May need to reference external database on same server: Reporting Graphing Use fully qualified table names select vusers. user_firstname, vusers. user_lastname, northwind. . orders. * from vusers left join northwind. . orders on user_userid = employeeid;

Q&A

Looking ahead to the classes DP 01: Introduction to the Development Partner Program DP 02: Entities and the Data Model (Part 1 of 2) DP 03: Entities and the Data Model (Part 2 of 2) DP 04: Implementing Screen Based Rules (Part 1 of 2) DP 05: Implementing Screen Based Rules (Part 2 of 2) DP 06: Screen and User Independent Business Rules DP 07: Workflow (Part 1 of 2) DP 08: Workflow (Part 2 of 2) DP 09: Using the API Objects in ASP Pages (Part 1 of 2) DP 10 : Using the API Objects in ASP Pages (Part 2 of 2) DP 11: Using the Component Manager DP 12: Programming for the Advanced Email Manager DP 13: Using the Web Services API DP 14: Using the Web Services API (Part 2 of 2) DP 15: Coding the Web Self Service COM API (Part 1 of 2) DP 16: Coding the Web Self Service COM API (Part 2 of 2) DP 17: Using the. NET API (Part 1 of 2) DP 18: Using the. NET API (Part 2 of 2)