5
Hello Everyone,
I'm really new to AX so i need your help.
I have a custom Form from custom Table which shows orker activity
and i want to make another Form with Query Group By to show only one worker per row. it should be like this
but instead, i get unretrieved Field like this
this is my query
any help/guide/correction would be appreciated.
Regards,
Fahmi
Your filters for ermpty strings have nothing to do either with NULL nor your problem with grouping.
If you group by Worker and WOId and you don't use any aggregations, only those two fields will be obtained from database. All other fields won't have any value at all. If you want to get other fields, you must either group by them or decide how multiple values in a group should be aggregated to a single value (e.g. to get the minimum start date from records in the group).
Why you don't get anything for Worker is a bit less intuitive. You group by Worker field, but it's not what's displayed in the form. Worker field contains record ID (a number), but it's not very useful, therefore you normally see worker names. But your don't group by worker name, therefore its value is undefined as well.
Hi Ziyao,
Thanks for the reply. After i searched about group by, AX does not able to works with null data so i added the range for supervisor, worker, WOID, starddate, and enddate with value = !""
the table i want to query is looked like this
i made the Form to summarize the working time of each Worker from each WOID, and i placed the Query Form in Summarize button
and it ended up like this
how to fix this?
Regards
Fahmi.
in SQL it should be like this :
SELECT WorkOrderID, Supervisor, Worker, EndDate, StartDate
FROM RS_WO_ActivityTrans
GROUP Worker;
it works just fine in SQL, but i dont know how to implement it on AOT Query.
is it mandatory to apply MAXOF() or MINOF() function to every Datetime field? because i dont need that field on my Form.
so you cannot just add fields you want, you should use aggregation functions. For example, 2 records for 1 worker may have different end date. So you need to decide what to do with it because you want to see only 1 row. You can use MAXOF() or MINOF() functions to select max or min date. You need to apply those functions to each field that is not a part of group by.
#1
André Arnaud de Cal...
291,431
Super User 2024 Season 2
#2
Martin Dráb
230,503
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
