Sage CRM Developers Course

1. Sage CRMDevelopers Course Entities and the Data Model (Part 2)

2. Looking ahead to the classes • DP01: Introduction to the Development Partner Program • DP02: Entities and the Data Model (Part 1 of 2) • DP03: Entities and the Data Model (Part 2 of 2) • DP04: Implementing Screen Based Rules (Part 1 of 2) • DP05: Implementing Screen Based Rules (Part 2 of 2) • DP06: Screen and User Independent Business Rules • DP07: Workflow (Part 1 of 2) • DP08: Workflow (Part 2 of 2) • DP09: Using the API Objects in ASP Pages (Part 1 of 2) • DP10 : Using the API Objects in ASP Pages (Part 2 of 2) • DP11: Using the Component Manager • DP12: Programming for the Advanced Email Manager • DP13: Using the Web Services API • DP14: Using the Web Services API (Part 2 of 2) • DP15: Coding the Web Self Service COM API (Part 1 of 2) • DP16: Coding the Web Self Service COM API (Part 2 of 2) • DP17: Using the .NET API (Part 1 of 2) • DP18: Using the .NET API (Part 2 of 2)

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

4. Use of SQL in Blocks, Use of SQL in Interface

5. Using SQL & CRM • 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

6. List Blocks in ASP pages • var comp_companyid = CRM.GetContextInfo('company','comp_companyid'); • var projectlist = CRM.GetBlock('list'); • with (projectlist) • { • //Table project is a custom added table • //SelectSQL is property of CRM ListBlock object • SelectSQL = 'select * from project, opportunity where oppo_projectid = proj_projectid ' • SelectSQL += ' and proj_companyid =' + comp_companyid; • var proj_name = AddGridCol('proj_name'); • var proj_userid = AddGridCol('proj_userid'); • } • CRM.AddContent(projectlist.Execute()); • Response.Write(CRM.GetPage());

7. # Codes

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

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

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

11. Entities and the Security Model

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

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

14. 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 • Also Intersection Table used to allow recording of ‘role’ of person within company. • Direct Relationship used in most views, exceptions vListPerson vUserContacts vReportUserContacts • Person_Link table used in peoplelist action called from company tab. Type of Person maintained via Translations

16. Userid, Createdby, Channelid, SecTerr • 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.

17. Building Views, Tips for Building views with Derived Fields

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

19. Mail Merge Views Sage CRM v7.1sp2 onwards

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

21. Example of Expression in View • The Case List "caselist" uses the view "vListCases". • CREATE VIEW vListCases AS SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, CASE WHEN Case_Status <> 'closed' AND Case_SLACloseBY < GETDATE() THEN 'Red' WHEN Case_Status <> 'closed' AND Case_SLAAmberCloseBy < GETDATE() THEN 'Amber' ELSE 'Green' END AS Case_Color, Pers_PersonId, Pers_CreatedBy, Cases.*, Comp_Name, Comp_CompanyId, Comp_CreatedBy, Pers_SecTerr, Comp_secTerr, Pers_PrimaryUserId, Comp_PrimaryUserId, Pers_ChannelId, Comp_ChannelId, Chan_ChannelId, Chan_Description, Comp_EmailAddress, Pers_EmailAddress FROM Cases LEFT OUTER JOIN Person ON Pers_PersonId = Case_PrimaryPersonId LEFT OUTER JOIN Company ON Comp_CompanyId = Case_PrimaryCompanyId LEFT OUTER JOIN Channel ON Comp_ChannelId = Chan_ChannelId WHERE Case_Deleted IS NULL • CASE statement • CASE WHEN Case_Status <> 'closed' AND Case_SLACloseBY < GETDATE() THEN 'Red' WHEN Case_Status <> 'closed' AND Case_SLAAmberCloseBy < GETDATE() THEN 'Amber' ELSE 'Green' END AS Case_Color • which evaluates whether the green, amber or red colour should be used.

22. 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_editswhere colp_colname = 'case_color‘ • select * from custom_captionswhere capt_family = 'colnames'and capt_code = 'case_color'

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

24. Q&A

25. Looking ahead to the classes • DP01: Introduction to the Development Partner Program • DP02: Entities and the Data Model (Part 1 of 2) • DP03: Entities and the Data Model (Part 2 of 2) • DP04: Implementing Screen Based Rules (Part 1 of 2) • DP05: Implementing Screen Based Rules (Part 2 of 2) • DP06: Screen and User Independent Business Rules • DP07: Workflow (Part 1 of 2) • DP08: Workflow (Part 2 of 2) • DP09: Using the API Objects in ASP Pages (Part 1 of 2) • DP10 : Using the API Objects in ASP Pages (Part 2 of 2) • DP11: Using the Component Manager • DP12: Programming for the Advanced Email Manager • DP13: Using the Web Services API • DP14: Using the Web Services API (Part 2 of 2) • DP15: Coding the Web Self Service COM API (Part 1 of 2) • DP16: Coding the Web Self Service COM API (Part 2 of 2) • DP17: Using the .NET API (Part 1 of 2) • DP18: Using the .NET API (Part 2 of 2)