Monday, February 20, 2017

SSRS - How to remove time from a date field with time values

=CDATE(FormatDateTime(Fields!DateCreated.Value,Dateformat.Shortdate))


This will remove the time, then reformat the field as a generic date field that will use the report formatting options

Honeywell Barcode Scanner cannot be detected by windows - Does not install properly


  • Install the drivers first
  • Then Plug in the USB port
  • If you have previously plugged in the USB without having the driver installed first, 
    • uninstall the driver completely, 
    • unplug the USB, 
    • einstall the driver, 
    • reboot the machine, 
    • then plug in the usb
  • Generally this rule of thumb should be followed for all usb peripherals

Wednesday, February 15, 2017

Dynamics GP - Report Writer - Force display 2dp currency for Prices even when currency is set to 5 for all currencies


  • Create Calculated Field zMCDP2 as Integer
    • Use formula
    • function_script(rw_currency_getphantomindex (c)Currindexbody 3)
  • For each field you want to format, double click on it
    • In the formatting window, in the bottom right menu, click on the table name until you see "Calculated Fields"
    • Select zMCDP2
    • Click ok
    • This will force the field to use the value of zMCDP2 as the format
    • The last parameter can be changed for the amount of decimal places you want to display  (0dp = 1, 1dp = 2, 2dp = 3)

Batchmaster Requirements Gathering


  • Inventory
  • UofMs
  • Costing
  • Lab Items
  • Mfg Data Entry Process and Documents
  • Discuss Input Templates
  • Reports & Analysis

Monday, February 6, 2017

Batchmaster Manufacturing for Dynamics GP Training


  • INSTALL & SETUP
    • Install 
      • Must install as admin and sa
      • Must have Office, Smartlist Builder
      • Must have eConnect
      • Office Web Component
      • Must setup conversion of KG to Grams for Nutrition Vertical
      • Must install on every client, and every database using Batchmaster
        • Run everything in the BMM Prerequisites folder
        • Run Product Install
          • Will Create Addins folder in GP Folder, copy all files into there
        • Run Service Patch for the destination version you want
        • Run BMMGPDatabaseUpdateUtility on each database
          • To Install Custom Smartlists, Click Install SmartList
          • Connect to server, click select all>Install Smartlist
        • Run BMMRegistration
          • Copy Site Key, send to Batchmaster, request new key
          • Receive Key File, import 
          • After import, must run BMMGDatabaseUpdateUtility on each database to update registration
          • creates sql BATCHMASTER user and pw as BATCHMASTER
        • Run all Vertical Installs as required
          • Run BMMFoodVerticalIntegrationDatabaseUpdate
          • Connect, Update system db, select company db, update company db
          • Complete Nutrition Integration Setup>BMM Item Master>Choose Path to Addins Nutritional DB
          • Cards>Nutrition Dashboard>Import>Map all items>Update Properties

        • Log files exist in GP folders
        • All Report files are located in Addins\CR Reports
        • Launch GP to include code
        • Play Macros in Toolbox>BMM-GP>Roles & Tasks to setup all security roles
      • Check version by going to any GP window > Additional > Batchmaster
      • Install BMMMobile
        • Separate Install - Install Client First, then server
        • Point to Batchmaster License Manager
        • Run Batchmaster MobileDatabaseUpdateUtility
          • Additional mobile objects in existing databases
        • First Time Login
          • Logging in as admin gives setup options
          • No Company
          • Create a new company, point it to your GP company (can create a second one for your test)
          • Define Security Roles - Write or None, can show all
          • Add Users
          • Has it's own users and security
          • Setup standard signatures
          • Screen Size Selection
          • Use Screen Size Allocation - Can define what screen sizes to use for each menu for each user
        • User Login
          • Setup
            • All numbers here are internal BMM numbers, not GP numbers
            • Batches must be  created in GP as continuous to act as placeholders to store all BMM Mobile transactions
            • BMM will automatically recreate batch when it's posted regardles of batch type
            • Purchase Setup
            • Sales Setup
              • Can setup additional step to package items 
              • Can setup Pallet/Packing labels
            • Inventory Setup
              • Can enable HACCP Sequential to force user to confirm boilerplate actions
              • Can setup staging setup to add smaller UofM conversions for more detailed issuing to production
            • Printer Setup
              • Setup labels, and printers
          • PO Receiving
            • Can track Vendor Lot no in addition to GP generated lot no if that was setup
            • Can generate pallet labels
            • Transfers transaction to batch to be reviewed and posted in GP
          • SOP Picking
            • Pick, allocate, fulfil SOP docs
          • INV Trx
            • Adjustments
            • Adv Stock Count
              • Can have multiple usres count at once
              • Central review dashboard
            • Reg Stock Counts
              • Start count in GP
              • Count in BMM Mobile
          • MFG Trx
            • Issue Raw Materials
            • Complete Batches
    • System Configurations
      • Set Posting Type
        • Run DynamicsGP\Addins\ProductionPostingMode.exe
        • Select Econnect (Ensure that econnect is functioning properly and is running as a service with access to sql)
      • Advanced Posting
        • Run DynamicsGP\Addins\Batch Posting Advanced.exe on a machine
        • It will automatically post mobile transactions on a schedule
    • Customization
      • Batchmaster has it's own customization editor
      • Click Customization from any window
      • Can click on top left square in column header row to hide or show columns
        • File>Save Screen layout will save current user's display settings
      • Additional info linked ot item card
        • Each vertical will install additional different defaults and data
        • Item Specification field - can store any amount of additional field data per item, can be used to add multiple barcodes
        • Physical Properties
        • Material Safety
        • BM Item Extension - GTIN Number can be used for barcodes
      • Can define potency controls that affect how much is received based on potency
      • Can do Kosher, Halal, Organic as verticals on item card
    • Items
      • Does not use Mfg Accounts setups
      • Lot Status Codes can control if some items in a lot are usable
      • Alternate Items - Can setup other items that can be used instead with a factor
        • Applies to batches only where substitutions may need to be made

      • UOFMs
        • Setup UofM Conversions for all standard UofM Conversions
        • Unit Of Measure>Cards
        • Unit of Measure > System
        • Can also be setup through the BM Item Extension screen per item
    • Formulas
      • Non-Inventory Items cannot be used in Formulas or BOMS
      • Formula Class - set wip and variance accounts per class
      • Formula Status - only allow certain statuses to produce (Production Setup)
      • Formula Approval - Setup if formula needs approval whenever changed
      • Formula Entry - Setup boilerplate instructions and text lines along with all line items and ingredients
        • Can resize the formula to some required output factor - all raw materials will be adjusted accordingly
        • The Line Sequence of the formula is used for the picking order when picking items to submit to manufacturing
      • Process Cell
        • Represents all activities between issue of RM and receipt of FG or INT for this item
        • Can be used to represent machines, or collection of machines
        • Process cell takes rm inputs, and produces fg outputs in some period of time
      • Create Intermediates
        • Will generate item card, and fill in all required data
      • Labour Lines - Can setup labour amounts and costs per hour per batch
      • Overhead - can set as a fraction/factor of labor costs
    • Bill Of Materials
      • Top Assembly - Main intermediary to be packaged
      • Sub Assembly - Final Packaging for final finished goods
      • Can enter labor and overhead cost per unit weight here
    • Container BOM
      • BOM for Packaging items only
        • Can create production batch to produce intermediate, and consume a Container BOM to have it consume packaging on the fly
        • Used for items that are not consistently packaged in one size
        • Sales will go toward the item, packaging gets adjusted out to cost of sales
    • Batch Entry
      • Production Batch Header
      • Create Production for Batch
    • Batch Ticket
      • Production Batch Lines
      • Raw Materials and Steps
    • Batch Close
      • Enter actual raw materials and finished goods in batch
    • Super Batch
      • System will generate batches for Finished good, and all intermediates required to produce that finished good
      • Can manually enter a single super batch for multiple finished goods that share an intermediate
    • Batch Series
      • Different number sequences can be setup for different types of batches
      • Numbers can be manually overridden
    • Economic Order Quantities
      • Setting the EOQ will force minimum batch sizes when creating any Batch
      • Can set Slabs (Tiers of EOQ)
  • PLANNING & PRODUCTION
    • Make to Order
      • Demand Supply Report - Used by Sales or Purchase Planner
        • Critical Items - Anyhting that you do not have enough stock of, or will not have enough stock of based on current Batch Production orders
      • Demand Supply Dashboard - Visual version of Report
        • Can drag fields to header to group on dashboard
      • Transfer Sales Orders, SO with BO amounts, BackOrders, Invoices to Production Batches
        • Club with Formula - Create one MO for all SO with same formula
        • Create Super Batch - Create MO and all dependant MO's
        • Fill from Inventory - Uses RM stock from inventory, and will not produce if not required
        • Once an order has been transferred to a Production Batch, it does not check the order for any changes. Any changes to the order must be manually done to the Production Batch.
      • Critical Material Report - Shows all critical materials
    • MPS
      • Generate Company Calendar, identify holidays
      • MPS Item - Define which items can be planned
      • Planning Calendar - Configure Planning Buckets
      • MPS Setup
      • Forecast Entry - Can export to excel, fill in and import
        • Can make lines inactive, and categorize lines to separate into sub-forecasts
      • MPS will generate orders
        • Planned Orders - will be removed and recalculated every time mps is run
        • Firm Planned - will not be removed, but could be manually deleted
        • Confirmed - Will not be removed, represents a real order
      • MPS Dashboard
        • MPS Production Order Tab
        • Confirm and transfer orders to confirm production
    • Reports
      • Critical Material Report 
        • Run this report, then go to Special Function>Create Purchase Orders
      • Production Role Center - Dashboard Pivot Table type view
        • Manage and control all batches
      • Batches in Process - Shows all current Batches
      • Max. Producible by Stock - Shows all that could be produced with current stock
      • Raw Material Requirement Analysis - Shows all Raw Materials required to produce
      • Material Usage Report - Shows average usage of raw materials
      • Projected Material Availability - Projected Raw Materials based on usage and receipts in future
        • Can also generate PO's from here
    • Smartlists
      • Smartlists exist for all batchmaster data
    • Production Scheduling
      • Production Scheduling Dashboard - Can schedule independently, or pull in existing make to order, MPS and MRP orders
    • Production
      • After stock is transferred to Production Location
      • Allocate raw materials - Useful if you want to dictate which lots should be taken
      • Issue raw materials (will allocate if not allocated) - Assign lots and quantities to Batch
      • Partial Close - Receive finished goods
      • Production History Detail Report
    • Sample Production Process
      • Sales - Creates Demand by entering MPS>Cards>Forecast Entry or SOP Documents
      • Planner - Run MPS to Suggest all FG and INT batches
      • Planner - Confirm INT Batches and firm up FG batches
      • Planner - Print Report Production Scheduling Dashboard>Projected Material Availabity to identify any inventory shortages
      • Planner - Update schedule including confirmed orders and MPS orders, Enter any manual tasks, Remove or reschedule based on inventory shortages
      • Planner - Print Production Schedule and confirm batches
      • Factory - Print Picklist, Execute Production,  Report variances
      • Production Clerk - Select Batch, Issue all materials, receive finished goods, Close batch
  • QUALITY CONTROL
    • Can be done against RM, FG and PROCESS
    • Create QC Type codes
    • Create QC Tests
    • Sample Plans - How much to sample? Method?
    • Create QC Item Tests - What tests to do on what items?
    • Item Extension - Enable production QC
    • Set test to trigger on Purchase or Production or both
    • Can Create Inspection Plans to be executed against process
      • Can enter timestamp log entries against test requirements throughout the day
  • LABORATORY
    • Material Physical Properties
      • Can set nutritional or other values per item that will be tracked
      • Can setup equations for ho the values should be calculated
      • These can be applied to Formulas to calculate nutritional values based on usage of the raw materials
  • LOT TRACEABILITY
    • Production>Utilities>Trace/Serial Lots
  • COSTING

Friday, February 3, 2017

SQL output to text file



--Export to File
DECLARE @command VARCHAR(1000)
SET @command = 'BCP "SELECT * from ESL.dbo.BIT_MAGNAFile" queryout "C:\Temp\myfile_'
    + CONVERT(VARCHAR, YEAR(GETDATE())) + RIGHT('00'
                                                + CONVERT(VARCHAR, MONTH(GETDATE())),
                                                2) + RIGHT('00'
                                                           + CONVERT(VARCHAR, DAY(GETDATE())),
                                                           2)
    + '.txt" -c -T -t "|" -S "ESHODB01\ESLNAV"'
EXEC xp_cmdshell @command

Wednesday, February 1, 2017

Dynamics NAV CAL - Open a Page on button click

PAGE.RUN(PAGE::"IC Outbox Transactions");

To Get the current record from a list and pass it to the open page, do not use CAL
Instead, on the properties of the button, set the following values

  • Runobject - Page Transaction Register
  • RunPageLink - Customer No.=FIELD(No.)