Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
fetch XML RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a simple fetch xml that  returns all items from a custom entity with a new_clearedFunds (date) of tomorrow.

    how can i expand this so it excludes weekend ( SAT & SUN ) and any business closures ( which in our case are Bank holidays)

    Ie

    If today is Thursday and Friday and Monday is in our business closures dates, i want the records with  a new_clearedfunds date for the Tuesday

    If today is Friday, and Monday is in our business closures dates, i want the records with  a new_clearedfunds date for the Tuesday

    If today is Friday, and Monday is NOT our business closures dates, i want the records with  a new_clearedfunds date for the Monday

    Is this possible with Fetch XML or should I just query the SQL tables direct in TSQL for my report ?

    Dont ask me .. i dont know

    Friday, September 6, 2013 11:02 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    AFAIK the next working day could not be resolved by the Fetch in CRM and you may achieve it through SSRS.


    My Weblog | My Website

    Friday, September 6, 2013 12:19 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Pete -

    Perhaps you can achieve this through FetchXml by creating a new entity, "BusinessClosures", which can store a record (date field) for each day of the year. Each record in the BusinessClosure entity should also have a corresponding boolean checkbox field called "Exclude". In your fetch, your custom entity can join (link) to this new entity and will select the next day where the "Exclude" checkbox is not checked. It would be tedious to manually enter dates into this new entity, but if you set it up in Excel first then import it, you could populate these fields in a matter of minutes.


    Friday, September 6, 2013 2:43 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    The date fields in CRM does not recognize the day of the week. So it could not be done through Fetch. You could create a custom report based on a SQL query (using the Datename(weekday, [the name of the field])) to exclude the mentioned dates. Then, the created rdl could be imported to CRM.


    My Weblog | My Website

    Friday, September 6, 2013 11:47 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I don't think i explained what i was looking for very clearly. The field new_clearedfunds is a datefield. The day name is not important, its the dates im interested in. If i was to put all weekend dates in our business closures could the fetch xml be modified for example;

    Today 06/09/2013

    Business closure dates 07/09/2013, 08/09/2013

    fetch xml to return all records for 09/09/2013.

    I need to be able to run this fetch xml for a daily report that shows the credits for the next 'working' day, ie the next day after the new_clearedfunds that the business is open.

    If it is not possible I will go the old route and run a ssrs and query using TSQL

    Dont ask me .. i dont know

    Friday, September 6, 2013 11:56 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    AFAIK the next working day could not be resolved by the Fetch in CRM and you may achieve it through SSRS.


    My Weblog | My Website

    Friday, September 6, 2013 12:19 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Pete -

    Perhaps you can achieve this through FetchXml by creating a new entity, "BusinessClosures", which can store a record (date field) for each day of the year. Each record in the BusinessClosure entity should also have a corresponding boolean checkbox field called "Exclude". In your fetch, your custom entity can join (link) to this new entity and will select the next day where the "Exclude" checkbox is not checked. It would be tedious to manually enter dates into this new entity, but if you set it up in Excel first then import it, you could populate these fields in a matter of minutes.


    Friday, September 6, 2013 2:43 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Are you creating an SSRS report or using the FetchXML query for some other custom application? If it is for the latter, you could simply build the exclusion logic into your application after you run the FetchXML query.

    Please feel free to contact me using the following methods

    Monday, September 9, 2013 10:18 AM