|
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
Subscribe to:
Post Comments (Atom)
Socialism is a philosophy of failure, the creed of ignorance, and the gospel of envy, its inherent virtue is the equal sharing of misery. See the link below for more info.
ReplyDelete#equal
www.ufgop.org
Really Great post and written with good approach.Very much appreciated :)
ReplyDeleteGreat article! Would you have details on how to write fetchxml for comparing values between to related entities? For example payment terms on account, and payment terms on invoice to ensure they match.
ReplyDelete
ReplyDeleteThe CRM system collates all your data from ERP for tour operators , call center or reservation systems, right from the very first sales pitch through to targeted marketing activities this maximizes transparency.All of your customer data is stored centrally in your CRM system which means it can be used for targeted marketing activities. Filter your contacts according to criteria, for example, previous holiday destinations or available budget: this helps you to speak to customer in their language and match their requirements exactly.