Browse By Tags

  • Blog Post: You may need to recreate views manually

    Please note that this is not an exhaustive discussion and but simply represents the things that come to my mind when seeing this error. Why does it appear? The can be several reasons why this message appears and it usually comes when there has been a change in the configuration of the system. This...
  • Blog Post: Allowing Views that Return Duplicate Person records to be used in Group definitions

    Please see the article " Reports, Saved Searches and Groups " for background information on how Groups are defined in Sage CRM. This article is relevent when needing to create Groups that are based on a view that returns multiple instances of the same person record. This maybe because of...
  • Blog Post: Building Cross Database Views for Reporting

    You may need to reference data held outside of Sage CRM and within another application's database for a variety of business requirements including Reporting Graphing Reports within CRM are based on views and so if the SQL within a view can provide a way of accessing data...
  • Blog Post: The Design of Web Service Applications and Performance

    My hope is that if you have attended any of my presentations about the Web Services interface for Sage CRM you will have taken away with you an impression of the importance of the design of the client application and how this would affect performance. The source code for the sample application which...
  • Blog Post: Getting the Database DataType for a field

    Occasionally you may be faced with a requirement to know what database datatype a field is before you start to work with it. This type of information is not held in the custom_edits table - only the Sage CRM entrytype is held there. These are all documented in the developers guide. We may also have...
  • Blog Post: Reports and Graphs using Key Attribute Data

    Reports and Report Charts can only be built from views. So If we want to create a Report Chart that uses Key Attribute Data then we must first create the view. The structure of the tables that holds the key attribute data is complex and trying to work this out on our own to build a view would be a deeply...
  • Blog Post: The Journal Table

    I was tempted to start a new series of articles called " Mysterious Tables of Sage CRM " but since this is supposed to be a sensible library of "Hints, Tips and Tricks" I have resisted. Nevertheless, there are a few tables in the Sage CRM database that whose purpose may be obscure...
  • Blog Post: The SQL Tab Clause and the User Admin screens

    Normally when you enter a SQL Tab condition associated with an option in a Tabgroup you can either work with the current entity in context or the current user in context. But what if you want to control the tabs for the Admin Screens in the user area then we will end up in a dilemma because which 'user'...
  • Blog Post: Discovering Data Relationships in Code

    You may have a business requirement to cascade a change through the child records of a company. I have discussed previously how a table level script can be used to update all opportunities if the company is reassigned to a new account manager. See the article " A Table Level Script to update Opportunities...
  • Blog Post: How to add mobile number to My Contacts List block

    A query was raised recently about how to add fields, such as Mobile Phone Number, to the My Contacts list block. This query can be answered in 2 parts; firstly how to do retrieve the data we want to display and secondly from where, or from what view, does the List block retrieve it's data. We...
  • Blog Post: How to display an oppo_opportunityid or another primary key in lists and reports.

    Once you have been working with Sage CRM for a little while you will notice that the primary keys (unique ID fields) have been suppressed from the interface. By that, I mean that you can't refer to them directly in Lists and Screens. This is normally fine as the keys job is only to allow Sage...
  • Blog Post: Who's been looking at my data? Recording access to Company and Person screens

    I have discussed creating logs and audit trails of changes to data in previous articles. See Using a Table Level Script to Track Changes to Data Made by Users. Validation rules don't always mean "Stop!" But many customers have the much more straight forward business need...
  • Blog Post: Why does N’ appear in system generated SQL?

    You may have seen in the SQL logs that Sage CRM passes strings using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT...
  • Blog Post: Some thoughts about the Library

    I hope that the material that I put here is of use and is relevant to developers and partners implementing CRM. I can't claim all the articles published here under my name as 'all my own work'. I am a magpie of code snippets and will quite happily steal the bright ideas of cleverer colleagues...
  • Blog Post: TableLevel Scripts and SQL TimeOuts

    Any SQL statement may take time to run and result in a timeout error. In Sage CRM the general timeout period is controlled within the Administration -> System -> Database screen by setting the Query Timeout field value. This is expressed in seconds and represents the maximum amount of time that...
  • Blog Post: Step away from the SQL and put your hands where I can see them!

    Amongst Sage CRM developers there are two almost irresistible urges. The first is the itch to write code and try to solve all problems with "if, else" statements. Those of us who are experienced with Sage CRM will eventually learn that much coding can be avoided and we are gradually weaned...
  • Blog Post: MSSQL Index analysis and creation from Sage CRM (Part 1 of 2)

    This article has come from a very experienced colleague in Australia. Introduction Microsoft SQL Server 2005 added new system tables to assist in the system reporting. Three of these tables in particular are written to every time a query is run. Microsoft SQL Server 2005 processes each query, determines...
  • Blog Post: Controlling the availability of a Company tab based on products purchased

    This article considers how the availability of a tab in the company context maybe controlled so that it is only displayed when the customer has purchased a particular product. The general availability of a Tab for a user is controlled by the tabs SQL Clause in the tab group definition. You can...
  • Blog Post: Including Islamic Dates in Sage CRM

    This article has come about because of a question from a partner working on implementing Sage CRM where the customer would like dates shown according to the Hijri (Islamic) calendar. This is a subject that I have now learnt far more about than I ever thought I would ever want to. Please note that...
  • Blog Post: Hiding the Global Library (Shared Documents) by teams

    Every time a user exports a file from the Company Search page or from the Marketing Actions, it automatically saves the spreadsheet into the Global Library (where everybody can view it). There may therefore be a requirements to hide tabs. The Global Library like most tabs can be controlled using the...
  • Blog Post: Current Context and User data used to control a Tab

    This article has been updated (29th April 2009) to correct hyperlink references. In a previous article " Tab SQL Clause " I discussed how it is possible to separate SQL clauses with a " ; " character. So in a Company Tab group the Opportunity tab can be guarded by a SQL clause...
  • Blog Post: Company and Person Workflow Progress with Tracking tab in CRM

    Many thanks must go to a colleague in the UK professional services team for this article. Introduction Customers often request the ability to track Company or Person changes in much the same way that CRM offers with Opportunity and Case entities. As standard, CRM does not offer a CompanyProgress...
  • Blog Post: HTML in the database

    It is a general rule that in CRM data fields are not supposed to hold HTML which can then be passed directly back to the interface. This is by design. So if you store a string in a text field like "<a href=https://community.sagecrm.com>https://community.sagecrm.com</a>" then...
  • Blog Post: Restricting Access to a Report to Specific Times of Day

    This article has been prompted by a question I was asked at a recent Developer training course in Johannesburg, South Africa. During the training course we discussed how when a user runs a large report it can place a load on the customer's server which in turn can create problems for other users...
  • Blog Post: The eWareQuery Object (COM API)

    The eWareQuery object allows use to issue queries against the database. These are either in the form of select statements or for data manipulation (Insert, update, delete). Retrieval of Data The basic pattern to use the eWareQuery object to select data is shown below: var mySQL = "Select * from...