Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

Query that checks the last record

Posted on by 304
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
  • Martin Dráb Profile Picture
    Martin Dráb 229,316 Most Valuable Professional on at
    Query that checks the last record
    You're claiming that your query gives you what you want, but it doesn't sort by CreatedDateTime at all. Can you confirm that you mean "the record with the highest ID in Table2" when you say you want "the last record is with Status new"? If so, it's a pity that you didn't write down your requirements correctly, because then we're all wasting time and you could have get a solution earlier.
  • DELDYN Profile Picture
    DELDYN 304 on at
    Query that checks the last record
    Hi Martin,

    Yes I know that the createdDateTime and status won't be returned in the view. I just put them so that it helps in understanding the requirement.

    And to answer your question below, yes it will return four
    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?
     

    So the original query is returning this (with other fields):
    select Table1.IDCommon,Table1.Status,Table2.Id, Table3.CreatedDateTime, Table2. LineRef, Table2. LineStatus
    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

    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                       Done       2024-10-09 10:46:55.000          a                  Z

    The view will return this:
    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

    Id    IdCommon    LineRef
    2       X                     W
    3                           Z


    Now when I join the view with the query
     based on Id, LineRef and IdCommon
    this means only this line will be returned as expected
    Id    IdCommon   Status     CreatedDateTime                  LineStatus    LineRef
    3                       Done       2024-10-09 10:46:55.000          a                  Z

    Now what is better in terms of performance? to join the query with the view OR use computed column?

    because i feel the code in the view and query is somehow repetitive? so maybe computed column is better?
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 229,316 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 304 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                       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                       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                       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 229,316 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 304 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 229,316 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 304 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                       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                       Done       2024-10-09 10:46:55.000         a                   Z
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 229,316 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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,316 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans