Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

View based on query with newest rows only based on date

(0) ShareShare
ReportReport
Posted on by 284

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. 

  • Andrew Huisman Profile Picture
    Andrew Huisman 284 on at
    RE: View based on query with newest rows only based on date

    Okay thank you, it finally worked.  I guess I was looking for an answer something along the lines of

    "Just add a %2 to the %9 at the end of your query there, and you should be good"

    I did that, and now everything is working perfectly.  It was a process, but we got there.  I do appreciate the help.

    pastedimage1676495783470v1.png

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: View based on query with newest rows only based on date

    Sorry, I thought it was obvioius. You generate ORDER BY CREATEDDATETIME but you want ORDER BY INVENTNONCONFORMANCEHISTORY.CREATEDDATETIME, therefore you must add INVENTNONCONFORMANCEHISTORY. before CREATEDDATETIME (%9 in your template for strFmt()).

  • Andrew Huisman Profile Picture
    Andrew Huisman 284 on at
    RE: View based on query with newest rows only based on date

    Martin,

    Thanks for showing me what is wrong, but could you also tell me how to fix it, if possible.

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: View based on query with newest rows only based on date

    The problem is that it's not the same query. You're testing something else than what your code actually generates.

    Your code produces ORDER BY CREATEDDATETIME, not ORDER BY INVENTNONCONFORMANCEHISTORY.CREATEDDATETIME.

  • Andrew Huisman Profile Picture
    Andrew Huisman 284 on at
    RE: View based on query with newest rows only based on date

    I did test my SQL.  Here it is and it works perfect

    SELECT TOP 1 HCMWORKER.PERSONNELNUMBER

    FROM INVENTNONCONFORMANCEHISTORY

    JOIN HCMWORKER ON HCMWORKER.RECID = INVENTNONCONFORMANCEHISTORY.WORKER

    WHERE INVENTNONCONFORMANCEHISTORY.INVENTNONCONFORMANCEID = '01774'

    AND INVENTNONCONFORMANCEHISTORY.INVENTNONCONFORMANCEHISTORYTYPE = 1

    ORDER BY INVENTNONCONFORMANCEHISTORY.CREATEDDATETIME DESC

    Is this line not indicating that CreatedDateTime is from InventNonConformanceHistory?
    pastedimage1676489061724v1.png

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: View based on query with newest rows only based on date

    It seems that you didn't really test your SQL code. The error message tells you that you didn't specify which CreatedDateTime field you mean (it exists in multiple tables).

  • Andrew Huisman Profile Picture
    Andrew Huisman 284 on at
    RE: View based on query with newest rows only based on date

    The build works, but the sync does not

    pastedimage1676488534131v1.png

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: View based on query with newest rows only based on date

    No, I don't know why you're "getting lots of errors". Please see my advice above.

  • Andrew Huisman Profile Picture
    Andrew Huisman 284 on at
    RE: View based on query with newest rows only based on date

    I'm just asking if you see anything wrong with the above code.  If it looks like it should work, then please let me know.

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: View based on query with newest rows only based on date

    I'm sorry, but "I'm getting lots of errors" is too vague; it doesn't allow me to give you any specific advice.

    If your code compiles, look at waht SQL code it returns and compare it with what you wanted.

    If it doesn't even compile, pay attention to what compiler tells you. If you need more assistance, please share details of those errors.

    Note that if you had a computed column for Worker (Int64) and now you want to use PersonnelNumber, you need to create a new field in the view with a different type of the computed column.

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

Announcing Our 2025 Season 1 Super Users!

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

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,817 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans