Browse By Tags
Home
»
All Tags
»
SQL
ASP
COM
create script
custom captions
eWareQuery Object
Fields
Graphs
Groups
Javascript
Library
List Block
Logging
Meta Data
Person
Record Object
Reports
System Administration
System Parameters
Table Level scripts
Target Lists
translations
Views
vsentinel
Web Services
Workflow
Blog Post:
Creating a Dynamic Group in Sage CRM using the COM object TargetLists
Jeff Richards
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...
on
20 Jan 2010
Blog Post:
Using the Database Tuning Advisor to Optimise CRM databases
Sage CRM Team
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...
on
6 Jul 2010
Blog Post:
More Adventures with the Library
Jeff Richards
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...
on
31 Jan 2008
Blog Post:
But I really, really need to search for the actual comp_companyid!
Jeff Richards
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...
on
19 Jul 2011
Blog Post:
MSSQL Index analysis and creation from Sage CRM (Part 1 of 2)
Jeff Richards
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...
on
5 Mar 2009
Blog Post:
Including Islamic Dates in Sage CRM
Jeff Richards
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...
on
4 Dec 2008
Blog Post:
Company and Person Workflow Progress with Tracking tab in CRM
Jeff Richards
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...
on
3 Jul 2009
Blog Post:
HTML in the database
Jeff Richards
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...
on
16 Jun 2009
Blog Post:
Investigating new and changed metadata structures in an installation of Sage CRM
Jeff Richards
I have added to the resources section two very simple SQL scripts that can be used to help you report what new Meta Data structures have been added to an install of Sage CRM and what has been changed since a certain date. The two scripts rely on the fact that Sage CRM is a Meta Data driven application...
on
8 Oct 2009
Blog Post:
Adding Fields, Deleting Fields, Views, vSentinel and SQL Errors
Jeff Richards
Sage CRM provides the system administrator with easy ways to add or remove fields from the system. But when you add or remove a field then you will implicity affect views which are based on those tables. And of course views may inturn be based on views. Each of these views in turn are referenced by...
on
26 Feb 2009
Blog Post:
Accessing the SQL of a Filter or Search Page using the COM ASP API
Jeff Richards
We may have a need to access the SQL clause of a search page. How can we know the SQL that is issued from a search page? Below is the code for a simple search page. [code language="javascript"] var searchBlock = CRM.GetBlock("opportunitysearchbox"); var gridBlock = CRM.GetBlock...
on
13 Mar 2009
Blog Post:
An example of when to test as the System Administrator
Jeff Richards
When I lecture I stress the need to test any system changes that are made with a user other the System Administrator. This is because of all the override rights that a System Administrator has, and therefore System Administrator may not experience the same problems that a standard user would see. But...
on
22 Jan 2008
Blog Post:
Using Joins in Web Services
Jeff Richards
Imagine you need to retrieve information from CRM via the webservices interface; this query should combine data from multiple tables. In the COM API (ASP pages, self service, table level scripts etc) you could use the QueryObject to build the exact SQL you need. var mySQL = "SELECT Person.*,...
on
9 Jun 2009
Blog Post:
Web Services, Where Clauses and Subqueries
Jeff Richards
This article follows on from a previous article " Using Joins in Web Services ". That article discussed how views that are linked into CRM and described as 'tables' in metadata can be used to provide access to data from multiple tables within a single webservice request. In that...
on
9 Jun 2009
Blog Post:
Finding %, _ and other Special Characters in SQL
Jeff Richards
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...
on
13 Feb 2009
Blog Post:
Merging Person records attached to different Companies
Jeff Richards
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...
on
30 Sep 2009
Blog Post:
Changing a Checkbox to a Selection list
Jeff Richards
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...
on
25 Oct 2010
Blog Post:
Linking to 3rd Party Databases
Jeff Richards
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...
on
31 Mar 2009
Blog Post:
Tips for Building Views: A round up of articles on views.
Jeff Richards
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,...
on
1 Jul 2009
Blog Post:
Updating the comp_librarydir Library Path in the Demo Data
Jeff Richards
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...
on
1 Feb 2011
Blog Post:
Connecting to a Sage MAS ERP 90 Database as a Linked Server within MS SQL Server 2008
Jeff Richards
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...
on
10 May 2010
Blog Post:
Converting default ntext fields from an old install of CRM to nvarchar(max)
Sage CRM Team
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...
on
11 May 2018
<