General Check

 

Back Office > Administration > SQL Tools > Database Doctor > Check Database Tables > General Check

 

Description

 

This menu item is used to execute data integrity checks against the SwiftPOS Back Office Database. For example : Fields that have null values or invalid characters.

 

An example of a data integrity check is: Fields that have null values or invalid characters as a result of importing data that may have been incomplete.

 

Check

 

 

Noteworthy

 

Members

 

  • Member Defaults - Select to set Active From to 01-01-1970 when it is blank. Remove Linked Member Classification records when the Member no longer exists.
  • Base Member Price Levels in Locations - Select to set Member Base Price to the Location default Price Level when the Price Level is no longer assigned to the Location.

 

Other

 

  • EJ dependant tables for Orphaned records - Select to remove Sale items, Media and String records when the header records no longer exists.
  • Connect tables for removed Terminals - Select to remove deleted terminals from the connect service.
  • EJ Items Table For Deleted Products - Select to change the Products in any Sales Transactions that have deleted Products (that is, Sales of Products, Cancelled Sales and String Items) associated to them, to the the ??DELSTOCK Product
  • EJ Table For Invalid Clerks - Select to create missing Clerks, Locations and Terminals that have Sales, Items or Media assigned in the Electronic Journal/Transaction (EJ Table). Fix invalid Clerks (less than 0) that have Sales assigned in the Electronic Journal/Transaction (EJ Table).
  • Media - Select to create any default Media Type that is missing. Set GL Link to 1 for all default Media. Set Media Type to Cash and no change allowed for media 1 and 990. Fix invalid or missing GL links for Master Groups and Locations
  • Menu Board Price Levels in Locations - Select to set Menu Board Location Price Level to the Location default Price Level when the Price Level is no longer assigned to the selected Location.
  • Orphaned POS Keys - Select to remove Touch POS Product Keys (free and fixed) when the Product has been deleted.
  • Selected tables for zero values/orphaned records - Select to ensure a check is done on the following, to ensure that no IDs with a value of zero or orphaned records exist:

 

  • Locations
  • Terminals
  • Print Group Headings
  • Master Groups
  • Categories
  • Members
  • Member Classifications
  • Product PLUs - Also, remove Combo Links and Product Combo sizes when the Combo has been deleted.

 

Note : Zero value and/or orphaned records records will be reported on a Zero / Invalid Values report.
  • Check Tax - Select to create missing Price Level tax and Category Tax records.
  • Report Top 10 Table sizes and Advertising Graphic sizes - Select to generate two reports to list the Top 10 Table Sizes and the Advertising Graphics Sizes reports. Note : It is recommended that Advertising Image/Video (MP4) files are kept small, and no longer used files are removed. These files are sent to SwiftPOS Touch terminals as part of a Full Update.
     

 

The above sample report shows that the CFD Advertising file is almost 5MB in size. Be aware this file may be sent to to SwiftPOS Touch terminals over the Internet. In more recent versions of SwiftPOS, the file size and number of graphics have been limited to prevent performance issues.

 

Products

 

  • Barcodes for Duplicates - Select to remove duplicate Barcodes (eg: 0012345 and 12345). Note : Barcodes with leading 0s will be stripped of leading zeros and if then found to be duplicate will be removed. These will not be reported on.
  • Barcodes Empty Or Null Or Invalid Characters - Select to remove Product Barcodes that are blank or when they contain non-numeric characters.
  • Blank Descriptions / POS Display - Select to set the POS Display, when is is blank, to the first 16 characters of the Product Description.
  • Case Quantity Zero Or Null - Select to set the Case Qty of a Product to 1 when it is 0 or blank.
  • Categories and Groups - Select to fix duplicate Descriptions in Categories, Groups, Master Groups and Print Groups. Category Link 1 and Link 2 are also updated if needed. For Categories and Groups, the first 16 characters must be unique. For Master Groups, the first 30 characters must be unique. For Print Groups, the first 20 characters must be unique.
  • Duplicate/Null Products in Promotions, Happy Hours - Select to ensure a report will be generated when Price Level / Product combinations are duplicated in Promotions.

Select to remove MetCash Promotions that do not exist in SwiftPOS. Remove Products from Promotions when the Produce Code is blank.

  • Invalid Characters - Select to removes invalid characters from the attributes of key Software Elements in the database. Fixes the following:
     
    • Products - Remove invalid characters from Description, Filter, POS Display, Long Description.
    • Categories - Set Default Modifier to NONE when it is blank. Set Print Group to 0 when it is blank. Remove invalid characters from Link 1, Link 2 and Description.
    • Clerk Groups - Remove invalid characters from Description.
    • Product Barcodes - Remove invalid characters from Barcode.
    • Members - Remove invalid characters from Surname, First Name, Address and Mailing Address.
    • Clerks - Remove invalid characters from Name. Set Password to 0 when it is blank.
    • Media Descriptions - Remove invalid characters from Description.
    • Print Groups - Remove invalid characters from Description.
    • Sizes - Remove invalid characters from Description and Plural Description.
    • Suppliers - Remove invalid characters from Name.
    • Location Groups (Venues) - Remove invalid characters from Name.
    • Location Filters - Set first filters to ALL when they are blank.
    • Locations - Remove invalid characters from Name.
    • Terminals - Remove invalid characters from Name.
    • Suppliers - Remove invalid characters from ID and Name.
    • Stock Movements - Remove invalid characters from Supplier ID.
       
  • Negative Priced Items - Select to ensure items with negative prices are reported. Note : Open priced items (-$0.01) will NOT be reported.
  • Product Prices - Select to round Products Prices to 2 decimal places when not a quantity based PLU.
  • Location Stock - Select to set Location Cost and Previous Cost to 0 when it is blank. Set Draw From to the same Location when it is 0. Set SOH to 0 when SOH * Location Cost > 9223372036854775808.
  • Looped Recipes - Select to check for Recipes that are reciped to themselves.
  • Stock against Recipe / Non-Inventoried Products - Select to set SOH to 0 for recipe and non-inventoried products.
  • Validate Imported Location Group (Venue) and Product Data -  Select to fix the following:
     
    • Products - Set UOM to ea when UOM does not exist.
    • Location Groups (Venues) - Set Venue Type to 50 when it is set to 0 (head office) and Location Group is not 0. This check may be obsolete as there does not seem to be a Venue Type 50 anymore.

 

Footer

 

  • Select All / Un-Select All - Select to select/unselect All checkboxes.
  • Start Now - Select to commence checking.
  • Close - Select to exit.

Modify

 

 

Noteworthy

 

Products

 

  • Convert all Product Descriptions to CAPITALS - Select to convert all Product Descriptions to upper case.
  • Convert all Product POS Displayes to CAPITALS - Select to convert all Product POS Displays to upper case.
  • Recalculate Recipe Costs - Select to recalculate Recipe costs for ALL Products in ALL Locations based on the current cost of the Purchased Items they are sourced from.
  • Fix Product UOM - Select to set the Product UOM (Unit of Measure) to ea when it is 0. Set Recipe Item UOM to match parent Product UOM when it is 0.

 

Images

 

  • Remove Unlinked Images associated with the following:
     
    • POS Keybaords
    • Products
    • Menu Boards
    • Kiosk
    • Label

 

Other

 

  • Remove zero value entries from closed periods - Select to remove Product records from Closed Trading Periods when all values are 0.
  • Update Available Bankable Media - Select to ensure that the Media displayed in the Banking screen is updated according to the Banking Type selected against Media Types. Only those Media Types that are NOT set to 'Non - Banking' will be displayed in the Banking screen. This option should not need to be selected if the Media Types have been configured correctly from the start. This option should only be used when a Media Type becomes redundant, and therefore Banking data needs to be removed. Note : If there is for some reason a requirement to remove the Cash Media Type, from the Banking screen, (ie. set Cash to Non - Banking. This, however, is NOT recommended) and run this option, then ensure the Check Media option is NOT selected. If selected, the Cash Media Type will not be removed from the Banking screen.
  • Numbering of Order Column in M & M Rules - Select to ensure a the numbering in the Order column of Mix n Match Rules is sequential. This will ensure that there are no duplicate numbers or gaps in the numbering.

 

Suppliers

 

  • Fix multiple Preferred Suppliers or No Primary Suppliers - Select to ensure that Products that have multiple Preferred Suppliers assigned, are fixed to ensure that the last Supplier purchased from is set as the Preferred Supplier. Also, Products that have a single Supplier assigned and are not set as Preferred, will be set as Preferred. Set the Product's Preferred Supplier to blank if a Product Supplier record does not exist.
  • Fix Preferred Suppliers with no Preferred Locations in Products - Select to set the Locations of all Product Preferred Supplier Records to all Locations when it is blank and to No Locations for all other Product Supplier records.

 

Footer

 

  • Start Now - Select to commence checking.
  • Close - Select to exit.

 

Related Topics

 

  1. Administration
  2. SQL Tools

 

Published 28 Mar 2024