Sriram Maringanti – ERP-Executive [email protected]
DOCUMENT HISTORY TABLE CHARACTERISTICS
When a document is posted, part of posting process is copying document tables to corresponding document history tables.
Document History Table has same fields with same field numbers, names and properties as original document tables.
Since document history tables record posted transactions, they are not editable by user, although they may be deleted.
Document History Tables and forms are the same as corresponding document tables.
Sub-form is a list form since modifications are not allowed.
Names include either "Posted/Issued" to indicate that it is a history table rather than a transaction entry table.
SETUP TABLE CHARACTERISTICS
Table that is designed to hold only one record.
This record contains various fields that are used to select options for MS Navision or to hold data that is applicable to the company as a whole.
No tables are related to setup table, although setup table can be related to other tables.
Naming setup tables
Name of table is name of functional area, it is meant to set up followed by "Setup".
One exception is Company Information Table.
Primary key and other standard fields
PK is a code field of length 10 named "Primary Key".
It is always blank as only one record per table is allowed.
Associated Setup Form
One form set up for this table and it is a setup form.
Form has same name as table.
PK field is not included in this form.
Types of Forms
Sriram Maringanti – ERP-Executive [email protected]
Let us briefly review types of forms that we will use in an application. Then we will step to several examples using our C/ANDL system to illuminate our path. From an application design point of view, we need to consider which form type to use under what circumstances. The following are the different form types:
Card form: These display and allow updating of a single record. A Card form is generally used forMastertable and Setup data. Complex cards can contain a number of tabs, and may even display data from subordinate tables.
Tabular or List form: These display a list of any number of records at one time, one line per record, with each displayed data field shown as a column.
The Reference table maintenance and inquiry use Tabular forms. List forms use the same format as Tabular forms but (usually) are not editable. They can be used, for example, to show a list of master records to allow the user to compare records or to easily choose one master record on which to focus.
Some specific List forms, such as Ledger Entries, allow editing of some fields (such as Invoice Due Dates).
Tabular/List forms are widely used as transaction entry forms. One of NAV’s design features is to allow volume data entry activities to be done with little or no mouse usage. This provides higher data entry speed in situations where volume entry is feasible.
You can create a version of Tabular forms that is particularly suitable for high volume data entry into transaction journals. In the NAV documentation these are referred to as “Worksheet Forms”. Worksheet forms use
the AutoSplitKey property combined with an integer field as the last field in the table’s primary key. This results in the entered data being automatically sequenced as it is entered. The C/AL code must handle the incrementing of the integer field as new records are appended.
The AutoSplitKey property will handle the creation of a new integer for a record being inserted between two other existing records. It does so by
“splitting” the number range between the two original records to assign an integer value to the new, inserted record. For example, if the original records had keys ending in the values 50000 and 60000, then AutoSplitKey will assign the value 55000 to the new inserted record key.
A simple tabular form may show all the fields in a reference table to allow entering data or choosing one entry from among the available set. A complex
Sriram Maringanti – ERP-Executive [email protected]
tabular or list form might show data from several tables and some computed fields.
Main/Sub or Header/Detail form: This consists of combination of two forms. The primary form is a card form that contains a subform control. This control references a secondary form, which is a tabular form. This form type is often appropriate whenever you have a parent record tied to a subordinate or child set of data in a one-to-many relationship.
Header/Detail forms are used in Sales and Purchasing functions for Quotes, Orders, and Invoices, both before and after Posting. Header/Detail forms are also used in other areas such as Manufacturing Work Orders, Production Bills of Material, and Production Routings.
Matrix form: This form type display results based on the intersections of two tables, called the source table and the matrix source table. The display is in a spreadsheet-style matrix format. The displayed data element of the source table is the leftmost column. The matrix source table principle data element is displayed across the top row, in the column header row position, with the results of the cell source data expression filling out the body of the matrix.
The actual data displayed for each matrix cell may be computed from the intersection of these two tables. For it could be from some other table, but selected based on the results of the intersection of these two tables, for example, where values from the intersects are used in an algorithm with or to filter values in other tables.
Trendscape form: This is equipped with Trendscape control buttons, which allows the displayed data to be filtered by a user selected date range. The following screenshot shows Trendscape buttons at the bottom of a form:
Trendscape forms may use different form types for the display of date-filtered data, and several variations occur in the standard NAV system. In Form 490, the Acc. Schedule Overview, the Trendscape date filter applies to
Sriram Maringanti – ERP-Executive [email protected]
all data appearing on the screen and the form is a Matrix form supplemented with a Tab control.
[View full size image]
In Trendscape Form 492, Item Availability by Location, shown in the
following screenshot, the date-filtered data is displayed using the subordinate Form 515, Item Avail. by Location Lines, which is a Tabular form placed in a subform control on the parent Form 492. All the data being displayed by Form 492 has the same date filter applied to it. The result is that the form is displaying the data “as of” the date filter range.
[View full size image]
Sriram Maringanti – ERP-Executive [email protected]
In Form 5983, Service Item Trendscape, shown in the following screenshot, the data is also displayed in a subform control, this time referring to form 5984. This form displays data for one date range on a line. The increment in the date range from line to line is controlled by the selected Trendscape button.
[View full size image]
Take a look at these additional standard out-of-the-box forms for a
representative sample of Trendscape forms: Forms 113, 157, 415, 490, 492, 5226, and 5983. You can access all of these forms via Tools | Object
Sriram Maringanti – ERP-Executive [email protected]
Designer | Form. Obviously, the data displayed must be time related (e.g.
generally tied to a Posting Date).
Dialog form: This is a simple display form embedded in a process, used to communicate with user/operator.
Request form: This is a relatively simple form consisting of several tabs, allowing control information to be entered to control the execution of a report object.
Print to Excel Functionality Variables
Name DataType Subtype Length
RecExcelBuffr Record Excel Buffer PrintToExcel Boolean
---Functions
Name
MakeExcelInfo CreateExcelbook MakeExcelDataHeader MakeExcelDataBody
---MakeExcelInfo()
RecExcelBuffr.SetUseInfoSheed;
RecExcelBuffr.AddInfoColumn('Company Name',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddInfoColumn(COMPANYNAME,FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.NewRow;
RecExcelBuffr.AddInfoColumn('Report Name',FALSE,'',TRUE,FALSE,FALSE,'');
Sriram Maringanti – ERP-Executive [email protected]
RecExcelBuffr.AddInfoColumn('VAT Exceptions',FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.NewRow;
RecExcelBuffr.AddInfoColumn('Report No.',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddInfoColumn('31',FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.NewRow;
RecExcelBuffr.AddInfoColumn('User ID',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddInfoColumn(USERID,FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.NewRow;
RecExcelBuffr.AddInfoColumn('Date',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddInfoColumn(TODAY,FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.ClearNewRow;
---CreateExcelbook()
RecExcelBuffr.CreateBook;
RecExcelBuffr.CreateSheet('Data','VAT Exceptions',COMPANYNAME,USERID);
RecExcelBuffr.GiveUserControl;
ERROR('');
---MakeExcelDataHeader()
RecExcelBuffr.NewRow;
RecExcelBuffr.AddColumn('Posting Date',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('Document Type',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('Document No.',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('Type',FALSE,'',TRUE,FALSE,FALSE,'');
Sriram Maringanti – ERP-Executive [email protected]
RecExcelBuffr.AddColumn('Gen. Bus. Posting
Group',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('Gen. Prod. Posting Group',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('Base',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('Amount',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('VAT Difference',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('VAT Calculation Type',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('Bill-to/Pay-to No.',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('EU 3-Party Trade',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('Closed',FALSE,'',TRUE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn('Entry No.',FALSE,'',TRUE,FALSE,FALSE,'');
//RecExcelBuffr.AddColumn(Value,IsFormula,CommentText,IsBold,IsItalics,IsUnderl ine,NumFormat)
---MakeExcelDataBody()
RecExcelBuffr.NewRow;
RecExcelBuffr.AddColumn(FORMAT("VAT Entry"."Posting Date"),FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn(FORMAT("VAT Entry"."Document Type"),FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn("VAT Entry"."Document No.",FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn(FORMAT("VAT Entry".Type),FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn("VAT Entry"."Gen. Bus. Posting Group",FALSE,'',FALSE,FALSE,FALSE,'');
Sriram Maringanti – ERP-Executive [email protected]
RecExcelBuffr.AddColumn("VAT Entry"."Gen. Prod. Posting Group",FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn("VAT Entry".Base,FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn("VAT Entry".Amount,FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn("VAT Entry"."VAT Difference",FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn(FORMAT("VAT Entry"."VAT Calculation Type"),FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn("VAT Entry"."Bill-to/Pay-to No.",FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn(FORMAT("VAT Entry"."EU 3-Party Trade"),FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn(FORMAT("VAT
Entry".Closed),FALSE,'',FALSE,FALSE,FALSE,'');
RecExcelBuffr.AddColumn("VAT Entry"."Entry No.",FALSE,'',FALSE,FALSE,FALSE,'');
---VAT Entry, Header (4) - OnPreSection()
IF PrintToExcel THEN MakeExcelDataHeader;
---VAT Entry, Body (5) - OnPreSection()
IF PrintToExcel THEN MakeExcelDataBody;
---Report - OnPre---Report()
IF PrintToExcel THEN MakeExcelInfo;
---Report - OnPost---Report()
Sriram Maringanti – ERP-Executive [email protected]
IF PrintToExcel THEN
CreateExcelbook;
---FIND
FINDSET: for getting multiple records, you intend to loop through them FINDFIRST: for getting only the first record in the filter, just one record FINDLAST: for getting only the last record in the filter, just one record
Specifies how to perform the search. The table is searched until either a record is found or there are no more records. Each character in this string can be present only once. You can combine the '=', '<', and '>' characters. You can use the following characters:
= to search for a record that equals the key values (default)
> to search for a record that is larger than the key values
< to search for a record that is less than the key values
+ to search for the last record in the table (+ can only be used alone) - tosearch for the first record in the table (- can only be used alone)
If this parameter contains '=', '>' or '<', then you must assign value to all fields of the current and primary keys before you call FIND.
FIND ('=') is more similar to GET function: the difference between those 2 is that GET ignores any filter applied to the set
UPDATEFORECOLOR Function (Control)
Dynamically changes the setting of the ForeColor Property property of a control.
UPDATEFORECOLOR(ForeColor)
This function is not supported in the RoleTailored client.
This function can only be called from the OnFormat Triggertrigger of the control
Sriram Maringanti – ERP-Executive [email protected]
Ex:
IF Amount < 0 THEN
CurrForm.Amount.UPDATEFORECOLOR(255);
ForeColor Codes 3268 - light magenta 32768 - green
255 - red
16711680 - dark blue 116687698 - light blue 165458 - light gren 5458 - brown
534458 - shade of red 90000 - light brown
50000825 - very light blue 50000890 - whitish blue 60000826 - peacock blue 963258741 - violet
963258 - greenish yellow 987654321 - purple XMLports
XMLports was introduced with Navision 4.0 – they act like dataports, with a small difference. They can only be used for XML-formatted data and they must by executed from some other routine.
What is NAS
NAS is a client without a user interface (GUI), running as a service. That’s all it is.
When it starts, it will run a function in codeunit 1, passing on the Start-up
parameter that has been set up. That’s all it does. There are two prerequisites for this function in codeunit 1:
It must have ID 99 (the name of the function is irrelevant)
It must have one parameter Text 250. The name of this parameter is also irrelevant.
Sriram Maringanti – ERP-Executive [email protected]
NAS on its own does not do anything else. So on its own it is a very simple component. But the application you build around it can, off course, be as complicated as any other application.