Wednesday, December 13, 2017

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.

Tuesday, December 12, 2017

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

Saturday, December 2, 2017

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

Friday, December 1, 2017

Dynamics GP - SSRS - Migrate SSRS Objects to new Server




Saturday, November 25, 2017

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  SUPERPHARMTT/Franchise/Item Import Files
  • Select your file, click “Set Status as OK”
  • Go to SUPERPHARMTT/Departments/LS Retail/Replenishment/Item Import/Item Import Journal
  • Click Navigate>Get Import Lines
  • Select your item import file>Click OK
  • Click Create/Process
  • If there are any errors, you deal with the errors accordingly

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.

Thursday, November 16, 2017

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'

Wednesday, November 15, 2017

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

Wednesday, November 8, 2017

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

Tuesday, November 7, 2017

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

Monday, November 6, 2017

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)

Friday, November 3, 2017

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

Thursday, November 2, 2017

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

Monday, October 30, 2017

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
    • Should always be "PICKUP"

Tuesday, October 24, 2017

Dynamics NAV - How to get dev keys


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:
  • 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

Monday, October 23, 2017

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 and open in notepad
    • 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

Saturday, October 21, 2017

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 Web Requests
    • Add "Customervalidation" to the web request

Tuesday, October 17, 2017

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] = ''

Tuesday, October 10, 2017

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

Sunday, October 8, 2017

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

Friday, October 6, 2017

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.

Thursday, October 5, 2017

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/

Monday, October 2, 2017

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

Friday, September 29, 2017

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

Thursday, September 28, 2017

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


Wednesday, September 27, 2017

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

Friday, September 22, 2017

Thursday, September 21, 2017

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

Wednesday, September 20, 2017

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

Friday, September 15, 2017

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

Wednesday, September 13, 2017

Dynamics GP - SQL View - Location Lookup

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

Monday, September 11, 2017

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

  • POP Entry

    • 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.

    Saturday, September 9, 2017

    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

    Wednesday, September 6, 2017

    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



    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

    Monday, September 4, 2017

    Dynamics GP - "Remember User" has been checked, and user login screen no longer appears

    • GP>Tools>Setup>System>System Preferences
      • Untick Enable Remember User
    • Disable the function by editing the dex.ini
      • RememberUser=False
      • If it is not there then add this Switch to your Dex.ini and open GP again. It works for sure.
    Related: Remember this company is marked
    • delete from SY01402 where sydefaulttype = 70 and userid = 'youruser'

    Dynamics NAV - LS Retail - Custom Gift Card Import

    SPL will send list of gift card numbers for $50 and $500 cards to be uploaded into system
    The numbers are based on cards physically printed with barcodes

    Clear all values from T50008 before starting, or they will be re-uploaded
    Copy Number, Initial amount, Used Amount into Table 50008 Gift Card Staging

    Modify Codeunit 50009 Proces Staging Gift Cards, change "Receipt" variable to increment xx number by one 00000INITxx00000000

    Run C50009

    Gift card entries will be created with incrementing numbers

    Check T990015558 POS Data Entry to confirm gift cards imported correctly
    Filter by "Created by receipt No." it will contain the INIT receipt numbers

    Check T99001467 Voucher Entries to confirm gift cards imported correctly
    Filter by "receipt No." it will contain the INIT receipt numbers

    ---------------------------------------------------------------------------------
    SELECT     [CRONUS$POS Data Entry].[Entry Type], [CRONUS$POS Data Entry].[Entry Code], [CRONUS$POS Data Entry].Amount,
                          [CRONUS$POS Data Entry].[Applied Amount], [CRONUS$POS Data Entry].[Created by Receipt No_] AS RctNum_POSDataEntry,
                          [CRONUS$Voucher Entries].[Receipt Number] AS RctNum_VchEntry, [CRONUS$POS Data Entry].[Date Created],
                          [CRONUS$POS Data Entry].[Expiring Date]
    FROM         [CRONUS$POS Data Entry] LEFT OUTER JOIN
                          [CRONUS$Voucher Entries] ON [CRONUS$POS Data Entry].[Created by Receipt No_] = [CRONUS$Voucher Entries].[Receipt Number]
    WHERE     ([CRONUS$POS Data Entry].[Created by Receipt No_] LIKE N'%INIT11%')

    Friday, September 1, 2017

    Dynamics NAV - Bin Transfer - Warehouse Movement


    • Use a Reclassification Journal
      • Whse. Reclassification Journal
      • Get Bin Content to get all quantities in specified bins


      • Or Use a Warehouse Movement
        • Create Warehouse Movement Template
        • Create Movement Worksheet
        • Get Bin Content to get all quantities in specified bins
        • Create Configuration Package for Table Whse. Worksheet Line (7326)
          • Set NAME filter to Worksheet Template Name
          • Export to excel
          • Update bin Code
          • Import and Apply
        • Movement Worksheet > Create Movement
          • Print Report
        • Movements>Register Movement

      Dynamics NAV - NAS Service or Web Services do not start, and are set to stopped

      This happens when the NAS services are trying to start, but the SQL service has not started up properly as yet.

      To resolve this issue

      • Set the NAS and Web Services to Delayed Start
        • This will delay the start of these programs to 2 minutes after the last automated program has started it's startup process
          • HKLM\SYSTEM\CurrentControlSet\services\\AutoStartDelay decimal number of seconds to wait
      • Set the services>Recovery>Restart after subsequent failures
        • This will keep trying to restart the service and not stop after 3 fails