Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / GET LAST 3 INVOICE WIT...
Microsoft Dynamics CRM (Archived)

GET LAST 3 INVOICE WITH ITEMS OF AN ACCOUNT

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Dear All,

Need help to generate following View. Im having 2 entity InvoiceHeader, InvoiceHeaderline

Details As below:

Invoice Header InvoiceHeaderLine VIEW REQUIRED
InvoiceNo Invoicedate InvoiceNo ItemNo Amount InvoiceNo Invoicedate ItemNo Amount
Inv1 01-01-2019 Inv1 Item1 100 Inv2 02-01-2019 Item1 100
Inv2 02-01-2019 Inv1 Item2 100 Inv3 03-01-2019 Item1 100
Inv3 03-01-2019 Inv2 Item1 100 Inv3 03-01-2019 Item2 100
Inv4 04-01-2019 Inv3 Item1 100 Inv3 03-01-2019 Item3 100
Inv3 Item2 100 Inv4 04-01-2019 Item1 100
Inv3 Item3 100 Inv4 04-01-2019 Item2 100
Inv4 Item1 100
Inv4 Item2 100

Query:

SELECT * FROM [DBO].INVOICEHEADERLINE IH  where InvoiceNo IN (SELECT TOP 3 InvoiceNo FROM INVOICEHEADER WHERE  CUSTACCOUNT ='C008-03418'  ORDER BY TRANSDATE DESC) 
Please provide FETCHXML for View or Any Suggestion

*This post is locked for comments

  • Suggested answer
    Kalpavruksh D365 CoE Profile Picture
    Kalpavruksh D365 CoE 2,545 on at
    RE: GET LAST 3 INVOICE WITH ITEMS OF AN ACCOUNT

    Hi Anshuman,

    Use keyword Count instead of TOP which will give you the result. If you will select <fetch count = "2".... , you will get top 3 records from the list.

    Please refer to the following link for your reference.

    crmtipoftheday.com/.../use-top-in-fetchxml

    If found useful, please mark this answer as verified.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: GET LAST 3 INVOICE WITH ITEMS OF AN ACCOUNT

    Thanks for your Reply..

    Its true Im not getting other way to achieve this.

    Im trying with this FetchXML. Returns only 3 Records of Items..

    string fetchXml = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true' top='3'>

       <entity name='acx_invoiceheader' >

           <attribute name='acx_invoiceno' />

    <attribute name='acx_transdate' />

            <attribute name='acx_name' />

            <order attribute='acx_transdate' descending='true' />

           <filter type='and'>

            <condition attribute = 'acx_salesvalue' operator= 'gt' value = '0'/>

            </filter>

           <link-entity name='account' from='accountid' to='acx_name' visible='false' link-type='outer' alias='a_667c2ecfebb0e711812cc4346bdc5e51' >

               <attribute name='name' />

           </link-entity>

            <link-entity name='acx_invoiceheaderline' from='acx_invoiceno' to='acx_invoiceheaderid' link-type='inner' alias='ab'>

            <attribute name='acx_invoiceno' />

    <attribute name='acx_itemid' />

    <attribute name='acx_netamount' />

    </link-entity>

       </entity>

    </fetch>";

    Any other help....

  • Suggested answer
    gdas Profile Picture
    gdas 50,085 on at
    RE: GET LAST 3 INVOICE WITH ITEMS OF AN ACCOUNT

    You can do only left outer join and inner join using fetch Xml.Take a look below reference-

    docs.microsoft.com/.../use-fetchxml-left-outer-join-query-records-not-in

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans