1. Home
  2. Point-Of-Sale Software
  3. New Customers / Cashiers

New Customers / Cashiers

Our business sends out e-mail campaigns so capturing the customer e-mail address is paramount. I've created a report that shows NEW CUSTOMERS within a date range (Customer.AccountOpened) along with the customer data (address, city, state, zip ... and, of course, e-mail address). I would also like to show the CASHIER ID on the report (so I know who IS and who ISN'T getting the customer's e-mail address). It seems as though the Customer.CashierId field is NOT getting populated when a new customer record is added to the table. Is this a BUG and, if so, does someone have a "workaround" for me so that I can show the cashier id on the report?

Reply to
Luminox
Loading thread data ...

Dear Luminox,

The cashier field in the customer table in the database is populated when a cashier is set on the Customer Options tab in the Primary Cashier box.

The Cashier who assigned to the transaction is in the CashierID field in the Transaction table.

Perhaps someone more versed in SQL will provide you with a more complete solution

Marc Wagner

Reply to
Marc Wagner

Luminox,

I'm not sure what the Customer.CashierID field is used for as it's completely empty in my database. The best way to achieve what you're looking for is via a detailed sales report that contains extended customer data. I've copied a report below which I use. It shows customers who have only made a single visit and which are missing the email address. It's summarized by cashier so you can quickly see the worst offenders. Because it's based on a detailed sales report there are as many lines under each account as items purchased. That only makes the report look cluttered but still allows you to find what you're looking for. The report also works in HQ but multiplies the data by the number of store in the db. If you're not familiar which creating a report file, they are all contained in: C:\Program Files\Microsoft Retail Management System\Store Operations\Reports Just create a new file with the code below and name it something like "Memorized-Cashier skipping email address.qrp" If you are using an older version of StoreOps you may need to restart the program for the report to show up in the Memorized report list.

Lance

//--- Report Summary --- //

Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Cashier skipping email address" PageOrientation = pageorientationPortrait WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 2 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "" PreQuery2 = "" TablesQueried FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber

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

SelCriteria = "" GroupBy = "" SortOrder = "" End ReportSummary

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

Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 16 Color = "Blue" End TitleRow

Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 12 Color = "Black" End TitleRow

Begin TitleRow Text = "Generated On " Font = "Arial" FontBold = True FontSize = 10 Color = "Black" End TitleRow

//--- Filters ---//

Begin Filter FieldName = "Customer.TotalVisits" FilterOp = reportfilteropEqual FilterLoLim = "1" FilterHilim = "1" FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter

Begin Filter FieldName = "Customer.EmailAddress" FilterOp = reportfilteropLess FilterLoLim = "0" FilterHilim = "0" FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter

//--- Columns ---//

Begin Column FieldName = "Cashier.Name" DrillDownFieldName = "Cashier.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Cashier" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2205 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Customer.AccountNumber" DrillDownFieldName = "Customer.AccountNumber" DrillDownReportName = "" StoreIDFieldName = "" Title = "Account #" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1305 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Customer.EmailAddress" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Email Address" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1515 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.FirstName" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "First Name" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1005 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.LastName" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Last Name" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1125 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "[Transaction].Time" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Date Sold" VBDataType = vbDate Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.AccountOpened" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Opened" VBDataType = vbDate Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 780 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.LastVisit" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Last Visit" VBDataType = vbDate Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 915 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.TotalVisits" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Total Visits" VBDataType = vbLong Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1065 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.Company" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Company" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1050 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.Address" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Address" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2190 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.Address2" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Address 2" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1395 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Department.Name" DrillDownFieldName = "Department.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Department" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2205 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Category.Name" DrillDownFieldName = "Category.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Category" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1515 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Supplier.SupplierName" DrillDownFieldName = "Supplier.SupplierName" DrillDownReportName = "" StoreIDFieldName = "" Title = "Supplier" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 825 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.ItemLookupCode" DrillDownFieldName = "Item.ItemLookupCode" DrillDownReportName = "" StoreIDFieldName = "" Title = "Item" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1380 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.Description" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Description" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2115 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.ExtendedDescription" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Ext'd Description" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2115 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.SubDescription1" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Sub Description 1" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2115 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.SubDescription2" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Sub Description 2" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2115 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.SubDescription3" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Sub Description 3" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2115 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.Quantity" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "On Hand" VBDataType = vbDouble Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 810 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Item.Price" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Price" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 555 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Item.LastSold" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Last Sold" VBDataType = vbDate Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "TransactionEntry.Quantity" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Qty Sold" VBDataType = vbDouble Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 810 GroupMethod = groupmethodSum ColFormat = "" End Column

Begin Column FieldName = "TransactionEntry.Price" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Sold Price" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 975 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Total" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Total Sales" VBDataType = vbCurrency Formula = "TransactionEntry.Price * TransactionEntry.Quantity" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1275 GroupMethod = groupmethodSum ColFormat = "" End Column

Begin Column FieldName = "[Transaction].TransactionNumber" DrillDownFieldName = "[Transaction].TransactionNumber" DrillDownReportName = "" StoreIDFieldName = "" Title = "Transaction" VBDataType = vbLong Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1080 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "TransactionEntry.Cost" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Cost" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodSum ColFormat = "" End Column

Begin Column FieldName = "ReasonCodeDiscount.Description" DrillDownFieldName = "Reasoncode.Description" DrillDownReportName = "" StoreIDFieldName = "" Title = "Discount Reason Code" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2280 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "ReasonCodeTaxChange.Description" DrillDownFieldName = "Reasoncode.Description" DrillDownReportName = "" StoreIDFieldName = "" Title = "Tax Change Reason Code" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2280 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "ReasonCodeReturn.Description" DrillDownFieldName = "Reasoncode.Description" DrillDownReportName = "" StoreIDFieldName = "" Title = "Return Reason Code" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2280 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Customer.City" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "City" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1395 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.State" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "State" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1395 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.Zip" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Zip Code" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1395 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.PhoneNumber" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Phone #" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1365 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.FaxNumber" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Mobile #" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1110 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.TotalSales" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Total Cust. Sales" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1185 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Customer.TotalSavings" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Savings" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1035 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "CurrentDiscount" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Discount" VBDataType = vbDouble Formula = "Customer.CurrentDiscount / 100" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 855 GroupMethod = groupmethodNone ColFormat = "0.00%" End Column

Begin Column FieldName = "Customer.Notes" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Notes" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 3000 GroupMethod = groupmethodNone ColFormat = "" End Column

Reply to
Lance

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.