Table field changes between Nav 2013 and Nav 2016

In addition to last postings about table changes you’ll find here the field changes.

New table fields in Nav 2016:

Table No.	Table Name	Field No.	Field Name
9	Country/Region	10	VAT Scheme
15	G/L Account	70	Omit Default Descr. in Jnl.
15	G/L Account	1700	Default Deferral Template Code
18	Customer	11	Document Sending Profile
18	Customer	90	GLN
18	Customer	288	Preferred Bank Account
21	Cust. Ledger Entry	172	Payment Method Code
21	Cust. Ledger Entry	173	Applies-to Ext. Doc. No.
21	Cust. Ledger Entry	290	Exported to Payment File
23	Vendor	90	GLN
23	Vendor	132	Partner Type
23	Vendor	170	Creditor No.
23	Vendor	288	Preferred Bank Account
25	Vendor Ledger Entry	170	Creditor No.
25	Vendor Ledger Entry	171	Payment Reference
25	Vendor Ledger Entry	172	Payment Method Code
25	Vendor Ledger Entry	173	Applies-to Ext. Doc. No.
25	Vendor Ledger Entry	288	Recipient Bank Account
25	Vendor Ledger Entry	289	Message to Recipient
25	Vendor Ledger Entry	290	Exported to Payment File
27	Item	10	Type
27	Item	120	Stockout Warning
27	Item	121	Prevent Negative Inventory
27	Item	1217	GTIN
27	Item	1700	Default Deferral Template Code
27	Item	7385	Next Counting Start Date
27	Item	7386	Next Counting End Date
36	Sales Header	56	Recalculate Invoice Disc.
36	Sales Header	165	Incoming Document Entry No.
36	Sales Header	1200	Direct Debit Mandate ID
36	Sales Header	1305	Invoice Discount Amount
37	Sales Line	56	Recalculate Invoice Disc.
37	Sales Line	84	Tax Category
37	Sales Line	1300	Posting Date
37	Sales Line	1700	Deferral Code
37	Sales Line	1702	Returns Deferral Start Date
38	Purchase Header	56	Recalculate Invoice Disc.
38	Purchase Header	165	Incoming Document Entry No.
38	Purchase Header	170	Creditor No.
38	Purchase Header	171	Payment Reference
38	Purchase Header	1305	Invoice Discount Amount
38	Purchase Header	9001	Pending Approvals
39	Purchase Line	56	Recalculate Invoice Disc.
39	Purchase Line	1700	Deferral Code
39	Purchase Line	1702	Returns Deferral Start Date
39	Purchase Line	5005396	Order No.
39	Purchase Line	5005397	Order Line No.
49	Invoice Post. Buffer	1700	Deferral Code
49	Invoice Post. Buffer	1701	Deferral Line No.
79	Company Information	50	Allow Blank Payment Info.
79	Company Information	90	GLN
81	Gen. Journal Line	165	Incoming Document Entry No.
81	Gen. Journal Line	170	Creditor No.
81	Gen. Journal Line	171	Payment Reference
81	Gen. Journal Line	172	Payment Method Code
81	Gen. Journal Line	1220	Data Exch. Entry No.
81	Gen. Journal Line	1221	Payer Information
81	Gen. Journal Line	1222	Transaction Information
81	Gen. Journal Line	1223	Data Exch. Line No.
81	Gen. Journal Line	1224	Applied Automatically
81	Gen. Journal Line	1700	Deferral Code
81	Gen. Journal Line	1701	Deferral Line No.
81	Gen. Journal Line	5618	Comment
85	Acc. Schedule Line	30	Double Underline
91	User Setup	21	Approval Administrator
96	G/L Budget Entry	16	Last Date Modified
98	General Ledger Setup	160	Payroll Trans. Import Format
98	General Ledger Setup	161	VAT Reg. No. Validation URL
112	Sales Invoice Header	710	Document Exchange Identifier
112	Sales Invoice Header	711	Document Exchange Status
112	Sales Invoice Header	712	Doc. Exch. Original Identifier
112	Sales Invoice Header	720	Coupled to CRM
112	Sales Invoice Header	1200	Direct Debit Mandate ID
112	Sales Invoice Header	1300	Canceled By
112	Sales Invoice Header	1301	Canceled
112	Sales Invoice Header	1302	Paid
112	Sales Invoice Header	1303	Remaining Amount
112	Sales Invoice Header	1304	Cust. Ledger Entry No.
112	Sales Invoice Header	1305	Invoice Discount Amount
113	Sales Invoice Line	84	Tax Category
113	Sales Invoice Line	1700	Deferral Code
114	Sales Cr.Memo Header	710	Document Exchange Identifier
114	Sales Cr.Memo Header	711	Document Exchange Status
114	Sales Cr.Memo Header	712	Doc. Exch. Original Identifier
114	Sales Cr.Memo Header	1300	Canceled
114	Sales Cr.Memo Header	1302	Paid
114	Sales Cr.Memo Header	1303	Remaining Amount
114	Sales Cr.Memo Header	1304	Cust. Ledger Entry No.
114	Sales Cr.Memo Header	1305	Invoice Discount Amount
115	Sales Cr.Memo Line	84	Tax Category
115	Sales Cr.Memo Line	1700	Deferral Code
122	Purch. Inv. Header	170	Creditor No.
122	Purch. Inv. Header	171	Payment Reference
122	Purch. Inv. Header	1300	Canceled By
122	Purch. Inv. Header	1301	Canceled
122	Purch. Inv. Header	1302	Paid
122	Purch. Inv. Header	1303	Remaining Amount
122	Purch. Inv. Header	1304	Vendor Ledger Entry No.
122	Purch. Inv. Header	1305	Invoice Discount Amount
123	Purch. Inv. Line	1700	Deferral Code
124	Purch. Cr. Memo Hdr.	1300	Canceled
124	Purch. Cr. Memo Hdr.	1302	Paid
124	Purch. Cr. Memo Hdr.	1303	Remaining Amount
124	Purch. Cr. Memo Hdr.	1304	Vendor Ledger Entry No.
124	Purch. Cr. Memo Hdr.	1305	Invoice Discount Amount
125	Purch. Cr. Memo Line	1700	Deferral Code
156	Resource	1700	Default Deferral Template Code
172	Standard Customer Sales Code	4	Valid From Date
172	Standard Customer Sales Code	5	Valid To date
172	Standard Customer Sales Code	6	Payment Method Code
172	Standard Customer Sales Code	7	Payment Terms Code
172	Standard Customer Sales Code	8	Direct Debit Mandate ID
172	Standard Customer Sales Code	9	Blocked
204	Unit of Measure	3	International Standard Code
232	Gen. Journal Batch	11	Allow Payment Export
232	Gen. Journal Batch	12	Bank Statement Import Format
242	Source Code Setup	49	Payment Reconciliation Journal
246	Requisition Line	7100	Blanket Purch. Order Exists
270	Bank Account	42	Last Payment Statement No.
270	Bank Account	62	Total on Checks
270	Bank Account	113	Bank Statement Import Format
270	Bank Account	116	Direct Debit Msg. Nos.
270	Bank Account	117	SEPA Direct Debit Exp. Format
270	Bank Account	1210	Payment Export Format
270	Bank Account	1211	Bank Clearing Code
270	Bank Account	1212	Bank Clearing Standard
270	Bank Account	1213	Bank Name – Data Conversion
270	Bank Account	1250	Match Tolerance Type
270	Bank Account	1251	Match Tolerance Value
270	Bank Account	1260	Positive Pay Export Code
271	Bank Account Ledger Entry	70	Check Ledger Entries
272	Check Ledger Entry	23	Data Exch. Entry No.
272	Check Ledger Entry	24	Data Exch. Voided Entry No.
272	Check Ledger Entry	25	Positive Pay Exported
272	Check Ledger Entry	26	Record ID to Print
273	Bank Acc. Reconciliation	6	Bank Statement
273	Bank Acc. Reconciliation	7	Total Balance on Bank Account
273	Bank Acc. Reconciliation	8	Total Applied Amount
273	Bank Acc. Reconciliation	9	Total Transaction Amount
273	Bank Acc. Reconciliation	20	Statement Type
273	Bank Acc. Reconciliation	21	Shortcut Dimension 1 Code
273	Bank Acc. Reconciliation	22	Shortcut Dimension 2 Code
273	Bank Acc. Reconciliation	480	Dimension Set ID
274	Bank Acc. Reconciliation Line	15	Related-Party Name
274	Bank Acc. Reconciliation Line	16	Additional Transaction Info
274	Bank Acc. Reconciliation Line	17	Data Exch. Entry No.
274	Bank Acc. Reconciliation Line	18	Data Exch. Line No.
274	Bank Acc. Reconciliation Line	20	Statement Type
274	Bank Acc. Reconciliation Line	21	Account Type
274	Bank Acc. Reconciliation Line	22	Account No.
274	Bank Acc. Reconciliation Line	23	Transaction Text
274	Bank Acc. Reconciliation Line	24	Related-Party Bank Acc. No.
274	Bank Acc. Reconciliation Line	25	Related-Party Address
274	Bank Acc. Reconciliation Line	26	Related-Party City
274	Bank Acc. Reconciliation Line	31	Shortcut Dimension 1 Code
274	Bank Acc. Reconciliation Line	32	Shortcut Dimension 2 Code
274	Bank Acc. Reconciliation Line	50	Match Confidence
274	Bank Acc. Reconciliation Line	51	Match Quality
274	Bank Acc. Reconciliation Line	60	Sorting Order
274	Bank Acc. Reconciliation Line	480	Dimension Set ID
279	Extended Text Header	8	Description
287	Customer Bank Account	1211	Bank Clearing Code
287	Customer Bank Account	1212	Bank Clearing Standard
288	Vendor Bank Account	1211	Bank Clearing Code
288	Vendor Bank Account	1212	Bank Clearing Standard
289	Payment Method	6	Direct Debit
289	Payment Method	7	Direct Debit Pmt. Terms Code
289	Payment Method	8	Pmt. Export Line Definition
289	Payment Method	9	Bank Data Conversion Pmt. Type
290	VAT Amount Line	19	Tax Category
311	Sales & Receivables Setup	44	VAT Bus. Posting Gr. (Price)
312	Purchases & Payables Setup	1217	Debit Acc. for Non-Item Lines
312	Purchases & Payables Setup	1218	Credit Acc. for Non-Item Lines
313	Inventory Setup	40	Prevent Negative Inventory
325	VAT Posting Setup	17	Tax Category
370	Excel Buffer	16	Double Underline
372	Payment Buffer	170	Creditor No.
372	Payment Buffer	171	Payment Reference
372	Payment Buffer	172	Payment Method Code
372	Payment Buffer	173	Applies-to Ext. Doc. No.
372	Payment Buffer	290	Exported to Payment File
379	Detailed Cust. Ledg. Entry	43	Ledger Entry Amount
380	Detailed Vendor Ledg. Entry	43	Ledger Entry Amount
386	Entry No. Amount Buffer	5	Start Date
386	Entry No. Amount Buffer	6	End Date
400	XBRL Linkbase	8	File Name
427	IC Outbox Sales Line	63	Shipment No.
427	IC Outbox Sales Line	64	Shipment Line No.
427	IC Outbox Sales Line	6600	Return Receipt No.
427	IC Outbox Sales Line	6601	Return Receipt Line No.
431	Handled IC Outbox Sales Line	63	Shipment No.
431	Handled IC Outbox Sales Line	64	Shipment Line No.
431	Handled IC Outbox Sales Line	6600	Return Receipt No.
431	Handled IC Outbox Sales Line	6601	Return Receipt Line No.
437	IC Inbox Purchase Line	63	Receipt No.
437	IC Inbox Purchase Line	64	Receipt Line No.
437	IC Inbox Purchase Line	6600	Return Shipment No.
437	IC Inbox Purchase Line	6601	Return Shipment Line No.
441	Handled IC Inbox Purch. Line	63	Receipt No.
441	Handled IC Inbox Purch. Line	64	Receipt Line No.
441	Handled IC Inbox Purch. Line	6600	Return Shipment No.
441	Handled IC Inbox Purch. Line	6601	Return Shipment Line No.
454	Approval Entry	21	Pending Approvals
454	Approval Entry	22	Record ID to Approve
454	Approval Entry	23	Delegation Date Formula
454	Approval Entry	26	Number of Approved Requests
454	Approval Entry	27	Number of Rejected Requests
454	Approval Entry	29	Entry No.
454	Approval Entry	30	Workflow Step Instance ID
454	Approval Entry	31	Related to Change
455	Approval Comment Line	8	Record ID to Approve
456	Posted Approval Entry	22	Posted Record ID
456	Posted Approval Entry	23	Delegation Date Formula
456	Posted Approval Entry	26	Number of Approved Requests
456	Posted Approval Entry	27	Number of Rejected Requests
456	Posted Approval Entry	28	Iteration No.
456	Posted Approval Entry	29	Entry No.
457	Posted Approval Comment Line	8	Posted Record ID
458	Overdue Approval Entry	15	Record ID to Approve
472	Job Queue Entry	10	Report Output Type
472	Job Queue Entry	44	User Language ID
472	Job Queue Entry	45	Printer Name
472	Job Queue Entry	46	Report Request Page Options
472	Job Queue Entry	47	Rerun Delay (sec.)
700	Error Message	8	Table Number
700	Error Message	10	Context Record ID
700	Error Message	11	Field Name
700	Error Message	12	Table Name
740	VAT Report Header	7	VAT Registration No.
740	VAT Report Header	10	Report Period Type
740	VAT Report Header	11	Report Period No.
740	VAT Report Header	12	Report Year
740	VAT Report Header	16	Processing Date
740	VAT Report Header	19	Test Export
740	VAT Report Header	20	Notice
740	VAT Report Header	21	Revocation
740	VAT Report Header	28	Trade Type
740	VAT Report Header	29	EU Goods/Services
740	VAT Report Header	31	Total Base
740	VAT Report Header	32	Total Amount
740	VAT Report Header	33	Total Number of Supplies
740	VAT Report Header	34	Total Number of Lines
740	VAT Report Header	40	Company Name
740	VAT Report Header	41	Company Address
740	VAT Report Header	42	Country/Region Name
740	VAT Report Header	48	City
740	VAT Report Header	49	Post Code
740	VAT Report Header	50	Tax Office ID
740	VAT Report Header	51	Sign-off Place
740	VAT Report Header	52	Sign-off Date
740	VAT Report Header	53	Signed by Employee No.
740	VAT Report Header	54	Created by Employee No.
741	VAT Report Line	17	EU Service
741	VAT Report Line	24	Number of Supplies
741	VAT Report Line	30	Trade Type
741	VAT Report Line	31	Line Type
741	VAT Report Line	32	Related Line No.
741	VAT Report Line	33	Trade Role Type
741	VAT Report Line	50	Corrected Reg. No.
741	VAT Report Line	51	Corrected Amount
741	VAT Report Line	54	Registration No.
741	VAT Report Line	99	System-Created
741	VAT Report Line	101	VAT Report to Correct
741	VAT Report Line	102	Able to Correct Line
743	VAT Report Setup	11000	Source Identifier
743	VAT Report Setup	11001	Transmission Process ID
743	VAT Report Setup	11002	Supplier ID
743	VAT Report Setup	11003	Codepage
743	VAT Report Setup	11004	Registration ID
743	VAT Report Setup	11005	Export Cancellation Lines
762	Account Schedules Chart Setup	3	Description
762	Account Schedules Chart Setup	51	Look Ahead
801	Online Map Parameter Setup	9	Directions from Location Serv.
1226	Payment Export Data	28	Sender Bank Name – Data Conv.
1226	Payment Export Data	29	Sender Bank Name
1226	Payment Export Data	32	Sender Bank Account Currency
1226	Payment Export Data	33	Sender Bank Country/Region
1226	Payment Export Data	35	Sender Bank Clearing Std.
1226	Payment Export Data	36	Sender Bank Clearing Code
1226	Payment Export Data	37	Sender Bank Address
1226	Payment Export Data	38	Sender Bank City
1226	Payment Export Data	39	Sender Bank Post Code
1226	Payment Export Data	45	Recipient Email Address
1226	Payment Export Data	46	Recipient ID
1226	Payment Export Data	48	Recipient Bank Clearing Std.
1226	Payment Export Data	49	Recipient Bank Clearing Code
1226	Payment Export Data	84	Payment Type
1226	Payment Export Data	89	Invoice Amount
1226	Payment Export Data	90	Invoice Date
1226	Payment Export Data	91	Recipient County
1226	Payment Export Data	92	Recipient Bank County
1226	Payment Export Data	93	Sender Bank County
1226	Payment Export Data	130	Importing Code
1226	Payment Export Data	131	Importing Date
1226	Payment Export Data	132	Importing Description
1226	Payment Export Data	133	Costs Distribution
1226	Payment Export Data	134	Message Structure
1226	Payment Export Data	135	Own Address Info.
1226	Payment Export Data	171	Transit No.
1226	Payment Export Data	200	Format Command
1226	Payment Export Data	201	Format Remittance Info Type
1226	Payment Export Data	220	Format Payment Type
1226	Payment Export Data	221	Format Expense Code
1226	Payment Export Data	222	Format Text Code
1226	Payment Export Data	283	Format Form Type
1228	Payment Jnl. Export Error Text	7	Additional Information
1228	Payment Jnl. Export Error Text	8	Support URL
5079	Marketing Setup	71	Exchange Service URL
5079	Marketing Setup	72	Exchange Account User Name
5079	Marketing Setup	73	Exchange Account Password Key
5108	Sales Line Archive	1700	Deferral Code
5108	Sales Line Archive	1702	Returns Deferral Start Date
5110	Purchase Line Archive	1700	Deferral Code
5110	Purchase Line Archive	1702	Returns Deferral Start Date
5700	Stockkeeping Unit	7385	Next Counting Start Date
5700	Stockkeeping Unit	7386	Next Counting End Date
5741	Transfer Line	43	Appl.-to Item Entry
5911	Service Mgt. Setup	950	Copy Time Sheet to Order
5992	Service Invoice Header	710	Document Exchange Identifier
5992	Service Invoice Header	711	Document Exchange Status
5992	Service Invoice Header	712	Doc. Exch. Original Identifier
5994	Service Cr.Memo Header	710	Document Exchange Identifier
5994	Service Cr.Memo Header	711	Document Exchange Status
5994	Service Cr.Memo Header	712	Doc. Exch. Original Identifier
7302	Bin Content	6503	Unit of Measure Filter
7380	Phys. Invt. Item Selection	12	Next Counting Start Date
7380	Phys. Invt. Item Selection	13	Next Counting End Date
8613	Config. Package Table	28	Processing Report ID
8614	Config. Package Record	10	Parent Record No.
8616	Config. Package Field	14	Create Missing Codes
8616	Config. Package Field	15	Mapping Exists
8619	Config. Template Line	16	Language ID
9053	Sales Cue	9	Average Days Delayed
9053	Sales Cue	10	Sales Inv. – Pending Doc.Exch.
9053	Sales Cue	12	Sales CrM. – Pending Doc.Exch.
9054	Finance Cue	22	New Incoming Documents
9054	Finance Cue	23	Approved Incoming Documents
9054	Finance Cue	24	OCR Pending
9054	Finance Cue	25	OCR Completed
9054	Finance Cue	26	Requests to Approve
9054	Finance Cue	27	Requests Sent for Approval
9054	Finance Cue	28	User ID Filter
9060	SB Owner Cue	11	Sales Invoices
9060	SB Owner Cue	12	Unpaid Sales Invoices
9060	SB Owner Cue	13	Overdue Sales Invoices
9060	SB Owner Cue	14	Sales Quotes
9060	SB Owner Cue	30	Purchase Invoices
9060	SB Owner Cue	31	Unpaid Purchase Invoices
9060	SB Owner Cue	32	Overdue Purchase Invoices
11014	Certificate	4	PFX File
11014	Certificate	5	Elster Certificate
11014	Certificate	6	PFX File Password
2000000053	Access Control	8	Scope
2000000053	Access Control	9	App ID
2000000053	Access Control	10	App Name
2000000069	Add-in	15	Category
2000000069	Add-in	25	Resource
2000000071	Object Metadata	30	Hash
2000000071	Object Metadata	33	Object Subtype
2000000071	Object Metadata	34	Has Subscribers
2000000073	User Personalization	27	Locale ID
2000000073	User Personalization	30	Time Zone
2000000079	Object Tracking	15	App Package ID
2000000079	Object Tracking	18	Tenant ID
2000000112	Server Instance	7	Management Port
2000000112	Server Instance	8	Status
2000000120	User	11	Authentication Email
2000000120	User	14	Contact Email
2000000121	User Property	7	Authentication Object ID

Removed table fields:

Table No.	Table Name	Field No.	Field Name
27	Item	7382	Next Counting Period
79	Company Information	11020	House Number
79	Company Information	11021	Floor Number
79	Company Information	11022	Room Number
744	VAT Report Line Relation	3	Line No.
5600	Fixed Asset	11100	Start of Use Date
5700	Stockkeeping Unit	7382	Next Counting Period
7380	Phys. Invt. Item Selection	8	Next Counting Period
8613	Config. Package Table	6	No. of Database Records
11004	Data Export Record Source	20	No. Of Sessions
11014	Certificate	3	Certificate Ref.
2000000072	Profile	6	Owner SID
2000000072	Profile	9	Owner ID
2000000078	Chart	12	Company
2000000100	Debugger Breakpoint	3	User SID
2000000100	Debugger Breakpoint	5	User ID
2000000104	Debugger Watch	3	User SID
2000000104	Debugger Watch	5	User ID

Removed tables: 452, 453, 464, 465, 1234, 2000000203

regards

 

Tables Changes from Nav 2013 to Nav 2016

For upgrade processes from nav 2013 to nav 2016 i have developed following table comparison.

New in Nav 2016:

60	Document Sending Profile
61	Electronic Document Format
130	Incoming Document
131	Incoming Documents Setup
132	Incoming Document Approver
133	Incoming Document Attachment
134	Posted Docs. With No Inc. Buf.
135	Acc. Sched. KPI Web Srv. Setup
136	Acc. Sched. KPI Web Srv. Line
137	Inc. Doc. Attachment Overview
249	VAT Registration Log
477	Report Inbox
487	Business Chart User Setup
710	Activity Log
806	Geolocation
870	Social Listening Setup
871	Social Listening Search Topic
980	Payment Registration Setup
981	Payment Registration Buffer
983	Document Search Result
1150	Report Totals Buffer
1200	Bank Export/Import Setup
1205	Credit Transfer Register
1206	Credit Transfer Entry
1209	Credit Trans Re-export History
1213	Data Exchange Type
1214	Intermediate Data Import
1220	Data Exch.
1221	Data Exch. Field
1222	Data Exch. Def
1223	Data Exch. Column Def
1224	Data Exch. Mapping
1225	Data Exch. Field Mapping
1227	Data Exch. Line Def
1231	Positive Pay Entry
1232	Positive Pay Entry Detail
1235	XML Structure
1237	Transformation Rule
1240	Positive Pay Header
1241	Positive Pay Detail
1242	Positive Pay Footer
1248	Ledger Entry Matching Buffer
1249	Bank Stmt Multiple Match Line
1250	Bank Statement Matching Buffer
1251	Text-to-Account Mapping
1252	Bank Pmt. Appl. Rule
1259	Bank Data Conv. Bank
1260	Bank Data Conv. Service Setup
1261	Service Password
1265	Data Exch. Field Mapping Buf.
1270	OCR Service Setup
1271	OCR Service Document Template
1275	Doc. Exch. Service Setup
1280	Bank Clearing Standard
1281	Bank Data Conversion Pmt. Type
1293	Payment Application Proposal
1294	Applied Payment Entry
1295	Posted Payment Recon. Hdr
1296	Posted Payment Recon. Line
1299	Payment Matching Details
1300	Mini Customer Template
1301	Mini Item Template
1302	Mini Dimensions Template
1303	Mini Vendor Template
1304	Sales Price and Line Disc Buff
1305	Mini Pages Mapping
1306	Mini User Removed Instructions
1310	Mini Chart Definition
1311	Mini Last Used Chart
1312	Mini Trial Balance Setup
1313	Mini Activities Cue
1319	Sales by Cust. Grp.Chart Setup
1400	Service Connection
1500	Workflow Buffer
1501	Workflow
1502	Workflow Step
1504	Workflow Step Instance
1505	Workflow – Table Relation
1506	Workflow Table Relation Value
1507	Workflow Step Buffer
1508	Workflow Category
1509	WF Event/Response Combination
1510	Notification Template
1511	Notification Entry
1512	Notification Setup
1513	Notification Schedule
1514	Sent Notification Entry
1515	Dynamic Request Page Entity
1516	Dynamic Request Page Field
1520	Workflow Event
1521	Workflow Response
1522	Workflow Event Queue
1523	Workflow Step Argument
1524	Workflow Rule
1525	Workflow – Record Change
1526	Workflow Record Change Archive
1530	Workflow Step Instance Archive
1540	Workflow User Group
1541	Workflow User Group Member
1550	Restricted Record
1650	Curr. Exch. Rate Update Setup
1700	Deferral Template
1701	Deferral Header
1702	Deferral Line
1703	Deferral Post. Buffer
1704	Posted Deferral Header
1705	Posted Deferral Line
5127	Deferral Header Archive
5128	Deferral Line Archive
5329	CRM Redirect
5330	CRM Connection Setup
5331	CRM Integration Record
5335	Integration Table Mapping
5336	Integration Field Mapping
5337	Temp Integration Field Mapping
5338	Integration Synch. Job
5339	Integration Synch. Job Errors
5340	CRM Systemuser
5341	CRM Account
5342	CRM Contact
5343	CRM Opportunity
5344	CRM Post
5345	CRM Transactioncurrency
5346	CRM Pricelevel
5347	CRM Productpricelevel
5348	CRM Product
5349	CRM Incident
5350	CRM Incidentresolution
5351	CRM Quote
5352	CRM Quotedetail
5353	CRM Salesorder
5354	CRM Salesorderdetail
5355	CRM Invoice
5356	CRM Invoicedetail
5357	CRM Contract
5359	CRM Team
5360	CRM Customeraddress
5361	CRM Uom
5362	CRM Uomschedule
5363	CRM Organization
5364	CRM Businessunit
5365	CRM Discount
5366	CRM Discounttype
5367	CRM Account Statistics
5368	CRM NAV Connection
5370	CRM Synch. Job Status Cue
5371	Service Connection Error
5372	Service Connection Status
8628	Config. Field Mapping
8640	Config. Text Transformation
8650	DataExch-RapidStart Buffer
9000	User Group
9001	User Group Member
9002	User Group Access Control
9003	User Group Permission Set
9070	Accounting Services Cue
9170	Profile Resource Import/Export
9500	Email Item
9600	XML Schema
9610	XML Schema Element
9611	XML Schema Restriction
9612	Referenced XML Schema
9650	Custom Report Layout
9651	Report Layout Selection
9656	Report Layout Update Log
9657	Custom Report Selection
9701	Cue Setup
9800	Table Permission Buffer
130400	CAL Test Suite
130401	CAL Test Line
130402	CAL Test Codeunit
130403	CAL Test Enabled Codeunit
130404	CAL Test Method
130405	CAL Test Result
130406	CAL Test Coverage Map
2000000114	Document Service
2000000150	NAV App Object Metadata
2000000151	NAV App Tenant App
2000000152	NAV App Data Archive
2000000153	NAV App Installed App
2000000160	NAV App
2000000161	NAV App Dependencies
2000000162	NAV App Capabilities
2000000163	NAV App Object Prerequisites
2000000165	Tenant Permission Set
2000000166	Tenant Permission

Removed from table list:

452	Approval Setup
453	Approval Code
464	Approval Templates
465	Additional Approvers
1234	CSV Buffer
2000000203	Database Key Groups

regards

 

How to: Get field values from dynamically loaded tables

If you need field values from different tables at runtime within the same function, then there is a nice solution:

 

// variables
tableType, Option : CompInfo,PayTerm
result : ARRAY [5] OF Variant 

OnRun()
LoadDynamicallyFieldValues(tableType::CompInfo, result);
MESSAGE(FORMAT(result[1])+', '+FORMAT(result[2])+', '+FORMAT(result[3]));
LoadDynamicallyFieldValues(tableType::PayTerm, result);
MESSAGE(FORMAT(result[1])+', '+FORMAT(result[2])+', '+FORMAT(result[3]));

LoadDynamicallyFieldValues(tableType : 'CompInfo,PayTerm';VAR result : ARRAY [5] OF Variant)
CASE tableType of
 tableType::CompInfo:
 begin
  compInfo.GET;
  recID := compInfo.RECORDID;
  GetFieldValue(recID,'Name',result[1]);
  GetFieldValue(recID,'Address',result[2]);
  GetFieldValue(recID,'City',result[3]);
 END;
 tableType::PayTerm:
 BEGIN
  payTerm.FINDFIRST;
  recID := payTerm.RECORDID;
  GetFieldValue(recID,'Code',result[2]);
  GetFieldValue(recID,'Discount %',result[3]);
  GetFieldValue(recID,'Description',result[1]);
 END;
END;

GetFieldValue(recID : RecordID;fieldName : Text;VAR fieldValue : Variant)
recRef.GET(recID);
field.SETRANGE(FieldName,fieldName);
field.SETRANGE(TableNo,recID.TABLENO);
IF field.FINDFIRST THEN BEGIN
 fieldRef := recRef.FIELD(field."No.");
 fieldValue := fieldRef.VALUE;
END;

cheers

 

Get next object version number

Every developer has sometimes the issue: What is my next version number for the new object ? If there is a couple of developers working in the some database, you’ll need a kind of a version control.

I’ve developed a page, which calculates the next version number for a defined version prefix. The version sytax is: <PREFIX><Main No.>.<2-digit Sub No.>.

Let’s start with a list of pages with different version list, version ARCH1.00 to ARCH1.06. It does not matter, if the version list of an object contains more than one value.

After running the page set the version prefix, here ARCH, and push action “Get next version”. We get then the new version: ARCH1.07.

You can download the page here.

 

Export Nav Objects by Code

Exporting locked nav objects can be a problem when importung in target database. it’s not that easy to unlock them in the target database. So for that you can export nav objects by code and check Lock status before exporting.

Create a new report, add dataitem Object. set report to processingonly.

select dataitem Object, set property ReqFilterFields to Type,ID.
set request page: add field Path (Text).

add following code to trigger OnOpenPage:

Object.SETRANGE(Type,Object.Type::Table);
Path := 'c:\temp';

add following code to report trigger OnPreReport():

finsql := 'C:\Program Files (x86)\Microsoft Dynamics NAV\100\RoleTailored Client\finsql.exe';
IF NOT FILE.EXISTS(finsql) THEN
  ERROR('finsql not found');

additional add that code to trigger Object – OnAfterGetRecord():

IF Object.Locked THEN BEGIN
  Message(FORMAT(Object.Type)+'-'+FORMAT(Object.ID)+' is locked.');
  // alternatively unlock object, then try again.
  // Object.Locked := FALSE;
  // Object.MODIFY;
end ELSE begin
  arguments := 'command=exportobjects,file=%1,servername=%2,database=%3,filter="Type=%4;ID=%5",ntauthentication=1';
  arguments := STRSUBSTNO(arguments,Path+'\'+FORMAT(Object.Type)+'-'+FORMAT(Object.ID)+
'.fob','localhost','Cronus',Object.Type,Object.ID);
  Process.Start(finsql,arguments);
  result := result + FORMAT(Object.Type)+'-'+FORMAT(Object.ID)+'\';
END;

to trigger Object – OnPostDataItem():

MESSAGE(result);

Global Variables:

Process DotNet System.Diagnostics.Process.'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
arguments Text
finsql Text
Path Text
result Text

cheers

How to: Get record count for all tables

In newer Nav versions the tables overview in development environment/database information has gone. So how to get that list back ?

Let’s got to SSMS (Sql Server Management Studio).
There select the database, for which you want that information.
Right click on database -> Reports -> Standard Reports -> Disk usage by Top Tables

There we are ! 😉

 

How to work with big item descriptions

Sometimes it’s needed to save very long descriptions, but in Nav text fields can have only 250 characters. Additional you may want to search in these long text values. You can add a couple of these text fields to save long texts or save the text in text files and add them to the item. But what about searching? Not that easy.
An other option to save long texts is the usage of blob fields. For that option i developed a solution.

First add a new field “Description 3” to table Item, type BLOB, subtype Memo.
Then edit page Item Card, add a global variable Desc3Txt of type text with no length. Add the variable as new field to the item card, Editable=False, MultiLine=Yes.
Add following code to trigger OnAfterGetRecord in item card page:

// InStr | InStream
CALCFIELDS("Description 3");
IF "Description 3".HASVALUE THEN BEGIN
  "Description 3".CREATEINSTREAM(InStr);
  InStr.READ(Desc3Txt);
END;

Add to trigger Desc3Txt – OnAssistEdit()

// OutStr | OutStream 
// EditCtrl | DotNet | Archer.TextEdit.'Archer.TextEdit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1465b259ee2284cb' 
CLEAR(EditCtrl);
EditCtrl := EditCtrl.TextEdit;
EditCtrl.Load(Desc3Txt);
EditCtrl.ShowDialog;
Desc3Txt := EditCtrl.Save;
EditCtrl.Close;
CLEAR(EditCtrl);

"Description 3".CREATEOUTSTREAM(OutStr);
OutStr.WRITE(Desc3Txt);
MODIFY;
CurrPage.UPDATE;

Page item card with new multiline text field “Description 3” and Assist Button.

Clicking on Assist Button starts the TextEdit Control and loads the current text. After changing the text and closing the text control, you are asked, if you want to change the text.

Now we need the opportunity to search within the new text/blob field. For that we need a new page Item Search. That new field cannot be searched using the standard search function.

Create a new page with objectid 50000, name Item Search. Add a group under the contentarea, add a global text variable SearchString, add a new field under the group with SearchString as SourceExpr. Add another group, add a new line with type part. Later we set the value for property PagePartID.

To show the search result we need another page: type listpart, objectid 50001, name Item Search Result. As source of the new page we need a new table: objectid 50000, name Item Search Result.

The new page 50001:

Properties: Editable=False, SourceTable=50001, SourceTableTemporary=Yes.

Now add global function SetData(SearchFilter : Text) to the new page. Add following code to the new function:

// local variables
// Item, Record, Item 
// ItemSearchResultLine, Record, Item Search Result 
// InStr, InStream 
// Desc3Txt, Text 
// LineNo, Integer 

DELETEALL;

LineNo := 10;
Item.FINDSET;
REPEAT
  Item.CALCFIELDS("Description 3");
  IF Item."Description 3".HASVALUE THEN BEGIN
    Item."Description 3".CREATEINSTREAM(InStr);
    InStr.READTEXT(Desc3Txt);
    IF STRPOS(LOWERCASE(Desc3Txt),LOWERCASE(SearchFilter)) > 0 THEN BEGIN
      "Line No." := LineNo;
      "Item No." := Item."No.";
      Description := Item.Description;
      "Description 2" := Item."Description 2";
      "Description 3" := COPYSTR(Desc3Txt,1,250); // first 250 chars
      INSERT(FALSE);
      LineNo += 10;
    END;
  END;
UNTIL Item.NEXT = 0;

CurrPage.UPDATE(FALSE);

Now you can set the property PagePartID in the part line in page 50000 to 50001.

For calling the search function we need a Search button in page 50000.
Add following code to trigger Search – OnAction()
CurrPage.ItemSearchResultLines.PAGE.SetData(SearchString);

The new Item Search Page with a search result.

You can download the TextEdit Control here.

Followup:
You could simplify the solution by:
* Search page: Use only page 50001, add a second group at the top with field SearchString. So page 50000 is not needed.
* Page Item Card: remove the textedit control and the according code, set field Desc3Txt to editable, add the code to fill the blob field “description 3” using outstream to trigger Desc3Txt-OnValidate.

cheers

 

Mass data import

There was an issue with importing all UK post codes from a csv file, about 3m post codes. Importing using rapidstart services (excel import) can cause buffer overflow messages. excel itself has row/size limitations. Increasing MaxNoOfXMLRecordsToSend in config file ClientUserSettings.config from default value 5000 to e.g. 20000 is no problem and can help. Also changing MaxUploadSize in server config file CustomSettings.config is an option (also available via nav service admin console). Better choice for mass data import are dataports (older nav versions) and xmlports.

Another option is to develope a report, which imports the file contents and loops through the lines. quite simple, no memory issues.

create a new report, add following code to trigger OnPreReport():

OnPreReport()
// variables
 PostCode, Record, Post Code
 file, File
 fileName, Text, 250
 line, Text, 1024
 dlg, Dialog
 idx, Integer
 txtValue, Text, 100
// code
 PostCode.DELETEALL(FALSE);
 COMMIT;
 dlg.OPEN('#1###### #2######'); // show progress dialog
 idx := 1;

// downloaded post code file from https://www.doogal.co.uk/PostcodeDownloads.php
// as test file, size: 500k, 2.1m lines, some of them contain obsolete post codes
 fileName := 'c:\temp\England postcodes.csv';

 file.WRITEMODE := FALSE;
 file.TEXTMODE := TRUE;
 file.OPEN(fileName);
 file.READ(line); // skip header line
 WHILE file.READ(line) > 0 DO BEGIN
   // skip obsolete post codes: 2. value = No
   IF SELECTSTR(2,line) = 'Yes' THEN BEGIN 
     PostCode.Code := SELECTSTR(1,line);
     PostCode.VALIDATE(City,GetValue(SELECTSTR(15,line)));
     PostCode.County := GetValue(SELECTSTR(8,line));
     PostCode."Country/Region Code" := 'GB';
     PostCode.INSERT;
     dlg.UPDATE(1,idx);
     dlg.UPDATE(2,PostCode.Code);
     idx += 1;
   END;
 END;

 file.CLOSE;
 dlg.CLOSE;

 MESSAGE(FORMAT(idx) + ' post codes imported.');

LOCAL GetValue(txtValue : Text[100]) : Text
 txtValue := DELCHR(txtValue,'<','"'); // remove leading "  
 txtValue := DELCHR(txtValue,'>','"'); // remove trailing "
 IF STRLEN(txtValue) > 30 THEN // fields City,County are Text[30]
   txtValue := COPYSTR(txtValue,1,30); // cut text, leading 30 chars

 EXIT(txtValue);

report runs with 1.5m valid lines/records about 5 min.

cheers

Incoming Documents instead of Links

If you want to add files to a nav document like orders, then you normally use the link factbox. For that you have to link files on file shares, local paths are not allowed (not reachable from other systems). That only works, if the current user has the needed permission and the network path is reachable. There is also a problem, if the file is moved, deleted or the permissions were changed.

One alternative to add files to nav documents is the usage of third party components like dms systems (document management systems).

Since Nav 2016 there is the “Incoming Documents” feature available. This is used to add the original scanned document to the purchase order. This document is saved then in the database, not on a file share (network path).

 

The advantages:

• the nav client is not dependent on permission settings and network issues.
• No issue with moving and removing of the file on the network path or changing the file permissions.
• You can use that feature in general to add any kind of file, not only the scanned order.

The disadvantages:

• No versioning: the same with the links factbox
• When viewing/editing the added file, changes are not saved back.

To replace an added document (file) you can click on “Incoming Document” in the according factbox. The “Incoming Document” page opens. Here click on Actions/”Replace Main Attachment” and select a different document or a newer version.

Conclusion:

The feature is a kind of a small dms system with some nice features and interesting advantages compared to the links factbox. You can simply add files to the Incoming documents list, then create a nav document like a purchase invoice with that file as attachment.

cheers

 

Textual Data Export by Configuration

An often by customers wanted feature is to get a data export to a text/csv file, which can then be edited in excel or text editor.

Yes, you can use Rapidstart Services. But with that feature you can only export data in excel format, not in text format. That’s ok for editing in Excel and reimport the changed data back to NAV. For data exchange scenarios you always need text format. An other point is, that it’s quite often said, Rapidstart Services are not that easy to use for end users and has a couple of bugs. I also read quite often that this feature is not recommended at all. So what else can a customer do? Contact the NAV partner, who then shall develope a new xmlport, report or codeunit to do that job. That’s the usual way.

The german localised version of Dynamics Nav contains a really nice feature, simply called “Data Exports”. You can find it in menu /Departments/Administration/Application Setup/Financial Management/General/Data Exports. This feature is mainly used to export business data for auditing purposes according the GDPdU (Process for data access and testability of digital documents).

With the feature “Data Exports” there is an additional way for these kind of issues, it is possible to export data to csv files without developing! Great thing. So let’s have a look, how we can use that.

First we need at least one definition group. Then we define the according record definition (Button “Record Definitions”).

Here we need values for Code, Description and Export Path. In that case i want to export Sales Orders. “DTD File Name” is a mandatory field. I checked the code. The file is not processed, using different kinds of dtd files does not change anything in the resulting export files. The name of the dtd file is simply written to the file index.xml, which contains the datastructure. To get the thing run we create an empty text file, call it empty.dtd and add only one line of text:

“”

Save the text file and click on Button Import in menu tab “DTD File”, select file empty.dtd and click ok. After that the file is imported and the file’s name is set to column “DTD File Name”.

Now let’s define the table and the fields. For that select the record definition and click on “Record Source”.

In the header area we set table no. to 36 (Sales Header) and the Key No. to 1 (Primary Key). Optional you can set the period field no., here to 19 (Order Date). In Column Table Filter you can set filters, if you do not want to export all records. Here i set the field to “Sales Header: Document Type=Order”, because i only want to export Sales Orders.

In the Fields Area we add the export fields by clicking on the Add Button. The key fields should be in first place. With MoveUp and MoveDown you can change the position of the fields. That’s all.

You can, if you wish, add more tables in the header area, you can set relationships, e.g. between Sales Header and Sales Line. But for now let’s run the thing. First we click Validate and get “The data export record source validated correctly.”, means all is ok.

So, after closing the page “Data Export Records Source” we are back in page “Data Export Records Definitions”. Here we click on Button Export in menu tab “Process”. Report 11015 “Export Business Data” is started:

Here set start and end date. These fields are mandatory and are applied to the period field of the header line, in that case to field “Order Date”. After execution we get some files in the export path, subfolder SALESORDER.

File empty.dtd is simply copied, file index.xml contains the data structure:

and file SalesHeader.txt contains the exported data (csv format):

There we are!
We got a csv export file without any development, only by configuration!

This can, as told before, extended by adding more lines in the header area of page “record source”:

Here you can see 2 header lines “Sales Header” and “Sales Line”, which is indented and has a defined relationship shown in page relationship in the bottom right corner of the above screenshot. In that case you get 2 export files, one per table.

Important: If you are interested in that feature, you could download the german localised version. But … for that you need the according license!
Exporting the nav objects as text export and renumbering them to the 50000s object range is possible, but illegal! So if you like that kind of feature, try to redevelope that functionality. Do not copy these objects to your database, do not reuse the code. If you want to do that, please contact your Nav partner or Microsoft for license clarifcation. You could suggest to add that functionality to the W1 version.

For more details about that feature follow this.

cheers