multiple table query grouping

(0) Share

Report

Posted on by Community Member

Hello,

I have an issue. There is a query made of inventTrans and inventDim tables. I need to group by one of InventTrans and a few of InventDim fields. Query works fine, when I add invTransDS.addGroupByField(fieldnum(InventTrans, ItemId)) it groups by itemId from inventTrans, but other fields from inventDim becomes null in grouped record. How could I solve this? Thank you.

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (2)

Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report

If you use group by, only groupped and aggregated fields are going to having values.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

How could I define groupped field that I need?

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (2)

Report

I am unsure what exactly do you mean, but you need to group by on all datasources on all fields that you expect to return. Here is an example:

axatluegisdorf.blogspot.hu/.../select-group-by-and-join-order-by.html

Was this reply helpful? Yes No
Verified answer

Zain Bokhari 3,208 Moderator on at

Like (1)

Report

You can only get the grouped data from multiple fields, if you group by multiple fields.

For example if a table has three fields A, B, C and you group by A, you will only get the value for A. The other fields, if non-numeric, will be blank.

If you want results for all of these fields you can add all of these as group by fields on the datasource. Which will give you combinational groups of fields.

( A-value1 B-value1 C-value1 )

( A-value1 B-value1 C-value2 )

( A-value1 B-value2 C-value1 )

( A-value1 B-value2 C-value2 )

and so on ..

You get the idea.

Where do you want to use this query btw? Report? Because if so, you can do the grouping at report end as well.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I use this query in a form. When I group by two fields, it groups well (the amount of rows is correct), but one field still doesn't have value.

Was this reply helpful? Yes No
Zain Bokhari 3,208 Moderator on at

Like (0)

Report

If that field is a text field it will definitely not have a value. Like I said, non-numeric non-grouped fields are left blank when a grouping is added to the query.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Its non-numeric, but I group by this field and it shows no value.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

It groups correctly, it just doesn't show the value.

Was this reply helpful? Yes No
Zain Bokhari 3,208 Moderator on at

Like (0)

Report

Can you show us your query? with all groups ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Was this reply helpful? Yes No