MS Access Database Design Mode

We had this application in MS Access which read data from a DBF file and created a text file which could then be imported into Navision. One fine day the text file would not create and there was no error flashed on the screen. To debug the issue i wanted to get to the code window of access, but the moment i clicked on the application it would get into the run mode and i could not figure out the way to get into the design mode or the database design of the application.

I Later realized that if i held the shift key while opening the application then it would open in design mode. The application was not executing as one of the DBF table had certain deleted records which could not be read once these records were purged the application started to work.

Ashu Bhaiya's Avtaar

Loading Inventory Opening Balances

There could be two scenarios under this topic based on when the chart of accounts is live or not.

1. When the company is going live and the chart of accounts also has to be imported. In this case we know that the inventory account in the COA will also have some balance which would account for the inventory cost. Now when the inventory journal is passed with some positive adjustments then the inventory account will get updated in the GL which mean the opening JV should not update the inventory again else the inventory amount would double. So this is how it is done
1. Post the positive adjustments which will update the quantities in the inventory sub-ledger and the stock adjustment account with a credit of an equivalent amount.
2. When posting the opening JV the debit of inventory is posted to the stock adjustment account instead of the actual stock account.
   
3. For clarity sake we can created a new suspense account and use that instead of the regular stock adjustment account
2. The second case is when the chart of accounts has been already imported and the inventory cost is already sitting in the respective accounts. In this case we only want the quantities to be imported and we do not want to have any effect on the GL. This is how it would be done
1. Temporarily for the sake of posting the opening inventory map the GL accounts for inventory to an interim account.
   
2. Map the stock adjustment account to the same interim account.
3. Post the Inventory JV now the debit and credit would both go to the same account and nullify.
4. Revert back the account mappings to the original settings.
   

Automating the Parameters in a Crystal Report

Well the advantage with using the parameters is that is reduces the traffic from the server to the client. If a condition is used in the record selection with formulas then the filtering is done on the client which is not the best way when the data on the underlying report is too much i faced this issue and has to resolved to use the parameters collections of crystal.

Well as i was using a crystal reports control and did not want the ugly parameter box popped by crystal as it does not provide features like browse etc which i had build on my request form. I then realized that there is a way to this although easy but had to really look around to get he syntax right :-) anyways here it is: -

ParameterFields:
This property specifies the default value of the specified parameter field.When the prompting dialog box appears for the parameter field, the value specified with this property will be the default value that appears it can also be skipped as we will see later. This property is not available for subreports.


Syntax
[form.]Report.ParameterFields(ArrayIndex)[=”ParameterName;NewValue;SetCurrentValue”]

Remarks:

The parameter, SetCurrentValue can either be set to TRUE or FALSE.

If set to TRUE, the parameter value is passed to the current value in the report; the user is not prompted to enter the parameter value.

If set to FALSE, the parameter value is passed to the default value for the parameter; the user is prompted to enter the parameter value, with the value you set showing as the default value.

The default value for SetCurrentValue is FALSE.

Use a separate line of code for each parameter field for which you want to change the value.

The order of values in the array must conform to the order of parameter fields in the report.

The first parameter field in the report is array index (0), the second is (1), etc.


For example:

To change the value of the first parameter field in a report (parameter1) to “red” use the following syntax (user will not be prompted to enter a value):
CrystalReport1.ParameterFields(0) = “parameter1;red;TRUE”


To change the value in a Date parameter field use the following syntax (user will not be prompted to enter a value):

CrystalReport1.ParameterFields(0) = “DatePar;Date(1998,12,7);TRUE”


To prompt the user to change the value of the third parameter field in a report (parameter3) use the following syntax (user will be prompted to use the default value set using the NewValue parameter below - “blue”):

CrystalReport1.ParameterFields(2) = “parameter3;blue;FALSE”


Note:
Even though one might use parameters the filters will not be applied on the server if the parameters are used within conditions in the record selection formula.

I was trying to use if conditions in the record selection formula to have dynamic record selection as below :

local booleanvar showrecord;

showrecord := True;
showrecord := showrecord and ( {vw_transport_log.tr_closed} = 'N');

if {vw_transport_log.from_location} <> '' then
showrecord := showrecord and ( {vw_transport_log.from_location} = {@from_warehouse} );

if ({@to_warehouse} <> '') then
showrecord := showrecord and ({vw_transport_log.to_location} = {@to_warehouse});

if ({@transporter} <> '') then
showrecord := showrecord and ({vw_transport_log.transporter} = {@transporter});

if ( {@from_date} <> Date (1900,01,01) ) then
showrecord := showrecord and ({vw_transport_log.start_date} >= {@from_date});

if ( {@to_date} <> Date (1900,01,01) ) then
showrecord := showrecord and ({vw_transport_log.start_date} <= {@to_date}); showrecord; then i realized that in cases like this the filters are applied at the client so to force the filters at server the record selection formula should be modified as follows: and ({vw_sagya_movements.dated} >= {?FromDate})
and ( {vw_sagya_movements.dated} <= {?ToDate} ) and ({vw_sagya_movements.from_location} = {?FromWarehouse} );

which means "no if conditions"; the way around this could be to apply extreme values to the from and to parameters, in case a filter is not required. For eg if date filter is skipped then i apply a date filter like 1900-01-01 to 2099-01-01 it is as good as the filter is not applied.

SQL 2005 Startup Options

Well struggled for this a lot although quite silly, Well we all know that SQL 2005 to work with Navision requires that the trace flag 4616 is turned on. To enable this the best way is to specify this as a startup option for the SQL Service. This option can be set using the SQL Server Configuration manage here one needs to right click on the SQL service and select the advanced tab and use the startup parameter to specify one.

The only confusion is that traditionally we have been using multiple command line parameters separated by a space but in this case the parameters are delimited by a semicolon (;) and there should not be any space between two options.

this is a typical value for the startup parameters option

-T4616;-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

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