AX TableName in AOT and SQL

Well ran into this problem of searching for a table in SQL. I find the table in AX and was not able to find the corresponding table in SQL Server. Finally managed to figure out that AOT does not create table names more than 30 characters in the backend.

This maybe because AX wanted to keep the data structure portable between SQL and Oracle or any other ERP. Thus what AOT does is that if the table name exceeds 30 char it truncated the table name in such a way that when clubbed with the table id it runs into 30 characters

hence, if a table is created in AOT with name = HRPPartyPositionTableRelationship and id = 2226 it would be physically be created as HRPPARTYPOSITIONTABLERELAT2226

Limit Navision User Sessions Per Company

Had this interesting requirement from one of our customers who had more than one companies in the same database. The total no of concurrent sessions were 50 but it was required that not more than a certain no of users are allowed to log on into one company.

ie. Not more then 15 users in Company 1
Not more then 10 users in Company 2
Not more then 25 users in Company 3

Below the solution :-)

//Following code goes into ApplicationManagement Codeunit 1 CompanyOpen

//changes to Company table to add a new field No of Users type Int
//a new table to be created to store the active connections as follows
//"Connection ID" int
//Company Text(50)
//"User Id" Code(20)

//check for any orphan sessions which may be lying due to navision crashes
//orphan sessions to be cleaned. This can also be used to drop inactive
//sessions before starting a new one.
ActiveConnections.RESET;
IF ActiveConnections.FIND('-') THEN REPEAT
Session.SETRANGE("Connection ID", ActiveConnections."Connection ID");
Session.SETRANGE("Application Name", 'Microsoft Dynamics NAV client');
IF Session.FIND('-') = FALSE THEN
ActiveConnections.DELETE;
UNTIL ActiveConnections.NEXT() = 0;

ActiveConnections.RESET;
Session.RESET;
Session.SETRANGE("My Session", TRUE);
IF Session.FIND('-') THEN BEGIN
ActiveConnections.SETRANGE("Connection ID", Session."Connection ID");
IF ActiveConnections.FIND('-') = FALSE THEN BEGIN
ActiveConnections.INIT;
ActiveConnections."Connection ID" := Session."Connection ID";
ActiveConnections.Company := COMPANYNAME;
ActiveConnections."User Id" := USERID;
ActiveConnections.INSERT;
END;
END;

Company.GET(COMPANYNAME);
ActiveConnections.RESET;
ActiveConnections.SETRANGE(Company, COMPANYNAME);
IF (ActiveConnections.COUNT > Company."No Of Users") THEN BEGIN
WHILE CONFIRM('Limit for no of users (%1) has reached. Do you wish to try again ?',TRUE, Company."No Of Users")
DO BEGIN
ActiveConnections.RESET;
ActiveConnections.SETRANGE(Company, COMPANYNAME);
IF (ActiveConnections.COUNT <= Company."No Of Users") THEN
EXIT;
END;

YIELD;
IF ISCLEAR(WshShell) THEN
CREATE(WshShell);

YIELD;
WaitForKeys := TRUE;
WshShell.SendKeys('%{F4}', WaitForKeys);
CLEAR(WshShell);
END;



//Following code goes into ApplicationManagement Codeunit 1 CompanyClose
Session.SETRANGE("My Session", TRUE);
IF Session.FIND('-') THEN BEGIN
ActiveConnections.SETRANGE("Connection ID", Session."Connection ID");
ActiveConnections.DELETEALL;
END;

Slow Navision Report Preview

Had this funny incident today we designed a report which was working perfectly well then i made my office printer as the default printer to test the printing of the report. Working from my home i noticed suddenly the report was very slow.

The issue is the default printer which is the network printer was taking the time to connect when the printer was changed to the local printer the issue was taken up.

sp_change_users_login

sp_change_users_login does not work for sa and dbo instead use the sp_changedbowner

GenJrnlLine.SetUpNewLine Dimension Lines Error

the SetUpNewLine function does the initialization of a new line when creating an automated Journal. I noticed when this function was used for some reason the Journal Line Dimension stopped getting populated. I did not get into the route of the issue however it solves when this function is called after a new line no has been assigned thus i had to change from code from:

LineNo += 10000;
GenJrnlLine.INIT;
GenJrnlLine."Journal Template Name" := GLSetup."Consumption JV Template";
GenJrnlLine."Journal Batch Name" := GLSetup."Consumption JV Batch";
GenJrnlLine.SetUpNewLine(GenJrnlLine, 0.00, FALSE);
GenJrnlLine."Line No." := LineNo;


to

LineNo += 10000;
GenJrnlLine.INIT;
GenJrnlLine."Journal Template Name" := GLSetup."Consumption JV Template";
GenJrnlLine."Journal Batch Name" := GLSetup."Consumption JV Batch";
GenJrnlLine."Line No." := LineNo;
GenJrnlLine.SetUpNewLine(GenJrnlLine, 0.00, FALSE);

Adjust Currency Exchange Rate

The currency exchange rate batch job looks into the customer and vendor sub ledgers and posts the difference to a gl account.

for the same to work there must be a posting type defined against the payables account mentioned on the vendor posting group, else the internal JV posting fails

Minimize Outlook to Tray

To minimize the Outlook as a tray icon instead of Taskbar add the following key in the registry

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Preferences

add a new DWORD value

MinToTray = 1

Not In Vs Not Exists

The Not in and Not exists clauses are quite similar except for one major difference which can be illustrated using the example below:

EMP_NBR	EMP_NAME	MGR_NBR
1	DON	5
2	HARI	5
3	RAMESH	5
4	JOE	5
5	DENNIS	NULL
6	NIMISH	5
7	JESSIE	5
8	KEN	5
9	AMBER	5
10	JIM	5

Now, the aim is to find all those employees who are not managers. Let’s see how we can achieve that by using the “NOT IN” vs the “NOT EXISTS” clause.

SQL> select count(*) from emp_master where emp_nbr not in ( select mgr_nbr from emp_master );

COUNT(*)
———-
0

SQL> select count(*) from emp_master T1 where not exists ( select 1 from emp_master T2 where t2.mgr_nbr = t1.emp_nbr );

COUNT(*)
———-
9

Now there are 9 people who are not managers. So, you can clearly see the difference that NULL values make and since NULL != NULL in SQL, the NOT IN clause does not return any records back.

There is no value entry within the Filter

Well this is the error we received at one of the customer sites when they were executing the famous adjust cost batch job. Did some real head breaking into this one and discovered that when the Inventory setup has been set as:
1. Average Cost Calc. Type = Item
2. Average Cost Period = Month

The error was something like:
There is no Value entry within the Filter
Filters : Item No: 1100053749, Valution Date: 01/01/09..31/12/08

On debugging we found the error was being caused by an FINDSET; statement which was issues in the following Codeunit:

Codeunit : 5895, Inventory Adjustment
function : AvgValueEntriesToAdjustExist(OutbndValueEntry;ExcludedValueEntry,AvgCostAdjmtEntryPoint)


Got around the issue after a detailed profiling of the code and found that when the valuation date changes and a new filter is being evaluation in the code it somehow caused caused a wrong filter on the Value Entry Table the code snipped was as shown below

IF "Valuation Date" > CalendarPeriod."Period End" THEN BEGIN
CalendarPeriod."Period Start" := "Valuation Date";
AvgCostAdjmtEntryPoint.GetValuationPeriod(CalendarPeriod);
END;

How i managed to get around the issue was to add one line here as shown below:
IF "Valuation Date" > CalendarPeriod."Period End" THEN BEGIN
CalendarPeriod."Period Start" := "Valuation Date";
AvgCostAdjmtEntryPoint."Valuation Date" := "Valuation Date";
AvgCostAdjmtEntryPoint.GetValuationPeriod(CalendarPeriod);
END;

I finally concluded that the problem was encountered when a value entry record was found for a new financial year (2009 in this case) and there were no records for the previous year end (December 2008).

If we look at the code GetValuationPeriod(CalendarPeriod) in Table 5804 "Avg. Cost Adjmt. Entry Point" at the end of the function the following lines were setting the values to be used to set the filter :

//*****************************************************************************
IF FiscalYearAccPeriod."Starting Date" IN [CalendarPeriod."Period Start"..CalendarPeriod."Period End"] THEN
IF "Valuation Date" < FiscalYearAccPeriod."Starting Date" THEN
CalendarPeriod."Period End" := CALCDATE('<-1D>',FiscalYearAccPeriod."Starting Date")
ELSE
CalendarPeriod."Period Start" := FiscalYearAccPeriod."Starting Date";
//*****************************************************************************
All we did was to set the valuation date so that it does not evaluate to less then the Financial Period."Start Date"

Navision Filters

We all know the basics of Filters now lets turn to some advanced ways of using and understanding filters.

? which can be used while building a filter to substitute one unknown character pretty much like the Wildcard Characters used in DOS days

@ which can be used to ignore the case of the text being searched for
thus @co* would search for anything beginning with Co , cO, CO or co followed by any character as indicated by an asterix.

There has been some other types of filters which are referred to in Navision code with commands like FIND('=><') OR FIND('=<>')

1. '=><' when the above three expressions are combined it would stand for. If equal '=' rec is not found, search for a record which is smaller '<' and if smaller rec is not found search for a rec which is bigger '>' basically means find anything which was used in olden days to check if the rec filter was empty or not.

2. '=<>' this would translate to find something "equal to" or "not equal to" which means "find anything" similar to the command above

Today we also have ISEMPTY commmand to determine if a recordset is empty.

Procedure to Update Statistics for All indexes of a Table

sp_updatestats (Transact-SQL)
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.


Find the user defined procedures to run it for a table.

/*
select 'usp_update_statistics [' + name + ']' + char(13) + char(10)
+ 'go' + char(13) + char(10)
from sysobjects where type = 'U'
and not ascii( right ( [name], 1 ) ) between 48 and 57
*/


Alter procedure usp_update_statistics
@Table_Name varchar(255)
as
declare @Index_Name varchar(255)
declare @SQL nvarchar(500)


declare index_cursor cursor for ---Getting index name
select name from sysindexes
where id = object_id(@Table_Name)
and indid > 0
and indid < 255
and (status & 64)=0
order by indid

open index_cursor

fetch next from index_cursor
into @Index_Name

while @@fetch_status=0
begin
set @SQL = ''
set @SQL = @SQL + 'update statistics [' + @Table_Name + '] ( [' + @Index_Name + '] )'
set @SQL = @SQL + ' WITH FULLSCAN, NORECOMPUTE'
exec sp_executesql @SQL

print @SQL
print ''

fetch next from index_cursor
into @Index_name
end
close index_cursor
deallocate index_cursor
print 'Complete.'

sp_cursorfetch

Had this terrible issue with Navision Value Entries form. Whenever the form was opened from the Item Card Navision would freeze did a profile in SQL for all the SQL statements and found that the system was taking time to execute a command which started like sp_cursorfetch.

Some RND and found that the sp_cursorfetch is implemented by the database library to manage the cursors at the server side. Before a cursor fetch can be issue a cursor open statement has to be declared which would contain the base statement for the cursor. Once i found the statement being used for the cursor i did a execution plan display and found that it was not using the correct index.

I then updated the statistics for the index using the Update Statistics command we all know that statistics decide the selectivity of an index once this was done the cursor started using this index and the issue was resolved.

Format Strings

Format function with Navision is really powerful all we need to do is to understand in detail how the format string is built

Below is one example what i wanted to do was to generate a file name in the format DDMMYY-HHMMSS.txt also i wanted DD (Date) to be two characters and MM (Month) to be padded with 1 zero if it was 1 character in length. Amazingly the entire thing could be achieved just by one amazing function FORMAT

FORMAT(CURRENTDATETIME,0,'<FillerCharacter,0><Day,2><Month,2><Year><Hours24,2><Minutes,2><Seconds,2>')

All the different parameters that can be used with FORMAT function are well documented in the online help.

Browse For Folder in Navision

Navisions implementation of Common Dialog Box does not cater to Browse for folder. There is a small workaround for this although it is not very neat but it does the work. Below is the code for the same this is a function which returns the FolderName and DefaultFolderName is the parameter to the function.


IF DefaultFolderName = '' THEN
DefaultFolderName := 'C:\Folder'
ELSE
DefaultFolderName := DefaultFolderName + '\Folder';

FolderName := CmmDlg.OpenFile('Select Folder'
, DefaultFolderName
, 4
, 'All File (*.*)|*.*'
, 0 );

//Truncate the file name from the path
Ctr :=STRLEN(FolderName);
WHILE Ctr > 0 DO BEGIN
IF COPYSTR(FolderName, Ctr, 1) = '\' THEN BEGIN
FolderName := COPYSTR(FolderName, 1, Ctr -1 );
EXIT;
END;
Ctr -= 1;
END

The only thing we are doing here is that we are providing a default filename in the browse window thus the open button is enabled without waiting for the user to select a file name.

Arabic Data

Came across this client who wanted to capture the item descriptions in Arabic in addition to the english descriptions. Changing the System Keyboard alone does not help for this the following steps are involved.

1. Install Supplemental Language Support for this go to Control Panel -> Regional and Language options -> Languages Tab select the Install files for complex script and right to left languages.
2. In the input languages click on the details button and add arabic keyboard support.
3. Change the Language for Unicode Programs this can be changed from Control Panel -> Regional and Language options -> Advanced Options Tab.
4. Once the language has been changed the database collation should be change to support the new code page. For this use the alter database option -> Collation Tab and select the collation for the arabic language.

Now you are all set to save english and arabic in the Navision database.

OnCreateHyperLink and OnHyperLink

Ever wondered about these triggers in navision and what they are meant for. Well Navision offers a facility to create links to the different objects within it. Using this feature a hyperlink could be created for a Form in Navision from the desktop. Creation of the hyperlinks is simple open the desired Form and then use the File -> Send to option to place a hyperlink on the desktop.

When the send to desktop option is used the first thing the option does is to call the OnCreateHyperlink trigger with the URL as the parameter. The URL parameter can be modified in the trigger giving one a control over what needs to be placed in the URL string. The OnHyperLink trigger is called when the form is accessed using this link and the URL is passed in the trigger.

PrintOnlyIfDetail Property in Navision Reports

Figured out that this propery does not work if for a Dataitem if the child DataItem does not have a section defined in the section view. The way around was to create a section and use CurrReport.ShowOutPut(False) to suppress the section as well as make the PrintOnlyIfDetail property work in the desired manner.

Adjust Exchange Rates Dimension Error

The adjust exchange rates batch job calculates and post the exchange gain/loss entries due to transactions in foreign currency. In doing so the batch job is required to pass a no of entries into the G/L.

In cases where the G/L has been tightly bound by dimension rules it can sometimes fails in this case it is necessary to understand the entries that this batch job passes and accordingly have the rules in place.

The Batch job as mentioned in the help creates one entry per currency per posting group of the banks defined. Because it consolidates the entries it cannot pick the dimensions from the source transactions. Thus it picks the dimensions which are defined as the default dimensions on the bank card. Thus we need to make sure that the dimension rules are met by these default dimensions. The entries are passed to the control accounts defined in the posting groups for the banks and the exchange gain or loss accounts.

In case of adjusting the Customer and Vendor Accounts it passes entries to the control accounts and a balancing entry is passed to the Exchange gain or loss accounts as configured in the the currency setup.

Columns in a Primary Key Index

Query to get the columns in a primary key for a table

select object_name(SI.id) tableName, SI.name indexName, SC.name
from sysindexes SI
inner join sysindexkeys SIK
on SIK.id = SI.id
and SIK.indid = SI.indid
inner join syscolumns SC
on SC.colid = SIK.colid
and SC.id = SI.id
where 1=1
and object_name(SI.id) = 'SalesHeader'
and SI.status in ( 2066 , 2048 )

sysindexes (T-SQL)

Contains one row for each index and table in the database. This table is stored in each database. This same table can be used to identify clustered indexes and non-clustered indexes the clustered index always has the indid = 1 and a non-clustered index will have an indid > 1

Column name	Data type	Description
id	int	ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.
status	int	Internal system-status information:
		1 = Cancel command if attempt to insert
		duplicate key.
		2 = Unique index.
		4 = Cancel command if attempt to insert
		duplicate row.
		16 = Clustered index.
		64 = Index allows duplicate rows.
		2048 = Index used to enforce PRIMARY KEY
		constraint.
		4096 = Index used to enforce UNIQUE constraint.
first	binary(6)	Pointer to the first or root page.
indid	smallint	ID of index:
		1 = Clustered index.
		>1 = Nonclustered.
		255 = Entry for tables that have text or image
		data.
root	binary(6)	For indid >= 1 and <>root is the pointer to the root page. For indid = 0 or indid = 255, root is the pointer to the last page.
minlen	smallint	Minimum size of a row.
keycnt	smallint	Number of keys.
groupid	smallint	Filegroup ID on which the object was created.
dpages	int	For indid = 0 or indid = 1, dpages is the count of data pages used. For indid=255, it is set to 0. Otherwise, it is the count of index pages used.
reserved	int	For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data. For indid = 255, reserved is a count of the pages allocated for text or image data. Otherwise, it is the count of pages allocated for the index.
used	int	For indid = 0 or indid = 1, used is the count of the total pages used for all index and table data. For indid = 255. used is a count of the pages used for text or image data. Otherwise, it is the count of pages used for the index.
rowcnt	binary(8)	Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for indid >1. For indid = 255, rowcnt is set to 0.
rowmodctr	int	Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
soid	tinyint	Sort order ID that the index was created with. 0, if there is no character data in the keys.
csid	tinyint	Character set ID that the index was created with. 0, if there is no character data in the keys.
xmaxlen	smallint	Maximum size of a row.
maxirow	smallint	Maximum size of a nonleaf index row.
OrigFillFactor	tinyint	Original fillfactor value used when the index was created. This value is not maintained; however, it can be helpful if you need to re-create an index and do not remember what fillfactor was used.
reserved1	tinyint	Reserved.
reserved2	int	Reserved.
FirstIAM	binary(6)	Reserved.
impid	smallint	Reserved. Index implementation flag.
lockflags	smallint	Used to constrain the considered lock granularities for an index. For example, a lookup table that is essentially read-only could be set up to do only table level locking to minimize locking cost.
pgmodctr	int	Used to track the number of pages that have changed in an index.
keys	varbinary(816)	List of the column IDs of the columns that make up the index key.
name	sysname	Name of table (for indid = 0 or 255). Otherwise, name of index.
statblob	image	Statistics BLOB.
maxlen	int	Reserved.
rows	int	Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for indid >1. For indid = 255, rows is set to 0. Provided for backward compatibility.