|
Advanced Find Operator
|
Fetch XML Operator
|
FetchXML Filter
|
SQL Where condition
|
|
Equal
|
eq
|
<condition attribute="firstname"
value="John" operator="eq"/>
|
WHERE
firstname = 'John'
|
|
Not Equal
|
ne
|
<condition attribute="firstname"
value="John" operator="ne"/>
|
WHERE
firstname != 'John'
|
|
Contains
|
like
|
<condition attribute="firstname"
value="John" operator="like"/>
|
WHERE
firstname LIKE '%John%'
|
|
Does Not Contains
|
not-like
|
<condition attribute="firstname"
value="John" operator="not like"/>
|
WHERE
firstname NOT LIKE '%John%'
|
|
Begins with
|
like
|
<condition attribute="firstname"
value="John%" operator="like"/>
|
WHERE
firstname LIKE 'John%'
|
|
Does Not Begins with
|
not-like
|
<condition attribute="firstname"
value="John%" operator="not like"/>
|
WHERE
firstname NOT LIKE 'John%'
|
|
Ends with
|
like
|
<condition attribute="firstname"
value="%John" operator="like"/>
|
WHERE
firstname LIKE '%John'
|
|
Does Not End with
|
not-like
|
<condition attribute="firstname"
value="%John" operator="not like"/>
|
WHERE
firstname NOT LIKE '%John'
|
|
Contains Data
|
not-null
|
<condition attribute="firstname"
operator="not-null"/>
|
WHERE
firstname IS NOT NULL
|
|
Does Not Contains Data
|
null
|
<condition attribute="firstname" operator="null"/>
|
WHERE
firstname IS NULL
|
|
Is Greater than
|
gt
|
<condition attribute="annualincome"
value="123" operator="gt"/>
|
WHERE
AnnualIncome >123
|
|
Is Greater than or Equal To
|
ge
|
<condition attribute="annualincome"
value="123" operator="ge"/>
|
WHERE
AnnualIncome >=123
|
|
Is Less than
|
lt
|
<condition attribute="annualincome"
value="123" operator="lt"/>
|
WHERE
AnnualIncome <123 span="">
|
|
Is less than or Equal To
|
le
|
<condition attribute="annualincome"
value="123" operator="le"/>
|
WHERE
AnnualIncome <=123
|
|
Equals Current User
|
eq-userid
|
<condition attribute="createdby"
operator="eq-userid"/>
|
WHERE
CreatedBy ='7BC449A8-5A8F-E311-A63C-0050568C6D7D'
|
|
Does Not Equal Current user
|
ne-userid
|
<condition attribute="createdby"
operator="ne-userid"/>
|
WHERE
CreatedBy !='7BC449A8-5A8F-E311-A63C-0050568C6D7D'
|
|
Equals Current User' Teams
|
eq-userteams
|
<condition attribute="ownerid"
operator="eq-userteams"/>
|
WHERE
OwnerId IN ('7BC449A8-5A8F-E311-A63C-0050568C6D7D')
|
|
Equals Current user Or User's Teams
|
eq-useroruserteams
|
<condition attribute="ownerid"
operator="eq-useroruserteams"/>
|
WHERE
OwnerId IN('7BC449A8-5A8F-E311-A63C-0050568C6D7D',' 09C5427D-88B7-E411-8BFB-0050568C6D7D')
|
|
On
|
on
|
<condition attribute="createdon"
value="2015-04-01" operator="on"/>
|
WHERE
CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-04-02 05:00:00'
|
|
On or After
|
on-or-after
|
<condition attribute="createdon"
value="2015-04-01"
operator="on-or-after"/>
|
WHERE
CreatedOn >= '2015-04-01 05:00:00'
|
|
On or Before
|
on-or-before
|
<condition attribute="createdon"
value="2015-04-01"
operator="on-or-before"/>
|
WHERE
CreatedOn < '2015-04-02 05:00:00'
|
|
Yesterday
|
yesterday
|
<condition attribute="createdon" operator="yesterday"/>
|
WHERE
CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-04-02 05:00:00'
|
|
Today
|
today
|
<condition attribute="createdon" operator="today"/>
|
WHERE
CreatedOn >= '2015-04-02 05:00:00' AND CreatedOn < '2015-04-03 05:00:00'
|
|
Tomorrow
|
tomorrow
|
<condition attribute="createdon" operator="tomorrow"/>
|
WHERE
CreatedOn >= '2015-04-03 05:00:00' AND CreatedOn < '2015-04-04 05:00:00'
|
|
Next 7 Days
|
next-seven-days
|
<condition attribute="createdon" operator="next-seven-days"/>
|
WHERE
CreatedOn >= '2015-04-02
21:25:41.110' AND CreatedOn < '2015-04-10 05:00:00'
|
|
Last 7 Days
|
last-seven-days
|
<condition attribute="createdon" operator="last-seven-days"/>
|
WHERE
CreatedOn >= '2015-03-26 05:00:00' AND CreatedOn <= '2015-04-02
21:25:41.110'
|
|
Next Week
|
next-week
|
<condition attribute="createdon" operator="next-week"/>
|
WHERE
CreatedOn >= '2015-04-05 05:00:00' AND CreatedOn < '2015-04-12 05:00:00'
|
|
Last Week
|
last-week
|
<condition attribute="createdon" operator="last-week"/>
|
WHERE
CreatedOn >= '2015-03-22 05:00:00' AND CreatedOn < '2015-03-29 05:00:00'
|
|
This Week
|
this-week
|
<condition attribute="createdon" operator="this-week"/>
|
WHERE
CreatedOn >= '2015-03-29 05:00:00' AND CreatedOn < '2015-04-05 05:00:00'
|
|
Next Month
|
next-month
|
<condition attribute="createdon" operator="next-month"/>
|
WHERE
CreatedOn >= '2015-05-01 05:00:00' AND CreatedOn < '2015-06-01 05:00:00'
|
|
Last Month
|
last-month
|
<condition attribute="createdon" operator="last-month"/>
|
WHERE
CreatedOn >= '2015-03-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
|
|
This Month
|
this-month
|
<condition attribute="createdon" operator="this-month"/>
|
WHERE
CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-05-01 05:00:00'
|
|
Next Year
|
next-year
|
<condition attribute="createdon" operator="next-year"/>
|
WHERE
CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00'
|
|
Last Year
|
last-year
|
<condition attribute="createdon" operator="last-year"/>
|
WHERE
CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00'
|
|
This Year
|
this-year
|
<condition attribute="createdon" operator="this-year"/>
|
WHERE
CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
|
|
Last X Hours
|
last-x-hours
|
<condition attribute="createdon"
value="1" operator="last-x-hours"/>
|
WHERE
CreatedOn >= '2015-04-02 20:00:00' AND CreatedOn <= '2015-04-02
21:53:49.933'
|
|
Next X Hours
|
next-x-hours
|
<condition attribute="createdon"
value="1" operator="next-x-hours"/>
|
WHERE
CreatedOn >= '2015-04-02
21:53:49.933' AND CreatedOn < '2015-04-02 23:00:00'
|
|
Last X Days
|
last-x-days
|
<condition attribute="createdon"
value="1" operator="last-x-days"/>
|
WHERE
CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn <= '2015-04-02
21:53:49.933'
|
|
Next X Days
|
next-x-days
|
<condition attribute="createdon"
value="1" operator="next-x-days"/>
|
WHERE
CreatedOn >= '2015-04-03
19:48:07.257' AND CreatedOn < '2015-04-05 05:00:00'
|
|
Last X Weeks
|
last-x-weeks
|
<condition attribute="createdon"
value="1" operator="last-x-weeks"/>
|
WHERE
CreatedOn >= '2015-03-27 05:00:00' AND CreatedOn <= '2015-04-03
19:48:07.260'
|
|
Next X Weeks
|
next-x-weeks
|
<condition attribute="createdon"
value="1" operator="next-x-weeks"/>
|
WHERE
CreatedOn >= '2015-04-03
19:48:07.260' AND CreatedOn < '2015-04-11 05:00:00'
|
|
last X Months
|
last-x-months
|
<condition attribute="createdon"
value="1" operator="last-x-months"/>
|
WHERE
CreatedOn >= '2015-03-03 06:00:00' AND CreatedOn <= '2015-04-03
19:48:07.260'
|
|
Next X months
|
next-x-months
|
<condition attribute="createdon"
value="1" operator="next-x-months"/>
|
WHERE
CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn < '2015-05-04 05:00:00'
|
|
Last X Years
|
last-x-years
|
<condition attribute="createdon"
value="1" operator="last-x-years"/>
|
WHERE
CreatedOn >= '2014-04-03 05:00:00' AND CreatedOn <= '2015-04-03
19:48:07.260'
|
|
Next X Years
|
next-x-years
|
<condition attribute="createdon"
value="1" operator="next-x-years"/>
|
WHERE
CreatedOn >= '2015-04-03
19:48:07.260' AND CreatedOn < '2016-04-04 05:00:00'
|
|
Any Time
|
not-null
|
<condition attribute="createdon"
operator="not-null"/>
|
WHERE
CreatedOn IS NOT NULL
|
|
Older than x months
|
olderthan-x-months
|
<condition attribute="createdon"
value="1" operator="olderthan-x-months"/>
|
WHERE
CreatedOn < '2015-03-03 06:00:00'
|
|
In Fiscal Year
|
in-fiscal-year
|
<condition attribute="createdon"
value="2015" operator="in-fiscal-year"/>
|
WHERE
CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
|
|
In Fiscal Period
|
in-fiscal-period
|
<condition attribute="createdon"
value="1" operator="in-fiscal-period"/>
|
WHERE
dbo.fn_GetFiscalPeriod('2014-01-01 00:00:00', 4, CreatedOn, '20') = 1
|
|
In Fiscal Period and Year
|
in-fiscal-period-and-year
|
<condition attribute="createdon="
operator="in-fiscal-period-and-year=">
<value>01</value>
<value>2015</value>
</condition>
|
WHERE
CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
|
|
In or After Fiscal Period
|
in-or-after-fiscal-period-and-year
|
<condition attribute="createdon"
operator="in-or-after-fiscal-period-and-year">
<value>01</value>
<value>2015</value>
</condition>
|
WHERE
CreatedOn >= '2015-01-01 06:00:00'
|
|
In or Before Fiscal Period
|
in-or-before-fiscal-period-and-year
|
<condition attribute="createdon"
operator="in-or-before-fiscal-period-and-year">
<value>01</value>
<value>2015</value>
</condition>
|
WHERE
CreatedOn < '2015-04-01 05:00:00'
|
|
Last Fiscal Year
|
last-fiscal-year
|
<condition attribute="createdon" operator="last-fiscal-year"/>
|
WHERE
CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00'
|
|
This Fiscal Year
|
this-fiscal-year
|
<condition attribute="createdon" operator="this-fiscal-year"/>
|
WHERE
CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
|
|
Next Fiscal Year
|
next-fiscal-year
|
<condition attribute="createdon" operator="next-fiscal-year"/>
|
WHERE
CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00'
|
|
Last X Fiscal Year
|
last-x-fiscal-years
|
<condition attribute="createdon"
value="1" operator="last-x-fiscal-years"/>
|
WHERE
CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00'
|
|
Next X Fiscal Year
|
next-x-fiscal-years
|
<condition attribute="createdon"
value="1" operator="next-x-fiscal-years"/>
|
WHERE
CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00'
|
|
Last Fiscal Period
|
last-fiscal-period
|
<condition attribute="createdon" operator="last-fiscal-period"/>
|
WHERE
CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
|
|
This Fiscal Period
|
this-fiscal-period
|
<condition attribute="createdon" operator="this-fiscal-period"/>
|
WHERE
CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-07-01 05:00:00'
|
|
Next Fiscal Period
|
next-fiscal-period
|
<condition attribute="createdon" operator="next-fiscal-period"/>
|
WHERE
CreatedOn >= '2015-07-01 05:00:00' AND CreatedOn < '2015-10-01 05:00:00'
|
|
Last X Fiscal Period
|
last-x-fiscal-periods
|
<condition attribute="createdon"
value="1" operator="last-x-fiscal-periods"/>
|
WHERE
CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
|
|
Next X Fiscal Period
|
next-x-fiscal-periods
|
<condition attribute="createdon"
value="1" operator="next-x-fiscal-periods"/>
|
WHERE
CreatedOn >= '2015-07-01 05:00:00' AND CreatedOn < '2015-10-01 05:00:00'
|
Monday, April 6, 2015
Fetch XML Condition to SQL WHERE
Tuesday, March 31, 2015
Microsoft Dynamics CRM Installations, Updates and Documentation
This article lists all the installations, updates and documentation for the currently released MS CRM products.
List included For MS CRM 4.0, MS CRM 2011, MS CRM 2013 and MS CRM 2015
Click Here..
Microsoft Dynamics CRM Installations, Updates and Documentation
List included For MS CRM 4.0, MS CRM 2011, MS CRM 2013 and MS CRM 2015
Click Here..
Microsoft Dynamics CRM Installations, Updates and Documentation
Monday, March 30, 2015
Restrict record deletion from sub grid / Associated view based on condition
In CRM you can restrict record
deletion by removing delete permission from that entity for
particular security role.
Sometimes we need to restrict user to delete record based on some
conditions.
You can do by customizing delete button command, and adding some
JavaScript functions.
More tricky when we don't want to allow delete child record based
on parent condition.
Suppose you have two custom entities, say Course and
Students.
One course might have multiple Students.
Relationship between Course and Students is 1: N.
To show students who attending course, there is a sub-grid on
Course form.
From this Sub Grid students will be added and removed from Sub
grid.
But there is requirement that students cannot remove their names
before 5 days of course.
In this case we cannot remove user's delete permission from
Student entity. But we can add some logic to restrict delete when course
deadline meets.
1.
First Create JavaScript web resource in customization solution.
I have new_student.js JavaScript web resource.
In this JavaScript add following code
function RestrictDeleteFromSubgrid() {
var today = new Date();
var courseStartDate = Xrm.Page.getAttribute('new_startdate').getValue();
var millisecondsPerDay = 1000 * 60 * 60 * 24;
var millisBetween = courseStartDate.getTime() -
today.getTime();
var NoOfdays = millisBetween / millisecondsPerDay;
if (NoOfdays < 5) {
alert('You
cannot remove Student. Course is starting in ' +
NoOfdays);
return false;
} else {
return true;
}
}
2.
Open Student Entity customization in Ribbon workbench.
3.
Select Delete button from Sub grid, and right click – Customize command
4.
Now Add Enable Rule
Give proper name to Enable
Rule
5.
Add steps to newly created enable rule
6.
Need to add Custom JavaScript Rule
7.
For the Custom JavaScript rule set
Default : True
Function Name: RestrictDeleteFromSubgrid
InvertResult: False
Library: $webresource:edm_student.js
8.
Add Enable rule to delete button command
9.
Publish Customization using Ribbon Workbench.
10. Now if course is not
starting in 5 days, CRM will allow to delete record.
11. When course is starting in 5 days, then
You can use same technique for Remove button also.. (when N:N relationship).
If you don't want to allow delete from sub grid but wants to allow from Associated view.
We need to add one Parameter to JavaScript function, and modify above function as
function RestrictDeleteFromSubgrid(callingGrid) {
var today = new Date();
var courseStartDate = Xrm.Page.getAttribute('new_startdate').getValue();
var millisecondsPerDay = 1000 * 60 * 60 * 24;
var millisBetween = courseStartDate.getTime() -
today.getTime();
var NoOfdays = millisBetween / millisecondsPerDay;
if (NoOfdays < 5) {
if (callingGrid.get_id() != 'crmFormProxyForRibbon') {
alert('You cannot remove Student. Course is starting in ' + NoOfdays);
return false;
}
else
return true;
} else {
return true;
}
}
you will get calling Grid Id as "crmForm" for Form sub grid and "crmFormProxyForRibbon" for associated view grid.
and pass this parameter from delete button enable rule.
Add Crm Parameter
Name: callingGrid
Value: PrimaryControl
Save and publish your customization..
Subscribe to:
Posts (Atom)