Dynamics Community Forum Thread Details

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

Quick Links

Leaderboard