Modify Query in AOT

This question has suggested answer(s)

I created a query in AOT name Q1. I have two variable StartDate and EndDate and want to AddRange to query based on these two variable. Below is my code:

How can I implement this code in query Q1 in AOT?

Date startDate;

Date endDate

startDate=~~~~~~~~~~~~~~~~~~~

endDate=~~~~~~~~~~~~~~~~~

Q1.addRange(fieldNum(TabeleName,FieldName)).value(SysQuery::range(startDate,endDate));

All Replies
  • Hi

    On AOT Query, create a range on field "Fieldname" and on property Value set the date range as 01/01/2014..31/01/2014 according the right date format.

    Regards

    Thanks & Regards

    Denis Macchinetti

    Senior Technical Architect

  • My startDate and endDate are not constant. I should caculate them based on the today date. Like below.

    StartDate = DateStartMth(GetDate() -1)

    endDate= endmth(GetDate() -1)

  • My startDate and endDate are not constant. I should caculate them based on the today date. Like below.

    StartDate = DateStartMth(GetDate() -1)

    endDate= endmth(GetDate() -1)

  • Hi Ali,

    Check out the SysQueryRangeUtil class. You can use existing methods there or create your own.

    (msdn.microsoft.com/.../cc618616%28v=ax.50%29.aspx)

    For example, say I have an AOT query with SalesTable as datasource. Then I can add a Range to createdDateTime field with value:

    (dayRange(-100,0))

    It would mean I will only see SalesOrder from the past 100 days relative to the session date.

    Note that when using SysQueryRangeUtil methods, there should be outer brackets enclosing the method itself.

    Regards,

    Dominic

    My blog | PBC

    This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

  • Thanks Dolee for you answer. How about if today date is Jan 13 and I only want to see SalesOrder in date range 12/01/2013 to 12/31/2013??

    In this case I need to create these to dates based on the today date.

  • Check if any existing methods fits your needs.

    If not, create one that can help under SysQueryRangeUtil. I think monthRange can be a reference.

    Regards,

    Dominic

    My blog | PBC

    This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

  • Hi Denis,

    To follow up on your suggestion above, in the 'value' of the range, instead of hard-coding a date value, I would like to use the ValidTo field and set its Value (> Today). So that it doesn't return values modified today.

    Is there a way to achieve this?

  • Hi Denis,

    To follow up on your suggestion above, in the 'value' of the range, instead of hard-coding a date value, I would like to use the ValidTo field and set its Value (> Today). So that it doesn't return values modified today.

    Is there a way to achieve this

  • Hi

    On Range condition you can write (greaterThanDate())

    Take care use the parenthesis first and at the end of the method.

    More info at technet.microsoft.com/.../aa569937.aspx

    Regards

    Thanks & Regards

    Denis Macchinetti

    Senior Technical Architect

  • try to analyse following

    class\HcmWorkersHiredInPeriodController

    in this class we have a method called setRanges()

    Further go inside the SrsReportHelper::addDateTimeRangeToQuery()

    for more details how to add range to the Query.

    Please verify and update


    Please update with your feedback.

    If this post helps you, Please verify this answer and earn Thank you Badge for yourself (Follow this link for more details https://community.dynamics.com/braggerbadges/badge/48.aspx)

    Regards, NITESH RANAJN (PMP) | Dynamics AX Consultant

  • Thanks Denis and Nitesh,

    I haven't resolved my issue yet, however, I think you have provided enough material. I will 'parse' through this.

    What I'm trying to achieve is not really through code. it's just in an AOT query data sources, I have a table for which I need to set a range value for the 'ValidTo' field to (> Today).

    thanks for your help.

  • Hi

    As I have say above, on the AOT Query Range condition you have to write (greaterThanDate())

    That's it!

    Regards

    Thanks & Regards

    Denis Macchinetti

    Senior Technical Architect

  • Hi Denis,

    this does exactly what I needed. thanks a bunch.

    I can't seem to flag your answer as 'verified' because it's someboby else's post.

    Regards,

  • Hi Boubacar

    No issue.

    I'm happy that you find a solution about your problem and I hope to have other opportunity to help you again.

    Regards

    Thanks & Regards

    Denis Macchinetti

    Senior Technical Architect