I just was wondering if anybody is using DMF (Data Flow Manager) from Atandra to extract POS data from RMS and could provide me some help setting this up.
I am looking for creating a permanent view in MSDE that allows me to use ODBC to extract my daily sales details by batch number. I currently use a customized sales report to extract the data I need into Excel, then "massage" the data there to create an iif file for QB import. I want to streamline this process by accessing the SQL view by batch number, get the same fields from the report via ODBC and then directly import it onto QB.
Any help/comment would be greatly appreciated.
//--- Report Summary --- //
Begin ReportSummary ReportType = reporttypeSales ReportTitle = "QB Daily Sales Extract" PageOrientation = pageorientationLandscape OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = False LogoFileName = "MyLogo.bmp" ProcedureCall = "" 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 Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID
SelCriteria = "" GroupBy = "[Transaction].BatchNumber, Department.Name, Category.Name, Item.ItemLookupCode, Item.Description, Item.Price, Item.Quantity, Item.Cost, Batch.ClosingTime, Batch.OpeningTime, Item.TaxId, Item.ReorderPoint, Item.Quantity, Item.ItemType, Item.Inactive, Item.RestockLevel, Register.Number, Supplier.SupplierName" SortOrder = "" End ReportSummary
//--- Title Rows ---//
Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 16 Color = "Blue" End TitleRow
Begin TitleRow Text = "QB Interface " Font = "Arial" FontBold = True FontSize = 12 Color = "Black" End TitleRow
//--- Filters ---//
Begin Filter FieldName = "[Transaction].BatchNumber" FilterOp = reportfilteropEqual FilterLoLim = "311" FilterHilim = "311" End Filter
//--- Columns ---//
Begin Column FieldName = "[Transaction].BatchNumber" DrillDownFieldName = "[Transaction].BatchNumber" DrillDownReportName = "" Title = "Batch" VBDataType = vbLong Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 800 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Department.Name" DrillDownFieldName = "Department.Name" DrillDownReportName = "" Title = "Department" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1300 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Category.Name" DrillDownFieldName = "Category.Name" DrillDownReportName = "" Title = "Category" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1300 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Item.ItemLookupCode" DrillDownFieldName = "Item.ItemLookupCode" DrillDownReportName = "" Title = "Item" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1300 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Item.Description" DrillDownFieldName = "" DrillDownReportName = "" Title = "Description" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1700 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "TransactionEntry.Quantity" DrillDownFieldName = "" DrillDownReportName = "" Title = "Qty Sold" VBDataType = vbDouble Formula = "SUM(TransactionEntry.Quantity)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 810 GroupMethod = groupmethodSum ColFormat = "##########" End Column
Begin Column FieldName = "Item.Price" DrillDownFieldName = "" DrillDownReportName = "" Title = "Price" VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "GrossTotal" DrillDownFieldName = "" DrillDownReportName = "" Title = "Gross" VBDataType = vbCurrency Formula = "SUM(Item.Price * TransactionEntry.Quantity)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1100 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "TotalDiscount" DrillDownFieldName = "" DrillDownReportName = "" Title = "Discount" VBDataType = vbCurrency Formula = "SUM((Item.Price - TransactionEntry.Price) * TransactionEntry.Quantity)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodsum ColFormat = "" End Column
Begin Column FieldName = "NetTotal" DrillDownFieldName = "" DrillDownReportName = "" Title = "Net" VBDataType = vbCurrency Formula = "SUM(TransactionEntry.Price * TransactionEntry.Quantity)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1100 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "TransactionEntry.salestax" DrillDownFieldName = "" DrillDownReportName = "" Title = "Tax" VBDataType = vbcurrency Formula = "SUM(TransactionEntry.salestax)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodsum ColFormat = "" End Column
Begin Column FieldName = "Total" DrillDownFieldName = "" DrillDownReportName = "" Title = "Total Sales" VBDataType = vbCurrency Formula = "SUM((TransactionEntry.Price * TransactionEntry.Quantity) + TransactionEntry.salestax)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1100 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "Batch.OpeningTime" DrillDownFieldName = "Batch.OpeningTime" DrillDownReportName = "" Title = "Batch Date" VBDataType = vbLong Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1200 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Register.Number" DrillDownFieldName = "" DrillDownReportName = "" Title = "Register" VBDataType = vbLong Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Supplier.SupplierName" DrillDownFieldName = "" DrillDownReportName = "" Title = "Supplier" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1700 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Item.TaxID" DrillDownFieldName = "" DrillDownReportName = "" Title = "TaxID" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.ItemType" DrillDownFieldName = "" DrillDownReportName = "" Title = "ItemType" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.ReorderPoint" DrillDownFieldName = "" DrillDownReportName = "" Title = "Reorder Pt." VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.RestockLevel" DrillDownFieldName = "" DrillDownReportName = "" Title = "Restock Level." VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.Quantity" DrillDownFieldName = "" DrillDownReportName = "" Title = "On-Hand" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1000 GroupMethod = groupmethodSum ColFormat = "#.##" End Column
Begin Column FieldName = "Item.Price" DrillDownFieldName = "" DrillDownReportName = "" Title = "Price" VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.Cost" DrillDownFieldName = "" DrillDownReportName = "" Title = "Cost" VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.Inactive" DrillDownFieldName = "" DrillDownReportName = "" Title = "Inactive" VBDataType = vbBoolean Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1057 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignCenterCenter End Column