while select, join and group by query

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hi,

I want combine two table by using join and group by query in AX. I am specifying my requirement below -

while select _GeneralJournalAccountEntry
group by _GeneralJournalAccountEntry.GeneralJournalEntry
where _GeneralJournalAccountEntry.PaymentReference == ‘01371885’
{
while select _GeneralJournalEntry
group by _GeneralJournalEntry.SubledgerVoucher
where _GeneralJournalEntry.RecId == _GeneralJournalAccountEntry.GeneralJournalEntry
{
info(strFmt("%1",_GeneralJournalEntry.SubledgerVoucher));
}
}

Can some one help me ?

2

What about something like this?

GeneralJournalEntry        journalEntry;
GeneralJournalAccountEntry accountEntry;
    
while select journalEntry
    group by SubledgerVoucher
    exists join accountEntry
        where accountEntry.GeneralJournalEntry == journalEntry.RecId
           && accountEntry.PaymentReference == '01371885'
{
    info(journalEntry.SubledgerVoucher);
}

By the way, please ask questions about development in the developer forum, not the technical forum.

3

Thank you very much Martin

4

If it answered your question, please don’t forget to mark the thread as resolved (with “This helped me” below the helpful reply).

5

Hi Martin,
I have done below code , but it’s giving repetitive records. How to avoid?

GeneralJournalEntry journalEntry;
GeneralJournalAccountEntry accountEntry;
RetailTransactionTable _RetailTransactionTable;

while select _RetailTransactionTable where _RetailTransactionTable.salesOrderId == ‘01371885’
{
while select journalEntry group by SubledgerVoucher exists join accountEntry
where accountEntry.GeneralJournalEntry == journalEntry.RecId
&& accountEntry.PaymentReference == _RetailTransactionTable.salesOrderId
{
info(strFmt("%1 = %2",_RetailTransactionTable.salesOrderId,journalEntry.SubledgerVoucher));
}
}

6

Yes, your code can return the same voucher several times, and it’s pretty inefficient. Why exactly do you ignore my code, especially if you said it’s a solution?
My code group by SubledgerVoucher, so it doesn’t have the same problem as yours.

7

Hi Martin,
My requirement is I am filtering data on form by using multiple sales order id. Then I need to check multiple ledger vouchers posted against respective sales orders. How to do it?

8

Which part of the requirement is the problem that you need some assistance with? For example, do you know how to filter data in a form?

Is the last question still on the topic of this thread (while select, join and group by query)? It seems to me it’s not, so it doesn’t belong here. Let’s keep this thread about GROUP BY and such things, if you still don’t understand that, and let’s discuss your other questions (such as filtering forms) elsewhere.