Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / Query that checks the ...
Finance forum
Suggested answer

Query that checks the last record

(1) ShareShare
ReportReport
Posted on by DELDYN 254
Hi,

I have a tile linked to a query

let's say I have two records for LineRef "W" that has a status "a"

Id    IdCommon   Status     CreatedDateTime                  LineStatus    LineRef
1       X                  Done       2024-10-09 08:46:55.000         a                  W
2       X                  New        2024-10-09 09:46:55.000         a                   W

I want my query to return the last record created for that line, if it's with Status Done. So since the last record is with Status new , my tile count shouldn't return anything.
if the 2nd record wasn't there then my query should return a count of 1.
how to create a tile with an AOT query that achieves this?

This is the query that i did, and currently the tile is returning 1 record. But it shouldn't return anything because that last record created for that line is with status New

select Table1.IDCommon,Table1.Status,Table2.Id, Table3.CreatedDateTime
From Table1 
join Table2 on Table2.LineRef= Table1. LineRef
join Table3 on Table3.Id = Table2.Id
where Table1.IDCommon= 'X'
and Table1.LineStatus = a
and Table1.Status = Done
Categories:
Integrations
Development / Customization / SDK
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 228,552 Most Valuable Professional on at
    Query that checks the last record
    It's a pity that you gave us wrong code and description, because we both wasted time with something else than what you actually wanted. In your own interest, do a better job in describing your requirement next time, as you can get a solution faster.
     
    Are you now saying that you want to get either one or no record in each group? E.g. if you have ten groups and four of them has Status = Done as the last record, your query will return four records?
     
    You're wrong in thinking that your current query will give you information like CreatedDateTime. You don't need to wait for me to tell you that; simply test your code to see that it returns. You'll actually get just Table3.IDCommon, Table3.LineRef and Table2.ID. All other fields will be empty, because they're neither grouped by not aggregated, therefore the group doesn't have any value defined for them.
     
    You wanted to take the Status and CreatedDateTime into account, but your query does neither, therefore it doesn't meet your requirements.
     
    In my opinion, you should create a view that returns the ID of the latest record inside each group. I leave to you which fields to group by, because here I'm again lacking information from you about your data mode. You can use a computed column to give you an ID of the latest record. Then join this view with the Table1 to get the Status field to filter by, and any other fields you want to show. See Join first line in AX 2012, where I've demonstrated something similar.
     
  • DELDYN Profile Picture
    DELDYN 254 on at
    Query that checks the last record
    Hi Martin,

    there is "first only" property in the view, but there is no property for sorting. How to do that?

    I also explained in the 2nd comment that i don't want to filter by IDCommon=X because there could be multiple ones.

    i assume i should use group by and Max instead in the view

    so as a start if I create a view with the following meaning (ignore the syntax):

    select Table3.IDCommon, Table3.LineRef , max(Table2.ID)
    From Table3
    join Table2
    on Table2.LineRef = Table3.LineRef
    Exists join Table1 
    on Table1.ID = Table2.ID
    exists join Table4 
    on Table4.IDCommon = Table3.IDCommon
    where
    Table1.FieldZ = 0
    and Table3.LineStatus = 'a'
    and Table3.TYPE = 100
    and Table3.FieldX = Class::X()
    group by Table3.IDCommon, Table3.LineRef


    Now out of the three lines:
    Id    IdCommon   Status     CreatedDateTime                  LineStatus    LineRef
    1       X                  Done       2024-10-09 08:46:55.000         a                  W
    2       X                  New        2024-10-09 09:46:55.000         a                  W
    3       Y                 Done       2024-10-09 10:46:55.000          a                  Z

    The view should return to me 2 lines:

    Id    IdCommon   Status     CreatedDateTime                  LineStatus    LineRef
    2       X                  New        2024-10-09 09:46:55.000         a                  W
    3       Y                 Done       2024-10-09 10:46:55.000          a                  Z


    now, if i create a new query with this view inner joined with Table1 where Table1.Id = View.Id  and Table1.Status = Done

    this means only this line will be returned as expected
    Id    IdCommon   Status     CreatedDateTime                  LineStatus    LineRef
    3       Y                 Done       2024-10-09 10:46:55.000          a                  Z

    What do you think of this solution? I didn't try it but i feel it will work

    Also is there anything worying you about     Class::X()?
  • Martin Dráb Profile Picture
    Martin Dráb 228,552 Most Valuable Professional on at
    Query that checks the last record
    The solution I gave you was based on the information you shared here. For example, you wanted a filter IDCommon= 'X'. Now your picture mentions Class::x(), but you've never told us anything about it, therefore it's not something I could consider. I suggest you start with the fixed value, test the overall approach and complicate the design later.
     
    Yes, FirstOnly can give you the last record. Which record is the first or the last is just a question of sorting. Do it on the root data source (and test it; I said that I merely assume that it'll work).
     
    Question: then i should put my view as a datasource in the query?
    Answer: Correct. It's already covered in my previous reply.
     
    Question: but joined with what?
    Answer: If your view returns all the data you need, you don't need any join. But if you want fetch some data from other tables, you can. It's up to you.
     
     
  • DELDYN Profile Picture
    DELDYN 254 on at
    Query that checks the last record
    Hi Martin,

    My current query is like this:



    So you want me to create a new view with the same structure? but without filtering on the status Done?
    and you mentioned to use "first only" to get the last record? how would first only get the last record? and on which datasource i shall put the first only?

    then i should put my view as a datasource in the query? but joined with what?
  • Martin Dráb Profile Picture
    Martin Dráb 228,552 Most Valuable Professional on at
    Query that checks the last record
    You'll use the query, as you wanted. Just the query will have a view as a data source.
     
    The query used for the view definition can use all the joins and ranges as in your sample SQL code. Or maybe you have some additional requirements. If so, share them with us ASAP so we don't waste time with designing something else than what you actually want.
  • DELDYN Profile Picture
    DELDYN 254 on at
    Query that checks the last record
    Hi Martin,

    But can i use views with tiles? can you please explain further

    Also the query can have records for different IdCommons

    Id    IdCommon   Status     CreatedDateTime                  LineStatus    LineRef
    1       X                  Done       2024-10-09 08:46:55.000         a                  W
    2       X                  New        2024-10-09 09:46:55.000         a                  W
    3       Y                 Done       2024-10-09 10:46:55.000          a                  Z

    So if i have those three records, then the tile count should be 1  for Id Common Y only .  X shouldn't appear because it's last record is New 

    So when i click on the tile, i should see this 1  record only out of the three
    Id    IdCommon   Status     CreatedDateTime                  LineStatus    LineRef
    3       Y                 Done       2024-10-09 10:46:55.000         a                   Z
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 228,552 Most Valuable Professional on at
    Query
    I would create a view returning the last record (I'm assuming that you can use FirstOnly there). Then I would use a query with a filter for Status = Done. If the last record has a different status, the query will return nothing.

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

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

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

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,552 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,552 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans