MS SQL Server Installation and Configuration of NAV 2015

Some frequently used built-in functions in CAL programming

As a programmer everyone knows what is function..!! A function is nothing but a subprogram or a subroutine mainly used for code re usability. In CAL programming we call the code of a function as Trigger code.

Function call is written as same as in C programming-

result := add(a,2);
or
result := add(a+2*3,b);

Pass by value

Change i.e. made in function trigger if it doesn't affect the variables in calling trigger then it 's call by value.

If a change in function trigger affects the variables in calling trigger then it' s call by reference.
You cannot use constants for pass by reference.

actual parameter is used in a calling function trigger.
Formal parameter is used in called function trigger.

5 commonly used built-in functions are:

MESSAGE - Displays message on the screen

STRLEN - returns no. of characters used.
MAXSTRLEN - returns the defined length of  the string variable.
COPYSTR - Returns a part or whole of a string.
CLEAR - clears the passed-in variable.
ARRAYLEN - returns the no. of elements in an array.

1. DATA access functions :

GET, FIND, FINDFIRST, FINDLAST, FINDSET, NEXT 

GET function-doesn't respect filters, produces run-time error if it fails.

FIND-You can find a value based on specified key value and sorting order.

Avoid using find function as much as possible.

FINDFIRST- to find the first record in a table based on key and filter.

FINDLAST- to find the last record in a table based on key and filter.

FINDSET - to find set of records in a table that corresponds to specified set of filters.
note: In FINDSET you can iterate only from first to last. To iterate from last to first use FIND(+).
Usually, we use this in combination with repeat-until loop.

NEXT - used with FIND and FINDSET to step through records of the table.


2. SORTING AND FILTERING functions : 

For key selection,sorting and filtering we use these functions.

SETCURRENTKEY - To select a key for a record.

SETRANGE - sets a simple range ex. to restrict a phone no. to be b/w 8 to 14 numbers we use this.

SETFILTER - It includes in more general way, These filtering includes placeholders, operators, wildcard expressions.

GETRANGEMIN and GETRANGEMAX

retrieve the minimum or maximum value of the filter that is currently in effect on a field.

4. DATA MANIPULATION functions : 

INSERT - inserts a record in a table

MODIFY - changes an existing record

MODIFYALL - performs bulk update of record. Respects filters. It doesn't return values and it doesn't display any errors.

DELETE - to delete a record from the database. 

DELETEALL - to delete all records that are selected by the filter settings.

5. Frequently used functions with fields

CALCFIELDS - to calculate flowfields,

if you're accessing record from the code then calcfields must be explicitly specified. 

Rec or xRec variable is automatically calculated when a record is retrieved. (no need to call here)

SETAUTOCALCFIELDS - improves performance by reducing no. of round trips to database. 

CALCSUMS - calculates total of a column in a table. It respects filters.

note : Maintaining an index for a SumIndexField improves the performance of CALCSUMS, but may decrease the performance of insert, modify, and delete operations.

FIELDERROR - stops execution of code causing run-time error. It creates an error message for a field. 

FIELDCAPTION - returns caption of a field. We can show the messages very accurately to user by displaying FIELDERROR along with fieldname. 

INIT - initializes record by assigning default values. 

This function is not applied for primary key. 

TESTFIELD - tests if value of field matches the specified value. 

VALIDATE - to call OnValidate trigger of a field. It checks the business rules and makes sure that it is not violated by storing invalid value in the field. 

6. USER INTERACTION functions:

MESSAGE - displays a text string in a message window. 

It's displayed after CAL code finishes execution or when it waits for user interaction. 

% - placeholder

\ - new line character 

CONFIRM - prompts the user for yes/no option. (to confirm that user wants to continue with the process)

STRMENU - creates a menu window that displays series of options from a comma-delimited string.

ERROR - displays error message and ends the CAL execution. All uncommitted data are rolled back. 

7. String Functions:

MAXSTRLEN, STRLEN, STRPOS(case sensitive), COPYSTR, LOWERCASE, UPPERCASE, INCSTR, SELECTSTR(retrieves sub-string from a comma separated string).

8. Date Functions:

DATE2DMY, NORMALDATE, CALCDATE, CLOSINGDATE

9. Numeric functions:

ROUND, ABS, POWER, RANDOM, RANDOMIZE

10. ARRAY functions:

ARRAYLEN,COMPRESSARRAY, COPYARRAY

11. STREAM functions:

CREATEINSTREAM, CREATEOUTSTREAM, READ, READTEXT, WRITE, WRITETEXT, EOS, COPYSTREAM,

12. System functions:

EXIT, CLEAR, CLEARALL, EVALUATE, FORMAT

How to Create your own table and make a report of it..!!

Always it is necessary to create a table with your own scenario because that will improve your creativity mind and will also give you a deep knowledge of how to learn things quickly. So, Now I'm going to show a scenario based on movie list.

I'm going to explain how to create a table along with how to create a report linking two tables. 

1.       Create table with name MovieMakingTable with fields including (Serial no., movie name, sensor certification,audience rating, production cost)

2.      Set Serial no. and production cost as primary key, because you need to relate two tables based on serial no. and movie name can be repeated with  d/f serial no. based on different productions. In order to repeat names for same serial no. we also take sum of productions as primary key.

3.      Let the properties of this table be not disturbed.

1.       Create MovieMaking table with fields including(film code, director name, email, ph. no, profit, sum of productions,director, producer name , shooting locn and no. of ppl involved)

2.      Set film code as primary key.

3.      In Salary field properties,

Go to field class and select FlowField(to sum the production cost of each directory of movie released table we chose flow field)

4.      Now, update CalcFormula by choosing assit edit

Select method as Sum, table as –MovieReleased table

Field-production cost

Table Filter-Based on serial code, type-field, Value-film code

5.      Now, the code you ‘ve developed will sum up the table of each director and will display the sum in another table.

Now, we should filter the table displayed in moviemaking table based on movie rating and sensor certification.

1.       Come back to Movie released table. Create a field called type and choose option as datatype (give option string property with values U,U/A,A).

2.      Save and come back to MovieMaking table. Create two fields called Audience rating with datatype as option(set option string property with values 1,1.5,2, so on upto 5) and create another field called Sensor certification with its data type as option(set option string property with values U,U/A,A).

3.      Select flow filter in field class property for these two newly created fields)

1.       Come back to sum of production field of movie making table and go to its calcformula property, go to table filter, Add two additional fields called search based on certification and search based on rating, type is option. Click Ok and Save.

2.      Run the Moviemaking table type ctrl+shift+F3 add filter select either (select by date or select by month or both) and you’re done with the task.

MovieList Table- 2 different tables  that has been created

 Create a Table link report for Customer Table and Customer Ledger Table with your selected columns from these two d/f table. Sort with only date column by removing time and calculate net amount and total amount.

     

    

  1.       Add new Report, Create DataItem and select DataSource table as Customer, select properties of DataItem Set DataItemTableView Assist Edit

Select key as No., order-ascending, Table Filter-(field-balance, type-filter, value>0)

   2.      ReqFilterFields property – No., Name

   3.      CalcFields Property-Balance(LCY),save and compile.

   4.      Select FieldMenu and select columns customer no.,name,name2 and balance(LCY).

   1.       Create another DataItem with data source as CustomerLedger Entry table.

 2.      Go to properties of DataItem, choose DataItemLinkReference as Customer and DataItemlink Field-Customer No., Reference Field-No. 

  3.      Select field menu and add columns(entryno., customer no., posting date,amount) save and compile.

  4.      Goto View-Layout.

  5.      Drag a table and give tablix properties-select datasetname as datasetresult.

  6.      Create a header row with customer no. ,name, name1 and balance details)

   7.      Next row-add parent header values.

   8.     Next row-add child header(entry no.,custno, posting date, amount).

   9.      Add details in next row.

  10.  Calculate individual total amount and net amount. Finally save, compile and preview. To calculate sum(double click on column and add a text as sum).

Ex. [Sum(Amount_CustLedgerEntry)] compile and save

  11.   If you need to filter by date and remove time, select posting date details column, on the right bottom corner on properties window go to Number header, Format give value as d. save and compile.

Customer Report created between two tables

Some basic things to know before working on MS Dynamics NAV

To know which version of MS Dynamics you are using-

Click on Help and select ‘about Microsoft dynamics NAV development environment’

A window appears which shows the latest version you have been upgraded and the product is licensed to Microsoft and some more information.

Difference b/w version and version list in MS Dynamics NAV

Version	Version List
It displays the latest version i.e. being used in MS Dynamics NAV (upgraded version)	It displays the version of various objects which were added some extra features. It differs from 1 object to another or from 1 field to another.
It shows licensed information	It doesn’t show any such things
We can’t edit in version window	We can edit in version list column
It is used to know the details of which version we’re using	It used mainly to update the changed information. Ex. Suppose developer ‘A’ will change a customer table and move to some other project after few months. To know which table the developer modified we edit version list column. (if the version list of column is 12345.34, we edit it has name,12345.34. So that we will come to know who actually modified which column)

Some rules to be followed while changing field id or table id

Suppose we are modifying any existing table i.e. created by Microsoft then the field no. of that existing table should be between 50,000 to 99,999 disagreeing this will lead to compile time error.

Suppose we are creating any new table then the table id should be assigned the no. b/w 50,000 to 99,999 disagreeing leads to CTE.  If we’ve created a new table then that field no. can take any no > 0. To know more about this id go through architecture of NAV.

Do’s and Don’ts while working on NAV IDE

·         Don’t compile and save if you have not did any changes on objects.

·         If you save and compile without any changes then there will be a check mark on the modified screen. Other developers will get confused and waste their time by also reviewing this unchanged saved object. (instead just escape if you are not changing any fields)

·         All the table name, field name and word after space should begin with capital letter.

Locked and Unlocked

When several developers use the same database then it’s better to lock and work by particular developer so that no one except him can edit or modify or save. Others can only view the locked content but cannot modify.

Only the locked developer can unlock. If there’s any case to unlock when the locked developer is not available then you can choose forced unlock option.

Import and export to and from the file.

Import means uploading any file from drive to nav software.

Export means saving a file from nav software to drive.

Extensions used in dynamics NAV

License file is stored as .flf

In 2009 native db was saved as (.fdb) now in 2015 its no more used.

Objects are saved as .fob

Note: Its better to save any object you are using both in .fob and .txt format.

Toggle mark

Sometime there arises a case to save 3 or more object elements to a single file we do this by using toggle mark (to select object elements we need to save), go to view click marked only and click all button below objects window then select all and save to your desired location.

Difference b/w upload and change in license information

Change will only affect table or any other objects but changes are not made at role tailored client.

Upload will do changes both on objects elements and also on role tailored client.

Know More-

Option - It will show some information, path where we’re saving etc.

Autolock - will automatically lock your data’s so that it cannot be modified by anyone.

Prevent data loss from table - you cannot delete any fields which you created.

Frequently used shortcut keys in MS Dynamics NAV 2015

Name	Shortcut keys	Description
To access object designer	Shift-F12	Opens 7 types of objects alt-f3=to design
To create object type (table/pages etc.)	F3	Select object type and click new
To edit object	Na	Select object in object designer, click design
To compile object manually	Ctrl-S(saves and compiles)/ F11	Tools->compile
To toggle mark on object manually	Ctrl-F1	Select object->edit menu ->Togglemark
To view errors of objects (it happens when u compile multiple objects)	View->show all or  (view->marked only)	(view->marked only)  all objects that contain a compile-time error are automatically marked by the compiler
Select appropriate field or table properties	Shift-F4	View-properties
To open triggers	F9	View->C/AL code
To view table keys		View->keys
To view field groups		View->fieldgroups
To Delete all the current records in the table.	Ctrl-del	select upper left gray box which selects all records, then select ctrl-del
The Limit totals to(table filter)	CTRL+SHIFT+F3	This is where you can change the value of a FlowFilter. Remember that changing the value of the FlowFilter might change the value of the FlowField
To open Help	F1	Click on any field and press F1
To refresh the current page	F5	Refresh will update the changes done in the current page
To display statistics	F7	Display statistics of desired page
To copy the field above current field	F8	Copies the value of field above to the current field
To post the document	F9/(c/al code)	Posts the desired document
To discard the changes	Esc	1.       Undo’s the value if it is in edit mode. 2.      Saves and closes the current window.
To export to MS Excel	Ctrl-E	Ledger or any other entries will be exported to MS Excel
To delete the selected line	Ctrl-Delete	Deletes the selected line on the worksheet or on any other edit mode
To search pages	Ctrl-F3	To search pages
Type to filter	F3	Start typing filter value. Ex: type 23 to find the customer id’s that start with 23

Pages- 2nd object type in Nav 2015

Pages

Why pages rather than forms since NAV 2013?

Pages Enables users to view, add, change, or delete records in a table.

In 2009 there was both pages(role tailored) and forms(classic client). Whereas since 2013 it is only role tailored client introduced and the role tailored client was renamed as windows client, web client and SharePoint client was an added addition here.

Forms object was removed in nav 2013 mainly because pages got all the features of forms and web services were introduced(we can view pages as web pages in internet browsers by giving address path, I mean URL to the internet browsers.)

Everything we write was exported in xml or text format which support internally the .Net feature.

As the technology raising Nav is getting adopted to IT fields recent technologies, It turned from

Form to pages, from normal native database to M.S Sql server and web client, I am sure by Microsoft will also replace C/AL language by C#(.Net) by end of 2017.

A brief overview about Pages

When it comes to pages let me now start discussing about a page called CustomerList which is already existing page in Nav.

Usually, Page tool bars at top will include

Action: Includes some action to be performed.

Ex: remainder action is used to remind the customer that he has to do some actions(transaction, sales etc.) in specified time.

Navigation: To give some additional information.

Ex: Comment provides some header information about what’s going on.

Report: previews the page summary.

Ex: summary

Home: frequently used action, navigation or report buttons are dragged to Home tool bar.

As per industrial standard it is to be noted that at least one action button should be present in Home tool bar.

Page Types and Characteristics

• It’s one of the most important page property where we need to specify which type the page belongs in the PageType property.

• Page type determines how the page is displayed in the client.

• It also controls certain behavioral characteristics of the page.

• Selecting the correct page type when you design pages is very important, because the pages are interpreted differently in the client, depending on their page type.

• Each pagetype also use d/f set of controls which become the important concept during page design.

1. Card Page

Why we need it?

Use a card page when there are too many fields to view on one line

·          Tables that use card pages only have one field in their primary key. (Primary key here is restricted to only one field)

·         This field always is displayed as the first field in the General FastTab.

·         They’re related to master table.

·         Primary key should be of length 20 and should be integer no. or code as data type. (name can be code/no.) where as no. should be integer.

·         A card page enables users to view and edit one record at a time.

In fact box,

1.       Link address is used to give web path ( URL for setup table.

2.      Notes is used to notify any particular/ regular customer (to give some additional benefits and discounts)

Ex: a person who is regular to sales gets his discount of 10% than other customer and his delay time will be also extended.

In ERP, invoice is nothing but bill, condition and rules given to customer, I’ll be often using this word from hereon to indicate some transactions topic.

Naming Card Page

Use the associated table name to name card pages, followed by the word “Card”. For example, the card page that is associated with the Customer table is named Customer Card.       

 2.  List Page

·         A list page is a multi-record page that enables users to view multiple records from a table at the same time (provides brief information about the page).

·         The primary key fields of the associated table are displayed in the left-most columns.

·         Every master table should’ve list, card and statistical page data(brief detail like amount, balance)

Naming List Pages

If you cannot edit the list page the list page that is associated with the Customer table is named Customer List. 

If you can edit the list page then the list page associated with the Currency table is named Currencies (plural name).

3. RoleCentered Page

·         Page represents a customized user home page displays the parts or controls that are relevant to a user’s role.

·         Role centers may also include the ListParts with the most relevant information for the user, such as My Customers or My Items. A developer can add system parts, such as Outlook, My Notifications, and charts, during design time. Users can add these system parts during run time.

Naming Role Center Pages

The role center page that is associated with the Order Processor role is named Order Processor Role Center.

4. CardPart Page

·         This is related to particular customer. 

·         CardPart page is used to create a single-column FactBox.

·         They are used  to display fields or a special control like a picture viewer.

Naming CardPart Page

CardPart pages are named with the associated information, followed by the word FactBox. For example, the CardPart page that displays the sales history of the sell-to customer is named Sales Hist. Sell-to FactBox.

5. ListPart Page

·          Use a ListPart page to display subpages for documents.

·          You can also used a ListPart page as a FactBox to display multiple rows and columns from a single table

Difference b/w List page, card part page and listpart page.

List page: displays multiple record with info.

Card page: to display only 1 record.

Card part: to display 1 record/ single column fact box.

List part: to display multiple records/ multiple row, column fact boxes. 

Naming ListPart Pages

The ListPart page that displays the Dimensions is named Dimensions FactBox. When you use ListPart pages as subpages for documents, name them with the associated document name, followed by the word “Subform” or “Subpage”. For example, the ListPart page that displays the lines for the Sales Order is named Sales Order Subform.

6. Worksheet Page

·         This page is used very frequently.

·         It is a multi-record page that enables users to view multiple records from a table and edit them.

·         Worksheet pages let users set filters or options that are shown over the repeater control.

·         Worksheet pages may include a footer which summarizes information from the worksheet or shows detailed information about the currently selected line.

·         You can use worksheet pages as journals.(D.T uses line no. of type integer.

·         Worksheet are related to journal table. They ‘re used to distinguish b/w different records.

·          The primary key fields of the associated table do not show on the worksheet page.

·         Worksheet pages differ from ordinary list pages. 

·         When a user inserts a new record in a Worksheet page, the record does not jump to another position within the page, but remains in the same order in which the user inserted it. 

·         The AutoSplitKey property of the page, combined with an integer as the last field in the table's primary key enables this order.

·         Implicitly adds +1 to the document no. if AutoSplitKey is yes. If it is no there’s no automatic increment and it raises an error to give document no.

Naming Worksheet Pages

Worksheet pages are named according to the purpose of the associated table. For Journal tables, the name of the Worksheet page ends with the word Journal. For example, the main journal for the General area of the Financial Management application area is called General Journal.

7. Document Page

·           A document page is a page that contains a series of FastTabs and a page part with lines on the same page. It displays information from two tables with the master-detail relationship, such as Sales Header and Sales Line, or Purchase Header and Purchase Line tables.

·            They can’ve 1 line page.

·             Except line tab in the below diagram all other tabs are related to document header.

·            As, I discussed in document table here also there’ r two types.

1.       Document header: contains main information

2.      Document line: Contains detailed information(In below fig of line tab it includes line document table information.

Naming Document Pages

The name of a document page is the function that it represents, such as, Sales Order.

8. NavigatePage page

·         As, the word says it is used to navigate to some other information. You can achieve this by clicking on the navigation bar.

·         It display only posted information(ex. Posted sales)

·         We cannot edit in this page only display.

·         This page is used for searches that are based on individual posting transactions, such as page 344, Navigate

·         Navigate page type supports showing multiple tabs of information, changing visibility of tabs during run time, and positioning actions in the footer bar.

·         This layout makes NavigatePage pages good candidates for wizards because they show actions as command buttons, and enable toggling visibility of tabs during run time. For example, page 5077, Create Interaction, shows a wizard-like interface that moves the user through a series of questions, before it makes changes to the database.

Naming Navigate Pages

There is no specific requirement for naming NavigatePage pages. Page 344 is named simply Navigate, while typical wizards may be named according to the task they accomplish, such as page 5077, Create Interaction.

9. ListPlus Page

·         A ListPlus page resembles a document page, because it also contains FastTabs and a ListPart named Lines.

·         Whereas document pages typically allow for inserting and deleting lines, and use on the AutoSplitKey property,

·         Document line can’ve only 1 line page, whereas listplus can’ve more than one document line.

·         This page is used very rarely

·         ListPlus pages typically only allow for viewing, or editing existing lines.

·           ListPlus pages do not allow insertion or deletion of lines from the subpage. For example, page 397, Sales Invoice Statistics is a ListPlus page. Because ListPlus pages do not allow the insertion of lines, and show fixed, or pre-determined information in the lines, they may be used instead of card pages for master data management, such as, page 5050, Contact Card.

   

Naming ListPlus Pages

There is no specific convention regulating the naming of the ListPlus pages. If you use a ListPlus page instead of a card page, such as in the example of page 5050, Contact Card, then it should follow the naming conventions for the card pages.

10. ConfirmationDialog Page

·         A confirmation dialog asks the user a question that can be answered with minimal data input, and requires the user to click Yes or No. Pages of this type contain a question, and may contain a single FastTab named Details. This provides the necessary information for the user to make a choice.

·         It’s used for user confirmation we can write code and call this page.

Naming ConfirmationDialog Pages

There is no strict convention for naming confirmation dialog boxes. However, you may want to consider using the object of the confirmation preceded by the word Check or Confirm. For example, the confirmation dialog that requires the user to confirm a transaction for a customer who is over the credit limit is named Check Credit Limit.

11. StandardDialog Page

·         Its used to display information after result(ex. Posted

·         A standard dialog page is a simple page type that you use when users only input data and do not perform any other actions from the page.

·         Standard dialog boxes do not contain ribbon, FastTabs, or FactBoxes.

Naming StandardDialog Pages

Users cannot customize the appearance and behavior of these pages.

For example, page 511, Change Exchange Rate is of the StandardDialog page type.

There is no strict convention for naming standard dialog boxes. You may name them according to the task that they perform. 

For example, the standard dialog for changing the currency exchange rate on documents is named Change Exchange Rate.