Fetch XML Condition to SQL WHERE


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"/>
WHEREdbo.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'

Comments

  1. SRS Travel GuideApril 7, 2020 at 9:25 AM

    Our amazing CRM for tour operators travel agent software helps Cruises, Tour Operators, Safaris & Bespoke holiday sellers. Manage the customer journey & Gain up to 30% efficiency!It helps Tour Operators in promoting their travel products, increase sales, organise day to day operations and reduce admin cost.Travel Agency CRM is crafted for travel agencies and tour operators on award-winning Zoho CRM. Get started with a no-obligation demonstration.

    ReplyDelete

Post a Comment

Popular posts from this blog

Workflow Activity Input and Output Parameters and Data Types in MS CRM 2013/15/16

Microsoft Dynamics CRM supports two types of parameters for a workflow activity. Input Parameters Output Parameters Input Parameters The input parameter is annotated with the .NET attribute  "Input" . DefaultAttribute DefaultAttribute class can be used to specify a default value (using  "Default"  attribute) for an input parameter.  Bool [Input("Bool input")] [Output("Bool output")] [Default("True")] public InOutArgument<bool> Bool { get; set; } DateTime [Input("DateTime input")] [Output("DateTime output")] [Default("2013-07-09T02:54:00Z")] public InOutArgument<DateTime> DateTime { get; set; } Decimal [Input("Decimal input")] [Output("Decimal output")] [Default("20.75")] public InOutArgument<decimal> Decimal { get; set; } Double [Input("Double input")] [Output("Double
Read more

Get Workflow Guid in Javascript using OData query

I find that using workflow id might be problematic across deployment, so I wrote a function to retrieve workflow’s guid given the workflow’s name. However, please take some care in assigning workflow name to avoid pain. function call() {       alert("Tets"); var name="Create Phone Call Via Js"; alert(name); var wf=getWorkflowId(name); alert(wf);  } function getWorkflowId(workflowName) { var aa=workflowName; var entityId = Xrm.Page.data.entity.getId(); alert(entityId); alert(workflowName); var serverUrl = Xrm.Page.context.getClientUrl(); alert(serverUrl); var odataSelect = serverUrl + "/xrmservices/2011/OrganizationData.svc/WorkflowSet?$select=WorkflowId&$filter=StateCode/Value eq 1 and ParentWorkflowId/Id eq null and Name eq 'Create Phone Call Via Js'"; alert(odataSelect);     var xmlHttp = new XMLHttpRequest();     xmlHttp.open("GET", odataSelect, false);     xmlHttp.send();     if (xmlHttp.status ==
Read more

Convert Plugin to Sandbox mode,

While working with Dynamics CRM Plugins, the thumb rule for Plugin Registration is: If you are working with CRM On-Premise, use Plugin isolation mode as  None. If you are working with CRM Online, use Plugin isolation mode as  Sandbox. So, now the scenario is you are working on big implementation for CRM On Premise. Now your organization decides to move to CRM Online. The first task comes to mind is to move all the Plugins to Sandbox mode and see if they are still working considering running Plugin in sandbox comes with some  limitations  (such as you cannot access file system/registries/external assemblies/protocols/IP Addresses etc). To convert the Plugin assemblies to Sandbox mode, one option is to open Plugin Registration tool and convert the assemblies one by one which is a tedious task. The other simple option is do this by Customization.xml file. Below are the steps how to do that. Create a solution with all Plugin assemblies or use existing solution if already a
Read more