Finance | Project Operations, Human Resources, ...

While select with sum() and group by across joined tables (CustInvoiceTrans and CustInvoiceJour)

(2) Share

Report

Posted on by JeanB

208

Hi everyone,

I'm building a custom form in D365FO that summarizes sales by ItemId. Now I need to extend it to group by both ItemId and InvoiceAccount.

The issue is that InvoiceAccount is in CustInvoiceJour, and I’m trying to join it with CustInvoiceTrans using:

CustInvoiceJour.SalesId == CustInvoiceTrans.SalesId CustInvoiceJour.InvoiceId == CustInvoiceTrans.InvoiceId CustInvoiceJour.InvoiceDate == CustInvoiceTrans.InvoiceDate CustInvoiceJour.NumberSequenceGroup == CustInvoiceTrans.NumberSequenceGroup

I've tried this :

while select sum(LineAmount), sum(Qty), invoiceTrans.ItemId, invoiceJour.InvoiceAccount
    from invoiceTrans
    join invoiceJour
        where ...
    group by invoiceTrans.ItemId, invoiceJour.InvoiceAccount

And I have errors.

How can I correctly write a grouped aggregation using sum() across two joined tables in X++?

Thanks in advance!

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Jonas "Jones" Melgaard 4,991 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi JeanB,

In X++ you define aggregates as fields in the join statements: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-ref/xpp-data/xpp-select-statement#grouping-and-ordering-the-query-results

Here is an example of what it could look like, I like to structure my aggregated selects like this:

internal final class community_selectExample { public static void main(Args _args) { CustInvoiceJour custInvoiceJour; CustInvoiceTrans custInvoiceTrans; while select InvoiceAccount from custInvoiceJour group by custInvoiceJour.InvoiceAccount, custInvoiceTrans.ItemId join sum(LineAmount), sum(Qty), ItemId from custInvoiceTrans where custInvoiceTrans.InvoiceId == custInvoiceJour.InvoiceId && custInvoiceTrans.SalesId == custInvoiceJour.SalesId && custInvoiceTrans.InvoiceDate == custInvoiceJour.InvoiceDate && custInvoiceTrans.numberSequenceGroup == custInvoiceJour.numberSequenceGroup { info(strFmt("Invoice Account %1, ItemId %2, qty %3", custInvoiceJour.InvoiceAccount, custInvoiceTrans.ItemId, custInvoiceTrans.Qty)); break; //Break early } } }

However, keep in mind that you will have to build the query in the form in a different way, either using a query defined in the AOT or one that is built in the form using QueryBuildDataSource.

Was this reply helpful? Yes No
Raj Borad 1,455 on at

Like (1)

Report
Copy link

Link copied!

Hi,

Can you please share the SS of the errors which you have got?

When performing a grouped aggregation across joined tables, you must ensure the group by clause includes only fields in the select and the join is correct. In your case, grouping by ItemId and InvoiceAccount is valid.

while select sum(LineAmount), sum(Qty), ItemId, InvoiceAccount
from invoiceTrans
group by ItemId, InvoiceAccount
join InvoiceAccount from invoiceJour
where invoiceJour.SalesId == invoiceTrans.SalesId &&
invoiceJour.InvoiceId == invoiceTrans.InvoiceId &&
invoiceJour.InvoiceDate == invoiceTrans.InvoiceDate &&
invoiceJour.NumberSequenceGroup == invoiceTrans.NumberSequenceGroup
{
// Get the values
}

Ensure both tables are properly indexed for the join fields. Also, the fields in the group by are the same as those selected. Are the SalesId, InvoiceId, etc.

Thanks,

Raj D Borad

Was this reply helpful? Yes No
JeanB 208 on at

Like (0)

Report
Copy link

Link copied!
Hello Jonas, thanks a lot for your detailed reply!

I'm still new to X++ and I'm having some difficulty understanding exactly how to use QueryBuildDataSource in this context. Your explanation helped, but I'm unsure how to build the query dynamically in code, especially with aggregation (sum and group by).

Would you happen to have a simple code example? Or maybe a standard form I could look at that uses QueryBuildDataSource for grouping and joining?

Just to give more context:

The form has two date filters (StartDate, EndDate)

I'm aggregating sales data by ItemId

I now need to include InvoiceAccount (from CustInvoiceJour) because the same item can be sold to different customers

Then I store those results in a temporary table (MET_TmpSalesInvoicesSum)

Later, a second button uses that list of ItemId to find the related PriceCalcId and calls BOMCalcProvider::createDetails() to compute added value

Here is the current logic I use with a basic while select:

while select sum(LineAmount), sum(Qty), ItemId from invoiceTrans group by ItemId, InvoiceId where invoiceTrans.InvoiceDate >= startDate && invoiceTrans.InvoiceDate <= endDate && invoiceTrans.ItemId == 'A/PROTO48/A00' { // Insert aggregated result into tmp table }

But since InvoiceAccount is in CustInvoiceJour, I understand that I now need to join it and properly group by both ItemId and InvoiceAccount. My current approach isn't working.

Any tips or examples on how to build this query dynamically with QueryBuildDataSource would be really appreciated!

Thanks again for your help!

Was this reply helpful? Yes No
JeanB 208 on at

Like (0)

Report
Copy link

Link copied!

Hello Raj, sorry I didn't saw your response before to reply.

Here is the error message I have with your proposal :

Was this reply helpful? Yes No
Jonas "Jones" Melgaard 4,991 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Raj's proposed solution is a changed version of my select statement, but I can chip in anyway.

The issue is that you are including the table name in the field list, it should only be the field name.

I.e. while select sum(LineAmout), sum(Qty), ItemId from invoiceTrans

group by invoiceTrans.ItemId, invoiceTrans.InvoiceAccount

X++'s select syntax requires some getting used. But the documentation is great: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-ref/xpp-data/xpp-select-statement

I'll answer your other questions later today, but it's good to hear that you are interested in X++ (-:

Was this reply helpful? Yes No
Martin Dráb 238,281 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You don't necessarily have to resort to code when designing a form with grouping and aggregations. You can define an AOT query with these things and then use it as the data source of your form.

If you want code, it still doesn't mean you need to do everything from scratch. You can define data sources and join them in the Data Sources node of your form.

Then you need to get a reference to the QueryBuildDataSource object of your data source. If you do in it a method of the data source, you can simply use this.queryBuildDataSource(). If you do it elsewhere, e.g. init() of the form itself, use the automatically created variable for the data source, e.g. custInvoiceJour_ds.queryBuildDataSource().

Then use addGroupByField() method to add grouping and addSelectionField() for aggregated fields (e.g. transQbds.addSelectionField(fieldNum(CustInvoiceTrans, Qty), SelectionField::Sum)).

Note that you can use Find All References to find examples in the standard code.

Was this reply helpful? Yes No