Hi Folks! In standard CRM escalation conditions you must have seen “#” symbol used to get certain values on run time. Through this article I would be explaining you in detail about the usage of “#” symbol.
New Stuff: How to bind Custom List in Dot net using Select SQL
The #Codes are a set of codes that indicate to CRM as the dll parses the Meta data it should substitute these references with internal function calls. These are for use where SQL is used directly as the control mechanism to limit the data returned.
Below table will help you to understand available parameters that can be used in various operations in CRM.
Usage in Escalation Rules
The escalation rule (Communication Reminder) that is provided by default within the system to create on screen notification for meetings and tasks is based on the vEscalationComms view.
To examine the rule go to
Administration | Advanced Customization | Escalation
The where clause used is
Escl_DateTime<#T And Escl_UserID=#U AND Upper(RTRIM(comm_status))=N’PENDING’
This shows how the #T and #U system variables are referenced. When the rule is processed the #T and #U are substituted for functions that generate the actual SQL that is run.
For the above “Communication Reminder” rule the actual SQL would look like this:
Select * from vEscalationComms WITH (NOLOCK) WHERE Escl_DateTime<‘20060922 07:52:45′ And Escl_UserID=1 AND Upper (RTRIM (comm_status)) =N’PENDING’ ORDER BY Comm_CommunicationId
The #T has been substituted for the current System Date/Time, the equivalent of a getDate() function or sysdate reference depending on RDBMS used.
The #U has been substituted for the current users unique ID. It should be noted that this #Code can only be used for rules that depend on a user being logged on to the system.
Usage in Dashboard Blocks
The #Codes may be used within the SQL clause of the Dashboard block.
A few of the default blocks may be examined to show usage. The blocks discussed in the following section can be found under:
Administration | Customization | Opportunity
For example the Chart Block “My Team Oppos In Progress”
Has the following SQL:
Select count (*) as a, oppo_stage from Opportunity where oppo_channelid=#C and oppo_status=’In Progress’ and oppo_deleted is Null group by oppo_stage
This shows the usage of the #C code to return the ID of the current user’s team (user_primarychannelid).
The List block “My Recently Created Opportunities”
Uses the following where clause to limit the data returned:
oppo_assigneduserID=#U and (oppo_createdDate + 7)>#T
The List block “Team Opportunities 28 day target”
Has the following SQL that demonstrates the usage of the #T code in date ranges.
(oppo_channelid = #C) and (oppo_deleted is NULL) and (oppo_targetclose > #T ) and ((oppo_targetclose – 28) < #T)
#R in Dashboard Blocks
The #R has a very particular usage. It allows easy reference to the current users ‘recent list’ information for the entity that the block references. The records that a user has viewed are stored in the user table. This is the field user_recentlist and will contain information about the last 20 primary entities viewed of whatever type.
The List Block “Recently Viewed Opportunities” shows its usage.
#R
Will generate the following SQL to be run against the database to support the list creation:
select count(*) as fcount from vListOpportunity WHERE oppo_OpportunityID IN (65,22,124,128,146)
select TOP 11 * from vListOpportunity WHERE oppo_OpportunityID IN (65,22,124,128,146) ORDER BY oppo_description, oppo_opportunityId
Usage in Order Quotes
The example usage of the #Codes to generate the IDs for Orders and Quotes can be found at:
Administration | Data Management | Products
The QuoteFormat field within the Product Configuration screen has a default value of
QT-#O/#N
#O will be substituted for the current Opportunity ID and the #N the number of the Quote within the opportunity. Only #Codes that generate numeric returns are suitable for use here.
Also Read:
1. Apply Styles to Sage CRM Fields
2. Limit number of characters in Sage CRM fields
3. Make up of SSA fields in Sage CRM
4. Create Custom Fields in Sage CRM Cloud/On-Premise for Sage CRM – QuickBooks Integration
5. Highlight fields using Client side API in Sage CRM