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

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

If numbers need to be reloaded, delete all entries in the T990015558 and T99001467 for that init number, and reimport all
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
--delete
FROM [POS Data Entry] where [Created by Receipt No_] like '00000INIT14%' and [Applied Amount] = 0
SELECT *
--delete
FROM [Voucher Entries] where [Receipt Number] like '00000INIT14%' and [Voucher No_] not in ('20170811349')
---------------------------------------------------------------------------------
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%')

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

Smartconnect and GP - Limitations

This is a list of integrations that cannot be done, or are not straightforward when using smartconnect and GP

Dynamics GP - How to Remove GL Transactions manually without closing the year (testing and development only)