Well add a linked server using the procedure sp_linkedserver and drop the same using sp_dropserver
Found an issue when data is being pushed from a Windows 2000 sql server to a Windows 2005 sql server using a link. The same would not succeed when the update statement used columns with spaces it seemed when the statement was passed to the remote server the square [] brackets around the field name were not getting carried this issue is confirmed as a bug on the microsoft site the work around for this could be to use a nested query for comparison with the field coz if compared with a memory variable it would generate an error.
Thursday, December 27, 2007
Saturday, December 22, 2007
CRC Routine with VB
The CRC (Cyclic Redundancy Checksum) algorithm is a highly optimised and powerful way of checking whether a large number of bytes have been modified or not. The algorithm scans through all the bytes and generates a 32 bit number to represent the contents - small changes in the file contents result in large changes in the check sum, and there is a very low chance that two different streams of bytes will have the same CRC.
The implementation here uses a pre-calculated lookup table to do a lot of the heavy work of generating the polynomial. Once this has been calculated then the actual algorithm becomes relatively simple.
Option Explicit
' This code is taken from the VB.NET CRC32 algorithm
' provided by Paul (wpsjr1@succeed.net) - Excellent work!
Private crc32Table() As Long
Private Const BUFFER_SIZE As Long = 8192
Public Function GetByteArrayCrc32(ByRef buffer() As Byte) As Long
Dim crc32Result As Long
crc32Result = &HFFFFFFFF
Dim i As Integer
Dim iLookup As Integer
For i = LBound(buffer) To UBound(buffer)
iLookup = (crc32Result And &HFF) Xor buffer(i)
crc32Result = ((crc32Result And &HFFFFFF00) \ &H100) And 16777215 ' nasty shr 8 with vb :/
crc32Result = crc32Result Xor crc32Table(iLookup)
Next i
GetByteArrayCrc32 = Not (crc32Result)
End Function
Public Function GetFileCrc32(ByRef stream As cBinaryFileStream) As Long
Dim crc32Result As Long
crc32Result = &HFFFFFFFF
Dim buffer(0 To BUFFER_SIZE - 1) As Byte
Dim readSize As Long
readSize = BUFFER_SIZE
Dim count As Integer
count = stream.Read(buffer, readSize)
Dim i As Integer
Dim iLookup As Integer
Dim tot As Integer
Do While (count > 0)
For i = 0 To count - 1
iLookup = (crc32Result And &HFF) Xor buffer(i)
crc32Result = ((crc32Result And &HFFFFFF00) \ &H100) And 16777215 ' nasty shr 8 with vb :/
crc32Result = crc32Result Xor crc32Table(iLookup)
Next i
count = stream.Read(buffer, readSize)
Loop
GetFileCrc32 = Not (crc32Result)
End Function
Private Sub Class_Initialize()
' This is the official polynomial used by CRC32 in PKZip.
' Often the polynomial is shown reversed (04C11DB7).
Dim dwPolynomial As Long
dwPolynomial = &HEDB88320
Dim i As Integer, j As Integer
ReDim crc32Table(256)
Dim dwCrc As Long
For i = 0 To 255
dwCrc = i
For j = 8 To 1 Step -1
If (dwCrc And 1) Then
dwCrc = ((dwCrc And &HFFFFFFFE) \ 2&) And &H7FFFFFFF
dwCrc = dwCrc Xor dwPolynomial
Else
dwCrc = ((dwCrc And &HFFFFFFFE) \ 2&) And &H7FFFFFFF
End If
Next j
crc32Table(i) = dwCrc
Next i
End Sub
The implementation here uses a pre-calculated lookup table to do a lot of the heavy work of generating the polynomial. Once this has been calculated then the actual algorithm becomes relatively simple.
Option Explicit
' This code is taken from the VB.NET CRC32 algorithm
' provided by Paul (wpsjr1@succeed.net) - Excellent work!
Private crc32Table() As Long
Private Const BUFFER_SIZE As Long = 8192
Public Function GetByteArrayCrc32(ByRef buffer() As Byte) As Long
Dim crc32Result As Long
crc32Result = &HFFFFFFFF
Dim i As Integer
Dim iLookup As Integer
For i = LBound(buffer) To UBound(buffer)
iLookup = (crc32Result And &HFF) Xor buffer(i)
crc32Result = ((crc32Result And &HFFFFFF00) \ &H100) And 16777215 ' nasty shr 8 with vb :/
crc32Result = crc32Result Xor crc32Table(iLookup)
Next i
GetByteArrayCrc32 = Not (crc32Result)
End Function
Public Function GetFileCrc32(ByRef stream As cBinaryFileStream) As Long
Dim crc32Result As Long
crc32Result = &HFFFFFFFF
Dim buffer(0 To BUFFER_SIZE - 1) As Byte
Dim readSize As Long
readSize = BUFFER_SIZE
Dim count As Integer
count = stream.Read(buffer, readSize)
Dim i As Integer
Dim iLookup As Integer
Dim tot As Integer
Do While (count > 0)
For i = 0 To count - 1
iLookup = (crc32Result And &HFF) Xor buffer(i)
crc32Result = ((crc32Result And &HFFFFFF00) \ &H100) And 16777215 ' nasty shr 8 with vb :/
crc32Result = crc32Result Xor crc32Table(iLookup)
Next i
count = stream.Read(buffer, readSize)
Loop
GetFileCrc32 = Not (crc32Result)
End Function
Private Sub Class_Initialize()
' This is the official polynomial used by CRC32 in PKZip.
' Often the polynomial is shown reversed (04C11DB7).
Dim dwPolynomial As Long
dwPolynomial = &HEDB88320
Dim i As Integer, j As Integer
ReDim crc32Table(256)
Dim dwCrc As Long
For i = 0 To 255
dwCrc = i
For j = 8 To 1 Step -1
If (dwCrc And 1) Then
dwCrc = ((dwCrc And &HFFFFFFFE) \ 2&) And &H7FFFFFFF
dwCrc = dwCrc Xor dwPolynomial
Else
dwCrc = ((dwCrc And &HFFFFFFFE) \ 2&) And &H7FFFFFFF
End If
Next j
crc32Table(i) = dwCrc
Next i
End Sub
Sunday, December 09, 2007
Inventory Costing
Every Positive inventory quantity is applied against a negative quantity this is known as the item application and this information is maintained in Navision in a table called 339 - Item Application Entry
The Item Application Entry table has three columns which can be used to track the base of a entry these columns are:
1. Item Ledger Entry No.
2. Inbound Item Entry No.
3. Outbound Item Entry No.
The Item ledger entry is the base for this tracking in every line the item ledger entry no would be equal to one of the two columns "Inbound Item Entry No" or "Outbound Item Entry No" the column which is not equal is the column which resulted into the currenty entry.
Entries are usually applied according to the cost flow assumption that is defined by the costing method. However, if more accurate information about the cost flow exists, the user can overrule the general cost flow assumption by using a fixed application, which creates a link between an inventory decrease and a specific inventory increase and vice versa.
In the case of average cost items, a fixed application has the purpose of avoiding errors in the average cost calculation. Creating a fixed application can be useful, for example, when correcting an erroneous posting. Item ledger entries that are applied to each other are not valued by average. The two relevant entries serve to cancel each other, and the sum value of the Cost Amount (Actual) field for the transaction becomes zero. Thus, the program excludes it from the normal average cost calculation
The Item Application Entry table has three columns which can be used to track the base of a entry these columns are:
1. Item Ledger Entry No.
2. Inbound Item Entry No.
3. Outbound Item Entry No.
The Item ledger entry is the base for this tracking in every line the item ledger entry no would be equal to one of the two columns "Inbound Item Entry No" or "Outbound Item Entry No" the column which is not equal is the column which resulted into the currenty entry.
Entries are usually applied according to the cost flow assumption that is defined by the costing method. However, if more accurate information about the cost flow exists, the user can overrule the general cost flow assumption by using a fixed application, which creates a link between an inventory decrease and a specific inventory increase and vice versa.
In the case of average cost items, a fixed application has the purpose of avoiding errors in the average cost calculation. Creating a fixed application can be useful, for example, when correcting an erroneous posting. Item ledger entries that are applied to each other are not valued by average. The two relevant entries serve to cancel each other, and the sum value of the Cost Amount (Actual) field for the transaction becomes zero. Thus, the program excludes it from the normal average cost calculation
Saturday, December 01, 2007
Creating Navision Shortcuts
Well we had this situation when the client wanted a given navision task to be schedule every night and they didn't have the license for the Navision Task Scheduler so it clicked we could use the navision shortcuts to launch navision using windows scheduler and execute the desired object.
Creating Navision Shortcuts
Client-Parameter:
servername=Name of the server
database=Database Name
company=Company Name
id=The name for the user setup file
nettype= Netb, TCP, TCPS
ntauthentication=[Yes/No]
dbreadonly=[Yes/No]
This program property allows you to specify that the database has read access only.
This prevents other users from entering data into the database
dbtest=[Min|Max|Normal]
You can use this program property to test the consistency and integrity of the
database.
commitcache=[Yes/No]
The Commit Cache program property allows Dynamics NAV to postpone writing the information stored in cache on the server to the database until later. Storing this information in cache allows Dynamics NAV to work faster.
cache=CacheInKB
objectcache=CacheInKB ( More than 0 KB and less than 1,000,000 KB )
The Object Cache property increases the speed of the program. Objects such as code,
descriptions and windows that will be used on the client computer are stored in the
object cache. This means that the client computer only needs to retrieve these objects once from the server, and then they will be stored in the object cache. The client computer must have enough memory to store the objects while they are being used in order to benefit from the object cache
temppath=TempPath
When Dynamics NAV is running it creates a number of temporary files, which are
automatically deleted when you close the program.
testtarget=[@screen|@eventlog|filepath]
You use this program property to specify how any error messages that are generated
during a database test are managed. They can be displayed on the screen or stored in
the Event Log or in a text file
ShowHelpID=[Yes/No]
Example
navision://client/run?servername=SANTOSH\MRMS&company=Global Link Communications LLC&database=GLC-2507&target=Form50061&servertype=MSSQL
if you are thinking how to launch the url using the command shell then its easy just use the command start as cmd.exe does not recognize commands with protocol
E.g.
start navision://client/run "servername=SANTOSH\MRMS&company=Global Link Communicatio
ns LLC&database=GLC-2507&target=Form50061&servertype=MSSQL&ntauthentication=1"
Creating Navision Shortcuts
Client-Parameter:
servername=Name of the server
database=Database Name
company=Company Name
id=The name for the user setup file
nettype= Netb, TCP, TCPS
ntauthentication=[Yes/No]
dbreadonly=[Yes/No]
This program property allows you to specify that the database has read access only.
This prevents other users from entering data into the database
dbtest=[Min|Max|Normal]
You can use this program property to test the consistency and integrity of the
database.
commitcache=[Yes/No]
The Commit Cache program property allows Dynamics NAV to postpone writing the information stored in cache on the server to the database until later. Storing this information in cache allows Dynamics NAV to work faster.
cache=CacheInKB
objectcache=CacheInKB ( More than 0 KB and less than 1,000,000 KB )
The Object Cache property increases the speed of the program. Objects such as code,
descriptions and windows that will be used on the client computer are stored in the
object cache. This means that the client computer only needs to retrieve these objects once from the server, and then they will be stored in the object cache. The client computer must have enough memory to store the objects while they are being used in order to benefit from the object cache
temppath=TempPath
When Dynamics NAV is running it creates a number of temporary files, which are
automatically deleted when you close the program.
testtarget=[@screen|@eventlog|filepath]
You use this program property to specify how any error messages that are generated
during a database test are managed. They can be displayed on the screen or stored in
the Event Log or in a text file
ShowHelpID=[Yes/No]
Example
navision://client/run?servername=SANTOSH\MRMS&company=Global Link Communications LLC&database=GLC-2507&target=Form50061&servertype=MSSQL
if you are thinking how to launch the url using the command shell then its easy just use the command start as cmd.exe does not recognize commands with protocol
E.g.
start navision://client/run "servername=SANTOSH\MRMS&company=Global Link Communicatio
ns LLC&database=GLC-2507&target=Form50061&servertype=MSSQL&ntauthentication=1"
Thursday, November 08, 2007
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.
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.
Wednesday, November 07, 2007
Wednesday, October 31, 2007
Loading Inventory Opening Balances
There could be two scenarios under this topic based on when the chart of accounts is live or not.
- 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
- 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.
- When posting the opening JV the debit of inventory is posted to the stock adjustment account instead of the actual stock account.
- For clarity sake we can created a new suspense account and use that instead of the regular stock adjustment account
- 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
- Temporarily for the sake of posting the opening inventory map the GL accounts for inventory to an interim account.
- Map the stock adjustment account to the same interim account.
- Post the Inventory JV now the debit and credit would both go to the same account and nullify.
- Revert back the account mappings to the original settings.
Sunday, October 28, 2007
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.
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.
Tuesday, October 02, 2007
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 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
Wednesday, August 29, 2007
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.
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.
Monday, August 27, 2007
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.
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.
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.
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.
Sunday, August 12, 2007
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.
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.
Saturday, August 11, 2007
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.
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.
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
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;
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
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
Subscribe to:
Posts (Atom)