Dynamics NAV - Edit Purchase Invoice to display associated Item Charge Lines in summary

• R406 Purchase Invoice
• Add global ShowItemCharges
• Add global to Request Page
• T122 Purch Inv Hdr
• For each line, run through each value entry, get all associated value entries for Item Charges
• Item Receipt entry No = Item Ledger entry no in Value entry
• T121 Purch Rct Line

Dynamics NAV - Add container number to "Get Receipt Lines"

• T38 Purchase Header
• Add ContainerNo field text 250
• P50 Purchase Order
• Add ContainerNo Field
• T121 Purch Rcpt Line
• Add Flowfield to lookup ContainerNo based on OrderNo
• Lookup("Purchase Header".ContainerNo WHERE (No.=FIELD(Order No.)))
• P5806 Purch. Receipt Lines
• Add ContainerNo

Dynamics NAV - Edit Item Charge Assignment Screen - Add % to apply

• 
  
• T5805 - Item Charge Assignment (Purch) (5805)
• Add field 50000 Percent to Assign Decimal
• P5805 - Item Charge Assignment (Purch) (5805)

Percent to Assign - OnValidate()

//MOD
  CurrPage.UPDATE(TRUE);
  VALIDATE("Qty. to Assign",ROUND(AssignableQty*("Percent to Assign"/100),0.00001,'='));

//MOD

LS NAV - PULLTRANS configuration - Setup HO to Pull transactions periodically from terminals

• Departments/LS Retail/Scheduler/Scheduler/Jobs
• Create PULLTRANS Job
• Scheduler Job Type Code: DD-TO-HO
• From Dist: Include List (List of all Terminals to pull from)
• To-Location Code: HO
• Data Replication
• Subjob Table No. Subjob ID Subjob Description
• 99009038 T_PROCESS_ORDER Process Order Entry
• 99001674 T_TRANS_BENEFIT Trans. Disc. Benefit Entry
• 99001477 T_TRANS_COUPON Trans. Coupon Entry
• 99001642 T_TRANS_DISC Trans. Discount Entry
• 99001475 T_TRANS_INCOME Trans. Income/Expense Entry
• 99001478 T_TRANS_INFOCODE Trans. Infocode Entry
• 99001490 T_TRANS_INVENTORY Trans. Inventory Entry
• 99001496 T_TRANS_MIX Trans. Mix & Match Entry
• 99001474 T_TRANS_PAYMENT Trans. Payment Entry
• 99001473 T_TRANS_SALES Trans. Sales Entry
• 99001465 T_TRANS_TENDERD Trans. Tender Declar. Entry
• 99001472 T_TRANSACT Transaction
• 99001467 T_VOUCHER_ENTRY Voucher Entries

Dynamics NAV - Update Bank Account - Check entries Report to indicate reconciled status and allow filters

• Objects:
• P388 Bank Acc Reconciliation List
• Add button to runobject R1406
• R1406 Bank Account - Check Details
• Check Ledger - On after get record
• Get Reconciled Status of Bank Line

BLine.SETCURRENTKEY("Entry No.");
BLine.SETFILTER("Entry No.",FORMAT("Check Ledger Entry"."Bank Account Ledger Entry No."));
IF BLine.FIND('-') THEN BEGIN
IF BLine."Statement Line No." > 0 THEN BEGIN
IsReconciled := TRUE;
StmtLine := FORMAT(BLine."Statement Line No.");
END ELSE BEGIN
IsReconciled := FALSE;
StmtLine := '';
END;
END;

Dynamics NAV - Auto Pay invoices and send balance to an account. Payment Invoices are automatically paid when prepayment invoices are generated.

Payment Method Setup - If you define a balancing account, the system will auto pay and apply gl entries to offset all transactions using that payment method, and send them to the balancing account specified.

If you notice all of your Invoices automatically generating and applying to payments, it is because there is a balancing account defined on your payment method.

NAV - Purchase Prepayment VAT

The Vat will calculate on the prepayment only if the Vat Product Posting group on the prepayment account is set to have vat calculate.

Outlook 2016 - Expanding address groups - cannot perform requested operation. the command selected is not valid for this recipient.

The autocomplete value being entered is not the real thing.

• Clear the cache and restart outlook.
• File, Options, Mail, Scroll to "Send Messages" > Empty Auto-complete list button.

LS NAV - Enable Cashier Float Permission

Staff Permissions>Floating Entry: Yes

LS NAV - How to create an Adjust costs automated job in Job Scheduler

1. Departments/LS Retail/Scheduler/Scheduler/Jobs
2. Create new job
1. Jobtype: Misc
2. Object Type: Report
3. Object ID: 795 - Adjust cost - Item Entries
4. Set schedule

NAV - Purchasing Process - How to Invoice First, Receive Later

• https://forum.mibuso.com/discussion/36046/purchase-invoice-earlier-receipt-later-in-navision
• 
  
• Option 1
• Use prepayments
• Generate Prepayment Invoices for AP, sends value to a prepayment accont
• Post PO Receive and Invoice when goods arrive
• Will receive goods, deduct prepayment from invoice that was already paid
• Option 2
• Use INTERIM Location
• Create INTERIM Location
• Setup Inv Posting group to send all value to an interim inventory account
• Receive and invoice to that location
• When goods actually arrive, transfer to WAREHOUSE
• Option 3
• Use Additional GL Lines on the PO
• PO Line1: Debit GL Account-Prepayments, 5 x $100 (Qty to Receive = 5)
• PO Line2: Credit GL Account-Prepayment, -5 x $100 (Qty to Rcv = 0)
• PO Line3: Item 5 x $100 (Qty to Rcv = 0)
• Post the PO>Invoice and Receive to generate an invoice for Line1 only
• Pay Invoice
• Goods arrive
• Enter Qty to receive for other lines
• Post the PO>Invoice and Receive to generate an invoice for Line2 and 3 only
• Goods are received, GL entry is reversed
• No invoice is generated

Dynamics NAV - Modify General Journal Page

Add field that displays the account number for user visibility to allow users to change the description as necessary

• Use a flowfield to prevent any posting issues with the table
• Objects
• T81 Gen. Journal Line
• P39 General Journal
• T81
• add accountname flowfield 
• calcformula: Min("G/L Account".Name WHERE (No.=FIELD(Account No.)))
• P39 
• add accountname field - editable false
• add Lineno - editable false

NAV - Unable to see Debit or Credit columns even when they have been added to a page

1.  Departments/Financial Management/Setup/Administration/General Ledger Setup
2. Set  Show Amounts to  All Amounts (Enables debit/credit columns)

LS NAV - Pole Welcome display

terminal card > display > enabled “display terminal closed” and set customer display text for welcome message

LS NAV - Pole display does not show anything

 Hardware Profile card>Displays>Display Characterset to ‘0’

LS Nav - Customer History Shows error

Change the URL to point to the correct web address

POS Browser control card>##Default#tablet_cust_lookup>URL>point to live, or test, or update in restore script

SQL - Get into SQL when sa and all other users are locked out

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-2017

Requires local admin in sysadmin fixed server role

LS NAV - POS - Prevent staff from making price changes or discounts

Staff permission groups control this functionality.
The buttons will be enabled, but they will not be allowed to complete the function.

LS NAV - Setup Quick Cash Menu to display cash suggestions on cash tender

• Generic Cash Button
• Command: TENDER_K
• Parameter: 1 (cash)
• Quick Cash Button
• Command: Menu
• Parameter:QUICKCASH
• Quickcash menu displays blank even after setting this
• Edit the Menu Profile
• Set the QuickCash Menu to QUICKCASH

Excel 2016 - Screen does not refresh, or refreshes slowly

• File>Options>Add-Ins
• Manage>COM Add-Ins>Go
• Untick Team Foundation Add-In (untick ALL)>Ok

Close all Browser windows, especially Sharepoint or Office 365 or CRM screens in the background

NAV - Development Environment - Change Table Name

• It is extremely easy to accidentally change a table name when attempting to search for a table in the NAV Development Environment.
• You should always use Ctrl+F to find your objects
• If you do change the table name by accident, 
• The system will not prompt to save, it will just change the name
• The timestamp will not change, it does not count this as an object change
• The SQL object name will not change
• Any objects that require that object will IMMEDIATELY break in the NAV client, there is no need for a recompile, or service restart

To resolve, just change the name back to the exact original name

Transfer Large files

wetransfer.com

Dynamics NAV - Limit Item List on Purchase Order Item Lookup to Assigned Vendor only

1. Edit the Purchase Line Table in the dev Environment
2. Field No. > Properties
3. Tablerelation>Type=CONST(Item) Line
4. Add Table Filter> Vendor No.=FIELD(Buy-from Vendor No.)

Item Cross Reference No.

1. This can be used to store additional vendor item numbers on an item to allow for lookup using the Cross Reference no. Column that must be added to the Purchase Order line Screen

LS Retail - Retail Purchase Order uses different lookup logic, you should use Purchase Order for regular purchases.

1. Retail Setup - Other>PO Item Lookup Method

Dynamics NAV - Adjust Costs job takes very long.

Create a routine that runs the adjust cost job for one item at a time. in small groups.
Eventually, once all of the items have had the adjust cost complete relatively recently, the entire job will run faster.

Get all items where "Cost is Adjusted" = False

Write a routine to get the next X items ahead in the list.

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

GetEndItemRange(ItmRange : Integer) : Text[50]

Itm3.SETCURRENTKEY("No.");

Itm3.SETRANGE("Cost is Adjusted",FALSE);

Itm3.SETFILTER("No.",'>='+FirstItem);

Ct:= 0;

IF Itm3.FINDFIRST THEN BEGIN

  REPEAT

    Ct:= Ct + 1;

    Itm3.NEXT;

    TheNo := Itm3."No.";

  UNTIL Ct = ItmRange;

  EXIT(TheNo);

END;

Call the Adjust Cost Item Entries with the item number filter begin and end to run it only for that small range

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

 Itm2.SETRANGE("No.",CurrItemNo, EndCurrItemNo);

    InvtAdjmt.SetFilterItem(Itm2);

    InvtAdjmt.MakeMultiLevelAdjmt;

Include a COMMIT at the end of the REPEAT to ensure that batch is written so the whole list doesn't get undone if it runs into an error.

Dynamics GP - Stock Count Entry - Posting Date is greyed out

Once the Stock count entry has a status of "Entered" after entering and verifying all lines, you will be able to change the posting date.

LS NAV - Get Z-Report info if Z-report sticks

Run T99008955 Z-Report Statistics
Get last entry

Gross Sales - Includes VAT

Net Sales - Vat not included

LS NAV - Update Barcode UofM field with Item UofM code

--Update Barcode UofM's to match Item UofM's for Bulk Label Printing

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

update BARCODE

Set BARCODE.[Unit of Measure Code] = ITEM.[Base Unit of Measure]

From [CRONUS LS 1101 W1 Demo$Barcodes] as BARCODE

INNER JOIN [CRONUS LS 1101 W1 Demo$Item] as ITEM on BARCODE.[Item No_] = ITEM.No_

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

SQL Update From Select Examples

https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server

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

UPDATE Table_A 

SET Table_A.col1 = Table_B.col1, Table_A.col2 = Table_B.col2 

FROM Some_Table AS Table_A 

INNER JOIN Other_Table AS Table_B ON Table_A.id = Table_B.id 

WHERE Table_A.col3 = 'cool'

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

--Update Barcode UofM's to match Item UofM's for Bulk Label Printing

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

update BARCODE

Set BARCODE.[Unit of Measure Code] = ITEM.[Base Unit of Measure]

From [CRONUS LS 1101 W1 Demo$Barcodes] as BARCODE

INNER JOIN [CRONUS LS 1101 W1 Demo$Item] as ITEM on BARCODE.[Item No_] = ITEM.No_

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

Dynamics NAV - Modify Bank Account Statement Report Notes

T1407 - Bank Account Statement (Defined under Bank report selections)
T1408 - Bank Acc Recon Test

Dynamics NAV - Default Dimensions

Default dimensions should be set on

• Location
• Customer
• Specific GL accounts

Dimensions - Single: Set default dimensions on this one record

Dimensions - Multiple: Set default dimensions on all the records you have selected

---------------------------
Some pages will allow you to add the global dimensions as fields on the page.
Add them, and set ShowMandatory = True to force them to be required

LS NAV - Check for AR Over 30 Days in addition to Credit Limit on POS

Create customer Function to check the Customer Balance
---------------------------------------------------------------------------------
OnRun()
MESSAGE (FORMAT(GetCusBalOverXDays('30000',30)));

GetCusBalOverXDays(CusNo : Text;Days : Integer) : Decimal
CLE.SETCURRENTKEY("Customer No.");
CLE.SETRANGE("Customer No.",CusNo);
CLE.SETRANGE(Open,TRUE);
DayExp := '<-'+FORMAT(Days)+'D>';
OldDate := CALCDATE(DayExp,TODAY);
CLE.SETFILTER("Posting Date",'>='+FORMAT(OldDate));
CusAmt := 0;
IF CLE.FIND('-') THEN BEGIN
  REPEAT
    CLE.CALCFIELDS("Amount (LCY)");
    CusAmt := CusAmt + CLE."Amount (LCY)";
  UNTIL CLE.NEXT <= 0;
END;

EXIT (CusAmt);

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

Modify the C99008900 POS functions, Search for Customer."Credit Limit and include the balance from the previous function in the balance check.

-------------------------------------------------------------------------------------
  IF (BillToCustomer."Credit Limit (LCY)" <> 0) THEN BEGIN
    cBalanceOverLimit := (BillToCustomer."Balance (LCY)" + Customer."Amt. Charged On POS" +
      BillToCustomer."Amt. Charged On POS" - Customer."Amt. Charged Posted" -
      BillToCustomer."Amt. Charged Posted" + Payment) - BillToCustomer."Credit Limit (LCY)";
    //MOD1
    //IF cBalanceOverLimit > 0 THEN BEGIN
    IF (cBalanceOverLimit > 0) AND (DT.GetCusBalOverXDays(BillToCustomer."No.",30)>0) THEN BEGIN
    //MOD1
      IF NOT MgrKey THEN BEGIN
        MessageTxt := STRSUBSTNO(Text050,Customer."Bill-to Customer No.");
        EXIT(FALSE);
      END;

LS NAV - Prevent Manager Override of Credit Limits on POS

In LS Retail POS, if a customer is assigned to a transaction, and it goes over the credit limit, a manager is allowed to override the credit limit.

This should be removed to only display a warning, but not allow override

-------------------------
C99001570
C99008900 POS functions
Customer."Credit Limit


IF (Customer."Credit Limit (LCY)" <> 0) THEN BEGIN
  cBalanceOverLimit := (Customer."Balance (LCY)" + Customer."Amt. Charged On POS" - Customer."Amt. Charged Posted" + Payment) - Customer."Credit Limit (LCY)";
  IF cBalanceOverLimit > 0 THEN BEGIN
//MOD1
    //IF NOT MgrKey THEN BEGIN
    IF (MgrKey) OR (NOT MgrKey) THEN BEGIN
//MOD1
      MessageTxt := STRSUBSTNO(Text050,Customer."No.");
      EXIT(FALSE);

Dynamics NAV - Unable to connect to the web services

Run the NAV Install
Add components
Web server

-----
In NAV Admin tool>NAV Instance>SOAP>Enable SOAP Services must be ticked
Ensure port is open, and nothing else is running on it
Ensure service running nav instance is local admin, has access to the database

-----
 run sync-navtenant dynamicsnav110(replace with your nav instance)

---

Be sure to fill in Functionality Profile / Web Servers -> Dist. Location.

------
Increase concurrent Connections and max upload size in the server instance settings

------------
Distributions Locations>Ensure Web Service URL for HO location is correct -----------
Functionality Profile>Web Servers> 
Ensure the "Local Request is not ticked
Ensure the HO location is selected

LS NAV - Add columns to a lookup for additional info or filtering

• From Lookup, Right click>Lookup Properties>Data Table Id>Three Dots
• Edit the POS Data Table Columns to change which columns you want to see instead
• Special Field type: FindByNameField if it's a text search field you need

OR 

Build a custom lookup

https://www.archerpoint.com/blog/Posts/dynamics-nav-how-program-lookups

LS NAV - POS - Change the color of a specific button

• Button colors are based on the Skin setup on the Style profile used for the Line
• Buttons will all get the default colors based on the Style profile assigned to the line , or the ##Default Style profile in the Style setup
• Buttons change colors based on the POS Command assigned to them
• You can define a skin style on a pos command
• You must restart the pos to see the color changes

LS NAV - Copy Live to Test

BACKUP DATABASE [NAV] TO  DISK = N'S:\Backup\NAV_temp.bak' WITH NOFORMAT, INIT,  NAME = N'NAV-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
USE [master]
ALTER DATABASE [TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TEST] FROM  DISK = N'S:\Backup\NAV_temp.bak' WITH  FILE = 1,  MOVE N'w1-ls-nav-11-0-release_Data' TO N'S:\SQLSVR2016\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_Data_0.mdf',  MOVE N'w1-ls-nav-11-0-release_1_Data' TO N'S:\SQLSVR2016\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_1_Data_0.ndf',  MOVE N'w1-ls-nav-11-0-release_Log' TO N'S:\SQLSVR2016\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_Log_0.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [TEST] SET MULTI_USER

GO
  FROM
  update [TEST].[dbo].[CRONUS$Distribution Location] set [Db_ Path && Name] = 'TEST' where [Db_ Path && Name] = 'NAV'
  update [TEST].[dbo].[CRONUS$Distribution Location] set [Db_ Path && Name] = 'POSTEST' where [Db_ Path && Name] = 'NAV'
  update [TEST].[dbo].[CRONUS$Distribution Location] set [Web Server Name] = 'TEST'
  update [TEST].[dbo].[CRONUS$Distribution Location] set [Web Service URI] = ''

--Change Company Name

UPDATE [TEST].[dbo].[CRONUS$Company Information] set [Name] = 'CRONUS STAGING'

  update [TEST].[dbo].[Company] set [Display Name] = 'CRONUS STAGING' where [Name] = 'CRONUS'

LS NAV - Object Replication - Object Transfer error in DD -The database has already been converted by a newer version

You would need to select the correct version (or manually edit the connection string) on the Distribution Location.
The version should reflect the version you are connecting to.
For example 11.00-SQL for NAV 2018, 10.00-SQL for NAV 2017 etc.


The Dist. Location Versions table may not have all of the newer versions, use the latest Cronus to get the settings.

Be sure to change all locations to the new Dist. Location Version, and synchronize the data to all terminals before attempting to push the object.
If the object cannot compile, the database will be left in a non-functional uncompiled state.

Test the object push on a single machine before pushing to multiple.

LS NAV - REGDB_E_CLASSNOTREG

The dll you're trying to call is not registered, or only works on 32-bit.
Launch the 32-Bit NAV Client instead