Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX (Archived)

Max Query in QueryBuildDataSource

(0) ShareShare
ReportReport
Posted on by 3,803

public void processReport()

{

   QueryBuildDataSource        qbds,qbds1;

   QueryRun                               qr;

   Query                                       query = new query();

 

try

    {

        ttsBegin;

 

        qbds  = query.addDataSource(tableNum(smmActivityParentLinkTable));

        qbds1 = qbds.addDataSource(tablenum(smmActivities));         

        qbds1.addRange(fieldNum(smmActivities, Purpose)).value(strFmt('(Purpose !=0)'));

        qbds1.addRange(fieldNum(smmActivities, Closed)).value(queryValue(enum2str(NoYes::Yes)));      

        qbds1.addRange(fieldNum(smmActivities, ActualEndDateTime)).value(queryRange('(ActualEndDateTime)'), dateMax());

        qbds1.addSelectionField(fieldNum(smmActivities, ActualEndDateTime), SelectionField::Max);

        qbds1.relations(true);   

 

        qr = new queryrun(query);

 

        while (qr.next())

        {

            _smmActivityParentLinkTable = qr.get(tableNum(smmActivityParentLinkTable));

           

            select _smmActivities where _smmActivities.ActivityNumber == _smmActivityParentLinkTable.ActivityNumber;

                select _ProjTable where _ProjTable.RecId == _smmActivityParentLinkTable.RefRecId; 

            {

                    _PII_AllProjectReportTmp.clear();

                    _PII_AllProjectReportTmp.PII_ProjectName       = _ProjTable.Name;

                    _PII_AllProjectReportTmp.PII_Time              = _smmActivities.ActualEndDateTime;

                    _PII_AllProjectReportTmp.PII_LastActivity      = _smmActivities.Purpose;           

                    _PII_AllProjectReportTmp.PII_Issues            = _smmActivities.memo_issues;

                    _PII_AllProjectReportTmp.PII_Summary           = _smmActivities.memo_summary;

                    _PII_AllProjectReportTmp.PII_FollowUp          = _smmActivities.memo_followup;

                    _PII_AllProjectReportTmp.insert();

            }

 

Example :

Activity

Issues

Summary

FollowUp

ActualEndDateTime

Test1

Test1

Test1

Test1

6/10/2014

Test2

Test2

Test2

Test2

6/11/2014

Test3

Test3

Test3

Test3

6/12/2014

In report Now, for Field ActualEndDateTime, all data show in report.

I want just maximum date from field ActualEndDateTime in my report.

So, i just want show Test3 with maximum value from ActualEndDateTime  = 6/12/2014.

Any body help me.

Please..

 

*This post is locked for comments

  • Mohammad Raziq Ali Profile Picture
    Mohammad Raziq Ali 2,457 on at
    RE: Max Query in QueryBuildDataSource

    Hi PII FDC,

    did you get this result.

    Please let me know.

    Thanks

  • fajar Profile Picture
    fajar 3,803 on at
    RE: Max Query in QueryBuildDataSource

    This is my source code fot this report.

    But, all data show in report.. Not based on maximum ActualEndDateTime.

    [SysEntryPointAttribute(false)]

    public void processReport()

    {

       QueryBuildDataSource        qbds,qbds1;

       QueryBuildRange             qbr;

       QueryRun                    qr;

       Query                       query = new query();

       smmActivities                                  _smmActivities;

       PII_AllProjectReportTmp              _PII_AllProjReportTmp;

       smmActivityParentLinkTable        _smmActivityParentLinkTable;

       ProjTable                                          _ProjTable;

       try

       {

           ttsBegin;

           qbds  = query.addDataSource(tableNum(smmActivityParentLinkTable));

           qbds1 = qbds.addDataSource(tablenum(smmActivities));          

           qbds1.addRange(fieldNum(smmActivities, Purpose)).value(strFmt('(Purpose !=0)'));

           qbds1.addRange(fieldNum(smmActivities, Closed)).value(queryValue(enum2str(NoYes::Yes)));      

           qbds1.relations(true);

           qbds1.addOrderByField(fieldNum(smmActivities, ActualEndDateTime), SortOrder::Descending);

           qr = new queryrun(query);

           while (qr.next())

           {

               _smmActivityParentLinkTable = qr.get(tableNum(smmActivityParentLinkTable));

               select _smmActivities where _smmActivities.ActivityNumber == _smmActivityParentLinkTable.ActivityNumber;

                   select _ProjTable where _ProjTable.RecId == _smmActivityParentLinkTable.RefRecId;  

               {

                       _PII_AllProjectReportTmp.clear();

                       _PII_AllProjectReportTmp.PII_ProjectName       = _ProjTable.Name;

                       _PII_AllProjectReportTmp.PII_Time              = _smmActivities.ActualEndDateTime;

                       _PII_AllProjectReportTmp.PII_LastActivity      = _smmActivities.Purpose;

                       _PII_AllProjectReportTmp.PII_Issues            = _smmActivities.memo_issues;

                       _PII_AllProjectReportTmp.PII_Summary           = _smmActivities.memo_summary;

                       _PII_AllProjectReportTmp.PII_FollowUp          = _smmActivities.memo_followup;

                       _PII_AllProjectReportTmp.insert();

               }

       }

           ttsCommit;

       }

       catch

       {

           ttsAbort;

       }

    }

    Please help me :(

  • fajar Profile Picture
    fajar 3,803 on at
    RE: Max Query in QueryBuildDataSource

    Thanks Denis for your Answer..

    I try, but all data stay show in my report.
    I just want show maximum ActualEndDateTime in my report.

    I will explain... in Modul Project Management and Accounting.

    I want create report use data from tabel ProjTable and smmActivities.

    Example my report ::

    Proj ID

    Activity

    Issues

    Summary

    ActualEndDate

    PROJ-1405-01

    AA1

    AA1

    AA1

    5/10/2014

    AA2

    AA2

    AA2

    5/14/2014

    PROJ-1405-02

    BB3

    BB3

    BB3

    5/16/2014

    BB4

    BB4

    BB4

    5/18/2014

    PROJ-1405-93

    CC5

    CC5

    CC5

    5/20/2014

    CC6

    CC6

    CC6

    5/22/2014

    All record in one Proj ID show.

    I just want show record based on maximum ActualEndDateTime.

    So, i want report as follows ::

    ID Project

    Activity

    Issues

    Summary

    ActualEndDate

    PROJ-1405-01

    AA2

    AA2

    AA2

    5/14/2014

    PROJ-1405-02

    BB4

    BB4

    BB4

    5/18/2014

    PROJ-1405-03

    CC6

    CC6

    CC6

    5/22/2014

    Please help me :(

  • Suggested answer
    Denis Macchinetti Profile Picture
    Denis Macchinetti 16,444 on at
    RE: Max Query in QueryBuildDataSource

    Hi Fajar

    As per my understanding, I think you have to modify your code as below.

    Practically, order the query by "ActualEndDateTime" field

    After that, the first record will be the one with highest date.

           qbds = query.addDataSource(tablenum(smmActivities));        

           qbds.addRange(fieldNum(smmActivities, Purpose)).value(strFmt('(Purpose !=0)'));

           qbds.addRange(fieldNum(smmActivities, Closed)).value(queryValue(enum2str(NoYes::Yes)));      

           qbds.addRange(fieldNum(smmActivities, ActualEndDateTime)).value(queryRange(dateNull(), dateMax() ) );

           qbds.addOrderByField( fieldNum(smmActivities, ActualEndDateTime), SortOrder::Descending);

           qr = new queryrun(query);

           while (qr.next())

           {

               smmActivities = qr.get(tableNum(smmActivities));

               info ( smmActivities.ActivityNumber + " " + queryValue(smmActivities.ActualEndDateTime) );

               break;

           }

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,162 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,962 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans