SQL to FetchXML converter

Charts and Reports in CRM are very powerful, a lot of people using Advanced Find to generate the FetchXML to help them get some ideas about writing FetchXML, but Advanced Find has some limitations, such as cannot aggregate, no table hint, no unique filter, cannot order, top number, alias, group by and etc.

 

Today, a new tool went online for those developers who knows SQL, but not familiar with FetchXML! Please go to www.sql2fetchxml.com to have a try.

 

Any feedback will be appreciated.

 

Here is the FAQ:

 

·         Q1: What's the table name for the query?

·         A1: table name will be the entity name, such as "account", "contact", "task" etc.

·         Q2: What functionalities are supported by this tool?

·         A2: Features supported by FetchXML will be supported by this tool, but since FetchXML only support part of SQL features, so, please be aware of this.

o    We can support: top, distinct, nolock, where, order by, inner join, left outer join, aggregate (count, max, min, avg, sum), group by, alias

o    CRM functions ( using eq and x prefix, for example:  where createdon = xlastyear() or modifiedon=xyesterday(), if any parameters, you can put them in the function, like modifiedon = lastxweeks(4) or modifiedon = inorafterfiscalperiodandyear(4,2006)

o    For link entity conditions, they should be put along with the nearest join conditions, for example, select a.name, c.firstname, c.lastname from account an inner join contact c on a.accountid=c.parentcustomerid and (contact related conditions) where (account related conditions)

 

Here are some samples and instructions:

 

·         Simple table query:

Select name from account where name like '%Kingswaysoft%'

·         Query with top, nolock, order by

Select top 10 name from account with (nolock) where creditlimit > 1000000 order by creditlimit

·         Query with Join and with alias

select a.name, c.firstname, c.lastname from account a inner join contact c on a.accountid=c.parentcustomerid and (c.telephone1 is not null or c.telephone2 is not null or c.telephone3 is not null) where a.creditlimit > 10000

 

·         Aggregate

Select count(*) from account

Select count(distinct name) from account

Select avg(creditlimit) from account

·         Group by

Select count(accountid) from account group by accountratingcode