Dynamics NAV - Intercompany - The General Journal Template already exists

This can happen when copying companies for intercompany setup.

When selecting Sales Journals Page, you get this error


Navigate to General Journal Templates
Delete the template producing the error

Dynamics NAV - Message: An item with the same key has already been added.

Problem:
This error occurs after a separate NAS service has been started.
The service itself runs, but the window error log shows this error, and the NAS does not execute any scheduled tasks.



Solution:
Google has Vague descriptions about problems with duplicate language codes, or duplicate keys in tables.

It may have to do with sql security after restoring a db to a new sql server that has the same user accounts, but in caps or commons.
Remove all users from the db and re-add them to ensure the security on the db corresponds to the actual users on the machine.

Set SQL to run as the same account the NAV Instance is running under
Ensure the account is a member of db_owner in the company db

Run the Fix Orphan Script
---------------------------------------------------
SET NOCOUNT ON
USE AdventureWorks
GO
DECLARE @loop INT
DECLARE @USER sysname
DECLARE @sqlcmd NVARCHAR(500) = ''

IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL
 BEGIN
  DROP TABLE #orphaned
 END

CREATE TABLE #Orphaned (UserName sysname,IDENT INT IDENTITY(1,1))

INSERT INTO #Orphaned (UserName)
SELECT [name] FROM sys.database_principals WHERE [type] IN ('U','S') AND is_fixed_role = 0 AND [Name] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')

IF(SELECT COUNT(*) FROM #Orphaned) > 0
BEGIN
 SET @loop = 1
 WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned)
  BEGIN
    SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
    IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
     BEGIN
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] FROM WINDOWS'
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] WITH PASSWORD = N''password'''
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
     END
   
    SET @sqlcmd = 'ALTER USER [' + @USER + '] WITH LOGIN = [' + @USER + ']'
    Exec(@sqlcmd)
    PRINT @USER + ' link to DB user reset';
    SET @loop = @loop + 1
  END
END
SET NOCOUNT OFF

------------------------------------------------------------

Dynamics GP - Printing to File - Selecting DOCX but only getting HTM in file location

Word Form Template for this report does not exist.

Create a word form template for this report using the correct original or modified version.
Create it from existing report, not blank.

Dynamics NAV - Setup Flow Fields

• Go to Table
• Create new field
• Click properties
• Switch Fieldclass to Flowfield
• Use Lookup if you just need to get a value from a related table eg. Barcode, Shipment No.
• Set Calcformula to your formula
• Set editable to no
• These fields are available and sortable in pages

Dynamics NAV - Item Attributes vs LS Retail Attributes

• LS Retail has an Attributes list that can be used to add multiple attributes to an Item, Customer, Vendor
• These can also be displayed on the POS
• Can be selected as a Hard Attribute on the Retail Item Card
• Will be passed on to LS OMNI, and will be visible when requesting items
• NAV also has an Item Attributes
• These can be assigned directly to items, or to item categories
• The values will be displayed in the Item Attribute factbox in the item list
• 
  

Dynamics NAV - Configuration Templates

These can be used to set default values for Items, Customers, Vendors, Etc.

Item Category now has an Item Template Code field

Item Categories can also have their own attributes using the Item Categories page from Financials

• Create Config Template that matches your item categories
• Set all default values that should be populated on the Config Template
• Assign Config Template to Item Category
• When Item Category is used, all default values will be applied
• Item Categories can have parent categories
• Parent categories are existing categories in the item category table
• Child categories inherit all attributes from parent categories
• This allows for a complex hierarchy structure to be setup for Parent categories and regular categories

Dynamics NAV - Image previews do not display after importing image link records

This is because a Media Set ID is calculated in the Tenant Media Set table for each database as whenever a picture file is physically imported.

You must re-import all pictures, or write a codeunit to insert the new media set index values into the Tenant Media Set table of the new db.

SQL - Count All Rows in All Tables

SELECT T.name AS [TABLE NAME],
       I.rows AS [ROWCOUNT]
FROM   sys.tables AS T
       INNER JOIN sys.sysindexes AS I
               ON T.object_id = I.id
                  AND I.indid < 2
--WHERE T.name like 'Cronus%'
ORDER  BY I.rows DESC

Dynamics GP is Slow - Use SQL to review Query Performance

https://www.codeproject.com/Articles/579593/How-to-find-the-top-most-expensive-cached-queries

Management Reporter - Crashes when trying to import Building Blocks

The screen itself is bugged, do not mouseover anything on the screen
Use the keyboard to tab and arrow to navigate, and crtl+a to select everything, then tab down to import and hit enter

Dynamics GP - SSRS - Migrate SSRS Objects to new Server

https://www.mssqltips.com/sqlservertip/2627/migrating-sql-reporting-services-to-a-new-server/

• Use The RSScripter tool
• Download from 
• http://sqlserverfinebuild.codeplex.com/wikipage?title=Install%20Reporting%20Services%20Scripter
• Look in the archive download link
• Use it to copy all SSRS objects from one location to another
• Run the tool on the current ssrs server to capture all objects
• It will generate a cmd file
• Edit the cmd file
• Change the server name to the new server
• Remove the rs.exe path
• Run the file to push all objects to the new site

Dynamics NAV - LS Retail - POS Transactions - IsReturnSale, Voided, Training

• "Transaction Header"."Sale Is Return Sale" - True Is return
• "Transaction Header"."Entry Status" - Option set  ,Voided,Posted,Training
• 
  

Dynamics NAV - LS Retail - Item Import

• Create a Retail Vendor
• From the Vendor Card, go to Vendor Setup to define the file import format (You can copy this from any existing vendor
• Place the file to be imported in the location defined in the vendor setup
• Click Import File
• This will create a file in NAV in       Franchise/Item Import Files
• Select your file, click “Set Status as OK”
• Go to Departments/LS Retail/Replenishment/Item Import/Item Import Journal
• Click Navigate>Get Import Lines
• Select your item import file>Click OK
• Click Navigate>Create/Process
• If there are any errors, you deal with the errors accordingly

Objects used

• Item Import Journal Line (T10012359) - Item Import Journal (P10012359)
• Create/Process Button - C10012352 Item Import Create
• Attribute Setup (T10000788) - Attribute Setup (P10000906)

• After an upgrade, the Hard Attributes from the item import function will no longer work.
• Item Import function must be modified to insert attribute-item assignment instead to Attribute Value (10000786)
• Use Attribute Setup instead of Retail Setup
• Attribute Option Values column names change

Dynamics NAV - LS Retail - Hotkeys do not work. F keys do not work.

For LS NAV 2017, if the hotkeys do not work, you have to set the rows and columns for the Fixed menu. It has to be a "fixed key" menu type.

SQL - How to temporarily pause replication

https://cavemansblog.wordpress.com/2012/03/12/startstop-sql-server-replication-agent/







--STOP the Distribution Agent:

sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db

--START the Distribution Agent:

sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db</pre>










exec distribution.dbo.sp_MSstartdistribution_agent @publisher  = 'PUBLISHERSERVER',

@publisher_db   = 'PUBLISHERDB',

@publication    = 'TABLE_EMPLOYEE',

@subscriber     = 'SUBSCRIBERSERVER',

@subscriber_db  = 'WAREHOUSEDB'

SQL - How to setup Replication

• Prerequisites
• Windows Server 2012
• SQL 2016 Standard
• Machine1 Primary
• Machine2 Secondary
• Create a folder on Machine2 called REPL, share it
• Connect to Machine1>Right click on Replication>Configure Distribution
• Tick It's own distributor
• Set network path to snapshot folder
• leave default distribution database name
• Finish
• Under security>select or create the user account being used for the replication service
• Assign user as db_owner to distribution and company db to replicate
• Publish a db
• Expand Replication>Publication>New Publication
• Transactional - each transaction is sent, only works on tables with primary keys
• Snapshot - entire db is restored over replica
• Select all objects to publish
• Enter your replication account
• Create Publication
• Right Click on Publication>Properties>Add Service account to access list
• 
  
• Connect to Machine2>Expand Replication>Subscription
• Create new subscription,point to publisher
• Choose Pull to allow the subscription machine to do the processing
• Enter name of destination db to replicate into
• Enter your replication account
• Run Continuously
• Initialize Immediately
• Assign service account as db_owner on destination db
• Connect to Machine1
• Right click publication>Snapshot agent status
• After the Snapshot has been generated, Click Start
• 
  
• NOTES
• If new objects are added to the Source database, the Publication needs to be updated to include the new objects as Articles before it can publish them
• If your log agent is not starting, use a different account that has full access to all the databases

LS Retail - Data Director - SQL Timeout, Failed to Insert, Failed to Update

• Causes
• The DBFileSize is too small, and SQL is taking too long to resize itself.
• DD gives up and rolls back
• SQL gives up and doesn't resize
• Infinite loop of trying and failing
• Multiple jobs accessing the same table cause table locks
• Ensure you only have one job per table running at a time
• If a large job fails, it will send a rollback request to SQL which takes forever to execute
• This will generally lock up the table and prevent any further transactions for an extremely long time
• You cannot kill the rollback process
• If you restart the service, the database goes into recovery mode until it clears the rollback, and will then be available again
• DO NOT Increase Data File Size in DD Options
• Controls size of data file generated on local drive
• Max is around 2gb
• DO NOT Increase Message Size in DD Options
• Controls the number of records that can be sent in a single job
• Max is around 2million records
• 
  
• Solution
• Manually resize the database file size by going to properties>file size> Set it to something larger that will not resize constantly (+20gb)
• Use the table filters to reduce the records to around 2 million, or 2gb

Dynamics NAV - Inventory Valuation View - Historical Stock Status Equivalent

USE [CRONUS]
drop table BIT_Temp_InvVal
drop table BIT_Temp_InvVal_CombA
drop table BIT_Temp_InvVal_CombB



DECLARE @D Date Set @D = '07/12/17'
DECLARE @L varchar(5) Set @L = 'WM'

--Precalc CombA
Select * Into BIT_Temp_InvVal_CombA from (
SELECT     [Item No_], [Location Code], CAST(SUM(Q) + SUM(Q2) AS float) AS Qty, CAST(SUM(InventoryValue) AS float) AS InventoryValue
                                                    FROM          (SELECT     [Entry No_], [Item No_], [Location Code], Quantity AS Q,
                                                                                                       (SELECT     ISNULL(SUM(IAE.Quantity), 0) AS EXPR1
                                                                                                         FROM          [CRONUS$Item Application Entry] AS IAE INNER JOIN
                                                                                                                                [CRONUS$Item Ledger Entry] AS ILE2 ON IAE.[Inbound Item Entry No_] = ILE2.[Entry No_]
                                                                                                         WHERE      (IAE.[Posting Date] <= @D) AND (ILE2.[Posting Date] <= @D) AND (ILE.[Item No_] = ILE2.[Item No_]) AND
                                                                                                                                (IAE.[Outbound Item Entry No_] = ILE.[Entry No_]) AND (IAE.[Item Ledger Entry No_] = ILE.[Entry No_])AND [Location Code] = @L) AS Q2,
                                                                                                       (SELECT     SUM([Cost Amount (Expected)] + [Cost Amount (Actual)]) AS EXPR1
                                                                                                         FROM          [CRONUS$Value Entry] AS V
                                                                                                         WHERE      ([Posting Date] <= @D) AND ([Item Ledger Entry No_] = ILE.[Entry No_])AND [Location Code] = @L) AS InventoryValue
                                                                            FROM          [CRONUS$Item Ledger Entry] AS ILE
                                                                            WHERE      ([Posting Date] <= @D) AND (Positive = 0) AND [Location Code] = @L) AS OutBoundEntries
                                                    GROUP BY [Item No_], [Location Code]) as CombA


--DECLARE @D Date Set @D = '07/12/17'
--DECLARE @L varchar(5) Set @L = 'WM'
--Precalc CombB
Select * Into BIT_Temp_InvVal_CombB from (
         SELECT     [Item No_], [Location Code], CAST(SUM(Q) - SUM(Q2) AS float) AS Qty, CAST(SUM(InventoryValue) AS float) AS InventoryValue
                                                    FROM         (SELECT     [Item No_], [Location Code], Quantity AS Q,
                                                                                                      (SELECT     ISNULL(SUM(IAE.Quantity), 0) AS EXPR1
                                                                                                        FROM          [CRONUS$Item Application Entry] AS IAE INNER JOIN
                                                                                                                               [CRONUS$Item Ledger Entry] AS ILE2 ON IAE.[Item Ledger Entry No_] = ILE2.[Entry No_]
                                                                                                        WHERE      (IAE.[Posting Date] <= @D) AND (ILE2.[Posting Date] <= @D) AND (ILE.[Entry No_] = IAE.[Inbound Item Entry No_]) AND
                                                                                                                               (IAE.[Outbound Item Entry No_] <> 0) AND (IAE.[Item Ledger Entry No_] <> ILE.[Entry No_]) AND [Location Code] = @L) AS Q2,
                                                                                                      (SELECT     ISNULL(SUM([Cost Amount (Expected)] + [Cost Amount (Actual)]), 0) AS EXPR1
                                                                                                        FROM          [CRONUS$Value Entry] AS V
                                                                                                        WHERE      ([Posting Date] <= @D) AND ([Item Ledger Entry No_] = ILE.[Entry No_])AND [Location Code] = @L) AS InventoryValue
                                                                           FROM          [CRONUS$Item Ledger Entry] AS ILE
                                                                           WHERE      ([Posting Date] <= @D) AND (Positive = 1)AND [Location Code] = @L) AS InBoundEntries
                                                    GROUP BY [Item No_], [Location Code]) as CombB

--Combine Final Report

select * into BIT_Temp_InvVal from (
SELECT     CRONUS$Item.No_ AS ItemNo, CRONUS$Item.[Vendor No_], CRONUS$Vendor.Name AS VendorName, CRONUS$Item.Description,
                      Detail.[Location Code], CRONUS$Item.[Division Code], CRONUS$Item.[Attrib 4 Code] AS CatReportingGrp,
                      CRONUS$Vendor.[Vendor Posting Group], Detail.Qty, Detail.InventoryValue, ISNULL(ROUND(Detail.InventoryValue / NULLIF (Detail.Qty, 0), 2), 0)
                      AS UnitCost
FROM         CRONUS$Item INNER JOIN
                          (SELECT     [Item No_], [Location Code], SUM(Qty) AS Qty, SUM(InventoryValue) AS InventoryValue
                            FROM          (SELECT * from BIT_Temp_InvVal_CombA
                                                    UNION
                                           SELECT * from BIT_Temp_InvVal_CombB) AS CombinedEntries
                            GROUP BY [Item No_], [Location Code]) AS Detail ON CRONUS$Item.No_ = Detail.[Item No_] LEFT OUTER JOIN
                      CRONUS$Vendor ON CRONUS$Item.[Vendor No_] = CRONUS$Vendor.No_
--ORDER BY ItemNo
) as a
GO

Dynamics GP - Auto Age AR - Automatically Age Receivables

--Set this up as a sql job to run daily

USE TWO
DECLARE @O_iErrorState int, @I_dAgingDate datetime
SELECT @I_dAgingDate = CONVERT(VARCHAR(10), GETDATE(), 102)
EXEC dbo.rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate,
          127, 0, 0, '', @O_iErrorState OUT

SSRS - Value expression error: 'RdlObjectModel' is not a member of 'Reporting Services'

https://stackoverflow.com/questions/25022276/value-expression-error-bc30456-rdlobjectmodel-is-not-a-member-of-reportin

When you copy/paste some functions, the act of pasting resets the name of the function, and adds extra characters.
Go into your formulas, and remove any instances of "Microsoft.ReportingServices.RdlObjectModel.ExpressionParser.VBFunctions."

SQL - Get Time from Datetime or Date field

RIGHT (CAST(dbo.SOP10200.DEX_ROW_TS AS varchar), 7)

Dynamics GP - eOne Smartconnect - GL Transaction

• Do not use a sequence
• Use a column for journal numbers
• Key by journal numbers
• Create Journal - Group by journal numbers
• Tick update existing for add distribution and create journal
• 
  

Dynamics GP - Insert values into Comment Tables. SOP Comment Tables.

insert into sy04200(cmtsries,commntid,cmmttext) values(3,'C6','WRITE OFF')

Tables involved

SOP10106 - Sales Document Comments

SY04200 - Comment ID's

SOP10202 - Sales Line Comments

Dynamics GP - VAT setup for Trinidad

• Setup 4 tax schedules, and corresponding Tax Schedule ID's. Assign each one to itself.
• VATS
• VATP
• NOVATS
• NOVATP
• Company Setup
• Tick Use Shipping Method when selecting default tax schedule
• Set Sales Tax Schedule to VATS
• Set Purchase tax schedule to VATP
• Shipping Methods
• "PICKUP" - Will use tax schedule from SITE
• "DELIVERY" - Will use tax schedule from Ship-To Address ID

If you do not tick "Use Shipping Method"

• Tax is calculated based on Shipping Address and Item, regardless of shipping method

Dynamics NAV-Ls OMNI-Generic Connectors, Web Methods (Not Magento or nopCommerce)

http://mobiledemo.lsretail.com/LSOmniHelp24/html/Methods_T_LSOmni_Service_IeCommerceService.htm

Dynamics NAV - How to get dev keys

• https://businesscenter.mbs.microsoft.com/#
• Developer Tools>License Key configuration
• 
  

Dynamics NAV - Upgrade Checklist from LSNAV 2009R2 to LSNAV 2017

Stuff you need:

• Install media for NAV2009R2 is here 
• Install Media for NAV 2013 R2 is in the install media here (You still need to install this,  you can jump from 2013 to 2017 using the 2015 toolkit objects)
• NAV 2015 install media here (this is not necessary, all you need are the objects in the CU1 download, this is just here for reference)
• Upgrade toolkit for NAV 2015 CU1 is a separate download here (specifically for 2009R2 to 2015 jump)
• Upgrade toolkit for NAV 2017 is in the install media here
• User License Key for NAV 2017 or higher
• Partner License key for NAV 2017 or higher
• If Using LS Retail
• Backup for LSNAV2009
• Partner keys for LSNAV2009 (this key has some objects not available in the 2017 keys)
• Backup for LSNAV2013
• Backup for LSNAV2015
• Backup for LSNAV2017
• Partner keys for LSNAV2017
• LS Upgrade Tools 6.1,6.2,6.3,6.4,7.00.03,7.1to8

Stuff to Read:

• https://blogs.msdn.microsoft.com/nav/2015/02/23/upgrading-from-microsoft-dynamics-nav-2009-r2-or-microsoft-dynamics-nav-2009-sp1-to-microsoft-dynamics-nav-2015/
• https://msdn.microsoft.com/es-es/library/dn271668(v=nav.71).aspx

Hardware Requirements:

• https://msdn.microsoft.com/en-us/dynamics-nav/system-requirements-for-microsoft-dynamics-nav
• 
  
• NAV 2017 requires 64-bit OS
• Server: Windows Server 2012 R2 +
• Client: Win 8 pro +
• DB: SQL 2012 SP2 +

Upgrade Steps
https://msdn.microsoft.com/en-us/library/hh169484(NAV.70).aspx
https://msdn.microsoft.com/en-us/dynamics-nav/upgrading-the-application-code
https://msdn.microsoft.com/en-us/dynamics-nav/upgrading-the-data

• NAV2009 to NAV2013 on SQL2008 - use guide in NAV2013 UpgradeToolkit
• NAV2013 to NAV2017 on SQL2016 - use links above
• POS UPGRADE
• In the 2009 Dev Environment, Backup the POS database as an FBK
• In 2009  Dev, create a new SQL company database
• Launch 2009 Dev, Login to blank database, Tools>Restore fbk
• ~ 1 hour
• Set a new password Tools>Security>Password
• Set a new password Tools>Security>Logins>Create SA>Give Role SUPER
• Tools>Security>Synchronize all logins
• Backup your SQL database
• CODE UPGRADE
• If using LS Retail
• Install 2017 LS Toolboxes
• Install 2017 Data Director
• Tick top 2, and bottom 2 options
• Create Folder structure E:\Upgrade
• \Final
• \Modified
• \Original
• \Result
• \Target
• Install NAV2009R2 Demo
• Create a new blank company (to ignore the demo data)
• Import LS2009 if using LS Retail into the blank company
• Launch Dev Client>Connect to Demo>Export all Objects from new company to a single text file called "OldBaseVersion.txt" in Original folder
• If using LS Filter out OM and POS Weighing is required
• <11000010|99000749..99001849|>99001850
• Launch Dev Client>Connect to your company>Export all Objects to a single text file called "OldCustomVersion.txt" in Modified folder
• Install NAV2017 Demo
• Create a new blank company (to ignore the demo data)
• Import LS2017 if using LS Retail
• Launch Dev Client>Connect to Demo>Export all Objects to a single text file called "NewBaseVersion.txt" in Target Folder
• Merge Code
• Launch NAV2017 Development Shell
• Change to E:\Upgrade, run script to output to Result folder
• cd E:\Upgrade
• Merge-NAVApplicationObject -OriginalPath .\ORIGINAL -TargetPath .\TARGET -ModifiedPath .\MODIFIED -ResultPath .\RESULT 
• Run the Join to combine all text files into one file per object type to import in Final folder

Join-NAVApplicationObjectFile -Source E:\Upgrade\RESULT\COD*.txt -Destination E:\Upgrade\FINAL\all-cod.txt
Join-NAVApplicationObjectFile -Source E:\Upgrade\RESULT\MEN*.txt -Destination E:\Upgrade\FINAL\all-men.txt
Join-NAVApplicationObjectFile -Source E:\Upgrade\RESULT\PAG*.txt -Destination E:\Upgrade\FINAL\all-pag.txt
Join-NAVApplicationObjectFile -Source E:\Upgrade\RESULT\QUE*.txt -Destination E:\Upgrade\FINAL\all-que.txt
Join-NAVApplicationObjectFile -Source E:\Upgrade\RESULT\REP*.txt -Destination E:\Upgrade\FINAL\all-rep.txt
Join-NAVApplicationObjectFile -Source E:\Upgrade\RESULT\TAB*.txt -Destination E:\Upgrade\FINAL\all-tab.txt
Join-NAVApplicationObjectFile -Source E:\Upgrade\RESULT\XML*.txt -Destination E:\Upgrade\FINAL\all-xml.txt

• Find and replace all references to Forms (check Tables file)
• Find "LookupFormID=Form"
• Replace with "LookupPageID=Page"
• Find "DrillDownFormID=Form"
• Replace with "DrillDownPageID=Page"
• Find ": Form "
• Replace with ": Page"
• Compile final code in Demo company
• Import and all-codeunits.txt to NAV2017 demo company
• Test and Resolve Conflicts
• https://msdn.microsoft.com/en-us/dynamics-nav/how-to--compare-and-update-application-object-source-files
• For each file in your E:\Upgrade\Result\ConflictModified folder 
• Review the object to confirm if the merge was done correctly
• Code comparisons are included in the cal code with {>>>>>>>} characters
• Large blocks of code than could not be merged will be commented out, take special notice of these
• Ctrl+Shift+O to uncomment
• Make any adjustments as necessary
• Save without compiling
• Fix each object and export it to track your changes
• Add any documentation and comments if required
• Compile all objects, some will not compile
• Keep compiling until only real errors remain, deal with them
• Some globals do not come across, add them
• Some functions are deprecated, remove them
• Force Compile
• Export all finalized objects as all-objects-final.txt
• DATA UPGRADE
• Attempting to do the data upgrade from LSNAV2009 to LSNAV2017 is extremely difficult due to the need to pass through 2013 and 2015, and properly upgrade the code and drop and reload data through each version
• We did not have success with this method
• Upgrade Method 2 - Data Migration
• The upgrade process requires you to drop data out of some fields and set them to '' or 0
• This means at some point, you will have to reimport the data anyway
• You may as well just export all data, then import to the final version, with whatever changes need to be made
• Codemerge LSNAV2009 and LSNAV2017 to get final code
• Create Blank LSNAV2017 company 
• Restore NAV DB, import localizations
• Attach to server instance
• Compile final code
• Create new company, delete Cronus when not needed
• Setup DD sync jobs to pull all data from LSNAV2009 database
• Use Field lists and filters to manage table differences
• Use Linked tables to connect all related data
• Redo all customizations in LSNAV2017 where necessary
• Redo all configurations in LSNAV2017 where necessary

Dynamics NAV LS Retail - LS OMNI - How to change the company LS Omni is pointing to

• Re-Run the C:\NAV\LS Omni\LSOmni2.3.2\LSOmniServer\LSOmni.server.setup.2.3.2
• This will create the required objects in the new db
• If the objects are already created, you can just adjust the appsettings.xml file and change the path info

• Navigate to C:\LS Retail\LSOmni\LSOmniService\appsettings.xml (.config) and open in notepad (keep an original copy)
• Change the value string to your new company string
•   <add key="BOConnection.Nav.Url" value="http://nav2017devsrv:7047/DynamicsNAV100/WS/CRONUS%20LS%20100100%20W1%20Demo/Codeunit/RetailWebServices"/>
• 
  
• Change the NAVCompanyName string to your new company name string
•   <add key="SqlConnectionString.Nav" value="Data Source=NAV2017DEVSRV\NAVDEMO;Initial Catalog=w1-ls-nav-10-01-00-release;User ID=LSOmniUser;Password=LSOmniUser;NAVCompanyName=CRONUS LS 100100 W1 Demo;Persist Security Info=True;MultipleActiveResultSets=True;Connection Timeout=10;"/>  

• Restart the machine, or all services (SQL, IIS, Windows, NAV)
• Confirm web services available for LS Cronus company here
• http://nav2017devsrv:7047/DynamicsNAV100/WS/CRONUS%20LS%20100100%20W1%20Demo/Codeunit/RetailWebServices
• 
  
• Confirm *.svc handlers exist in iis - Handlers
• 
  
• Confirm Ping
• http://nav2017devsrv/LSOmniService/json.svc/ping
• 
  
• 
  
• Synchronize data from the new company into LS OMNI
• http://help.lsnav.lsretail.com/Content/LS%20Omni/Step%20By%20Step%20Setup/Step3%20Configuration%20in%20NAV/Data%20Replication.htm
• Create all of the sync jobs by running page "WI Default Data"
• Create jobs and subjobs for mobile loyalty
• Start data Director Service
• Create NAV and SQL user account used for distribution communications
• Configure Distribution Locations
• HO - Main db
• M_MOB - OMNI db
• Run M_2mobile job
• Configure NAS to schedule M_MOB_Actions job

LS Nav - Enable Web services for retail functions - AR Credit Check

• POS Functionality Profile
• Tick which services to enable web services for, select the location to query
• Go to Web Requests
• Add "Customervalidation" to the web request

Dynamics GP - LS NAV - AR Credit Limits on POS don't work

Synchronize the Transaction Status Table to all POS machines
T99001493
T99001494

OR

Enable Web requests for customer validation to allow the pos to check the head office for credit validation

• Go to Functionality Profile>Web Requests
• Add "Customervalidation" to the web request

Dynamics NAV - "Unit of Measure does not exist" error when calculating warehouse movements for inventory journals

This is happening because a transaction exists in the whse journals with no uofm


update  [CRONUS].[dbo].[CRONUS$Warehouse Entry] set [Unit of Measure Code] = 'EA' where [Unit of Measure Code] = ''


update [CRONUS].[dbo].[CRONUS$Warehouse Journal Line] set [Unit of Measure Code] = 'EA' where [Unit of Measure Code] = ''

Dynamics GP - Field Service - How to find out Which User Created/Modified an Equipment ID?

Activity Tracking does not track any of the Field Service Tables.
The only thing it will track is opening and closing of the windows.

This table has a log of equipment number creation / modification.
It will only track changes to the reference number, and no other fields.
It does not track the value of the reference numbers, only whether or not it was changed.

select * from svc00310

or

SELECT        EQUIPID, LNITMSEQ, DATE1, TIME1, USERID, From_Customer_Number, To_Customer_Number, From_Serial_Number, To_Serial_Number, From_Item_Number, To_Item_Number, DSCRIPTN, DEX_ROW_ID
FROM            SVC00310

Samsung un40f5500af - Turns off by itself.

Left the TV off for a few hours
Changed the batteries in the remote
It came back on all by itself
Not sure if it was trying to update itself, it says it has firmware 2207.
I'm not sure what the latest version is supposed to be, but it also says the last time it checked for updated was 2015

Dynamics GP - Purchase Receipt does not post correctly, causes PO to think it's received, but there is no stock

Situation: PO has been created, PO receipt is done and posted, but posting fails. The PO line says it's completely received, the receipt transaction is corrupted, and there is no inventory available from the receipt.

Solution:In this case, the PO receipt actually did not post, and we just have to recover the corrupt transaction and delete it.

--Get the Receipt number for the PO
select * from pop10310 where ponumber = 'PO000000000022484'

--Get the Receipt Header
select * from pop10300 where poprctnm = 'GSR00000000068562'

--Update the VENDORID and POPTYPE = 1 if they are blank to allow you to access the transaction in the receiving work screen
--Get the vendor id
select vendorid from pop10100 where ponumber = 'PO000000000022484'

update pop10300 set VENDORID  = 'APAS01', POPTYPE = 1 where POPRCTNM =  'GSR00000000068562'

After running the scripts, pull up the transaction in the purchase receipt screen and delete it.
This should free up the PO and allow you to receive the lines properly.

Check Network Traffic and Connectivity issues - Microsoft Nework Monitor, Microsoft Message Analyzer

https://www.microsoft.com/en-us/download/details.aspx?id=4865


Use Filters
https://blogs.technet.microsoft.com/rmilne/2016/08/11/network-monitor-filter-examples/

Property.Source >= "MYPC" and Property.Source <= "MYPD"



A Newer version of this program with better message reporting is available here
https://www.microsoft.com/en-us/download/details.aspx?id=44226



Or you can also use Wireshark
https://www.wireshark.org/download.html

Dynamics GP - You cannot receive this purchase order line item because it hasn't been encumbered by encumbrance management. Please encumber the purchase in the purchase order entry window.

There is nowhere on the purchase order entry window to physically encumber this.

• Encumbrance occurs when any one of the following occurs
• Workflow approves PO line
• PO approval Approves PO line
• Mass Encumbrance processes PO line

Problem: The ENC10110.ENCBSTAT is set to 4, and is waiting for Approval or workflow, but neither of these are enabled, so the line can never be encumbered.

Solution:Set the ENC10110.ENCBSTAT to 2 to enable it in the Mass purchasing>transaction>Mass Encumbrance screen to complete the approval before it can be received.

 --Use this to get the dex_row_id's for the lines that cannot be received

select * from enc10110 where ponumber = ' PO00293'

--Use this to update the lines to enable them in the Mass Encumbrance window for processing

--Change the dex_row id's to match the lines that cannot be received

update ENC10110 set ENCBSTAT = 2 where DEX_ROW_ID in (298800,298811)

Dynamics GP - Instructor Training Manuals

https://mbs.microsoft.com/partnersource/northamerica/readiness-training/instructor-training-materials/

Dynamics GP - Encumbrance Tables

https://dyndeveloper.com/DynTable.aspx?ModuleID=ENC

• ENC10110 - Encumbrance - PO Lines
• ENCBSTAT
• 1-Pre-Encumbered - After approved or mass encumbered
• 2-Invalid - Default setting. Displays trx in Mass encumbrance for encumbrance.
• 3-Blanket Control - Used for blanket control lines
• 4-Pre-Budget - Default setting before approval if approvals in use
• ENC10500 - Encumbrance Received Trx

Dynamics NAV - LS Retail - How to setup Object Transfer

• Ensure your object is compiled
• Search for Object Transfer
• Create a new object transfer for your objects
• Confirm it
• Search for Jobs
• Create a Job 
• with type "Object Replication"
• using codeunit 99001475
• set your location destinations
• Under object replication fasttab, add your object job
• set a schedule, or click run
• If you have multiple versions of NAV installed, you may get the error:
• [22924070] The database on the server cannot be opened by this version of the Microsoft Dynamics NAV Development Environment. The database has already been converted by a newer version.
• You must upgrade the Microsoft Dynamics NAV Development Environment to the latest version to open the database.
• 
  

Dynamics GP - Last time an SOP Transaction was saved/printed/accessed

The DEX_ROW_TS will update to the current time every time a record is saved, printed or otherwise accessed in GP.

Dynamics GP - eOne Extender - Extender Windows do not open after importing solution

Check the DYNAMICS..EXT00001

It should have an entry for each object.

If it is missing ,create the required records for the company you're in

Dynamics GP - SQL View - Receipt PO Numbers for posted and unposted receipts

/** This view will get the min PO number from the posted and unposted receipts
**** Object:  View [dbo].[BI_PORcts]    Script Date: 9/28/2017 10:25:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_PORcts]
AS
SELECT        POPRCTNM, PONum
FROM            (SELECT        POPRCTNM, MIN(PONUMBER) AS PONum
                          FROM            dbo.POP30310
                          GROUP BY POPRCTNM
                          UNION
                          SELECT        POPRCTNM, MIN(PONUMBER) AS PONum
                          FROM            dbo.POP10310
                          GROUP BY POPRCTNM) AS RctPO

GO

eOne Smartview - Smartview tables - security access to smartview favorites, lock or unlock favorites

Sometimes, if you accidentally remove access to SA for a smartview, you can never get back in to see it again.

You can manually update the security tables to give access again

You can also use the SV00108 table to force-lock favorites to prevent anyone from making changes.

• dynamics.dbo.sv00100 - Favorites index
• dynamics.dbo.sv00101 - Favorites layout
• dynamics.dbo.sv00102 - Favorites restrictions
• dynamics.dbo.sv00103 - Favorites grouping
• dynamics.dbo.sv00104 - Favorites to Company link
• dynamics.dbo.sv00105 - Favorites security roles (insert security records here to grant access)
• dynamics.dbo.sv00108 - Favorite Lock (Put your favorite id here to lock it from changes, remove it to allow changes)
• dynamics.dbo.sv00110 - Favorites status
• 
  

Dynamics NAV - Export and Import or backup and restore a single company in a database with multiple companies

https://msdn.microsoft.com/en-us/library/dn789595(v=nav.90).aspx


Go To Cronus/Departments/Administration/IT Administration/General

• Export to a Data file - Export company to a file
• Import from a Data File - Import from a file

Dynamics GP - Smartlist Builder - Go To Triggers do not fire in the correct order

Sometimes certain scripts run when the window opens, causing unpredictable behaviour with the way the go to triggers will submit data to the open window.

Most times, if you leave the window open and click the go to a second time, the trigger will work as expected since the code that runs on window open has already run.

• If using a macro, ensure there is one blank line at the end of the macro to ensure the last macro line fires.
• when using a macro, smartlist builder does not wait for the macro to finish before moving on to the next step, which causes conflicts if the macro is affecting fields that smartlist builder is trying to update.
• The wait command stops the entire string of commands, and does not wait after each step
• Either do not use macros, and try to use smartlist builder tasks, or only use macros as the last step

Dynamics GP - SOP User Defined Fields Table

select * from sop10106

Dynamics NAV - How to login to nav on a domain using a local account

On the local account, use Windows Credential Manager and add the NAV windows domain account and the NAV address. This will allow the local user to map to the domain NAV user seamlessly.

Dynamics GP - Receivings Transaction does not print - Receivings Edit List, POP Receivings Posting Journal

ASSESSMENT: Is the report you are printing modified or default?:

- if it is a modified report, please use the Alternate/Modified Forms and Reports window (Administration | Setup | System | Alternate/Modified Forms and Reports) to temporarily switch to the default report when testing the issue.

If the default report prints fine it tells you there is something incorrect with the modification (possibly the template) that is causing issue and we can troubleshoot in this direction on next contact.

Regarding Templates, please insure that 'Standard' is the selection in the Report Destination screen and you are also printing the Edit List out to the screen in your testing.


- If not modified there is a chance that prior temp/cache information may be interfering with the new printing if it is linked to the User/Report in question in some manner (which will not populate the Process Monitor.

To help insure this is not the case please perform the following before re-testing the issue:
1. Insure ALL USERS are out of Dynamics GP.
2. Log into SQL Server Management Studio on the SQL Server housing Dynamics GP.
3. Make a new backup of the erring company db.
4. Run the following SQL commands against the erring SQL instance:

DELETE DYNAMICS..ACTIVITY
DELETE DYNAMICS..SY00800
DELETE DYNAMICS..SY00801
DELETE TEMPDB..DEX_LOCK
DELETE TEMPDB..DEX_SESSION

5. Right-click and stop | start the SQL instance, which will clear the rest of the temp/cache materials in the system from past processing.
6. Log into Dynamics GP as the 'sa' user and retest the issue.

 ------------------------------------------------------------------

We have attempted these solutions, but the issue persists.

• We are using the standard print, not the word templates
• The receiving edit list is not modified
• The live and TEST companies use the same dictionary, and same report, and it prints In the test company,  but not the live
• We also tested by using a clean gp client install and clean dictionary with the same issue
• We ran the script and restarted the server
• We ran a complete check links and reconcile
• We logged in and tested the print as sa, but the report does not print

-------------------------------------------------------------------

After a few days , the issue went away by itself, and the receivings edit list started printing again.

The only solutions we could think would be

• Some corrupted transaction was deleted, posted or processed to allow the printing to continue
• Some nightly process ran which allowed the printing to continue

Dynamics NAV - Restore NAV company to a new SQL Server - Setup test instance on different domain

Related Errors:
The Microsoft Dynamics NAV server is currently not ready to serve requests. Try again later or contact your system administrator.

• After installing Nav, ensure that all service account information remains unchanged until you can successfully login to your restored database
• Backup and restore company database to new server
• Whatever account you install nav with, you must reset the sql security for that account
• The NT AUTHORITY\NETWORK SERVICE security info will be incorrect when coming from a restore
• If you are using the network service account for your install, you must go to SQL Manager>Security>Logins>NT Authority\NETWORK SERVICE 
• Remove the default user mapping (to remove old network user credentials)
• re-add the user mapping
• Add your new administrator account to sql
• Add it to the db_owner role
• 
  
• Ensure NAV services are configured to use new accounts
• Use the SQL full name and user full domain names, avoid using . or localhost
• Set new admin account as db_owner under Security>Logins>User>Properties>User Mapping
• Drop all users to allow you to login with new admin account

------------------------------------------------------------------------
USE [MyDB]

GO

delete from [dbo].[User]
delete from [dbo].[Access Control]
delete from [dbo].[User Property]
delete from [dbo].[Page Data Personalization]
delete from [dbo].[User Default Style Sheet]
delete from [dbo].[User Metadata]
delete from [dbo].[User Personalization]
---------------------------------------------------------------------------

• Grant full access to your new admin user

USE [master]

GO

CREATE LOGIN [navdemo1\cloudadmin] FROM WINDOWS 

CREATE USER [navdemo1\cloudadmin] FOR LOGIN [navdemo1\cloudadmin] 

GRANT SELECT ON [master].[dbo].[$ndo$srvproperty] TO [navdemo1\cloudadmin]

GO

USE [CRONUS]

GO

CREATE USER [navdemo1\cloudadmin] FOR LOGIN [navdemo1\cloudadmin]

ALTER ROLE [db_owner] ADD MEMBER [navdemo1\cloudadmin]

GRANT VIEW DATABASE STATE TO [navdemo1\cloudadmin]

• Restart SQL Service
• Restart NAV Services
• 
  

Dynamics GP - SQL View - Location Lookup

select locncode, locndscr, rtrim(locncode) + ' | ' + rtrim(locndscr) as loclbl from iv40700

Dynamics GP - Extended prices not working after importing prices to table

This happens because the extended pricing tables look for the EXACT sheet id value, including all of the white spaces after the id.

You need to run this script to fill in all the white space in all the extended pricing tables to ensure everything matches up.


update sop10110 set prcshid = left(prcshid + '               ',15)
update rm00500 set prcshid = left(prcshid + '               ',15)
update iv10401 set prcshid = left(prcshid + '               ',15)
update iv10402 set prcshid = left(prcshid + '               ',15)

Skype - Recording session stuck on pending

• Plug your laptop in.
• Click the three dots on bottom right>Manage recordings
• The recording will start to process
• The default location is C:\Users\Username\Videos\Lync Recordings

Dynamics GP - Myridas Catchweights

Overview

• Catchweights should be used for items where quantities of items may be non-standard weights, for example chickens
• Each chicken is a different weight
• Customers order Qty of Chicken, and pay by the total weight
• System must be able to track 10 chickens, but also track the total kg's of actual weight sold
• System will sell at estimated weight per chicken and then adjust based on user entry at point of sale
• Not compatible with Drop-Ship or Manufacturing

System Setup

• Tools >> Setup >> m-hance >> Catchweights >> Catchweight Setup
• Define default % Variance accepted between estimated and actual
• Enable Catchweight UofM and Weight details on Item Description prints
• 
  

Setup Catchweight UofMs

• Cards >> m-hance >> Catchweights >> Catchweight Maintenance
• Enter conversion rate of pieces to weight
• Dual Quantities
• Tick the dual quantities to track stock in both the Base uofm and the additional Uofm's setup (Eg. Base is in Kilos, other units can be EACH or CASES)
• System will track the stock independently
• Stock counts and transactions will require you to enter KG AND EACH qtys
• Non-Dual Quantities means only base uofm is tracked for stock, and all other quantities will be calculated based on estimated values of uofm conversion

Item Setup

• Assign Catchweight to Items
• Cards >> m-hance >> Catchweights >> Assign Catchweight to Item Number
• Can also use the Assign items to Catchweights-Ranges
• Assign Individual Catchweight % to Items (optional)
• Cards >> m-hance >> Catchweights >> Assign CPV to Item Number

POP Entry

• On each line item entry, Catchweight screen prompts for Catchweight Qty, and defaults estimated Weight.
• PO's usually are done for the estimated weight

PO Receipt

• On each line item entry, Catchweight screen prompts for Catchweight Qty, and defaults estimated Weight.
• Actual weight is entered on receipt

SOP Invoice

• On each line item entry, Catchweight screen prompts for Catchweight Qty, and defaults estimated Weight. User can adjust the actual weight.

Dynamics GP - Returns Management - RMA Type Selected is invalid for this process

• To create a return from an SOP document go to
• Sales Transaction Entry>Additional>Create Return
• If you get the error message "RMA Type Selected is invalid for this process" untick the "Create RMA" box
• To permanently remove this default check 
• Go to the Registration window from Tools >> Setup >> System >> Registration
• Uncheck the Returns Management module
• This will prevent you from using the actual Field Service Returns Management, and limit the functionality to only SOP returns
• Click OK

• Select Return Type ID
• Select Customer
• Select Posted SOP Document to return
• Select Items to Return
• Adjust quantities to return in necessary in the "Return Quantity" line item field
• If you have already returned against this sop document, you will get a warning, but it will not stop you from returning against it again

Dynamics GP - Payroll - How to create a pay run

eOne Smartview - Users cannot see the Smartview menu button to launch Smartview

After installing Smartlist Builder and Smartview as administrator, confirm they are working by logging in as sa.
For all other regular users,

Create a security task for

• Product: Smartview
• Type: Windows
• Series: Financial
• Tick Smartview

Also ensure that you enable the Smartlist builder security to access SQL data smartlists under

• Product: Smartlist Builder
• Type: Smartlist Builder Permissions
• Series: Smartlist Builder
• Tick View Smartlists with SQL Tables

Windows - See what programs or processes are using or locking a file

https://superuser.com/questions/117902/find-out-which-process-is-locking-a-file-or-folder-in-windows

For Windows 7, 8 and 10 you can use the built-in Resource Monitor for this.

1. Open Resource Monitor, which can be found
   • By searching for resmon.exe in the start menu, or
   • As a button on the Performance tab in your Task Manager
2. Use the search field in the Associated Handles section on the CPU tab
   • Pointed at by blue arrow in screen shot below