Dynamics NAV - Recurring Methods

• Fixed
• Amount does not change after posting
• The value is split based on the rules set in the Allocations page in the Allocations button
• Variable
• Amount is reset to 0 after posting
• You need to manually re-enter a new value after every posting
• The value is split based on the rules set in the Allocations page in the Allocations button
• Balance
• Journals the balance of the account
• The value is split based on the rules set in the Allocations page in the Allocations button
• Reversing Fixed
• Same as Fixed, but reversal entry is also automatically posted to the next day after posting date
• Reversing Variable
• Same as Variable, but reversal entry is also automatically posted to the next day after posting date
• Reversing Balance
• Same as Balance, but reversal entry is also automatically posted to the next day after posting date

Document Description Formulas

• %1 – The current day number
• %2 – The current week number
• %3 – The current month number
• %4 – The current month name
• %5 – The current accounting period name
• Eg. RENT-%4

Date Formulas

https://www.clientsfirst-us.com/entering-date-formulas-in-microsoft-dynamics-nav/

• CM+1D first of next month

Dynamics NAV - How to reconcile Inventory to GL

https://www.archerpoint.com/blog/Posts/inventory-general-ledger-reconciliation-microsoft-dynamics-nav

https://dynamics-consultants.co.uk/blog/posts/2017/june/reconciling-inventory-in-microsoft-dynamics-nav/

NAV-Configuration Package Import Error-Posting date must have a value in Gen. Journal line: Journal template = GENERAL, Journal batch = XXX, Line number = 10000. The field cannot be zero or empty.

It looks like the TESTFIELD command on the Posting Date field causes this error.

The true cause of this error is a bad configuration package.
Redo the configuration package in the current version of BC/NAV.

Dynamics NAV / Business Central - What happens to assigned charge items if the original received quantity is partially returned?

• Scenario
• 100 items are purchased for $1 each
• Vendor Invoice is for $100
• duty item charge of $1 is added
• Customs Invoice is for $100
• Cut a cheque and pay customs to clear container
• Final Landed Cost
• $200
• Upon opening the container, we find 20 items damaged
• We contact the vendor, and he gives us a credit for $20
• Problem
• We cannot request a credit from Customs for the $20 duty we have paid on the damaged items
• The $20 value is still sitting in our inventory account
• Solution
• Pass a manual journal to move the value from Inventory to Damages/Writeoffs, (but not applied to any specific line item, as this will affect existing item costs)
• Debit Inv $20
• Credit Damages/Writeoffs $20
• Add original po,receipt,credit numbers as reference on journal

Dynamics NAV / Business Central - How to post Duties for Landed Costs before receiving goods at warehouse

https://www.navug.com/communities/community-home/digestviewer/viewthread?MessageKey=a0bc75aa-84ab-40e2-8e2a-afcc6d4946ed&CommunityKey=95503735-5a0b-4af1-8326-9bd7bb3b4d3d&tab=digestviewer

• Create a "DOCK" location to represent stock that has not arrived at your warehouse as yet
• "DOCK" location is connected to your Goods in transit account
• "WH" location should be set to require receive
• All PO's are done to the DOCK location
• Container Lands on dock
• Post PO>Receive and Invoice
• PO is received to the "DOCK" location
• This immediately receives stock, and generates the invoice for the full amount
• PO is closed and removed
• If you are using lot or serial tracking, this will only work if you are aware of the lot and serial information in advance. Any differences would need to be handled as stock count adjustments after completing the full receiving process at the WH.
• Landed Costs are entered and applied to the received PO lines
• Transfer order is prepared
• Tick Direct Transfer to default the Qty. to Ship and Qty. to Receive
• Actions>Functions>Get Receipt Lines>Clear Location Filter
• Add all lines from the receipt to a transfer order
• Transfer order is updated with actual dates when container is picked up from the DOCK and delivered to the warehouse
• Any additional charges can added on the transfer as well if required, but for simplicity all charges should have been applied after the initial receiving if possible
• When goods arrive at warehouse 
• Unstuff container and count goods received
• On Transfer order, Adjust Qty. to Ship to actual count 
• Post Transfer
• Overs
• Additional lines need to be added to the original PO, Received and transferred to WH
• OR Adjusted into stock in a separate transaction as a stock count variance
• Shortages
• Any shortages will be left in the DOCK location
• Reopen and delete the transfer order
• Purchase Return>New>Enter Vendor>Process>Get Posted doc Lines to Reverse>Select PO Lines
• By default, the remaining quantity in DOCK will be entered on the return line
• Enter Return Reason code "Shortage"
• Process>Apply entries (If this should be applied immediately)
• Post>Ship and Invoice

Otherwise, if you are not keen on the idea of receiving stock to a dock location

• You must receive the goods first, then apply landed costs
• You can create the separate landed cost invoices beforehand, but you cannot apply or post them until after the goods are received.

Transfer process details

https://docs.microsoft.com/en-us/dynamics-nav-app/design-details-transfers-in-planning

eOne Smartconnect - Calculate current Year for Batch Name

Create calculated field

IF _CREDITHOLD > "0" THEN
 return  "CREDITHOLD"
ELSE return "ORDER " + Convert.ToString(fn.YEAR(_RECEIVEDDATE))
END IF

NAV Business Central CAL/AL - nav could not find a part of the path

It is looking on the local server for the path.
Use network paths instead of local paths.

Business Central - Payables in a currency different from the Bank Currency

Leave the Bank Payment Type blank.

If you choose Manual or Computer check, it will force the transaction to be the same currency as the bank account

DEVOPS Excel Integration Add-In

Link to download Integration to Excel:
https://visualstudio.microsoft.com/downloads/

1. At bottom of screen, Browse to Other Tools and frameworks
2. Look for Azure Devops Office Integration 2019

Further instructions can be found here:
https://docs.microsoft.com/en-us/azure/devops/boards/backlogs/office/bulk-add-modify-work-items-excel?view=azure-devops

Dynamics GP - SOP Lot Qty and Attributes View

/****** Object:  View [dbo].[BI_SOP_LotAttr]    Script Date: 16/10/2019 09:40:44 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_SOP_LotAttr]
AS

SELECT     dbo.SOP10201.SOPTYPE, dbo.SOP10201.SOPNUMBE, dbo.SOP10201.LNITMSEQ, dbo.SOP10201.CMPNTSEQ, dbo.SOP10201.QTYTYPE, dbo.SOP10201.SERLTNUM, dbo.SOP10201.SERLTQTY, dbo.SOP10201.SLTSQNUM, dbo.SOP10201.DATERECD,

                  dbo.SOP10201.DTSEQNUM, dbo.SOP10201.UNITCOST, dbo.SOP10201.ITEMNMBR, dbo.SOP10201.TRXSORCE, dbo.SOP10201.POSTED, dbo.SOP10201.OVRSERLT, dbo.SOP10201.BIN, dbo.SOP10201.MFGDATE, dbo.SOP10201.EXPNDATE,

                  dbo.SOP10201.DEX_ROW_ID, dbo.IV00301.ITEMNMBR AS Expr1, dbo.IV00301.LOTNUMBR, dbo.IV00301.LOTATRB1, dbo.IV00301.LOTATRB2, dbo.IV00301.LOTATRB3, dbo.IV00301.LOTATRB4 AS BestBefore, dbo.IV00301.LOTATRB5,

                  dbo.IV00300.MFGDATE AS Manufactured, dbo.IV00300.EXPNDATE AS Expiry, dbo.IV00300.DATERECD AS Received

FROM        dbo.SOP10201 INNER JOIN

                  dbo.SOP10200 ON dbo.SOP10201.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10201.SOPNUMBE = dbo.SOP10200.SOPNUMBE AND dbo.SOP10201.LNITMSEQ = dbo.SOP10200.LNITMSEQ AND

                  dbo.SOP10201.CMPNTSEQ = dbo.SOP10200.CMPNTSEQ LEFT OUTER JOIN

                  dbo.IV00300 ON dbo.SOP10201.ITEMNMBR = dbo.IV00300.ITEMNMBR AND dbo.SOP10201.SERLTNUM = dbo.IV00300.LOTNUMBR AND dbo.SOP10200.LOCNCODE = dbo.IV00300.LOCNCODE LEFT OUTER JOIN

                  dbo.IV00301 ON dbo.SOP10201.SERLTNUM = dbo.IV00301.LOTNUMBR AND dbo.SOP10201.ITEMNMBR = dbo.IV00301.ITEMNMBR

GO

/****** Object:  View [dbo].[BI_SOP_BulkPick_Lots]    Script Date: 16/10/2019 09:41:13 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_SOP_BulkPick_Lots]
AS
SELECT     PHolds.SOPNUMBE AS IsPHold, dbo.SOP10200.SOPTYPE, dbo.SOP10200.SOPNUMBE, dbo.SOP10100.ORIGTYPE, dbo.SOP10100.ORIGNUMB, dbo.SOP10100.DOCID, dbo.SOP10100.DOCDATE, dbo.SOP10100.GLPOSTDT, dbo.SOP10100.ReqShipDate,
                  dbo.SOP10100.CUSTNMBR, dbo.SOP10100.CUSTNAME, dbo.SOP10100.CSTPONBR, dbo.SOP10200.ITEMNMBR, dbo.SOP10200.ITEMDESC, dbo.SOP10200.UOFM, dbo.SOP10200.LOCNCODE, dbo.SOP10200.UNITCOST, dbo.SOP10200.UNITPRCE,
                  dbo.SOP10200.XTNDPRCE, ISNULL(dbo.BI_SOP_LotAttr.SERLTQTY, dbo.SOP10200.QUANTITY) AS QUANTITY, dbo.SOP10200.ATYALLOC, dbo.SOP10200.QTYBSUOM, dbo.SOP10200.SALSTERR, dbo.SOP10200.SLPRSNID, RTRIM(dbo.SOP10200.SOPNUMBE)
                  + ' | ' + CAST(dbo.SOP10100.DOCDATE AS varchar) + ' | ' + RTRIM(dbo.SOP10100.CUSTNMBR) + ' ' + RTRIM(dbo.SOP10100.ShipToName) + ' | ' + RTRIM(dbo.SOP10200.ADDRESS1) + RTRIM(dbo.SOP10200.ADDRESS2) + RTRIM(dbo.SOP10200.ADDRESS3)
                  + RTRIM(dbo.SOP10200.CITY) AS SOPLbl, dbo.SOP10100.PCKSLPNO, dbo.SOP10100.PICTICNU, dbo.SOP10100.ShipToName, dbo.SOP10100.ADDRESS1, dbo.SOP10100.ADDRESS2, dbo.SOP10100.ADDRESS3, dbo.SOP10100.CITY,
                  RTRIM(dbo.SOP10200.ADDRESS1) + RTRIM(dbo.SOP10200.ADDRESS2) + RTRIM(dbo.SOP10200.ADDRESS3) + RTRIM(dbo.SOP10200.CITY) AS FullAdd, ISNULL(dbo.BI_SOP_LotAttr.SERLTQTY, dbo.SOP10200.QTYTOINV) AS QTYTOINV, dbo.RM00101.HOLD,
                  dbo.IV00101.ITEMSHWT, dbo.BI_SOP_LotAttr.BestBefore, dbo.BI_SOP_LotAttr.SERLTQTY
FROM        dbo.IV00101 INNER JOIN
                  dbo.SOP10200 INNER JOIN
                  dbo.SOP10100 ON dbo.SOP10200.SOPTYPE = dbo.SOP10100.SOPTYPE AND dbo.SOP10200.SOPNUMBE = dbo.SOP10100.SOPNUMBE INNER JOIN
                  dbo.RM00101 ON dbo.SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR ON dbo.IV00101.ITEMNMBR = dbo.SOP10200.ITEMNMBR LEFT OUTER JOIN
                  dbo.BI_SOP_LotAttr ON dbo.SOP10200.CMPNTSEQ = dbo.BI_SOP_LotAttr.CMPNTSEQ AND dbo.SOP10200.ITEMNMBR = dbo.BI_SOP_LotAttr.ITEMNMBR AND dbo.SOP10200.SOPTYPE = dbo.BI_SOP_LotAttr.SOPTYPE AND
                  dbo.SOP10200.SOPNUMBE = dbo.BI_SOP_LotAttr.SOPNUMBE AND dbo.SOP10200.LNITMSEQ = dbo.BI_SOP_LotAttr.LNITMSEQ LEFT OUTER JOIN
                      (SELECT DISTINCT SOPNUMBE
                       FROM        dbo.SOP10104
                       WHERE     (DELETE1 = 0)) AS PHolds ON dbo.SOP10100.SOPNUMBE = PHolds.SOPNUMBE
WHERE     (dbo.SOP10200.SOPTYPE = 2) AND (dbo.SOP10100.PICTICNU > '0') AND (PHolds.SOPNUMBE IS NULL)

GO

Dynamics GP - picking ticket blank picking ticket order entered - Total does not add up to lines

• Create calculated field that uses
• sopindividualpicktickettemp.Qty / 2
• Sum
• 
  

Dynamics GP - The stored procedure create SQLTmpTable returned the following results: DBMS:2627

Issue:
Users experience this issue for a single customer or vendor when opening the inquiry screen

Cause:
There are duplicate records across your work and posted tables.

Resolution:
Review all of the connected tables and records for the specific customer/vendor that is giving this issue

Refer to this article for duplicate checking scripts
http://cowmasterscorner.blogspot.com/2018/02/dynamics-gp-error-occurred-when.html

LS NAV - Only allow Managers to process certain member club cards

o   T99009000 - Add Req. Mgr. Login Boolean

o   P99009000 - Add Req. Mgr. Login Boolean

o   P99009001 - Add Req. Mgr. Login Boolean

o   C99001570

§  InputMemberCard> MemberClub_g

·        If MgrKey=False, then Errorbeep

o   X10032992

§  MemberClub- Add field for Req. Mgr Login

o   T99009643

§  GetMemberInfoForPos

§  Click Publisher & subscriber to reload web requests

·        Updates Request and Response with Req. Mgr Login field

NAV CAL - Boolean Yes / No vs True / False

Boolean type can accept Yes/No and True/False values.
However, if Yes/No is used, it must be explicitly called.

Eg.

This Works
----------------------------------
BooleanTest := True

If BooleanTest THEN
 Message ('true!');
----------------------------------

This does not work
----------------------------------
BooleanTest := Yes

If BooleanTest THEN
 Message ('true!');
-----------------------------------

This works
----------------------------------
BooleanTest := Yes

If BooleanTest = TRUE THEN
 Message ('true!');
-----------------------------------

Dynamics NAV - How to update an AL extension

https://cloudblogs.microsoft.com/dynamics365/no-audience/2018/01/16/generate-symbols-in-a-modern-development-environment-with-microsoft-dynamics-nav-2018/

Ensure the following are ticked on your instance

•  Development>Enable Developer Service Endpoint
• Odata Services>Enable Odata Services

Run This in CMD

• Open the CMD in administrator mode. Navigate to the Role Tailored Client folder (CD Path to folder). Run the following command
• cd 'C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\140\RoleTailored Client'
• .\finsql.exe Command=generatesymbolreference, Database=MYDB, ServerName=[Sql server/Instance]
• Go to Task Manager>Details to see if it's running. It silent, and takes about 15 minutes.
• If you get any errors with the next scripts, the finsql is still running

Run this in Powershell

• Uninstall-NAVApp -ServerInstance YourDynamicsNAVServer -Name 'App Name'
• UnPublish-NAVApp -ServerInstance YourDynamicsNAVServer -Name 'App Name'
• Publish-NAVApp -ServerInstance YourDynamicsNAVServer -Path ".\MyExtension.app" -SkipVerification
• Sync-NAVApp -ServerInstance YourDynamicsNAVServer -Name 'App Name'
• Install-NAVApp -ServerInstance YourDynamicsNAVServer -Name 'App Name'

example

Publish-NAVApp -ServerInstance UAT -Path 'C:\Mods\Module_1.0.0.0\Module_1.0.0.0.app' -SkipVerification

Sync-NAVApp -ServerInstance UAT -Name ‘Module’

Install-NAVApp -ServerInstance UAT -Name ‘Module’

Dynamics GP - Item Lot Quantity SQL View

IV00300 - Item Lot quantities
IV00301 - Lot Attributes


/****** Object:  View [dbo].[BI_ItemLotQty]    Script Date: 09/10/2019 12:01:05 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[BI_ItemLotQty]
AS
SELECT     dbo.IV00300.ITEMNMBR, dbo.IV00300.LOCNCODE, SUM(dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD) AS LotQtyOnHnd, SUM(dbo.IV00300.ATYALLOC) AS LotQtyAllocated, dbo.IV00101.ITMTRKOP,
                  SUM(dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD - dbo.IV00300.ATYALLOC) AS LotQtyAvlbl, dbo.IV00101.USCATVLS_2 AS Category, dbo.IV00101.CURRCOST, SUM(dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD * dbo.IV00101.CURRCOST) AS CostOnHand,
                  dbo.IV00101.ITEMDESC, CASE WHEN iv00101.itmclscd LIKE '%fg%' THEN 'Finished Goods' ELSE 'Other' END AS FGOTH, dbo.IV00301.LOTNUMBR, dbo.IV00301.LOTATRB1, dbo.IV00301.LOTATRB2, dbo.IV00301.LOTATRB3, dbo.IV00301.LOTATRB4 AS BestBefore,
                  dbo.IV00301.LOTATRB5, dbo.IV00300.DATERECD AS Received, dbo.IV00300.MFGDATE AS Manufactured, dbo.IV00300.EXPNDATE AS Expiry
FROM        dbo.IV00101 INNER JOIN
                  dbo.IV00300 ON dbo.IV00101.ITEMNMBR = dbo.IV00300.ITEMNMBR LEFT OUTER JOIN
                  dbo.IV00301 ON dbo.IV00300.LOTNUMBR = dbo.IV00301.LOTNUMBR AND dbo.IV00300.ITEMNMBR = dbo.IV00301.ITEMNMBR
GROUP BY dbo.IV00300.ITEMNMBR, dbo.IV00300.LOCNCODE, dbo.IV00101.ITMTRKOP, dbo.IV00101.USCATVLS_2, dbo.IV00101.CURRCOST, dbo.IV00101.ITEMDESC, dbo.IV00301.LOTNUMBR, dbo.IV00301.LOTATRB1, dbo.IV00301.LOTATRB2, dbo.IV00301.LOTATRB3,
                  dbo.IV00301.LOTATRB4, dbo.IV00301.LOTATRB5, dbo.IV00300.DATERECD, dbo.IV00300.MFGDATE, dbo.IV00300.EXPNDATE, CASE WHEN iv00101.itmclscd LIKE '%fg%' THEN 'Finished Goods' ELSE 'Other' END
HAVING     (dbo.IV00101.ITMTRKOP = 3) AND (dbo.IV00300.LOCNCODE > '') AND (SUM(dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD) <> 0)

GO

SSRS - Field Alignment - Fields and rectangles keep pushing each other around

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/add-a-rectangle-or-container-report-builder-and-ssrs?view=sql-server-2017

Put fields in tables or rectangles