Margin Master Handbook
Introduction
Part I · Installing
- Prerequisites
- Firewall and Network Requirements
- Microsoft Edge Download Block
- SQL Server Authentication Setup for IT Administrators
- Antivirus and Endpoint Security Exclusions
- SQL Server Tools
- Install or Upgrade SQL Server Express
- Install or Upgrade SQL Server Management Studio
Part II · Introducing the Main Window
- A Tour of the Main Window
- The Menus
- The Button Row
- The Main Data Grid
- The Summary Section
- Banners and Badges
Part III · Initial Configuration
- The Options Window
- Store Configuration
- Miscellaneous
- Export Options
- Vendor Settings
- POS / Connections Tabs
- Background Service
- AI Assistant (Experimental)
- How POS Import Works
- Supported Point-of-Sale Systems
- Data Import Workflows
- RockSolid POS Import
- Epicor Eagle FTP Import
- Epicor MySQL Compass Import
- Transact POS Import
- Paladin POS Import
- Falcon POS Import
- Spruce POS Import
- Bistrack POS Import
- RockSolid Max POS Import
- ECS POS Import
- Prosperity POS Import
- Catalyst POS Import
- Westlake POS Import
- MI9 POS Import
- AS/400 POS Import
- CounterWorks POS Import
- Dimension POS Import
- PacSoft POS Import
- ProStix POS Import
- Sympac POS Import
- Propello POS Import
- EagleVision POS Import
- MySQL/Compass Connection Configuration
- ECI VPN Requirement (Spruce, RockSolid MAX)
- Propello POS Integration Guide
- MI9 Data Pipeline — Import to Main Table
- Troubleshooting: Epicor Import Brought In 0 SKUs
- PACE POS Import
- Advantage POS Import
- General Store POS Import
- J-3 POS Import
- KeyStroke POS Import
- AutBusSystem POS Import
- Tomax POS Import
- Enterprise POS Import
- Computakey POS Import
- Redisell POS Import
- DART POS Import
- MIB POS Import
- Substruct POS Import
- DMAS POS Import
- RODS POS Import
- Procom POS Import
- Agility POS Import
- Acumen POS Import
- Cruise POS Import
- LSandE POS Import
- Retalix POS Import
- DIBCorp POS Import
- GMROI POS Import
- ECi Advantage POS Import
- Sunray POS Import
- ActivantAutomotive POS Import
- IBS POS Import
- Versys POS Import
- RMS POS Import
- TAMS POS Import
- Integrasoft POS Import
- Dynamic POS Import
- Microsoft ARS POS Import
- CDS POS Import
- Spartan POS Import
- Nitterhouse POS Import
- Jeds POS Import
- Theisens POS Import
- Emery Jensen POS Import
- SMS Pro POS Import
- Burdens POS Import
- Intact POS Import
- Bloom Retail POS Import
- NCR Counterpoint POS Import
- Horizon POS Import
- Cloud Vendor Data Sync
- Data Aging and Freshness Warnings
- Resetting Vendor and POS Data
- Rebuild Data and Rebuild Selection Lists
- Troubleshooting Vendor Sync
Part IV · Customizing the Workspace
Part V · Basic Application Functionality
Part VI · Learning Margin Master
- Understanding the Strategy Hierarchy
- Pricing Strategies
- Creating a Strategy Step
- Updating and Deleting Steps
- Reviewing a Strategy and Running It
- Shared (Premade) Strategies
- SKU-Level Exceptions
- Manage Custom Groups
- Strategy Execution Cloud Tracking
Part VII · Advanced Topics
- Importing from Excel
- Cost Break Analysis
- Cost Break Strategies
- Min/Max Strategies
- Strategy Backup & Restore
- Add Items from Catalog
- Store Grouping
Part VIII · Updates, Troubleshooting & Help
- Data Diagnostics and Missing Index Recommendations
- System Diagnostics
- Margin Master Cannot Save Settings
- SQL Server 2025 Express vs. Full License
- Support Issue Management
- Catalog Lookup
- What's New After an Update
- Version History
- Documentation and the Help Buttons
Appendix
- CTLD — Do It Best Catalog File
- MARGIN_MASTER — Ace Catalog File
- PCDITEMXREF — Do It Best SKU Classification File
- SAP_ZONE_PRICE_MARGIN_MASTER — Ace Zone Pricing File
- PCDPRODCLASS — Do It Best Product Classification Hierarchy File
- SAP_STORE_DEPT_ZONE_MARGIN_MASTER — Ace Store Zone Assignment File
- Taxonomy — Ace Product Classification File
- Mapp_Pricing — Ace MAP & IMAP Pricing File
- margin_mstr_plano — Ace Planogram File
On This Page
AppendixUpdated 2026-03-24
CTLD — Do It Best Catalog File
What is this file?
The CTLD file is the primary Do It Best catalog file. It is a fixed-width record format containing the complete product catalog for a single RSC (Retail Service Center), including product details, costs at multiple membership tiers (Classic, Vision, PAR), retail pricing, margins, and operational flags. This is the most field-rich import file in the pipeline with over 50 fields per record.
File Details
| Property | Value |
|---|---|
| File pattern | CTLD*.out |
| Format | Fixed-width (450 characters per record) |
| Vendor | Do It Best |
| Frequency | Daily |
| Cloud table | DIBCatalogRecords |
Raw File Fields
Identification
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 0 | 3 | RecordID | string | (not mapped) | Record type identifier |
| 3 | 6 | MemberNum | string | (not mapped) | Member number |
| 9 | 2 | RSC | string | RSCNum | Retail service center |
| 11 | 6 | Sku | string | SKUNum | Do It Best SKU |
| 17 | 9 | PersonalSku | string | (not mapped) | Personal SKU variant |
| 26 | 14 | GTIN | string | ProductCodeGTIN | Global Trade Item Number |
| 40 | 24 | MfgPartNum | string | ManufacturerPart | Manufacturer part number |
Dates
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 64 | 14 | CreateTimeStamp | datetime | CreateTimeStamp | Record creation timestamp |
| 78 | 8 | LoadDate | datetime | LoadDate | File load date (YYYYMMDD) |
Product Attributes
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 86 | 30 | Description | string | Description | Product description |
| 116 | 4 | VendorNum | string | VendorNum | Vendor number |
| 120 | 20 | VendorName | string | VendorName | Vendor name |
| 140 | 2 | Department | string | Department | Department code |
| 142 | 4 | FineLineClassCode | string | FineLineClassCode | Fine line class |
| 146 | 6 | CatalogPage | string | CatalogPage | Catalog page reference |
| 395 | 4 | ProductClassificationDept | string | ProductClassificationDept | Classification department |
| 399 | 4 | ProductGroup | string | ProductGroup | Product group |
Multipliers
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 152 | 5 | Mult3 | int | Mult3 | Multiplier 3 |
| 157 | 5 | Mult2 | int | Mult2 | Multiplier 2 |
| 162 | 5 | Mult1 | int | Mult1 | Multiplier 1 |
| 167 | 1 | CaseMultCode | int | CaseMultCode | Case multiplier code |
| 168 | 1 | MbrCostoption | string | MbrCostoption | Member cost option |
Costs (divided by 1000)
All cost fields are stored in the file as integers that must be divided by 1000 to get the dollar amount.
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 169 | 8 | Classic1Cost | money | Classic1Cost | Classic tier 1 cost |
| 177 | 8 | Classic2Cost | money | Classic2Cost | Classic tier 2 cost |
| 185 | 8 | Classic3Cost | money | Classic3Cost | Classic tier 3 cost |
| 193 | 8 | Vision3Cost | money | Vision3Cost | Vision tier 3 cost |
| 201 | 8 | Vision2Cost | money | Vision2Cost | Vision tier 2 cost |
| 209 | 8 | Vision1Cost | money | Vision1Cost | Vision tier 1 cost |
| 217 | 8 | PAR3Cost | money | PAR3Cost | PAR tier 3 cost |
| 225 | 8 | PAR2Cost | money | PAR2Cost | PAR tier 2 cost |
| 233 | 8 | PAR1Cost | money | PAR1Cost | PAR tier 1 cost |
| 241 | 8 | DropShipCost | money | DropShipCost | Drop ship cost |
Pricing (divided by 100)
Pricing fields are stored as integers divided by 100.
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 249 | 7 | RetailMember | money | RetailMember | Retail member price |
| 256 | 7 | SuggestedRetail | money | SuggestedRetail | Suggested retail price |
| 273 | 7 | SuggAuxRetail | money | SuggAuxRetail | Per-unit auxiliary retail price |
Unit of Measure
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 263 | 2 | Unit | string | Unit | Unit of measure |
| 265 | 1 | IMCSAuxRetailSwitch | string | IMCSAuxSwithc | Auxiliary retail switch flag |
| 266 | 2 | AuxUnit | string | AuxUnit | Auxiliary unit of measure |
| 268 | 5 | AuxQty | int | AuxQty | Auxiliary quantity per package |
Margins (divided by 10)
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 280 | 4 | GrossMargin | money | GrossMargin | Gross margin (÷100) |
| 284 | 3 | SuggAuxMargin | money | SuggAuxMargin | Suggested auxiliary margin |
| 287 | 3 | MCSMarginCode | string | MCSMarginCode | MCS margin code |
| 290 | 3 | SuggClassicMargin | money | SuggClassicMargin | Suggested Classic margin |
| 293 | 3 | SuggVisionMargin | money | SuggVisionMargin | Suggested Vision margin |
| 296 | 3 | SuggPARMargin | money | SuggPARMargin | Suggested PAR margin |
Physical Attributes
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 299 | 7 | UnitCube | decimal | UnitCube | Unit cube (÷10000) |
| 306 | 7 | UnitWeight | decimal | UnitWeight | Unit weight (÷1000) |
Operational Flags
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 313 | 6 | SubstituteItem | string | SubstituteItem | Substitute SKU |
| 319 | 6 | Substitute2 | string | SubstituteItem2 | Second substitute SKU |
| 325 | 1 | ItemType | string | ItemTypeSwitch | Item type switch |
| 326 | 1 | NewItemIndicator | string | NewItemIndicator | New item flag |
| 327 | 1 | StatusCode | string | StatusCode | Item status code |
| 328 | 1 | PromoStatusCode | string | PromoStatusCode | Promotional status |
| 329 | 1 | KeyItemCode | string | KeyItemCode | Key item flag |
| 330 | 1 | IMCSDBLVCode | string | IMCSDBLCCode | IMCS DBLC code |
| 331 | 1 | GTINBarCodePkgSwitch | string | GTINBarCodePkgSwitch | Barcode package switch |
| 332 | 3 | CountryOfOrigin | string | CountryOfOrigin | Country of origin |
| 335 | 1 | ReturnGoodsCode | string | ReturnGoodsCode | Return goods code |
| 336 | 5 | IMCSOrderPoint | int | IMCSOrderPoint | IMCS order point |
| 341 | 5 | IMCSOrderQty | int | IMCSOrderQty | IMCS order quantity |
| 346 | 1 | MbrSlowMovingItem | string | MbrSlowMovingItem | Slow-moving item flag |
| 347 | 1 | UPSSwitch | string | UPSSwitch | UPS shipping switch |
| 348 | 1 | CentralStockSwitch | string | CentralStockSwitch | Central stock flag |
| 349 | 2 | CSPrimaryRSC | string | CSPrimaryRSC | Central stock primary RSC |
| 351 | 2 | CSSecondaryRSC | string | CSSecondaryRSC | Central stock secondary RSC |
| 353 | 7 | ExceptionQty | int | ExceptionQty | Exception quantity |
| 360 | 7 | MaximumQty | int | MaximumQty | Maximum quantity |
| 367 | 1 | MaxQtySwitch | string | MaxQtySwitch | Maximum quantity switch |
Private Label & Misc
| Position | Length | Raw Field | Data Type | Cloud Column | Description |
|---|---|---|---|---|---|
| 368 | 1 | PrivateLabel | string | PrivateLabel | Private label flag |
| 369 | 25 | PrivateLabelDesc | string | PrivateLabelDesc | Private label description |
| 394 | 1 | PriceCode | string | PriceCode | Price code |
| 403 | 1 | DIB404 | string | DIB404 | Reserved field |
| 404 | 1 | DIB405 | string | DIB405 | Reserved field |
MMVendorData View Mapping
The MMVendorData view transforms DIBCatalogRecords into a unified vendor format. Key transformations:
| Cloud Column | View Column | Transformation |
|---|---|---|
| SuggestedRetail | Retail | Cast to money |
| (rounding scheme) | Level1 | Rounded price from DIBPriceLevelRoundingDetails (scheme ID + 1) |
| (rounding scheme) | Level2 | Rounded price from DIBPriceLevelRoundingDetails (scheme ID + 2) |
| (derived) | Level3 | 1st rounded price above SuggestedRetail |
| (derived) | Level4 | 2nd rounded price above SuggestedRetail |
| (derived) | Level5 | 3rd rounded price above SuggestedRetail |
| Classic3Cost, Classic2Cost, Classic1Cost | Cost | MAX of the three Classic costs |
| Department | Dept | Left 10 chars |
| FineLineClassCode | Class | Left 10 chars |
| ProductGroup | ProductGroup | Left 5 chars |
| VendorNum | VendorNumber | Left 10 chars |
| VendorName | VendorName | Left 50 chars |
| PriceCode | PricingStrategy | First char; NULL defaults to X |
| ManufacturerPart | MfgNumber | Direct |
| SuggAuxRetail | SuggAuxRetail | Cast to money |
| AuxQty | AuxQty | Cast to int |
| SuggClassicMargin | DIB_SuggClassicMargin | Cast to numeric(3,1) |
| SuggVisionMargin | DIB_SuggVisionMargin | Cast to numeric(3,1) |
| SuggPARMargin | DIB_SuggPARMargin | Cast to numeric(3,1) |
| Classic3Cost | DIB_Classic3Cost | Cast to money |
| Vision1Cost | DIB_Vision1Cost | Cast to money |
| Classic1Cost | DIB_PAR1Cost | Cast to money |
| SuggestedRetail | DIB_SuggestedRetail | Cast to money |
| SubstituteItem | DIB_SubstituteItem | Direct |
| SubstituteItem2 | DIB_SubstituteItem2 | Direct |
| StatusCode | DIB_StatusCode | Direct |
| Mult1 | DIB_Mult1 | Direct |
| Mult2 | DIB_Mult2 | Direct |
| Mult3 | DIB_Mult3 | Direct |
| (taxonomy view) | DIB_Taxonomy1–4 | From DIB_Sku_4ClassificationLevel view |
| (taxonomy view) | DIB_Taxonomy1Desc–4Desc | From DIB_Sku_4ClassificationLevel view |
| CreateDate | CreateDate | Cast to date |
| LastUpdate | LastUpdate | Cast to date |
| LastPriceChange | LastPriceChange | Cast to date; invalid dates default to 2000-01-01 |
View filter: Only records where
DeleteDate IS NULL,SuggestedRetail IS NOT NULL, andSuggestedRetail > 0.
Main Table Mapping
The UpdatemainTableFromMMVendorData.sql script updates existing SKUs. The SQLOtherMainTableFromMMVendorData.sql script inserts new SKUs. Both use the MMVendorData view as their source.
UOM Adjustment
Before applying prices, the script detects whether the store's POS sells per-unit (e.g., per foot) or per-package (e.g., per roll). It compares the current POS price to SuggAuxRetail (per-unit) vs Retail (per-package). Whichever is closer determines the adjustment ratio R:
- If
SuggAuxRetail > 0andCurrentPriceis closer toSuggAuxRetailthanRetail: R = SuggAuxRetail / Retail - Otherwise: R = 1.0 (no adjustment)
All price and cost fields marked with (×R) below are multiplied by this ratio.
Field Mapping
| View Column | Main Table Column | Transformation |
|---|---|---|
| Dept | Dept2 | Direct |
| Class | Class2 | Direct |
| ProductGroup | ProductGroup2 | Direct |
| Retail | Retail | ×R |
| Level1 | Level1 | ×R |
| Level2 | Level2 | ×R |
| Ace_MSURetail | Level3 | If Ace_MSURetail > 0, use it; otherwise 0 (Ace-only; always 0 for DIB) |
| Level4 | Level4 | ×R |
| Level5 | Level5 | ×R |
| SuggAuxRetail | Level5Zone | If SuggAuxRetail > 0, use SuggAuxRetail; otherwise Level5 × R |
| Cost | Cost2 | ×R |
| DIB_Vision1Cost | DIB_Vision1Cost | ×R |
| DIB_Classic3Cost | DIB_Classic3Cost | ×R |
| DIB_PAR1Cost | DIB_PAR1Cost | ×R |
| DIB_SuggestedRetail | DIB_SuggestedRetail | ×R |
| DIB_SuggClassicMargin | DIB_SuggClassicMargin | Direct (not scaled) |
| DIB_SuggVisionMargin | DIB_SuggVisionMargin | Direct (not scaled) |
| DIB_SuggPARMargin | DIB_SuggPARMargin | Direct (not scaled) |
| DIB_SubstituteItem | DIB_SubstituteItem | Direct |
| DIB_SubstituteItem2 | DIB_SubstituteItem2 | Direct |
| DIB_StatusCode | DIB_StatusCode | Direct |
| DIB_Taxonomy1 | DIB_Taxonomy1 | Direct |
| DIB_Taxonomy2 | DIB_Taxonomy2 | Direct |
| DIB_Taxonomy3 | DIB_Taxonomy3 | Direct |
| DIB_Taxonomy4 | DIB_Taxonomy4 | Direct |
| DIB_Taxonomy1Desc | DIB_Taxonomy1Desc | Direct |
| DIB_Taxonomy2Desc | DIB_Taxonomy2Desc | Direct |
| DIB_Taxonomy3Desc | DIB_Taxonomy3Desc | Direct |
| DIB_Taxonomy4Desc | DIB_Taxonomy4Desc | Direct |
| PricingStrategy | PricingStrategy | Direct |
| VendorNumber | VendorNumber | Direct |
| VendorName | VendorName | Direct |
| MfgNumber | MfgNumber | Direct |
| PrivateLabel | PrivateLabel | Direct |
| VelocityCode | VelocityCode | Direct |
| DOM | DOM | Direct |
| RDOM | RDOM | Direct |
| CreateDate | CreateDate | Direct |
| LastPriceChange | LastPriceChange | Direct |
| DIB_Mult3, DIB_Mult2, DIB_Mult1 | OrderMultipleSKU | Concatenated with pipes: Mult3\|Mult2\|Mult1 |
| (SKU match) | VendorOwned | VEND if SKU exists; otherwise NON |
Business Rules
- Composite key: RSCNum + SKUNum — each RSC receives its own catalog file.
- SuggestedRetail filter: Records with NULL or zero SuggestedRetail are excluded from the MMVendorData view and will not reach the main table.
- Cost calculation: Cost is the highest of Classic3Cost, Classic2Cost, and Classic1Cost.
- Level1–Level5: Derived from the DIB price-level rounding scheme, not directly from the file. Level1 and Level2 are rounded price points below retail; Level3–Level5 are the next three rounded prices above retail.
- SuggAuxRetail and UOM: When a product is sold per sub-unit (e.g., wire sold per foot but packaged per roll),
SuggAuxRetailcontains the per-unit price. This drives the UOM adjustment ratio applied to all price and cost fields.
