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
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
just to clarify the above, "SOME transactions has two tender entries. :P This happens when customer dont pay exact amount. get it? thnks.
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.
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?
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?
I tried deleting the tender type column from the report, but the cash transactions still show up twice.
We need to have the report separated by tender type. Is there any way to keep that, while eliminating the duplicates?
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
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.
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,802 Super User 2024 Season 2
Martin Dráb 229,129 Most Valuable Professional
nmaenpaa 101,154