web
You’re offline. This is a read only version of the page.
close
Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AOT Query
Microsoft Dynamics AX (Archived)

AOT Query

(0) ShareShare
ReportReport
Posted on by Dev 429

Hi ,

I have a view with a query which has CustTrans as a datasource which in turn has CustSettlement as a datasource. 

cr.jpg

Now I want to show only the payment journal & general journal transaction (for which I have added a range where trnastype is either payment journal or general journal ) that have been settled with one or more invoice and not the ones that are settled without invoice, so how should I get only these filtered records in my view ?

Thanks!

Regards

Aman

*This post is locked for comments

I have the same question (0)
  • Dev Profile Picture
    Dev 429 on at
    RE: AOT Query

    Thanks Andre it worked :)

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 297,339 Super User 2025 Season 2 on at
    RE: AOT Query

    Hi Aman,

    You should not set the Invoice range on the "main" custtrans datasource. There should be a range on the second (CustTrans_1).

    "!null" means that you want to have all transactions except where the invoice contains the value "null". The SQL keyword is not working here.

    If you need to filter on none empty values in a string field you can use:

    !""

  • Dev Profile Picture
    Dev 429 on at
    RE: AOT Query

    dvs.jpg

    This is how my query looks right now

  • Dev Profile Picture
    Dev 429 on at
    RE: AOT Query

    Hi Andre,

    I have settled an payment journal with multiple invoices so in that case CustTrans.Invoice remains blank and the transtype is payment and not matter or sales order and also when we settled a general journal against an invoice then we get CustTrans.Invoice blank , I tried keeping invoice range with not null as you can see in the screenshot I pasted but these scenarios fail in that case. How should we approach for this?

    Regards

    Aman

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 297,339 Super User 2025 Season 2 on at
    RE: AOT Query

    Hi Aman,

    I do think we are on the same page for the scope. You can add a datasource linked to the CustSettlement also with the CustTrans, but then use the Offset relation. Then you have a range with customer transactions settled by the General journal or Payment journal. On this second Offset customer transactions datasource you can filter on the field TransType which can contain the value 'sales order' or 'project' for invoices. Also the field Invoice should have a value. Set the fetch to 1:1 to prevent having multiple records in case a payment was received for multiple invoices.

    You can use the same TransType field for General journals or payments. Then you don't have to use the GeneralJournalTrans table. The query will be faster and more reliable as it would be possible to delete posted journal lines.

    Note that customer transactions can also contain lines related to e.g. foreign currency revaluation and collection letters. You should determine which transaction types are really used and how to deal with them for your query.

  • Dev Profile Picture
    Dev 429 on at
    RE: AOT Query

    Hi Andre,

    Currently I am getting all the settled transaction of payment and general journal but I want only those transactions which are settled against one or more invoices and not the transactions without invoices that are settled.

    For e.g two payment/general journal one with credit 100 and other with debit 100 without invoices when settled against each other payment/general journal should not be shown while a payment/ general journal of credit 100  settled with a payment/general journal of debit 500 with invoice should appear.

    For showing only general journal and payment journal - this has been achieved by the ranges but to restrict only the settled transactions with one or more invoice is to be achieved.

    I hope I have been able to explain the issue to you.

    Regards

    Aman

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 297,339 Super User 2025 Season 2 on at
    RE: AOT Query

    Hi Aman,

    Do you mean you need to have only records from a journal which has a settlement related to at least one customer transaction that was not created by a general or payment journal?

    e.g. some corrections were posted via a general journal. A payment journal was settled with a transaction created by the general journal, then this should be hidden as well?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Syed Haris Shah Profile Picture

Syed Haris Shah 9

#2
Mea_ Profile Picture

Mea_ 4

#3
Martin Dráb Profile Picture

Martin Dráb 2 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans