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)

Sort by

Suggested answer

gdas 50,091 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

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
a
(0)

Report
Copy link

Link copied!

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
a
(1)

Report
Copy link

Link copied!

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