Skip to main content

Notifications

Microsoft Dynamics RMS forum

Duplicates of cash transactions

Posted on by 180

My business is currently running Microsoft Dynamics RMS version 2.0. When we create a custom report that details the sales from a particular time frame, all of the cash transactions show up in duplicate. The totals for cash purchases are always wrong, because the system counts each cash transaction twice. The only time a cash purchase shows up once, is when someone pays with exact change. I've gone through several settings menus, and cannot figure out how to fix this problem. Does anyone have any ideas?

*This post is locked for comments

  • Bee Profile Picture
    Bee 20 on at
    RE: Duplicates of cash transactions

    Hello, im back again.

    The problem is exactly what COMDARK said. I customized a report because i need to know the sales done per tender type. I joined the TenderEntry table, so i have duplicates of cash sales in my report.  i noticed also that the sales where the customer gave the exact amount was not duplicated.. this is the report i customized

    Begin ReportSummary

      ReportType = reporttypeSales

      ReportTitle = "Tender Summary Report"

      PageOrientation = pageorientationLandscape

      OutLineMode = True

      Groups = 0

      GroupDescription = ""

      DisplayLogo = True

      LogoFileName = "MyLogo.bmp"

      ProcedureCall = ""

      TablesQueried = <BEGIN> SELECT DISTINCT TransactionEntry.ID

         FROM     TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber and TransactionEntry.ItemType <> 9

            INNER JOIN  Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber

            LEFT JOIN   Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID

            LEFT JOIN   Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID

            LEFT JOIN   Category WITH(NOLOCK) ON Item.CategoryID = Category.ID

            LEFT JOIN   Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID

            LEFT JOIN   ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID

            LEFT JOIN   ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID

            LEFT JOIN   ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID

            LEFT JOIN Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID

            LEFT JOIN   Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.ID

            LEFT JOIN   Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID

    LEFT JOIN   QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityDiscountID = QuantityDiscount.ID

    LEFT JOIN TenderEntry WITH(NOLOCK) ON TenderEntry.TransactionNumber = [Transaction].TransactionNumber

    <END>

      SelCriteria = ""

      GroupBy = ""

      SortOrder = ""

    End ReportSummary

    //--- Title Rows ---//

    Begin TitleRow

      Text = "<Store Name>"

      Font = "Arial"

      FontBold = True

      FontSize = 16

      Color = "Blue"

    End TitleRow

    Begin TitleRow

      Text = "<Report Title>"

      Font = "Arial"

      FontBold = True

      FontSize = 12

      Color = "Black"

    End TitleRow

    Begin TitleRow

      Text = "Generated On <Report Date>"

      Font = "Arial"

      FontBold = True

      FontSize = 10

      Color = "Black"

    End TitleRow

    //--- Filters ---//

    Begin Filter

      FieldName = "[Transaction].Time"

      FilterOp = reportfilteropBetween

      FilterLoLim = "<Today>"

      FilterHilim = "<Today>"

    End Filter

    Begin Filter

      FieldName = "Cashier.inactive"

      FilterOp = reportfilteropEqual

      FilterLoLim = "0"

      FilterHilim = "0"

    End Filter

    //--- Columns ---//

    Begin Column

      FieldName = "TenderEntry.Description"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Tender"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 2000

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftTop

    End Column

    Begin Column

      FieldName = "Sales"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Amount"

      VBDataType = vbCurrency

      Formula = "(TransactionEntry.Price * TransactionEntry.Quantity) + TransactionEntry.SalesTax"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1290

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Total"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Sales Amount"

      VBDataType = vbCurrency

      Formula = "(TransactionEntry.Price * TransactionEntry.Quantity) + TransactionEntry.SalesTax"

      ColHidden = TRUE

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1275

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Customer.AccountNumber"

      DrillDownFieldName = "Customer.AccountNumber"

      DrillDownReportName = ""

      Title = "Customer Acc. No"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1500

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "CustomerName"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Customer"

      VBDataType = vbString

      Formula = "Customer.LastName + ', ' + Customer.FirstName"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1350

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "[Transaction].Time"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Date"

      VBDataType = VbDate

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1500

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftTop

    End Column

    Begin Column

      FieldName = "TransactionEntry.ID"

      DrillDownFieldName = "TransactionEntry.ID"

      DrillDownReportName = ""

      Title = "Transaction ID"

      VBDataType = vbLong

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1245

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "[Transaction].TransactionNumber"

      DrillDownFieldName = "[Transaction].TransactionNumber"

      DrillDownReportName = ""

      Title = "Transaction"

      VBDataType = vbLong

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1245

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Of late, the report shows a duplicate for all the entries. So take for instance, a sale of $100 would be shown as Cash, Credit and Cheque when it was clearly just one of them. Maybe this is a virus on my clients system tho.

    But please could you please help in telling me what to do to remove the duplicate cash reports when the customer does not give the exact change?

    thank you

  • archelle16 Profile Picture
    archelle16 1,735 on at
    RE: Duplicates of cash transactions

    just to clarify the above, "SOME transactions has two tender entries. :P This happens when customer dont pay exact amount. get it? thnks.

  • archelle16 Profile Picture
    archelle16 1,735 on at
    RE: Duplicates of cash transactions

    considering the type of report mentioned above, store report differs from hq report. Each transaction has two tender entries. The amount given by the customer and the total amount to be paid and so theres two.

    Include "Select Distinct Transaction.TransactionNumber" in your select query to omit the duplicates.

    Let me know if this helps. tnx.

  • Suggested answer
    archelle16 Profile Picture
    archelle16 1,735 on at
    RE: Duplicates of cash transactions

    hi, can you send me the copy of your report so that i may know know what the problem is?  I guess this yields because of improper joining of your tables. And by the way, is this Headquarters or store report?

  • Bee Profile Picture
    Bee 20 on at
    RE: Duplicates of cash transactions

    hello..please can you help me with this?

    i am creating a report and i need Tender Type, Amount, Customer Account number, Customer, Date, Transaction Number and Transaction ID.

    I have succesfully created the report, but the rows are duplicate. I realize that each transaction ID is unique. Is there a way i can delete from the report the duplicate rows? im wondering if there is a way to say delete from report where the count of  Transaction ID > 1. or somthing like it should only display unique Transaction ID's. this way, i believe the report wont be duplicated.

    Can you please help me out urgently?

  • comdark Profile Picture
    comdark 180 on at
    Re: Duplicates of cash transactions

    I tried deleting the tender type column from the report, but the cash transactions still show up twice.

  • comdark Profile Picture
    comdark 180 on at
    Re: Duplicates of cash transactions

    We need to have the report separated by tender type. Is there any way to keep that, while eliminating the duplicates?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Duplicates of cash transactions

    This is what I'm saying, u have duplicates in ur report coz u r joining the 2 transaction tables with the TenderEntry table, so if you can live without the tender type, then ur report will be fine with no duplicates

  • comdark Profile Picture
    comdark 180 on at
    Re: Duplicates of cash transactions

    No, I already have the tender type in the report. My problem is that all cash transactions show up twice. For example, if an item is bought for $12.96, that amount is listed twice on our sales report, when it should only be on there once. I then have to go through the report and subtract all the cash transactions that are doubles.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Duplicates of cash transactions

    It seems u r joining the TenderEntry table, so what do u really need from the TenderEntry? Do u need the tender type in your report?

    A Hassan

    RMS Leaders

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,802 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,129 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans