Duplicate Record if they have duplicate payment in particular invoice

[Former Member]

Hi Experts,

i'm facing a challenge to call the latest incoming payment records in my reports if the customer do the payment more than one in one invoices.

how i can only show the only lastest incoming payment record which close the entire invoices?

Below will be my query.

SELECT "OOCR"."OcrCode", "OOCR"."OcrName", "OINV"."DocNum", "INV1"."ItemCode", "OITM"."ItemName", "INV1"."OcrCode2", "INV1"."LineTotal", "INV1"."OcrCode", "INV1"."GPTtlBasPr", "OINV"."DocDate", "INV1"."U_SE2", "INV1"."U_SE2Costs", "INV1"."U_SE2Amount", "OINV"."DocStatus", "OINV"."DiscPrcnt", "OINV"."DocEntry", "ORCT"."DocEntry", "ORCT"."DocDate"

FROM   (((("DATABSE"."dbo"."INV1" "INV1" INNER JOIN "ITS_LIVE_TEST"."dbo"."OITM" "OITM" ON ("INV1"."TreeType"="OITM"."TreeType") AND ("INV1"."ItemCode"="OITM"."ItemCode")) INNER JOIN "ITS_LIVE_TEST"."dbo"."OOCR" "OOCR" ON "INV1"."OcrCode"="OOCR"."OcrCode") INNER JOIN "ITS_LIVE_TEST"."dbo"."OINV" "OINV" ON "INV1"."DocEntry"="OINV"."DocEntry") INNER JOIN "ITS_LIVE_TEST"."dbo"."RCT2" "RCT2" ON "OINV"."DocEntry"="RCT2"."DocEntry") INNER JOIN "ITS_LIVE_TEST"."dbo"."ORCT" "ORCT" ON "RCT2"."DocNum"="ORCT"."DocNum"

WHERE  ("ORCT"."DocDate">={ts 'Date'} AND "ORCT"."DocDate"<{ts '2015-01-01 00:00:00'}) AND ("OOCR"."OcrCode"=N' ') AND ("OINV"."DocStatus"='C')

appreciate all the great help

[Former Member · Accepted solution]

Hi Raghavendra,

if follow the Dell's steps i'm just able to see the single line information under my reports detail section. which mean i cant see all the remain lines items.

Under my detail report section, i have call all line detail of the individual invoices (INV1 table).

Maybe i have suppress the wrong section? or i need to apply any formula under the suppress field? it will possible that i suppress on the PayNo fields, just list the biggest PayNo transaction number?

Thanks.

[raghavendra_hullur · Accepted solution]

Hi,

hmm, yes I think a point is missed.

Can you provide the details of fields used for groups?

Do you have a group on PayNo field? If not, include one.

Then move the details from detail section to the newly created group header while retaining the descending sorting on DocDate.

Suppress the detail section.

Let me know the output.

Thanks,

Raghavendra

[Former Member · Accepted solution]

Hi Raghavendra,

FYI, i have group the report by invoice transaction no.

While, i have follow your advice:

1- create a group on PayNo Fields and move all the detail section to new group header and suppress the all the detail section fields.

here the results

while, the actual result which i need will be:

i have try to suppress the PayNo and all the transaction will not show under the reports.

[abhilash_kumar · Accepted solution]

Hi Ben,

It appears to me that you're trying to show 'DocEntry' rows that have the latest 'DocDate', is that correct?

Now that you have a Group on the the Transaction Number, which I suppose is '13060063', go to the Report Option on the top > Selection Formulas > Group and type in this code:

{DocDate} = Maximum({DocDate}, {TransactionNumber_group_field})

Replace {TransactionNumber_group_field} with the right field you've used as the first group.

-Abhilash

[Former Member]

Hi Abhilash,

Yup.you are right!!!

finally i'm able achieve the results which i wish for it. apprecaite your great help.

thank you so much .

[DellSC]

The easiest way to do this in the report itself (instead of in the query) is to do something like the following:

1.  Add a descending sort on the DocDate field.  This will then sort the most recent date to the top inside the inner-most group.

2.  Put the data in a group header section instead of details.  Suppress the details section.

This doesn't work well if you have to do any summaries (sum, count, etc) using the detail data, but there are ways around that too.

-Dell

[Former Member]

Hi Dell,

Thanks for your reply.

i have follow your advice but it seen the suppress did not work out.

have any others way? as i did do few summaries in the reports that i work for.

thanks.

[raghavendra_hullur]

Hi,

Is it related to suppress on Details section as suggested by Dell?

Do you have any suppress condition formula for details section ? If so, comment that and check again.

Thanks,

Raghavendra

[Former Member]

Hi Raghavendra,

Thanks for advice.

What i have do will be:

1.  Add a descending sort on the DocDate field

2. Follow by supress the docdate and pull the docdate under group header

i need to list all the line item of individual invoices.

is that a way to check that if the particular invoice have two docentry and only show the last docentry which close the entire invoices?

Thanks again for your great help.

[raghavendra_hullur]

Hi,

Yes, if you have followed Dell's steps to sort the DocDate descending and add it to the inner most group header, only the latest line item details should be displayed.

But, your response Dell was that you are not seeing the suppression working.

Can you elaborate more on the issue whether you are not getting the correct data or the detail section is not getting suppressed?

Also provide information on groups currently used in the report.

Thanks,

Raghavendra