The OSQL utility

Well this is more of a tip a smart tip is guess we came across this scenario where we were working a interface between Navision database for the financials and 17 other POS databases from where the daily sales was imported into navision.

osql is a sql client with a command line interface it is very similar to what query analyzer does in functionality i.e it takes a SQL batch and passes it on to the relevant server just that it accepts command line parameters which makes it that useful.

The interface was based on SQL procedures and everytime there was any change required someone was required to physically connect to these 17 servers manually and update the scripts one by one it was really painful, it was then that we thought of using a shortcut we just made one batch file with connection information to all the 17 stores and then provided a file name as a parameter to osql this file contained the batch we wanted to execute on all the servers the syntax was like this

osql -S192.168.10.3 -Usa -Psk74fa5z -dAlainMall -iBatch.sql -n

The parameters help can be got on the osql command line "batch.sql" was the file we would place our sql code into and it would execute on all the servers in the right databases.

Automate Log

Well the Audit logs functionality of Navision does not work for any modifications made to the data via CAL code. There could be specific instances where even thought the changes are made using the CAL code it might still be desired to log it in. Well there is a solution to it.

what one would need is two recordRef variables one of these variables would store the field values before modifications and the other would store the values after modification. Additionally we would also need a variable of the "Change Log Management" codeunit.

The xRecRef should be populated before any changes are commenced using a command like xRecRef.GETTABLE("Sales Price");
make sure that the desired record is the current record at this point.

After all the manupulations are done using the other reference variable to populate the new values using a command like:
RecRef.GETTABLE("Sales Price");

and then call :

ChangeLogMgt.LogModification(RecRef,xRecRef);

this would ensure that the modifications are logged into the navision audit tables.

Creating a Custom Dataport

Dataports can have tricky requirements. There was this case where we were creating an export file for a system in Unix and the requirement was to have some special characters in the file being created so how does on do it as we know we can use the char datatype to handle special or invisible characters in navision.

How we handled this was that we created a dataport with the table to be exported and did not define any datport field for this, then on the pre-dataitem trigger we initialized an outstream variable as

CurrFile.CREATEOUTSTREAM(fpCustomer);

then on the OnAfterExportRecord one can directly write to the export file of the dataport as follows:


//writing to the text file
fpCustomer.WRITETEXT(Customer."No." + FORMAT(Delimeter) ); //customer
fpCustomer.WRITETEXT(Customer."No." + FORMAT(Delimeter) ); //No
fpCustomer.WRITETEXT(Customer.Name + FORMAT(Delimeter) ); //Name
fpCustomer.WRITETEXT(Customer.Address + FORMAT(Delimeter) ); //Address
fpCustomer.WRITETEXT(Customer."Address 2" + FORMAT(Delimeter) ); //Address2
fpCustomer.WRITETEXT(Customer.City + FORMAT(Delimeter) ); //City
fpCustomer.WRITETEXT(Customer."Post Code" + FORMAT(Delimeter) ); //Post Code


Well by the way on InitDatport trigger one can also assign the dataport filename as

CurrDataport.FILENAME := SRSetup."Customer Export Folder" + '\' + FileName;

handy when the dataport is automated.

Indirect Permissions

Well there was a long time that i was not clear about what the indirect permissions in Navision are meant for. What we thought was if a user has indirect permissions to an tabledata then he would be allowed to execute any such piece of code which could modify that data.

Well we investigated further and found that the indirect permissions are the permissions which are assigned to objects. Yes every object in Navision has a permissions property where we can define the objects it has indirect permissions to it would be required that the user atleast has indirect permissions to these objects in order to execute this code.

Navision SQL 2005 Trace Flag

Well Navision uses indirect permissions on objects which are achieved using the application roles functionality of SQL Server in SQL 2005 this functionality has been disabled by default and has to be manually configured using a Trace flag 4616. Being very new to SQL 2005 i couldn't figure out how to set the flag so that it is applied each time SQL runs.

Simple but only if you have known this. The option is there in a utility called SQL Server configuration utility you will have to right click the SQL Server in the right pane of the window and select properties in the Advanced tab there is a field to enter the Startup Parameters if the flag is applied here then it will be applied each time SQL Starts up.

Session Information

Well one could read the current session information from navision. This information is available in a system table called Session.

All the fields in this table could be seen by creating a temporary form with Session table as the source expression.

Setting a filter on the My Session field one can read the current connection information like User ID, Database Name, Login Time Date, and Idle time the idle time can also be used to kill idle sessions in navision. Once can schedule a piece of code to monitor this information and delete these records from Navision.

A record deletion from this table would lead to the dropping of the session from SQL server thus saving on the Sessions.

Global Vairables

For all we knew all this while was that global variables were not possible with Navision. Navision has always supported module level variables i.e. a variable accessible throughout a module like a form, report... what we are talking about here is a variable which is accessible across objects.

Well there is only one way to achieve this and it is using the Single Instance property of a Codeunit. Any codeunit which is set as single instance is static in nature and is initialized only once.

Error Handling

Did you know that you can handle errors in navision. For all we thought all this while is that once an error occurs navision handles it itself and there is hardly anything a programmer could do about it.

Well handling errors is something which is available to a limited degree but atleast there is an option. The errors could only be handled if they occur in a codeunit. Thus a codeunit is required to encapsulate any such code one needs to handle.

If the If statement is used while executing a codeunit then an errors would not break out of the code instead would allow us handling of the same. There are two commands one need to know for this
GETLASTERRORTEXT
CLEARLASTERROR
these are available in the CAL Symbol menu which is accessed using the F5 key under
SYSTEM -> Error Handling

the properties are self explanatory, all one needs to do is execute the codeunit as shown :-


IF CODEUNIT.RUN(50005) THEN BEGIN
//Code to execute on success
END ELSE BEGIN
//Code to execute on failure
END

Accessing File system through Navision

Well this is an interesting case where we were required to import a text file into Navision and then take an action if it succeeded or failed. The requirement was such that there was a designated folder where files would be created on a scheduled basis with the timestamp being an part of the filename so that duplicates don't arise which means we could not hardcode a name for the text file in our code.

luckily for the File system table this was possible. The record has the following fields :
1. Path
2. Is a File
3. Name
4. Size
5. Date
6. Time
7. Data

As we knew the folder to search for this is what we had done

FileRec.RESET;
FileRec.SETRANGE(FileRec.Path,SalesSetup."Import Folder");
FileRec.SETRANGE(FileRec."Is a file",TRUE);
IF FileRec.FINDFIRST THEN REPEAT
Filename1 := FileRec.Path + '\' + FileRec.Name;
Filename2 := SalesSetup."Success Folder"+ '\' + FileRec.Name;
Filename3 := SalesSetup."Error Folder"+ '\' + FileRec.Name;
DataPortCodeunit.DefineFileName(Filename1);

IF DataPortCodeunit.RUN THEN BEGIN
IF FILE.COPY(Filename1,Filename2) THEN BEGIN
IF ERASE(Filename1) THEN;
END;
END
ELSE BEGIN
IF FILE.COPY(Filename1,Filename3) THEN BEGIN
IF ERASE(Filename1) THEN;
END;
IF GUIALLOWED THEN
MESSAGE('There was an error importing file: \\%1 \\%2',Filename1,GETLASTERRORTEXT);
END;
UNTIL FileRec.NEXT = 0;

The CHAR datatype

Ever wondered what would be the use of a char datatype when we already have datatypes like text and code. Well you'll see one very important use though which i came across i am not sure if there other other uses as well.

This happened on a project where i was required to create a export from navision in a format that did not have field level delimeters and i figured out that this was not possible using the dataport as it always added delimeters before and after a field so i had to create this text file which a carriage return after each line.

Carriage return as we know is a combination of two characters chr(13) and chr(10) i wished at that point i could use the chr command like in VB thatz when the char datatype came to rescue with this datatype you create a variable and assign it an ASCII integer value it automatically translates it back into a character and then FORMAT command can be used to concatenate it with your string operations