Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

How to get only last record in table by using query in AX 2012

Posted on by 630

Hello Friends,

I want to get only last record from table by using query in Ax 2012.

I am using below code.

queryBuildDataSource.addSortField(fieldNum(ABC, RecId), SortOrder::Descending);

By using above code I am getting all records but I want to get only last record.

Please tell me how to get only last record from query.

Thanks & Regards

Rahul

  • ergun sahin Profile Picture
    ergun sahin 8,812 Super User on at
    RE: How to get only last record in table by using query in AX 2012

    Yes, RecId is not the right field for sorting. There is no guarantee.

  • Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    RE: How to get only last record in table by using query in AX 2012

    If the goal is getting the record that was inserted last, then just taking the record with the highest record ID doesn't always do the job (if there is more than one AOS, which should always be the case in production).

  • ergun sahin Profile Picture
    ergun sahin 8,812 Super User on at
    RE: How to get only last record in table by using query in AX 2012

    I think, Rahul means the last record in the table (bigest RecId), not the last record in the query.

    By the way, if you have a createdDateTime-style field for sorting, or a field from a number sequences (continuous), I recommend using it.

  • Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    RE: How to get only last record in table by using query in AX 2012

    No, I did mean Ascending. If we want to to get the last record when using descending sort order (as in the question), we can use FirstOnly and use the opposite sort order (therefore we need to change Descending to Ascending).

    If we just take the first record instead of the last, we would get different record than required.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,696 Super User on at
    RE: How to get only last record in table by using query in AX 2012

    Small correction on the answer from Martin. It should be set to "descending". www.differencebetween.com/.../

    I think Martin intended to use descending as well. He might typed too quickly.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: How to get only last record in table by using query in AX 2012

    As Martin told you, you can use firstOnly to get only the first record that matches your criteria. Then all you need to do is to adjust your criteria so that the "last" is first.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    RE: How to get only last record in table by using query in AX 2012

    Set query.firstOnly1(true) to get just the first record.

    And change sort direction to SortOrder::Ascending, therefore the last record will become the first.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans