Finance | Project Operations, Human Resources, ...

Unretrieved field in grid using query with group by

(0) Share

Report

Posted on by themet

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

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Martin Dráb 236,124 Most Valuable Professional on at

Like (0)

Report

RE: Unretrieved field in grid using query with group by

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.

Was this reply helpful? Yes No
themet 5 on at

Like (0)

Report

RE: Unretrieved field in grid using query with group by

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.

Was this reply helpful? Yes No
Suggested answer

Ziyao on at

Like (0)

Report

RE: Unretrieved field in grid using query with group by

The group by has two fields, workerOrderId and Worker. It can not be group by at the same time. Try to remove WOId and leave worker only, and then try it again.

Was this reply helpful? Yes No
themet 5 on at

Like (0)

Report

RE: Unretrieved field in grid using query with group by

this is the Form and Query right now.

i get the WorkOrderID but not the supervisor and Worker, and what is _CANNOT_D-> ?

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

RE: Unretrieved field in grid using query with group by

You dont need having at all. Right click on fields node -> New -> Min (or Max) and add all the fields you need this way.

Was this reply helpful? Yes No
themet 5 on at

Like (0)

Report

RE: Unretrieved field in grid using query with group by

this is my query now. am i doing it wrong?

Was this reply helpful? Yes No
themet 5 on at

Like (0)

Report

RE: Unretrieved field in grid using query with group by

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.

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

RE: Unretrieved field in grid using query with group by

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.

Was this reply helpful? Yes No