Sage CRM Developers Course Entities and the Data

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

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 How data and record identity is handled Record Object vs. SQLQuery Object Role of Stored Procedures Allowed Database Changes Meta Data and Physical Data Types New Tables and Fields in CRM Linking to External Database tables How and where table data is stored in Meta Data

How data and record identity is handled Record Object vs SQLQuery Object Role of Stored Procedures

New Sage CRM v 7. 2 Installs on MS SQL Server use SQL Identity Columns In Sage CRM v 7. 1 and earlier Primary Key values were generated and maintained by stored procedures. This was to create common method mechanism between main install and SOLO dropped in favour of new Mobile Apps New installs on SQL Server will now use SQL Identities Significant performance improvements New version of Stored Procedure for existing installs NO Change for Oracle

New Installs on MS SQL Server use SQL Identity Columns SELECT t. TABLE_NAME , c. COLUMN_NAME FROM INFORMATION_SCHEMA. COLUMNS AS c JOIN INFORMATION_SCHEMA. TABLES AS t ON t. TABLE_NAME = c. TABLE_NAME WHERE COLUMNPROPERTY(OBJECT_ID(c. TABL E_NAME) , c. COLUMN_NAME, 'Is. Identity') = 1 AND t. TABLE_TYPE = 'Base Table'

e. Ware_get_identity_id stored procedure use in Query. Object code Problems will arise with add-on code that does a direct insert using eware_get_identity_id in Query object. ‘hidden’ or automatic rules within CRM – Validation Rules – Table Level Scripts – ASP and. NET Application Extensions Record object not effected

Inserts using Query. Object Sage CRM v 7. 1 var str. SQL = "DECLARE @ret int"; str. SQL += "EXEC @ret=eware_get_identity_id 'cases'"; str. SQL += "INSERT cases"; str. SQL += "(case_caseid, Case_Description, Case_Primary. Company. Id)"; str. SQL += "VALUES (@ret, 'abc', 10)"; var my. Query = CRM. Create. Query. Obj(str. SQL, ""); my. Query. Exec. SQL() Sage CRM v 7. 2 var str. SQL = "INSERT INTO Cases"; str. SQL += "(Case_Description, Case_Primary. Company. Id)"; str. SQL += "VALUES ('abc', 10)")"; var my. Query = CRM. Create. Query. Obj(str. SQL, ""); my. Query. Exec. SQL() NOTE: Upgraded Systems will still use old ID mechanism.

Oracle and Inserts No equivalent stored procedure in Oracle so there is no safe way of getting ids directly. SEQUENCES are used to control the identities. Sequence created in the CRM database for each table with the name Table. Prefix_SEQUENCE Eg comp_sequence, pers_sequence etc. Cases and Case. Progress share the same sequence as do Opportunity and Opportunity. Progress. Next value from a sequence obtained by calling the Next. Val function of the sequence. Returns the next val and increments the sequence. SELECT Comp_Sequence. Next. Val from DUAL; NOTE: Does not take into account the rep_ranges table, and therefore is not advised SOLO allocates Ranges to client machines Held in Rep_ranges table. When not using API object you will need to check that data is not going out of range. The process is as follows Look up Range_Range. Start and Range_Range. End in Rep_Ranges where Range_Table. ID is the identifier of the table. Table. ID is 5 for the Company table. Call SELECT Comp_Sequence. NEXTVAL FROM DUAL to get the next sequence number. If the sequence range is outside the range found in step 1 (which it will be because you dropped the sequence and recreated with a sequence start of 1), then assume that this range has been filled up, so move to the next available range. Ranges are allocated in blocks of 50000, so the first range is 8001 to 58000, the second range is 58001 to 108000, the third range is 108001 to 158000, and so on. Problems may arise if more than 1 process is trying to allocate a new range at the same time. NOTE: Do not run process when CRM is running. NOTE: Accessing the ranges table at the back end is not supported.

CRM Components & Database Type System Variables available in component allows the Install to be checked. Write components to accommodate database type. s. View. Text="CREATE VIEW v. My. Phone AS SELECT"; //i. Database - returns the current installed database. // Constants returned // ISQLServer // IOracle } // // // e. g. if (i. Database == IOracle) { //Do this; } if (i. Database == IOracle) { s. View. Text = s. View. Text + " Phon_Country. Code || N ' ' ||Phon_Area. Code || N ' ' || Phon_Number"; else { s. View. Text = s. View. Text + "RTRIM(ISNULL(Phon_Country. Code, '')) + ' ' +RTRIM(ISNULL (Phon_Area. Code, '')) + ' ' + RTRIM(ISNULL(Phon_Number, ''))"; } s. View. Text = s. View. Text + " AS Phon_Full. Number, Phone. * FROM Phone "; if (i. Database == IOracle) { s. View. Text = s. View. Text + ", Custom_Captions WHERE LOWER (TRIM(Phon_Type))= LOWER(TRIM(Capt_Code(+))) AND"; } else { s. View. Text = s. View. Text + "LEFT JOIN Custom_Captions ON Phon_Type = Capt_Code WHERE"; } s. View. Text = s. View. Text +" Phon_Deleted IS NULL"; Add. View("v. My. Phone", "This selects all of the phone numbers", s. View. Text, false, false);

Meta Data and Physical Data Types ALLOWED DATABASE CHANGES

Business Rules in Database CRM makes use of physical data types but meta data ‘adds value’ Custom_edits See Developer Guide reference for discussion of Entry. Block. Entry. Type property in API CRM DOES NOT use constraints within database. When installing CRM the only SQL rule that is written into the tables structure is whether the Primary Key column is NOT NULL. All constraints implemented in application layer.

CRM Field. Types & SQL Server Datatypes CRM Field Type Product Intelligent. Select Multiselect Date. Only Currency_CID Search. Select. Advanced Minutes Currency. Symbols Text Stored. Proc Checkbox Phone. Number Multiline. Text emailaddress WWWURL Selection User. Select Team. Select Integer Numeric Date. Time Data Type int nvarchar Datetime Numeric Int Int nvarchar ntext nvarchar Int int numeric datetime

How and where table data is stored in Meta Data

Meta Data Definitions of Tables Custom_Databases Manages all the connections to remote databases All passwords encrypted using encryption algorithms All database connections are opened at the first CRM logon Custom_Tables Bord_Web. Service. Table enables extra objects to be exposed to the WSDL Custom_Edits Entry. Type controls how the system displays fields Entry. Size controls how many characters can be entered on screen (can be changed, but must be <= column width) Colp_System may cause fields not to appear for selection. You can undo this, but be careful Custom_Views The whole system uses views to display data Views should be maintained from within the CRM system, never from SQL Server SQLLite relevant for SOLO only Custom_Table bord_tableid 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 17 Address Case. Progress Cases Communication Company Email Library Marketing Notes Opportunity. Item Opportunity. Progress Person Phone Products Team

Basic Table Description CREATE TABLE [dbo]. [My. Table]( [mytb_mytableid] [int] NOT NULL, [mytb_Created. By] [int] NULL, [mytb_Created. Date] [datetime] NULL, [mytb_Updated. By] [int] NULL, [mytb_Updated. Date] [datetime] NULL, [mytb_Time. Stamp] [datetime] NULL, [mytb_Deleted] [int] NULL, [mytb_Secterr] [int] NULL, [mytb_Workflowid] [int] NULL, [mytb_Description] [nchar](30)) Create externally and then link Not able to become full entity Create table via interface Advanced Customization Wizard. Entity Wizard All Ids managed by CRM Tabl_Secterr for primary entities only, enforces security on that entity. Security is discussed more later in the course New Table and Field definition will be in custom_tables and custom_edits

Problems occur if developer introduces Database constraints Foreign key constraints Checks Other changes such as identity columns.

Holding Data in CRM New Columns in Tables Adding columns to Entities, Company, Person, etc Marketing table for Company and Person Rows as Columns Key Attribute Data – DD tables – Target List technique for SQL – Logs for use in screens Data Sets – User_settings – Custom_sysparams

Columns versus Datasets Most data is held within a table within columns E. g. User_userid, user_lastname, user_firstname. Some instances of using Datasets (or rows) to model attributes User Preferences – Held in the usersettings table as a record set – E. g. To find a users timezone preference you would have to look in the usersettings table. Custom_sysparams – Hold system settings

Datasets and Customizations Can only use simple meta data definitions for screens where data is within a single row. Consider Company Summary Screen and – Companyboxlong – Addressboxshort – personboxshort Require Advanced Customization for Screens and Lists for Phone/Email screens Custom_sysparams User_settings Key Attribute Profiling Examples of creating screens not based on single rows are covered in later part of the course

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)