1 / 26

Sage CRM Developers Course

Sage CRM Developers Course. Entities and the Data Model (Part 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)

ajaxe
Download Presentation

Sage CRM Developers Course

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

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

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

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

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

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

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

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

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

  23. Q&A

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

More Related