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.
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.
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()).
Martin,
Thanks for showing me what is wrong, but could you also tell me how to fix it, if possible.
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.
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?
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).
The build works, but the sync does not
No, I don't know why you're "getting lots of errors". Please see my advice above.
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.
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.
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,817
Most Valuable Professional
nmaenpaa
101,156