GET LAST 3 INVOICE WITH ITEMS OF AN ACCOUNT

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

gdas 50,091 Moderator on at

Like (1)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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....

Was this reply helpful? Yes No
Suggested answer

Kalpavruksh D365 CoE 2,545 on at

Like (1)

Report

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.

Was this reply helpful? Yes No