Finance | Project Operations, Human Resources, ...

Query that checks the last record

(1) Share

Report

Posted on by DELDYN

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

All responses (9)

Answers (0)

Martin Dráb 230,370 Most Valuable Professional on at

Like (0)

Report

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 304 on at

Like (0)

Report

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       Y                 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       Y     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       Y                 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 230,370 Most Valuable Professional on at

Like (0)

Report

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 304 on at

Like (0)

Report

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 230,370 Most Valuable Professional on at

Like (0)

Report

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 304 on at

Like (0)

Report

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 230,370 Most Valuable Professional on at

Like (0)

Report

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 304 on at

Like (0)

Report

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 230,370 Most Valuable Professional on at

Like (0)

Report

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.