View based on query with newest rows only based on date

(0) Share

Report

Posted on by Andrew Huisman

313

Hello everyone,

I was hoping someone could help point out what I'm doing incorrectly here.

I have a form showing NCR's with a table as it's datasource called InventNonConformanceTable. Right now that table has 2556 rows.

I have a client request to show the person who approved the NCR, and that field be filterable.

The info on those NCR's on whether or not they are approved is in a table called InventNonConformanceHistory.

I created a query and then a view with that query as the datasource. The query only grabs the approved NCR's. I added it to the form and I got 2561 rows. When I did some digging, I noticed that some NCRs are approved more than once (5 of them) which is why I'm not getting 5 more records.

So I updated my query and put CreatedDateTime in the Order By, and InventNonConformanceID in the Group By. Then I changed First Only to Yes on the data source in the query. After all this, I'm still getting 2 lines for those 5 records. What am I doing wrong? I want to only grab the latest approved record for every NCR.

I hope this makes sense and hopefully it's something simple that I'm missing.

I have the same question (0)

All responses (35)

Answers (2)

Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

I'm a bit confused. Could you please share the current SQL query of your view?

From your description, I woud expect getting a value just of a single column and either a single record, or that "First Only" is ignored.

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report

Hi Martin,

Here is my query

The range is value = 1 (which is approved)

Then when you click on InventNonConformaceHistory, I switched the first only to yes.

Then here is my view....

But when I use SQL to query that view, and check the InventNonConformaceIDs that were approved twice, they are still there.

I'm trying to create a query that has no duplicate InventNonConformanceIDs. If it was approved twice, then just grab the latest one.....

Hopefully this helps a bit?

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

In SSMS, find your view in Object Explorer, right-click the name and choose Design. This will show you SQL code defining your view, where you're especially interested in the SELECT... part.

Review it and maybe you'll spot a problem by yourself. If not, please share the query with us.

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report

Hi Martin, thank you, I was not aware of this option.

I looked at my view and this is what the SQL is

SELECT T1.INVENTNONCONFORMANCEID, T1.INVENTNONCONFORMANCEHISTORYTYPE, T1.CREATEDDATETIME AS CREATEDDATETIME1, T1.DATAAREAID, T1.PARTITION, T1.RECID, T2.PERSONNELNUMBER,

T2.PARTITION AS PARTITION#2

FROM dbo.INVENTNONCONFORMANCEHISTORY AS T1 INNER JOIN

dbo.HCMWORKER AS T2 ON T1.WORKER = T2.RECID AND T1.PARTITION = T2.PARTITION

WHERE (T1.INVENTNONCONFORMANCEHISTORYTYPE = 1)

This doesn't make sense to me, because as I showed above, I have a Group By and Order By, but neither of them are showing up here. I'm going to try removing the query from my view and readding it, to see if I can get this to change at all.

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report

I removed the query from the view, then readded it. Then I ran a full build and sync. This time when I ran the design on the view, it returned this

SELECT T1.INVENTNONCONFORMANCEID, T1.INVENTNONCONFORMANCEHISTORYTYPE, T1.CREATEDDATETIME AS CREATEDDATETIME1, T1.DATAAREAID, T1.PARTITION, 1010 AS RECID, T2.PERSONNELNUMBER,

T2.PARTITION AS PARTITION#2

FROM dbo.INVENTNONCONFORMANCEHISTORY AS T1 INNER JOIN

dbo.HCMWORKER AS T2 ON T1.WORKER = T2.RECID AND T1.PARTITION = T2.PARTITION

WHERE (T1.INVENTNONCONFORMANCEHISTORYTYPE = 1)

GROUP BY T1.INVENTNONCONFORMANCEID, T1.INVENTNONCONFORMANCEHISTORYTYPE, T1.CREATEDDATETIME, T1.DATAAREAID, T1.PARTITION, T2.PERSONNELNUMBER, T2.PARTITION

The good news is the Group By is there, but yet I'm still having double entries. For example, I know one InventNonConformanceId that is approved twice and it still shows up twice.

How can this be if I'm grouping by InventNonConformanceId?

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

The result is correct. You're groupping by CreatedDateTime and these two records have different values in this field.

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report
Yes, I see that now. I took CreatedDateTime out of the view and then it got better, but now there are still some duplicates. It's because those particular InventNonConformanceIDs were approved by two different people, so there are two different personnel numbers. But I need that field because I want to show who was the last person to approve it.....

Here is the datamethod that is grabbing the information. It is located in InventNonConformanceTable

select firstonly PersonnelNumber from hcmWorker join RecId from inventNonConformanceHistory order by CreatedDateTime desc where inventNonConformanceHistory.InventNonConformanceID == this.InventNonConformanceID && inventNonConformanceHistory.InventNonConformanceHistoryType == InventNonConformanceHistoryType::Approved && inventNonConformanceHistory.Worker == hcmWorker.RecId; return hcmWorker.PersonnelNumber;

How do I replicate this code into a view so I can add the view to the form and show the approved by (personnel number) field......

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

I would consider two solutions:

1) Create a view returning the first worker for each InventNonConformanceID (filterered or grouped by HistoryType) and join it with InventNonConformaceHistory in the defintion of your primary view.

2) Create a computed column.

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report

I'm sorry Martin, but I don't understand.

My original form only has InventNonConformanceTable on it. Not InventNonConformanceHistory.

How do I create a view that returns the first worker? I feel like this has been my question from the beginning.....

If I create a view with InventNonConformanceHistory as my datasource, I can't add the field Worker to it, because then it will group by it.

Please walk me through this.....

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report
Sorry, join it with whatever the right data source is.

A view returning a single worker could have a query like this, for example:

select maxOf(PersonnelNumber) from hcmWorker join InventNonConformanceID from inventNonConformanceHistory group by inventNonConformanceHistory where ...

It doesn't take any timing into account. If you need it, you might consider getting the history with the highest ID (and then joining it with a worker).

select maxOf(RecId), InventNonConformanceID from inventNonConformanceHistory group by InventNonConformanceID where ...

If you must get the worker based on CreatedDateTime, using a computed column will be easier.

Was this reply helpful? Yes No