Browse By Tags

  • Blog Post: Finding %, _ and other Special Characters in SQL

    I have just spent a frustrating few minutes trying to quickly find where '%' codes are used in translations. Of course '%' is a special wild card character in SQL. The same is true for '_' and other characters. Don't do : select capt_code, capt_family from custom_captions...
  • Blog Post: Customizing the Opportunity Pipeline Summary Information

    Can we customise the "Statistics for All stages" that appear next to the pipeline in My CRM / Opportunities? The pipeline graphic and its associated statistics are driven from within the dll and have no definition within meta data. This means that we have no obvious way of changing the data...
  • Blog Post: Merging Person records attached to different Companies

    This article is related to the previous discussion " Cloning a Person and assigning to a new Company ". In a default install of Sage CRM it is not possible to merge a person record with another person that is attached to a different company. You can see in the image above only the...
  • Blog Post: Changing a Checkbox to a Selection list

    Checkbox fields are brilliant for letting a user quickly collect certain types of data. But they do have their limitations and you may discover that you want to be able to store answers such as 'Y' (Yes), 'N' (No), 'M' (Maybe) rather than a simple 'check'. If that is...
  • Blog Post: Linking to 3rd Party Databases

    Within the Administration screens is the location that allows the System Administrator to create an additional database connection to a 3rd party database. Administration -> Advanced Customization -> Tables and Databases Sage CRM allows us to connect to a different database on a different...
  • Blog Post: Tips for Building Views: A round up of articles on views.

    The following is a survey of all the different articles that have discussed creating and editing views within Sage CRM. Understanding SQL views is essential as they under pin just about every feature within Sage CRM and completely determine what can or can't be done in Reports, Marketing Groups,...
  • Blog Post: Updating the comp_librarydir Library Path in the Demo Data

    In the demo data there are a few company records that have entries in the comp_librarydir field that do not match the usual format of such data. You can see some of these in the image below. New company records that are added into the system will follow the correct pattern. The inconsistency...
  • Blog Post: Database Case-Sensitivity and Connecting to an External Case Sensitive Database

    If you are working with SQL Server you may have read in the System Administration guide that when installing Sage CRM the SQL server main install needs to have a case insensitive collation. A binary collation, or a case sensitive collation, is not supported for installation. Within the Custom_SysParams...
  • Blog Post: Adding Advanced Find and Mass Update and Groups for a Custom Entity (Project) in Sage CRM

    The article will take you though the steps to add a custom entity to the Advanced Find screen. To do this you will need to have already created the custom entity. I have made the assumption that you have created an entity called Project. Once we have the Project entity in existence...
  • Blog Post: How to track a SData provider request to see the SQL that is fired.

    Perhaps you want to control the logging to see the REST request get handled and then see the database interaction. You may want to see how SQL changes between users (e.g. Mayes/Admin/Ebden) and how that may effect the XML payload returned. To see that information you need to set the logging level...
  • Blog Post: Using Quick Notifications to alert users of reassigned records

    The links in this article were corrected 22nd May 2009. Oh how angry I get with myself sometimes. I am a very stupid person. Someone sends me an email entitled 'a quick question' and in my idiocy I start trying to answer the question and look up nearly 3 hours later to find I have consumed...
  • Blog Post: Adding Derived Fields in Views into Meta Data

    I originally wrote about creating derived fields in a view in the article " Custom_Edits and Derived Fields Created in Views ". This discussion is a continuation of the ideas expressed in that article and will show how meta data definitions of a calculated field can be easily added into Meta...
  • Blog Post: Write to the CRM logs in a server-side script

    If you are developing a server-side script such as a Create Script, Validate Script or Table Level Script, it is often useful to write debugging information to a log. Jeff has blogged about a technique for doing this using the Scripting.FileSystemObject. I'd like to share with you a very simple...
  • Blog Post: How to filter a pipeline object based on filter screen values

    We know that a LIST block can be filtered based on a standard filter screen block via var filter = eWare.GetBlock("MyFilterBoxName"); var list = eWare.GetBlock("MyListName"); list.ArgObj = filter; Can we filter a pipeline object in a similar fashion? Unfortunately you cannot...
  • Blog Post: Using SearchSQL to limit data returned by selection lists

    In the screen shot below we can see the demo user "Susan Maye" is limited to assigned the new opportunity to only people in the "Direct Sales" team. This has been accomplished by using the property of the field (a.k.a entryblock) called SearchSQL. The SearchSQL property is discussed...
  • Blog Post: Making a Private Group (Target List) Public

    What happens if a user spends a long time in creating a static group of contacts/records only to discover that they made it private and it can't be shared with colleagues? One little oddity in Sage CRM is that once a group has been created as a private group the interface doesn't provide a mechanism...
  • Blog Post: Sage CRM and Security Territories

    Sage CRM has a powerful Security mechanism implemented as an automatic alteration of the SQL used to fetch either the application data to the screen or the meta data that controls the availability of features. A user will be able to access a particular set of data records to which another user may not...
  • Blog Post: Creating a Dynamic Group in Sage CRM using the COM object TargetLists

    The COM API has an object within it called TargetLists. This object can be used within internal script (Validate, Table Level Script etc) to create both Dynamic and Static Groups. Below is a simple example of the object being used in an ASP page to create a Dynamic Group. You will see that I had...
  • Blog Post: Using the Database Tuning Advisor to Optimise CRM databases

    The Database Engine Tuning Advisor provides features that enable both new Sage CRM Administrators and/or experienced database administrators to tune databases for better query performance. Its primary recommendations are related to clustered/non-clustered indexes and statistics improvements on a database...
  • Blog Post: More Adventures with the Library

    I have been investigating the list of documents that appears under the Company tab group. This is actually part of a set of investigations into the library entity that I am working on. So there will be a few more blogs about documents and the library in the near future. I wanted to find out how to ensure...
  • Blog Post: But I really, really need to search for the actual comp_companyid!

    Once upon a time I wrote an article called " But I really, really need to see the actual oppo_opportunityid displayed! " This article looks at how you could use the column alias idea to allow for records to be searched for by the their unique ids. Note : Generally I think this is not...
  • Blog Post: Connecting to a Sage MAS ERP 90 Database as a Linked Server within MS SQL Server 2008

    A Linked Server within MS SQL Server is type of 'alias' within the SQL server that points to an external data source rather than a SQL Server database. This external data source can be in another RDBMS like Oracle or almost any other data system that can be accessed by OLE or ODBC--including...
  • Blog Post: Converting default ntext fields from an old install of CRM to nvarchar(max)

    This is a SQL script that will solve the problem associated with on-premise accounts that still have some ntext fields in their database from older versions of CRM. The ntext fields do not allow some of the actions that are used in the views of CRM, DISTINCT being an example of one of these commands...
  • Wiki Page: 536-17216 - SQL error shows when deleting records

    Summary: Deleting a person or company results in an SQL error: "An unexpected event has occured: Exception: SQL Error" However the record is actually deleted. Symptoms: SQL Error : Incorrect syntax near the keyword 'WHERE' (DELETE FROM WHERE Visi_CompanyId = 27579) SQL Error : Incorrect...
  • Wiki Page: 492-17410 - New Line Item button in Quotes performance

    Summary: Selecting the New Line Item button on a quote, where CRM is using a large number of products (100,000+) may result in a timeout. Symptoms: The user may experience a SQL timeout, or the screen may render, eventually. Cause: There are two separate issues here. Typically, a CRM install with an...