Difference between FINDSET, FINDFIRST and FIND('-')

1. FINDSET :

• Retrieves set of records.
• Transaction begins with FINDSET Command. 

2. FINDFIRST : 

• It will retrieve the first record
• Retrieves only 1 record, the last one within filter, sorted accordingly.
• Transactions begins when a call to update the database. (Insert, modify, delete).

1. Opens the connection with database
2. fetches the record from database
3. closes the connection

Example:

IF EmployeeVar.FINDFIRST THEN

      MESSAGE('employee no. %1',EmployeeVar."Employee No.");

Since we are not repeating the process its better to use FINDFIRST if you're fetching only one time from the database.

3. FIND('-') :

• It will also retrieve the first record
• Retrieves only 1 record, the last one within filter, sorted accordingly.
• Transactions begins when a call to update the database. (Insert, modify, delete).

1. Opens the connection with database
2. fetches the record from database
3. doesn't close the connection until the condition is false.

IF EmployeeVar.FIND('-') THEN

REPEAT

      MESSAGE('employee no. %1',EmployeeVar."Employee No.");

 UNTIL EmployeeVar.NEXT=0;

Since we are repeating the process its better to use FIND('-'), it will work fine even if we use FINDFIRST but each time it retrieves the record it opens the connection and before fetching next record it closes the connection. Which reduces performance. 

So, it better to use FIND('-) for repeated retrieval of record. 

Major Differences between Run and RunModal function

I have browsed through lots of blogs and I nowhere found the proper example on how to use RUNMODAL Function. So, I taught of writing this blog so that it might be useful other NAV Developers.

RUN Function :

• The page is opened and you can still go back to what you were doing on previous page.
• We can switch to other page which is shown.

RUNMODAL Function:

• The page is opened and you cannot go back to previous page until MODAL Page is closed.
• We cannot switch to any other page until current page is closed.

Example :

Scenario :

Assume I want to filter a Customer table who has balance between 10,000 to 1,00000 and to display the filtered values in the Customer list page. You can do the following in the coding part.


Customer.CALCFIELDS(Balance);//Calculates flowfield values
Customer.SETRANGE(Balance,10000,100000);//apply filter range
IF Customer.FINDSET THEN
    PAGE.RUNMODAL(PAGE :: "Customer List",Customer);//opens the filtered list.

Fig. above shows only the filtered customers based on balance between 10,000 to 1,0000 range.

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