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)

Zain Bokhari 3,208 Moderator on at

Like (0)

Report

RE: multiple table query grouping

This should bring all three values if they exist. Pick one item number and Cross check with your data, you can also iterate through this query in a job. You will see the results.

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (1)

Report

RE: multiple table query grouping

How does the query look like in pseudo-SQL if you do itrDS.toString() call before the queryRun object?

Maybe you should include the relation field InventDimId.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: multiple table query grouping

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

Like (0)

Report

RE: multiple table query grouping

Can you show us your query? with all groups ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: multiple table query grouping

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: multiple table query grouping

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

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

Like (0)

Report

RE: multiple table query grouping

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

RE: multiple table query grouping

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
Verified answer

Zain Bokhari 3,208 Moderator on at

Like (1)

Report

RE: multiple table query grouping

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
Suggested answer

Vilmos Kintera 46,149 on at

Like (2)

Report

RE: multiple table query grouping

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