Skip to Content
0

Items Duplication Multi times in Report

Aug 30, 2017 at 07:19 AM

76

avatar image

Hi experts

i had created a QUERY for Invoice Print out, 
when i run the query , same item is duplicating  2 & more times 

how can i fix it

select OINV.DocEntry, OCRD.CntctPrsn AS 'Customer Contact Person',OCPR.Position AS 'Designation',OINV.CardName AS 'Customer Name',OCPR.Address as 'Address',CRD1.Street AS 'PO Box No', 
CRD1.City AS 'City',CRD1.Country as 'Country',OCRD.Fax as 'Fax',OCRD.Phone1,OCRD.Phone2,OCRD.Cellular as 'Mobile No',OCRD.E_Mail as 'Email',OCRD.IntrntSite AS 'WebSite',OINV.DocDate as 'Postng Date',
OINV.NumAtCard as 'Ref No' ,OINV.DocCur AS 'Currency',OINV.U_Creater AS 'Doc Creater/ From',INV1.LineTotal,


Case  OINV.DocType when ('S') THEN 'Service Based'
                   when ('I') Then 'Item Based'


else 'Invalid Doc Type' end AS 'Invalid Doc Type',


INV1.Dscription as 'Description',INV1.Quantity as 'Qty',INV1.U_srv_qty AS 'Service Qty',INV1.U_SerRate as'Service Unit Price',INV1.Price as 'Unit Price',OINV.DocNum as 'Doc Number', OINV.Header as 'T&C1',OINV.Footer AS'T&C2'
from OINV 
left join INV1 on OINV.DocEntry = INV1.DocEntry
LEFT JOIN OCRD ON OINV.CardCode = OCRD.CardCode
left join CRD1 on OCRD.DocEntry = OCRD.DocEntry
LEFT join OCPR on OCRD.CardCode = OCPR.CardCode


where DocNum = 100029


Regards

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Agustin Marcos Cividanes Aug 30, 2017 at 11:06 AM
0

Hi

try this:

change this

left join CRD1 on OCRD.DocEntry = OCRD.DocEntry

for this:

inner join CRD1 on CRD1.cardcode = OCRD.cardcode

And after this check your BP has only one contact person. If you have more than one, you have to add a new condition to filter. For example, you can select the contact person in the document.

Kind regards

Agustín Marcos Cividanes

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi

am having only single contact person at present for my BP

late it may be Multi, but as of now am having only single contact persons

in that BP also the same duplicate is happening

Regards

0
Anand Singh Aug 30, 2017 at 11:09 AM
0

Try Below Code:-


select OINV.DocEntry, OCRD.CntctPrsn AS 'Customer Contact Person',OCPR.Position AS 'Designation',OINV.CardName AS 'Customer Name',OCPR.Address as 'Address',CRD1.Street AS 'PO Box No', 
CRD1.City AS 'City',CRD1.Country as 'Country',OCRD.Fax as 'Fax',OCRD.Phone1,OCRD.Phone2,OCRD.Cellular as 'Mobile No',OCRD.E_Mail as 'Email',OCRD.IntrntSite AS 'WebSite',OINV.DocDate as 'Postng Date',
OINV.NumAtCard as 'Ref No' ,OINV.DocCur AS 'Currency',
OINV.U_Creater AS 'Doc Creater/ From',
INV1.LineTotal,
Case  OINV.DocType when ('S') THEN 'Service Based'
                   when ('I') Then 'Item Based'




else 'Invalid Doc Type' end AS 'Invalid Doc Type',




INV1.Dscription as 'Description',INV1.Quantity as 'Qty',
INV1.U_srv_qty AS 'Service Qty',INV1.U_SerRate as'Service Unit Price',
INV1.Price as 'Unit Price',OINV.DocNum as 'Doc Number', OINV.Header as 'T&C1',OINV.Footer AS'T&C2'
from OINV 
inner join INV1 on OINV.DocEntry = INV1.DocEntry
inner JOIN OCRD ON OINV.CardCode = OCRD.CardCode
Inner join CRD1 on OCRD.CardCode  = CRD1.CardCode and oinv.PayToCode = CRD1.Address
LEFT join OCPR on  OINV.CntctCode = OCPR.CntctCode




where  CRD1.AdresType ='B' and DocNum = 1718010022 
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi

when i run your query , am getting the result only for 2 document, for rest The data output, its blank

Regards

0
Zapata Oscar Aug 30, 2017 at 04:47 PM
0

Resultado de imagen para left join inner join

Regards

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Sir

can you provide me the Query

Regards

0

Sure P Vinod, but can you show me the repeated lines? Because your situation are these:

- Your BP has 2 or more contact person.

- Your BP has 2 or more adress.

What do you do in this situation? show you the first, anything or the last?

Regards.

0

sir

if i have more then one contact person or more than one address, i need the default contact person & address to be appear in layout

Regrds

0

Hi P Vinod, I understand that default contact person & address are in the A/R Invoice, a example whit your code is:

SELECT
OINV.DocEntry, 
OCPR.Name AS 'Customer Contact Person',
OCPR.Position AS 'Designation',
OINV.CardName AS 'Customer Name',
OCPR.Address as 'Address',
OINV.Address AS 'Full Address',
OCRD.Fax as 'Fax',
OCRD.Phone1,
OCRD.Phone2,
OCRD.Cellular as 'Mobile No',
OCRD.E_Mail as 'Email',
OCRD.IntrntSite AS 'WebSite',
OINV.DocDate as 'Postng Date',
OINV.NumAtCard as 'Ref No',
OINV.DocCur AS 'Currency',
OINV.U_Creater AS 'Doc Creater/ From',
INV1.LineTotal,
Case  OINV.DocType when ('S') THEN 'Service Based'
                   when ('I') Then 'Item Based'
else 'Invalid Doc Type' end AS 'Invalid Doc Type',
INV1.Dscription as 'Description',
INV1.Quantity as 'Qty',
INV1.U_srv_qty AS 'Service Qty',
INV1.U_SerRate as'Service Unit Price',
INV1.Price as 'Unit Price',
OINV.DocNum as 'Doc Number', 
OINV.Header as 'T&C1',
OINV.Footer AS'T&C2'
FROM OINV 
LEFT join INV1 on OINV.DocEntry = INV1.DocEntry
LEFT JOIN OCRD ON OINV.CardCode = OCRD.CardCode
LEFT join OCPR on OINV.CntctCode = OCPR.CntctCode
where DocNum = 100029
0