Tuesday, June 27, 2017

Dynamics GP - Setup PO Approvals - Purchase Order Enhancements

Do not confuse this with Workflow Purchase Approvals, which is a completely different module that allows users to approve via e-mail.


Review this article
https://support.microsoft.com/en-us/help/934662/error-message-when-you-try-to-activate-the-purchase-order-approvals-fe
Run this to ensure no transactions have blank users.
This will prevent po approvals from enabling.
------------------
update pop10100 set USER2ENT = 'sa' where USER2ENT = ''
------------------


  • Go to GP>Tools>Setup>Purchasing>PO Enhancements
  • Tick Activate approvals
  • Click Approval Setup
  • Set sa for unlimited approval
  • Configure approvers accordingly


Thursday, June 22, 2017

Dynamics NAV - LS Retail - The POS Data Entry already exists. Identification fields and values: Entry Type := 'GIFTCARDNO', Entry Code='xxx'

Cause:
Possible data corruption from disconnection or bad sync

Resolution:
Clear the corrupted records.
Re-synchronize the table

Delete FROM [POS Data Entry] where [Entry Code] = 'xxx'


 

Wednesday, June 21, 2017

Dynamics NAV - LS Retail POS - Pharmacy - "POS Prescription Order Posting already exists."

Cause:
This happens when a prescription has had lines against it in the POS, but is then deleted or removed at the store level.
This results in orphan lines with references to a prescription that does not exist, and causes the POS to crash when attempting to exit the prescription from the POS.

Resolution:
Update the POS Trans. Line table, remove any references to the offending Prescriptions in the Prescription Order no. column.
Once removed, you will be able to void the pos lines as regular transactions.

-----------------------
  update [CRONUS].[dbo].[CRONUS$POS Trans_ Line]
  set [Prescription Order No_] = ''
  where [Prescription Order No_] = 'PRPO095043'
-----------------------

Tuesday, June 20, 2017

Dynamics GP - Purchasing - Blanket Orders

You can only have one item per blanket order.
The first line represents the total amount to be ordered.
All other lines represent the dates and quantities of receipts.


  • Create Blanket Purchase Order
  • Enter Line item for total amount to be purchased
  • Click the "Blanket" button in the toolbar
  • Enter the breakdown schedule for the quantities and dates of expected receipts

Dynamics GP - Purchasing - Drop Ship Process

There are multiple ways to use the drop-ship functionality, this is just one of the ways


  • Create Sales Order
  • Enter Line Item
  • Back Order Quantity
  • Tick Drop Ship (D Column) in the line item
  • Click Actions>Purchase to Generate PO
    • Will generate drop ship po to customer address with sales document commitment link
  • On PO Screen, click Actions>Invoice
  • Post Invoice
    • This fulfils the sales order
  • Transfer Sales Order to Sales Invoice
  • Post Invoice

Saturday, June 17, 2017

Data Director - Error: Table [$Item] Not found in Database [POS]

Cause:
A previous action or job is locking the specific table in question, causing DD to stop all pending jobs.
If you attempt to cancel the job from head office, it does not cancel.


Resolution:

  • You can only cancel the errant job from the head office after all destinations have been restarted or DD service restarted
  • Restart Machine or Data Director service on terminal
    • Login to machine
    • Run Data Director Configuration Tool as Administrator
    • Connect to terminal
    • Stop service
    • Start Service
    • Repeat for all offending terminals
  • Cancel the job on just this terminal
    • Run Job Monitor
    • Connect to terminal
    • Select the error job on top
    • Select the error terminal on the bottom
      • Right click>Cancel job on Destination>This Only
  • OR Cancel the offending job on all hosts (this will only work for the terminals that have restarted the DD service or machine)
    • Run Job Monitor
    • Connect to head office
    • Select the error job on top
      • Right click>Cancel job on All Hosts

Wednesday, June 14, 2017

eOne Smartconnect - Input variable contains a duplicate document (IVDOCNBR)

Other Errors: The Lot quantity passed in is <> to the transaction quantity
This error occurs during Purchase Receipt Imports, or Item Transaction imports

Cause:
The integration is creating the header first, and when it attempts to create the line, it detects the header already exists and fails.
By default, it attempts to create a new document instead of update the existing document.
The document numbers may genuinely already exist, ensure that your map is pointing to the correct company. Check the tables for any duplicate document numbers you may have already imported.

Solution:

  • If there is a flag to update lines, this should be marked
  • Key Field should be: Document Number
  • Include a LineItemSequence
  • Change the order of integration to Lots>Lines>Transaction Header


If all else fails, use Integration Manager.

Windows 10 - Prevent new windows from stealing focus

https://community.spiceworks.com/topic/1749029-program-to-prevent-any-windows-window-from-taking-precedence-focus


  • Windows>Run>Regdit
    • HKEY_CURRENT_USER\Control Panel\Desktop
      • Edit UserPreferenceMask value to be 9F 3E 07 80 12 00 00 00
      • (there are many numbers in here, I only had to update the 9F 3E 07 parts and the rest of the value remained)
    • HKEY_CURRENT_USER\Control Panel\Desktop
      • Edit ActiveWndTrkTimeout, change to 150 in decimal
    • HKEY_CURRENT_USER\Control Panel\Desktop 
      • Edit ForegroundFlashCount, change to something like 7000 in decimal
  • Optional, not sure if this does anything
    • HKEY_CURRENT_USER\Control Panel\Desktop 
    • Edit ForegroundLockTimeout to something very large (e.g. 9999999 in decimal)
  • Restart

Windows 10 - Disable Taskbar thumbnail hover preview

https://www.windowscentral.com/how-disable-taskbar-thumbnail-preview-windows-10


  • Windows>Run>Regedit
  • Browse to:
    • HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced
    • Right-click on the right side, select New, and click on DWORD (32-bit) Value.
    • Enter the name as ExtendedUIHoverTime and press Enter.
    • Double-click ExtendedUIHoverTime and change its value to 30000 (Leave it on hexadecimal)
    • Click OK. Close the Registry. Restart your computer to complete the task.
  • I had to disable the "Activate a window by hovering over it with a mouse" option because this seems to break a lot of the Windows 10 right click and popup menus
    • Click Windows>Search>Ease of Access
    • Make the mouse easier to use
    • Untick  "Activate a window by hovering over it with a mouse" 

Windows 10 freezes, moves slowly, or is not responding when opening or saving files

https://www.xgiovio.com/blog-photos-videos-other/blog/fix-windows-10-freeze-renaming-saving-files-and-folders/

  • Disable Quick Access 
    • From any folder menu toolbar click on View>Options>Change folder and search options
      • uncheck “Show frequently use folders in Quick access.”
      • uncheck “Show recently used folders in Quick access.”
      • press “Clear”

Friday, June 9, 2017

Dynamics NAV - LS Retail - Rounding to nearest 5 cents


  • Under POS Functionality Profile
    • Price Rounding To = 0.05
    • This affects invoice generation
  • Under Store>Tender Type>Amount
    • Rounding = Nearest
    • Rounding To = 0.05
    • This affects cash receipt allowance
This will round all transactions for this tender type to the nearest 5-cent denomination

Thursday, June 8, 2017

Dynamics GP - Send Document Via Email button is greyed out

Cause:
This happens when the customer does not have an e-mail address in the internet information window by the address id, or the customer is not configured for email in the email button on the customer card.

Wednesday, June 7, 2017

Dynamics GP - Cannot Email Posted Historical SOP Invoice


Tuesday, June 6, 2017

Dynamics GP - Footer is not printing when trying to print landscape

Cause:
The orientation of the page setup does not match the orientation of the printer setup


Solution:

  • Before printing, click on File>Page Setup and confirm the orientation
  • On your printer settings, go to printer defaults and set the default orientation
  • When printing use the print icon where possible, avoid using File>Print

Monday, June 5, 2017

Dynamics GP - Set Safety Stock values using SQL


  • Data is stored in the IV00102 table per location
  • Record without location is the default for the item
    • select ITEMNMBR, LOCNCODE, SFTYSTCKQTY, ORDRPNTQTY from iv00102
    • update  iv00102 set SFTYSTCKQTY = 5, ORDRPNTQTY  = 10 where ITEMNMBR = 'ITEM001' and LOCNCODE = 'LOC001'

Monday, May 29, 2017

Dynamics GP Purchase Return - Cannot select PO Receipt. number is not available. Number is not in list.

This can happen in any of the following situations

  • PO Receipt has a corrupt or stuck record in the POP10300 and POP10310 table from a previous return against this line item
    • Run this to identify any corrupted transactions. If it returns anything, you need to run check links.
      • Select * from POP10300 where poptype = 0
    • Run Check Links against your purchasing module
    • If this does not remove the errant record, update the type, pull up the record and delete it
      • update pop10300 set poptype = 5 where poprctnm = 'RCT00001'
  • PO has an Unposted Receipt - Cannot do purchase return
    • If the item has not been received, it cannot be returned
    • Go to Purchasing>Receiving Transaction Entries
      • Look through unposted receipts to see if any are for your PO
    • Or use this script to check for any unposted receipt lines
      • select * from pop10310 where PONUMBER = 'PO001'
  • PO Receipt has an Unposted Invoice - Cannot do purchase return with credit
    • If the invoice has not been posted, there is no credit to be given
    • Go to Purchasing>Enter/Match Invoices
      • Look through Unposted invoices to see if any are for your PO Receipt
    • Or use this script to check for any unposted invoice lines for your PO number
      • select * from pop10500 where PONUMBER = 'PO001'
  • Item has already been sold or removed from the cost layer
    • Go to Inventory>Inquiry>Receipts
    • Enter the item and the location
      • Look for the Receipt Number
      • If there is a value under "Quantity Sold", you cannot do a purchase return for what was sold
      • You must do an inventory adjustment
  • You can Return w/Credit for receipts of non-inventory items with item cards
  • You can Return w/Credit for receipts of non-inventory items without item cards
    • Choose Return w/Credit
    • On the Purchase Return Line, Type in the Item Number first
    • Now the Receipts lookup should populate correctly

Friday, May 19, 2017

GP 2016 R2 - Delete Row using right-click functionality missing when Manufacturing is installed from GP Windows

https://community.dynamics.com/gp/f/32/t/137298

Cause:
This happens when manufacturing is installed.

Resolution:
To enable the delete row function, go to


  • GP Button>User Preferences
  • Under window command display, change it to Menu Bar
  • Close and reopen your GP
  • Navigate to your transaction
  • Select your line
  • On the menu bar on top, click Edit>Delete Row
  • Any users experiencing this issue should do the same

Wednesday, May 17, 2017

Outlook - Conversation subject disappears when searching

Add the Subject column as a separate column

HITB Reset Tool - Data Validation - Account does not exist

CAUSE
The item may have quantities in one of the quantity type columns, and has no associated account with that quantity type.

select QTYTYPE, * from SEE99998 where qtytype <> 1

Review the Quantity Type field to see which "bucket" this item has receipt layers in. Here are the possibilities:
1 – On Hand
2 – Returned
3 – In Use
4 – In Service
5 – Damaged

Each of these quantity types maps to a different account type that would need to be populated.
1 – On Hand -- Inventory
2 – Returned -- Inventory Returns
3 – In Use -- In Use
4 – In Service -- In Service
5 – Damaged -- Damaged

Verify that you have accounts entered for the quantity types that exist for this item(and the others as needed) to resolve this warning message.


RESOLUTION
To Resolve this, set default posting accounts for the different quantity types in your posting setup, or on each item.

On the item card for each item, set a value for the account required for "Inventory Returns"

--This script can be used to update items with an account number
update iv00101 set IVRETIDX = x where ITEMNMBR = 'y'

--Where x is your account index, and y is your item number.

Friday, May 12, 2017

GRNI - Goods Received not Invoiced does not match Trial Balance


  • Run the GL Trial Balance for the AP Clearing account as at today
  • Run the Vendor Amt. Rcd. Not Invoiced as at today, compare the figures
  • Differences are due to Exchange Rate currency rounding and value entries that affect previous transactions. Depending on when the reports are run, it may or may not include the adjusting value entries that would affect the GL balance.

Batch File - How to copy files over a certain size from one folder structure into the exact same folder structure

https://superuser.com/questions/877976/windows-command-line-to-move-files-over-a-certain-size

robocopy "C:\My Source" "C:\My Destination" /s /max:10000000 /mir /R:1 /W:1

/s - copy subdirectories
/max - limit filesize in bytes
/mir - mirror structure (mirrors deletes)
/R - max retries if file is open
/W - max wait time in seconds when retrying

Friday, May 5, 2017

Tuesday, May 2, 2017

ReqLogic - Update GP PO Requisition Date with Reqlogic Requisition Date

UPDATE       POP10100
SET                REQTNDT = XW_ReqHdr.ReqDate
FROM            POP10110 INNER JOIN
                         POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER LEFT OUTER JOIN
                         XW_ReqHdr ON POP10110.Source_Document_Number = XW_ReqHdr.ReqNbr
WHERE        XW_ReqHdr.ReqDate > '1900-01-01'

Monday, May 1, 2017

LS Retail - AR Charge, Tender to customer Account and Credit Limits and Payment Terms


  • Tender type under store setup must be ticked for "Card/Account" to identify the tender type as a customer account to know it has to check credit limits
  • Customer Credit Limit must not be 0. 0 is treated as unlimited credit.
  • If the Sales and Receivables Setup is set to "Both Warnings"
    • The POS should give a warning about the transaction being over the customer's credit limit, and prevent you from moving forward
  • If your tender type under the store setup is ticked for "Manager Key Control" 
    • and your staff account permission is Manager it will allow you to use the customer account tender type
    • If it is not ticked, anyone can use this tender type
  • If Customer is Blocked on customer Card, Transaction will stop with error

  • C99001570 - POS Transaction - Handles Credit Check when Customer Account Tender type is selected
    • Function - TenderCharge
    • Text806 - Customer %1 is over creditlimit or blocked. Want to continue?
  • C99008900 - POS functions
    • Function - ValidateCustomer

Tuesday, April 25, 2017

eOne Flexicoder - When some segments are set to "Do Nothing" blanks are used for the segments instead of the default account segments


  • This is happening because the defaults are being taken from the item or customer master record.
  • That specific item or customer card does not have a default account setup on it.
  • Review the accounts, and set defaults.

Wednesday, April 12, 2017

Dynamics GP - Automatically Print separate remittance for check with more than 12 lines in remittance

This cannot be done, you must tick "Separate Remittance" when printing your checks.

Hi Ayana

In cases where there are too many lines to print on the remittance,when printing checks you need to
  • tick “Separate Remittance”
  • Print your checks as normal
  • When the screen comes up to post checks, click “process”
  • A new windows will open to prompt for a separate remittance. Select Remittance form, and print document, then click Process
  • This will prompt you to print the remittance separately.


To control the number of records displayed on the check stub

https://community.dynamics.com/gp/f/32/t/181070

Go to LAyout>Tools>Section Options

Make sure that in Report Writer, your "Remittance Header" has a "Records Per Report Body" set to 12, and that the "No Break At Record Count" checkbox is unmarked.
This is the default setup for check formats.


Tuesday, April 11, 2017

Dynamics GP - Stuck SOP documents - This order has already been posted

https://community.dynamics.com/gp/b/gplearn/archive/2009/12/14/sop-34-this-document-has-been-posted-34-error-38-resolution

Sometimes, when an sop transaction gets stuck or corrupted, you may not be able to reopen it.

Ensure that you update the CUSTNMBR, BACHNUMB and BCHSOURC fields to allow the transaction to be visible in the SOP lookup menu.


update sop10100 set custnmbr = 'AARON001',BACHNUMB = 'RECOVER', bchsourc = 'Sales Entry' where sopnumbe = 'ORD001'

Monday, April 10, 2017

Dynamics NAV - Write to text file in CAL

https://dynamicsuser.net/nav/f/developers/8867/best-way-tro-write-to-a-file?pi2079=1



FileMyHTML.TEXTMODE:=TRUE;
FileMyHTML.CREATE('C:\TEST\main.txt');
FileMyHTML.WRITE('<html>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('<head>');
FileMyHTML.WRITE('<title>My Page</title>');
FileMyHTML.WRITE('</head>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('<P>Hello world!</p>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('</html>');
FileMyHTML.CLOSE();

Variables
Name DataType Subtype Length
FileMyHTML File

Wednesday, April 5, 2017

SQL How to decrypt encrypted stored procedures

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e7056ca8-94cd-4d36-a676-04c64bf96330/decrypt-the-encrypted-store-procedure-through-the-tsql-programming-in-sql-server-2005?forum=transactsql




CREATE PROCEDURE dbo.ShowDecrypted(@ProcName SYSNAME = NULL)
AS
--Jon Gurgul 27/09/2010
--Adapted idea/code from shoeboy/joseph gama
SET NOCOUNT ON
IF EXISTS 
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e 
on (ec.[endpoint_id]=e.[endpoint_id]) 
WHERE e.[name]='Dedicated Admin Connection' 
AND ec.[session_id] = @@SPID
)
 BEGIN

 DECLARE @i BIGINT,@a NVARCHAR(MAX),@b NVARCHAR(MAX),@d NVARCHAR(MAX),@c NVARCHAR(MAX)
 SET @a=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)
 SET @b='ALTER PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS '+REPLICATE('-', 8000)

  BEGIN TRANSACTION
   EXECUTE (@b)
   SET @c=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1) 
  ROLLBACK TRANSACTION

 SET @d = REPLICATE(N'A', (DATALENGTH(@a) /2 ))
 SET @i=1
 WHILE @i<=(DATALENGTH(@a)/2)
 BEGIN
 SET @d = STUFF(@d, @i, 1,NCHAR(UNICODE(SUBSTRING(@a, @i, 1)) ^(UNICODE(SUBSTRING('CREATE PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS ' + REPLICATE('-', 8000), @i, 1)) ^UNICODE(SUBSTRING(@c, @i, 1)))))
 SET @i=@i+1
 END

 SELECT @d [StoredProcedure]

 END
 ELSE
 BEGIN
  PRINT 'Use a DAC Connection'
 END

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Dynamics GP - Stock Count View

--This view can be used to review and print completed stock counts before they have been posted.

---------------------------------------------------------------------
SELECT     dbo.IV10300.STCKCNTID, dbo.IV10300.STCKCNTDSCRPTN, dbo.IV10300.STCKCNTSTTS, dbo.IV10300.DOCDATE, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC,
                      dbo.IV10301.LOCNCODE, dbo.IV10301.BINNMBR, dbo.IV10301.CAPTUREDQTY, dbo.IV10301.COUNTEDQTY, dbo.IV10301.COUNTDATE, dbo.IV10301.DECPLQTY,
                      dbo.IV10301.ITMTRKOP, dbo.IV10301.IVIVINDX, dbo.IV10301.IVVARIDX, CASE WHEN VERIFIED = 1 THEN 'Yes' ELSE 'No' END AS Verified, dbo.IV40201.BASEUOFM,
                      dbo.GL00100.ACTDESCR AS IVActDesc, dbo.GL00105.ACTNUMST AS IVActNum, GL00100_1.ACTDESCR AS VarActDesc, GL00105_1.ACTNUMST AS VarActNum,
                      dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY AS VarQty, dbo.IV00101.CURRCOST, (dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY)
                      * dbo.IV00101.CURRCOST AS VarAmt
FROM         dbo.GL00105 AS GL00105_1 INNER JOIN
                      dbo.GL00100 AS GL00100_1 ON GL00105_1.ACTINDX = GL00100_1.ACTINDX RIGHT OUTER JOIN
                      dbo.IV10300 INNER JOIN
                      dbo.IV10301 ON dbo.IV10300.STCKCNTID = dbo.IV10301.STCKCNTID LEFT OUTER JOIN
                      dbo.IV40201 INNER JOIN
                      dbo.IV00101 ON dbo.IV40201.UOMSCHDL = dbo.IV00101.UOMSCHDL ON dbo.IV10301.ITEMNMBR = dbo.IV00101.ITEMNMBR ON
                      GL00100_1.ACTINDX = dbo.IV10301.IVVARIDX LEFT OUTER JOIN
                      dbo.GL00105 INNER JOIN
                      dbo.GL00100 ON dbo.GL00105.ACTINDX = dbo.GL00100.ACTINDX ON dbo.IV10301.IVIVINDX = dbo.GL00100.ACTINDX

Monday, April 3, 2017

SQL and EXCEL - How to get Sheet names from an Excel Workbook using SQL, then use those to cycle through each sheet. Import data from multiple excel sheets.


CREATE TABLE [dbo].[STFNAMES](
[TABLE_CAT] [nchar](10) NULL,
[TABLE_SCHEM] [nchar](10) NULL,
[TABLE_NAME] [varchar](50) NULL,
[Table_Type] [varchar](50) NULL,
[Remarks] [varchar](50) NULL
) ON [PRIMARY]

GO


exec sp_dropserver 'ExcelSource','droplogins'
exec sp_addlinkedserver 'ExcelSource','', 'Microsoft.ACE.OLEDB.12.0','E:\myfile.xlsx',NULL, 'Excel 12.0'
exec sp_addlinkedsrvlogin 'ExcelSource','false'
delete STFNAMES
insert into STFNAMES EXEC sp_tables_ex 'ExcelSource'
drop table STFNAMES2
select ROW_NUMBER() OVER(ORDER BY TableNm ASC) AS Rownum, TableNm into STFNAMES2 from (
select distinct left(right(table_name,len(Table_Name)-1),charindex('$',Table_Name)-1) as TableNm from stfnames where Table_Name not like '%Name%' and Table_Name not like '%Cases%') as Tbl
select * from STFNAMES2


Wednesday, March 22, 2017

Dynamics NAV - LS Retail - Block purchasing or sales

Item Status Link - set blocking rules
This affects Purchasing, Sales

Table 39 - Checkitemstatus
C 99001452 - boutils, finditemstatuslink

The suspend routine checks the Document date, and the line location.
If a suspend status is set for a specific time range, that item will be suspended until another active line date is entered.

Tuesday, March 21, 2017

SQL - how to use temp tables

sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO 


--Select output from sp into temp table
 SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;', 'EXEC getBusinessLineHistory')

Tuesday, March 14, 2017

SSRS Fails - Reports timeout, SSRS does not export to Excel, Reports take extremely long to run. OutofResources exception error

All of these errors are related to having some sql or ssrs function or formula that is being fed nulls.


  • Ensure that all lookup queries do not return any nulls for drop-down lookups
  • Ensure that no nulls are passed to any SQL formulas in views you are using
  • Ensure that no nulls are passed to any SSRS formulas in your report
  • Ensure that all SSRS outputs are 0 or "(none)" but never NaN or Null or Infinite
    • All nulls should be handled in SQL by ISNULL([field],0) or ISNULL([field],"(none)")
    • All nulls should be handled in SSRS by
      • =iif(isNothing(Fields!FV1.Value), Fields!FV2.Value, Fields!FV1.Value)

Saturday, March 11, 2017

LS Retail - Create a Button that prompts an Infocode

Use the INFO_K function on the button

Dynamics GP - When running Item Check Links you get the error 'A get/change next operation on table "IV_Item_MSTR" failed. A records was already locked.'

https://www.gpug.com/communities/community-home/digestviewer/viewthread?GroupId=247&MID=30191&tab=digestviewer&CommunityKey=4754a624-39c5-4458-8105-02b65a7e929e


The IV00101.Purchase_Tax_Options field for those records is 0.
Valid values are

1 – Taxable
2 – Nontaxable
3 – Base on customer / vendor


Update all affected items and item classes accordingly to resolve the error message.


--Alternatively, you can also run this script to set all items without a purchase tax schedule to non-taxable.
--Update Items

select * from iv00101 where Purchase_Tax_options = 0

update iv00101 set Purchase_Tax_Options = 2 where Purchase_Tax_options = 0

--Also run this to update the item classes to prevent new items from starting with 0
--Update Item Classes

select * from iv40400 where Purchase_Tax_options = 0

update iv40400 set Purchase_Tax_Options = 2 where Purchase_Tax_options = 0

Friday, March 10, 2017

Dynamics GP - SQL View - SRMaster - Salesperson and Territory


CREATE VIEW [dbo].[BI_SRMaster]
AS
SELECT     dbo.RM00301.SLPRSNID, dbo.RM00303.SALSTERR, dbo.RM00303.SLTERDSC, dbo.RM00301.EMPLOYID, dbo.RM00301.SLPRSNFN, dbo.RM00301.SPRSNSLN,
                      RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SrNm, RTRIM(dbo.RM00301.SLPRSNID) + ' | ' + RTRIM(dbo.RM00301.SALSTERR)
                      + ' | ' + RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SrTerrLbl, dbo.RM00301.STATE, dbo.RM00301.INACTIVE AS SRInactive,
                      dbo.RM00303.INACTIVE AS TerrInactive
FROM         dbo.RM00301 INNER JOIN
                      dbo.RM00303 ON dbo.RM00301.SALSTERR = dbo.RM00303.SALSTERR

GO


Dynamics GP - Force-Changing Item Valuation Methods

IV00101 - Item Master
VCTNMTHD - Valuation method 1 - FIFO, 3-Average

If you have any open sales orders, it will prevent the change valuation routine from running.

You can work around this issue using the following method

https://dynamicsuser.net/gp/f/users/40649/inventory-valuation-change



  • This method is not perfect, and may cause costing errors in the future. If the cost of your stock needs to be accurately maintained, all stock should be removed, valuation changed, then re-entered
  • This method should not be used in a production environment
  • Backup the current sop10200 table to track the original amounts and data
    • select * into sop10200_bak from sop10200
  • Backup the current iv00102 table to track original allocated amounts
    • select * into iv00102_bak from iv00102
  • Set all sop lines to non-inventory
    • Update sop10200 set NONINVEN = 1
  • Remove all allocated stock temporarily
    • update sop10200 set atyalloc = 0
    • update iv00102 set ATYALLOC = 0
  • Zero out all stock using inventory adjustments
  • Run Valuation Change Utility
  • Put back in all stock using inventory adjustments
  • Set the allocation back to the original values 
    • update sop10200 set sop10200.atyalloc = sop10200_bak.atyalloc inner join sop10200_bak on sop10200_bak.dex_row_id = sop10200.dex_row_id
  • Set the non-inventory flag back to original values
    • update sop10200 set sop10200.noninven = sop10200_bak.noninven inner join sop10200_bak on sop10200_bak.dex_row_id = sop10200.dex_row_id
  • Set the allocated qty back to original values
    • update iv00102 set iv00102.atyalloc = iv00102_bak.atyalloc inner join iv00102_bak on iv00102_bak.dex_row_id = iv00102.dex_row_id
  • Run reconcile and check links on everything

Dynamics GP - SQL Views - ItemMaster, ItemQty

SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.UOMSCHDL, dbo.IV40201.BASEUOFM, dbo.IV40201.UMDPQTYS, dbo.IV00101.ITMCLSCD, dbo.IV00101.CURRCOST, dbo.IV00101.ITMTRKOP, dbo.IV00101.LOTTYPE, dbo.IV00101.CREATDDT,
                  dbo.IV00101.MODIFDT, DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS AgeDays, dbo.IV40400.ITMCLSDC, dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.USCATVLS_4, dbo.IV00101.USCATVLS_5,
                  dbo.IV00101.USCATVLS_6, RTRIM(dbo.IV00101.ITEMNMBR) + ' | ' + RTRIM(dbo.IV00101.ITEMDESC) AS ItemLbl, LastDt.LastTrxDt,
                  CASE WHEN ITEMTYPE = 1 THEN 'Sales Inventory' WHEN ITEMTYPE = 2 THEN 'Discontinued' WHEN ITEMTYPE = 3 THEN 'Kit' WHEN ITEMTYPE = 4 THEN 'Misc Charges' WHEN ITEMTYPE = 5 THEN 'Services' WHEN ITEMTYPE = 6 THEN 'Flat Fee' END AS ItemTypeDesc,
                   dbo.IV00101.ITEMTYPE
FROM        dbo.IV00101 INNER JOIN
                  dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL LEFT OUTER JOIN
                      (SELECT     ITEMNMBR, MAX(DOCDATE) AS LastTrxDt
                       FROM        dbo.IV10201
                       GROUP BY ITEMNMBR) AS LastDt ON dbo.IV00101.ITEMNMBR = LastDt.ITEMNMBR LEFT OUTER JOIN
                  dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD

/****** Object:  View [dbo].[BI_ItemQty]    Script Date: 03/10/2017 09:13:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ItemQty]
AS
SELECT     dbo.IV00102.ITEMNMBR, dbo.IV00102.LOCNCODE, dbo.IV00102.QTYONHND, dbo.IV00102.ATYALLOC, dbo.IV00101.ITMTRKOP,
                      dbo.IV00102.QTYONHND - dbo.IV00102.ATYALLOC AS QtyAvlbl, dbo.IV00101.USCATVLS_2 AS Category, dbo.IV00101.CURRCOST,
                      dbo.IV00102.QTYONHND * dbo.IV00101.CURRCOST AS CostOnHand, dbo.IV00101.ITEMDESC,
                      CASE WHEN itmclscd LIKE '%fg%' THEN 'Finished Goods' ELSE 'Other' END AS FGOTH
FROM         dbo.IV00102 INNER JOIN
                      dbo.IV00101 ON dbo.IV00102.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE     (dbo.IV00102.LOCNCODE > '') AND (dbo.IV00102.QTYONHND <> 0) AND (dbo.IV00101.ITMTRKOP = 1)

GO


Registering Integration Manager

The IM keys do not need to match the GP keys

Friday, March 3, 2017

Dynamics GP - National Accounts - Parent customer cannot see child invoices in apply sales documents screen

Tables

  • RM00105 - National Account master
  • RM00101 - CPRCSTNM - Defines parent account

Solution
  • The parent account needs to have itself as a value in the CPRCSTNM field
  • run this script to set all national account parents to themselves
update rm00101 set cprcstnm = custnmbr where custnmbr in (select cprcstnm from RM00105)

Monday, February 20, 2017

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

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


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

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


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

Wednesday, February 15, 2017

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


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

Batchmaster Requirements Gathering


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

Monday, February 6, 2017

Batchmaster Manufacturing for Dynamics GP Training


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

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

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

Friday, February 3, 2017

SQL output to text file



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

Tuesday, January 31, 2017

Dynamics NAV - Menus do not show up when searching in search bar

http://www.erpsoftwareblog.com/2014/11/wont-search-find-report-dynamics-nav/

Saving a view on a page will allow you to find it when searching
Otherwise, modify the menusuite and add a menusuite with your object to have it visible in the search bar

Saturday, January 28, 2017

Dynamics NAV - How to setup and use EFT, Electronic Funds Transfer, ACH,Electronic Payment, Export Payment to File



  • Pre-Requisites
    • Setup EFT Bank information on the following windows mainly under the Transfer Fasttabs
      • Company Information Screen
      • Bank Accounts
      • Vendors
      • Payment Journal Template
    • Create a Payment Journal Batch with a Bal Account type of "Bank Account" and select a bank account
  • Navigate to Posting Exchange Column Definitions
    • Define Column definitions for EFT Output
  • Navigate to Posting Exchange Definitions
    • Define a format for your EFT output

Friday, January 27, 2017

LS Retail - Dynamics NAV Tables


  • Trans_ Sales Entry - Unposted POS Transaction Header
  • Transaction Entry - Posted POS Transaction Headers
  • Trans_ Infocode Entry - POS Infocode Entry Data

LS Retail - Remove "Purge Old Transactions?" prompt on POS


  • Functionality Profile
  • Set Trans. Delete Reminder to nothing
Related Errors:
"The Date is not Valid"

This occurs if you use a number that is too large for the Trans. Delete Reminder

LS Retail - No Terminal Selected


  • Retail Users> Set default terminal

Thursday, January 26, 2017

Windows 10 - USB Mouse disconnects or loses power randomly. You hear the sound of the USB device being unattached. How to restart a usb port or connection.

Original Workarounds here
https://support.microsoft.com/en-us/help/817900/usb-port-may-stop-working-after-you-remove-or-insert-a-usb-device

http://www.tomshardware.com/answers/id-1783536/usb-ports-disconnecting-reconnecting.html

At the end of the day the only thing that really worked for me was restating the machine, or restarting the Chipset Host Controller under the Universal Serial Bus Controllers


  • How to restart a usb port or connection
    • Go to Control Panel>Device Manager>Universal Serial Bus Controllers
    • Each USB port will have a corresponding Host Controller
    • For each one, right click and disable, then right click and enable
  • How to disable USB Suspend Power Settings
    • Scroll to the right and type 'Power Options' in the search field and click on it.
    • Click 'Change plan setting' on your chosen plan.
    • Click 'Change advanced power setting' on your chosen plan.
    • Find 'USB settings' and open.
    • Find 'USB selective suspend setting' and change it to disabled.

Friday, January 20, 2017

Dynamics GP - Table Open Error

This error occurs if a specific report in a dictionary may have been corrupted or is out of date.
Restore your dictionary to resolve.

eOne Flexicoder - Access to the window1 window was denied

Create a task to give users access to this window under security tasks Flexicoder>Windows>System>Window1

Thursday, January 19, 2017

Dynamics GP - Report Writer - Display 0 dp for Quantity on SOP Invoice


  • Create a calculated field of type integer equal to the Quantity field
    • Integer fields naturally have 0 dp, and require no formatting
  • To display 2 dp, create a calculated field of type currency equal to the Quantity field
    • go to the field properties, and select the formatting from the SOP Work Amounts table > Decimal Places Currency 
    • This will force it to format to the currency decimals for this item (which should be 2)
    • To remove the $ sign, apply the format DLR_RB0_STR, remove the format field (set to none)

Saturday, January 14, 2017

SQL - Dynamics NAV - Row Combine - How to combine a single field with multiple rows into a long string with | to use in filters


Example2
SELECT p1.grp,
          ( SELECT [Item No_] + '|' 
              FROM (select [Item No_], 1 as grp from [ESL].[dbo].[BI_ItemQty] where QtyOnHand <> 0 ) p2
             WHERE p2.grp = p1.grp
             ORDER BY [Item No_]
               FOR XML PATH('') ) AS AllComm
      FROM (select [Item No_], 1 as grp from [ESL].[dbo].[BI_ItemQty] where QtyOnHand <> 0 ) p1
     GROUP BY grp ;

Windows 10 - Disk Usage goes to 100% or very high after resuming from sleep, suspend, or raising the lid

https://support.microsoft.com/en-us/kb/2922899

Workaround
  • Change power plan to Balanced.
  • Start Command Prompt as administrator, and then run the following commands:

powercfg /setacvalueindex scheme_max sub_disk 0b2d69d7-a2a1-449c-9680-f91c70521c60 1
powercfg /setdcvalueindex scheme_max sub_disk 0b2d69d7-a2a1-449c-9680-f91c70521c60 1
  •  Change the power plan back to Power Saver.
 
You can also contact the computer vendor for a BIOS update that may fix the issue.

Thursday, January 12, 2017

Dynamics GP - "Duplicate Numbers are not allowed" in Edit List

One of your tables has errant or half-posted numbers

Use these queries to figure out what has been duplicated

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/12/04/identifying-duplicate-transactions/



— RM Duplicates
select RMDTYPAL, DOCNUMBR, COUNT(*) as [COUNT] from
(
select RMDTYPAL, RMDNUMWK as DOCNUMBR from RM10301 W
UNION ALL
select RMDTYPAL, DOCNUMBR from RM10201 W
UNION ALL
select RMDTYPAL, DOCNUMBR from RM20101 O
UNION ALL
select RMDTYPAL, DOCNUMBR from RM30101 H
) C
group by RMDTYPAL, DOCNUMBR
having COUNT(*) > 1

Microsoft Dynamics - How to submit a support request or raise an incident

https://mbs2.microsoft.com/support/newstart.aspx


  • Login to Partnersource
  • Click Business Center
  • Bottom Left > Click New Support Request

Wednesday, January 11, 2017

Sunday, January 8, 2017

Dynamics NAV - Force Post Item Journal Entry to INTRANSIT location

This is not recommended for live environments, and should only be used for testing or recovery from damaged data.
This will allow you to do Item Journals against the INTRANSIT location

  • Modify C21
    • Rem out this line
    • ERROR(UseInTransitLocationErr,"Location Code");
    • You need to leave this line remmed out for the adjust costs job to run correctly
  • Modify C22
    • Rem out these lines
    • OldItemLedgEntry.TESTFIELD("Order Type",OldItemLedgEntry."Order Type"::Transfer);
    • OldItemLedgEntry.TESTFIELD("Order No.",ItemLedgEntry."Order No."); 

Tuesday, January 3, 2017

Dynamics NAV - Stock Count


  • Before beginning
    • Identify all blocked items and remove blocks before counting if required
    • Post all transfers, shipments, invoices, journals and anything that will impact inventory 
    • Run the adjust costs job
    • Print an inventory valuation report
    • Ensure that the Unit Cost on all Items is correct (This is what all positive adjustments will be costed at)
      • Set all Unit Costs to the Last direct cost * Exchange Rate if necessary
      • It is also possible to enter the Cost manually on the Inventory Journal itself
  • Regular Location
    • Departments > Warehouse > Good Handling Multiple Orders > Periodic Activities > Phys. Inventory Journals
    • Create Batch if one does not exist
    • Home>Process>Calculate Inventory
      • Include Items not on inventory
      • Include items with no transactions
      • Enter Document number
      • Enter Filters for location
      • Where Blocked is No
      • Click Ok
      • This will populate the journal with all items that can be found in that location
      • Enter quantity counted in the Qty.(Phys. Inventory) column
    • If any items are not populated, manually enter them
    • Click Post to post adjustments
  • Store Location using LS NAV - Stock Counting Worksheet
    • Create  anew counting worksheet 
      • ESL/Departments/LS Retail/InStore Management/Setup/Inventory Management
    • Pull up worksheet on device with scanner
      • Departments/LS Retail/InStore Management/Worksheets
      • Scan Item barcode to pull up item on worksheet
      • Enter count under Qty. (Phys. Inventory) column
      • Click Accept
        • Compress transactions if you want it to summarize similar lines, otherwise say no
      • Click Post to post changes as Item Ledger Entries and update GL

  • Warehouse Location
    • Departments > Warehouse > Good Handling Multiple Orders > Periodic Activities > Whse. Phys. Inv. Journals
    • Create Batch if one does not exist
    • Home>Functions>Calculate Inventory
      • Include Items not on inventory
      • Enter Document number
      • Enter Filters for location: WH
      • Enter filter for BIN if required
      • Click Ok
      • This will populate the journal with all items that can be found in that location
      • Enter quantity counted in the Qty.(Phys. Inventory) column
    • If any items are not populated, manually enter them, but enter the quantity in the Quantity column instead of the Qty.(Phys. Inventory) column
    • Click Register to finalize adjustments (This has not updated inventory or GL as yet)
    • Create Item Journal
      • DEPARTMENTS > WAREHOUSE > INVENTORY > TASKS > Item Journals
      • Select Template and journal
      • Home>Process>Calculate Whse. Adjustment
      • Click Post (This updates the GL and Inventory)
  • Warehouse Location with Tasklet Offline Count
    • Departments > Warehouse > Good Handling Multiple Orders > Periodic Activities > Whse. Phys. Inv. Journals
    • Create Batch if one does not exist
    • Home>Functions>Calculate Inventory
      • Enter Document number
      • Enter Filters for location: WH
      • Enter filter for BIN if required
      • Click Ok
      • This will populate the journal with all items that can be found in that location
    • Enter quantity counted Using the Tasklet Handhelds
    • Navigate back to Departments > Warehouse > Good Handling Multiple Orders > Periodic Activities > Whse. Phys. Inv. Journals
      • Pull up the batch and journal you calculated originally
      • Click Actions>"Import Offline Count"
      • Enter Handheld Number (1-4)
      • This will import the Tasklet Count into the calculated list
      • It will automatically insert new lines if necessary
    • Make any changes necessary
    • Click Register to finalize adjustments (This has not updated inventory or GL as yet)
    • Create Item Journal
      • DEPARTMENTS > WAREHOUSE > INVENTORY > TASKS > Item Journals
      • Select Template and journal
      • Home>Process>Calculate Whse. Adjustment
      • Click Post (This updates the GL and Inventory)

Dynamics GP - "The date cannot be part of a different fiscal year"

http://mystiquesmemory.blogspot.com/2012/03/date-cannot-be-part-of-different-fiscal.html

http://mystiquesmemory.blogspot.com/2016/01/fiscal-period-setup-error-date-cannot.html


In this case we had an issue where the First start day could not be set to 1-Jan-2017 for the 2017 fiscal year. It kept giving us the error  "The date cannot be part of a different fiscal year".

After running a

delete sy40100
where year1=2017
delete SY40101
where YEAR1=2017
delete sy40102
where year1=2017
delete SY40103
where YEAR1=2017

and a

GP>Tools>Utilities>System>Reconcile
the system wiped out the 2005 fiscal periods, and replaced them with the 1-jan-2017 period
This looks like the table got corrupted in some way, and the 2005 values were replaced with 2017 dates

We were able to change the first and last dates for 2005 back to the correct dates through GP, and recalculate the periods.
This freed up the 1-jan-2017 period, and allowed us to recalculate the 2017 year correctly.

A full reconcile was done on the GL, AR and AP for all years from 2005 onward