Skip to Content
author's profile photo Former Member
0
Former Member

Duplicate Record if they have duplicate payment in particular invoice

Posted on Dec 11, 2014 at 04:38 PM 31 Views

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

11.png (15.5 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 12, 2014 at 07:25 AM
    2

    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.


    Item to show.png (13.1 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • Abhilash Kumar Former Member
      Dec 12, 2014 at 08:49 AM

      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

    Comment on This Answer
  • author's profile photo Dell Stinnett-Christy
    Dell Stinnett-Christy
    Posted on Dec 11, 2014 at 04:50 PM
    1

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Raghavendra Hullur Former Member
      Dec 12, 2014 at 07:00 AM

      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

    Comment on This Answer
  • author's profile photo Former Member
    Former Member
    Posted on Dec 12, 2014 at 09:16 AM
    0

    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 .

    😊

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.