Wednesday, June 29, 2016

Process Mapping Notes

  • What does my business do to add value to my customers? High level processes
    • Does this represent my core business?
    • Are these areas vital to my business for our current strategy? (Survival, expansion)
    • Can I break this up into subprocesses?
    • What plant and equipment do we need to make this process work efficiently?
    • Start with the high level map to help guide what needs to be mapped
    • always begins with some supply chain, and ends with some customer chain
    • Value chain needs to be analyzed for Standards, Quality and Risk in each process
    • Map the processes that will have the largest impact in terms of risk or quality
  • Analysis of Value Chain
    • Remove processes that do not add value
    • Identify root causes and symptoms
    • Provides cost indexes for labour across the world
  • Cost of Quality
    • Prevention Costs
    • Appraisal Costs
    • Internal Failure Costs
    • External Failure Costs

Dynamics NAV - The User cannot be deleted because the user has logged on to the system. You must set the user's state to "Disabled"

This is by design. If the user has logged in and already done transactions that have his/her username assigned tothe logs and the transaction, the user should be kept for reference.

If you are certain you do not wish to keep track of the user, the user can be deleted by deleting the User Personalization record.

Friday, June 24, 2016

Dynamics NAV - How to Add dimension values to posted transactions

Sales transactions have been posted, but users forgot to add dimensions to the sale.
The end result is your Sales figures on your account schedules go to Dimension "" instead of a proper dimension

Use the "Dimension Set Entry" Table to find the "Dimension Set ID" value for the dimension you need to add

  • General Ledger Entries
    • Enable the dimension column in NAV (Named your dimension name) and filter to see which transactions are missing dimensions
    • Run this script to update the Dimension valus on all GL trx for a specific sales transaction
    • update [CRONUS].[dbo].[CRONUS$G_L Entry] SET [Global Dimension 1 Code] = 'DIMVAL' ,[Dimension Set ID] = 12 where [Source Code] in ('SALES','INVTPCOST') and [Global Dimension 1 Code] = ''
  • Sales Orders
    • update [CRONUS].[dbo].[CRONUS$Sales Header] SET [Shortcut Dimension 1 Code] = 'DIMVAL' ,[Dimension Set ID] = 14 where [Shortcut Dimension 1 Code] = '' and [External Document No_] = '5257'
    • update [CRONUS].[dbo].[CRONUS$Sales Line] SET [Shortcut Dimension 1 Code] = 'DIMVAL' ,[Dimension Set ID] = 14 where [Shortcut Dimension 1 Code] = '' and [Document No_] = (select [No_] from [CRONUS].[dbo].[CRONUS$Sales Header] where [External Document No_] = '5257')
  • Sales Invoices
    • update [CRONUS].[dbo].[CRONUS$Sales Invoice Header] SET [Shortcut Dimension 1 Code] = 'DIMVAL' ,[Dimension Set ID] = 14 where [Shortcut Dimension 1 Code] = '' and [External Document No_] = '5257'
    • update [CRONUS].[dbo].[CRONUS$Sales Invoice Line] SET [Shortcut Dimension 1 Code] = 'DIMVAL' ,[Dimension Set ID] = 14 where [Shortcut Dimension 1 Code] = '' and [Document No_] = (select [No_] from [CRONUS].[dbo].[CRONUS$Sales Invoice Header] where [External Document No_] = '5257')

Additional Notes:
To prevent this from happening in the future, you can set required dimensions on ALL accounts. vendors, customers, etc.
Search Dimensions>Navigate>Dimension>Account Type Default Dim

However, if this is too general, you can set specific customer dimension defaults
This will allow you to set a required dimension for each customer.
If you leave it blank, it will require a value before posting.

Wednesday, June 22, 2016

Dynamics NAV - LS Retail - Data Director - Could not receive file. Could not send header.

This happens when the data file being sent to the terminal is too large.

  • To resolve this
    • Set the compression mode on the job to 7Zip
    • In the Data Director Configuration for hte head office, set the 7zip file size to 10
    • Save the DD config
    • Restart the DD service
Now, when this job fires, it will use 7-zip compression to compress the file as small as possible (but will take a lot longer to compress)
It will also split the files into 10mb chunks and send each to the terminal before extracting and applying.

This will cause the entire process to take a bit longer, but it will handle the large files eventually with no issues.

Data Director file location for sending

C:\ProgramData\LS Retail\Data Director\Work\

Thursday, June 16, 2016

Windows 10 is Slow - 100% Disk, 100%CPU, Delay when browsing, Slow Browsing

  • Disable Windows Defender (This will stop and scan EVERY single action you take)
    • Start>Settings>Windows Defender
  • Disable Notifications
    • Start>Settings>Notifications
  • Don't use Edge, Use Internet Explorer or Chrome
  • Remove all 32-bit software
  • Chrome DNS Error
    • Start DNS Client Service
    • ipconfig /flushdns

Tuesday, June 14, 2016

SQL - First and Last Day of the current month

 SELECT DATEADD(month, DATEDIFF(month, -1, getdate())-1, 0) as FirtDayPreviousMonthWithTimeStamp,
    DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, -1, getdate()), 0)) as LastDayPreviousMonthWithTimeStamp

SQL 2014 - How to import CSV files into a table on the fly

Check your Administrative tools>ODBC 64>Drivers and see if you have a Microsoft Access Text Driver.
This will exist if you've installed access or the database components

--Run these to enable the options

sp_configure 'show advanced options', 1;
sp_configure 'Ad Hoc Distributed Queries', 1;

--Run one of these to import your file, depending on which driver you have installed

SELECT * into MyTable FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\FilePath;', 'SELECT * from [MyFile.csv]')


SELECT * into MyTable FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DBQ=C:\Filepath', 'SELECT * from [MyFile.csv]')

Monday, June 13, 2016

One drive for Business Office 2016 Windows 10 - Syncing sticks. Syncing to sharepoint sticks. Cannot sync to onedrive or sharepoint. Use the Easy Fix Tool

Backup all your data to another folder, you're going to lose everything in the current sharepoint and onedrive folders.

Run this tool.

When entering the username and password to login to sharepoint, click on the password field and press enter.
Sometimes the "Sign In" button sticks, and doesn't let you click it.

Try this one as well

Friday, June 10, 2016

Dynamics SL - Tables


  • Accounts
    • Account
    • SubAcct
    • vs_acctsub (view)
    • AcctHist - Account transaction history
      • select distinct acct, sub, fiscyr from accthist
        where fiscyr >= 2012
  • Vendors
    • Vendor
    • Class - VendClass
  • Vendor Balances
    • vr_03680VendorDetail (view)
  • Purchasing
    • PurchOrd - PO Hdr
    • PurOrdDet - PO Lines
    • PORct - Receipts
    • POTran - Receipt Lines
  • Customers
    • Customer
    • Class - CustClass
  • Items
    • Inventory
    • Units of Measure - INUnit
    • Sites - Site
    • Item Class - ProductClass
    • Item Qty - Location
    • Bins - vp_DfltSiteBins
    • Item Prices - Inventory

SQL Server 2014 - White circle instead of green arrow in server instance and SQL Server Agent

The Green arrow shows the state of the service as "Running"
If it shows a white circle, this is because you don't have enough priveleges to see the status of the service.
Right click and Run Management Studio as Administrator.
This should resolve this issue.

Thursday, June 9, 2016

eOne Smartconnect - Invalid object name ‘SC9400’

This error occurs if you haven't created the GP resource cache.

  • Install the SmartConnect GP Addin 2015 (It's a separate file in the installer)
  • This will add the cnk file to the GP folder
  • Launch GP, include code
  • GP>Tools>Smartconnect>GP Resource Cache
  • After it completes, Launch Smartconnect
  • Setup the GP connector
  • Run maintenance

Wednesday, June 8, 2016

Dynamics NAV - CAL Comments

Use "//" to insert a single line comment. When the "//" symbol is encountered in your code, the rest of the line is interpreted as a comment.
  • Use "{" and "}" to mark the beginning and end, respectively, of a block of comments.
  • Monday, June 6, 2016

    Excel - CSV files do not open correctly delimited

    This happens because the regional settings of the machine is set to use the ; as the default delimiter instead of ,

    Solution: Change the Regional and Language settings  
    • go to Start | Control Panel | Regional and Language Options
    • Click Additional Settings
    • For Decimal Symbol, enter a dot: .
    • For List Separator, enter a comma: ,


    Add an extra line to your CSV file to tell Excel what the separator is.
    Add the following line to the top of your CSV file:

    Wednesday, June 1, 2016

    Connect to Jantek - Visual Foxpro ODBC and import data to Excel, Access or SQL

    • I ended up using the following steps to move data from FoxPro to SQL
      • Excel 2013 32-bit using Microsoft query Advanced ODBC connection using the FoxPro 32-bit ODBC driver
      • Set the connections to automatically refresh
      • Installed Microsoft Data Access Components 64-bit using /passive to get both drivers on the machine
      • Read data into sql using
      • SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0',
        'Excel 12.0; Database=C:\JantekLink\janteklink32.xlsx;HDR=Yes', 'Select * from [EMP$]' )
      • Setup System Scheduler to launch the excel sheet and close it
      • Setup system scheduler to run a script to read the updated data from the sheet into sql
    Notes on Connecting to Foxpro
    • Cannot connect as linked server if using 64-Bit SQL (FoxPro is 32-bit)
    • This method only works if connecting to 32-bit BQL.
    • Use Visual FoxPro OLE DB Driver to connect (ODBC has been replaced, however, the ODBC still works to connect to the table sources)

    Use Sybase Anywhere 64-bit ODBC driver

    Install 32-Bit office AND 64-bit Data Access Components in /passive mode and use an Access Database or Excel sheet as an intermediary between FoxPro and SQL.

    Jantek Folder Structure - Visual FoxPro Tables - Free Tables

    Jantek Database Folder - Free Tables
    \DBF.JTA\EMPCLOCK.DBF - employees site assignments
    \DBF.JTA\AUTOLOG.DBF - system data transfer log
    \DBF.JTA\TIME.DBF -  clockin/out transactions
    \DBF.JTA\Payroll.DBF -  approved payroll

    Jantek Databases - FoxPro databases
    \DBF\Company.dbf - Companies
    \DBF\Branch.dbf - Branches
    \DBF\Emp.dbf -Employees