About number of decimal property of an EDT

About number of decimal property of an EDT.

Extended Data Type (EDT) no of decimal property control the following :

When user input manually from a form (or direct table browser), whatever user input will be rounded to the nearest decimal places allowed by no of decimal. E.g. when no of decimal is 2, entering value 4.0179 will result in 4.02. The value 4.02 will be stored in database.

But the property does not control the value that is entered through code. E.g. there is a code that insert a record with value 4.0179, this exact value is stored in the database, up to 12 decimal places for real data type. However when dislayed back in form or table browser, the value will be rounded according to the no of decimal property.

Remember that this propery is not inherited from parent EDT, so that if there is a need to change EDT in many places, chance is that you need to change many EDTs instead of one parent EDTs.

Place to put range in data source

Sometimes, without much thinking we tend to change query of a form data sorce this way :
1. Put in the init method of datasource, after super() the range that will be applied to that table’s datasource.
2. Let the system retrieve the default query.
3. In the run method, which is after system generated query is executed, apply the range and then call the datasource.executeQuery again.

This is inefficient because of two things :
1. The data source query is executed two times, in which the first one is never used. This is a waste of resource.
2. When this form is a master table, go to main table will not work with this approach because go to main table is erased by the second exec ute query.

Better approach will be to follow standard AX InventJournalTable, where range query is applied before super() in the datasource.executeQuery method.
This will make sure only one execute query is needed (which is more efficient) as well as it will regard the go to main table.

PO Posting error after crash

After an application hung, purchase order invoice failed with the following error message :

One or more pending invoices cannot be displayed because they are in use.

Here is how to to get rid of it.

Go to Account Payable > Inquiries > History > Purchase Order > Invoice (Other document status may click on respected menu item).

Perform advanced filter, put this filter :
PurchParmTable.purchid = <purchid>
PurchParmTable.status = Pending.

Click OK.
Delete the record.

Now posting invoice should work.

Agus

Create new role center in Dynamics Ax 2009

Compiled from Dynamics Ax Developer help for quick reference.

1. Open Dynamics Ax enterprise portal (default installation is http://servername/sites/DynamcisAx).
2. Click on Site Action > Create . Choose Web part page (as most Ax role center using this). Complete the page creation.
   
3. Open Ax client > AOT > Web > Web menu item > URLs > New URL.
4. Specify URL Properties by clicking on the elipsis button. Browse Enterprise portal folder and choose the .aspx page created in the step 2.
   If got error go to here.
5. Set home page property to YES. (If not the page will not be available to be chosen as user profile role center page).
6. Right click > Import. This will import the page to AOT under web > web files > page definitions.
7. Locate the new page definition on step 6. Set the page title properties.
8. Go to Admin > Setup > User profile. Create a new record. Choose the role center column from drop down.
9. Click view role center button to test.
   

Dynamics AX 2009 workflow error

I have faced several problem getting workflow to run in Ax 2009.
Here is check list of what to look when workflow doesn't work like a flow.

- Reinstall .net business connector.
- Check that the IIS website where workflow reside is running
- Check that app pool used by workflow website is running
- Check that app pool used by workflow has identity the same with domain user set as business connector proxy account in ax (Admin > Setup > security > System service account > Business connector proxy).
- Check that there are two bath job : workflow message processing and workflow due date expiration in the basic > inquiry > batch job. If they don't exist or have problem, delete and create again using workflow configuration wizard (Admin > setup ).
- Else ? Please add.

Force delete inventtrans

Below is sample job to force delete inventtrans, which will take care of invent on hand update.
static void DeleteInventTrans(Args _args)
{
Dialog dlg = new Dialog("Delete inventtrans ?");
DialogField dlgFld;
InventMovement inventMovement;
PurchLine purchLine;
PurchLineRefRecId recId;
;

dlgFld = dlg.addField(typeid(PurchLineRefRecId));
if(dlg.run())
{
recId = dlgFld.value();
purchLine = PurchLine::findRecId(recId);

if(purchLine)
{
InventMovement = InventMovement::construct(purchLine);
InventUpd_DeleteMovement::newMovement(
inventMovement,true).updateNow();
info("done");
}
}

}

Dynamics AX 2009 reporting service error

I used to get this error message when opening reporting service from Ax :
Error during processing of ‘AX_CompanyName’ report parameter.
(rsReportParameterProcessingError).
Even this error occured in the AX 2009 VPC that previously works perfectly.

This information from partnersource (requires partnersource login) says that in SQL Server 2005 the execution account should not be used, and in SQL Server 2008 it is set to BC proxy account.
I am using SQL Server 2005, so I clear the execution account in the SSRS configuration (Start > Program > SQL Server 2005 > Configuration tools > Reporting Services configuration. In the execution account part (the bottom one), clear the option 'specify an execution account'.

Hope this helps many people trying to solve the same error message.

Configuring reporting service for Dynamic Ax 2009

I follow the steps in Ax installation guide on how to install reporting service for Ax.
When I came on setting up application pool, I got this error

The identity of application pool 'AxReportServer' is invalid, so the World Wide Web Publishing Service can not create a worker process to serve the application pool. Therefore, the application pool has been disabled.
This is corrected by assigning Ax proxy account to local IIS_WPG user group.

Later I need to assign ax proxy account to ReportServer and ReportServerTempdb database.

Code that must not be put in data source active() method

Recently I got this error when trying to update spec quantity in salesEditLines form.

Cannot edit a record in Sales order - Update table (SalesParmTable).
The values displayed in the form are not current, so an update or deletion cannot be made. To view the current values, on the Command menu, click Restore or press CTRL+F5.

Call stack shows nothing but suddenly system goes to the write() method of the SalesParmTable. It is strange that I didn't change any field but it fires SalesParmTable.write() data source method.

Comparing the modification, I found that my fellow developer put a field assignment code within the SalesParmTable.active() method, so the code will always be run repeatedly (not sure how many time per minute, but it is very often). Since system get message that a field has been changed, write method is fired although the stack trace does not show any indication why write() method is fired. Again, it is hard since debugger doesn't tell what previous event causing this write() method.

So then I remove the field assignment code from the datasource active() method and the problem is gone.
The lesson from this is that we must never put an field assignment code within datasource active() method. It should only contains code for changing field behavior such as alowedit, enabled, visible, etc.

Find database given physical file name

I was looking for ways to clean up my server disk space. One of them by shrinking sql server log file. I found a log file that was quite big. Unfortunately, there was no database name that has similar name with the physical file. I would not check physical name of each database one by one by issuing sp_helpdb command as my server hold tens databases. I would rather run script to find exactly what database hold this file.
Here is what I get:
SELECT
DB_NAME(database_id) database_name,
physical_name
FROM master.sys.master_files
where physical_name ='physicalfilename'

Axapta view of view may cause wrong results

When create view in Axapta, an existing view should not be used as data source because it may produce wrong result.
This happen when the originating view has more than one data source in it.
When view is created, Axapta uses the following technique :
- Use the first data source's dataareaid field as the view dataareaid field
- Use the second data source's dataareaid field as the view dataareaid#2 field
When used as a whole, this view will only show the first dataareaid as its dataareaid. Axapta kernel will filter based on dataareaid and dataareaid#2 with the correct company account. This case no problem.

Problem occurs when the view is used in another view, with additonal datasource attached to it.
Using the same rule as above, the new view's dataareaid#2 field will be replaced by the new datasource attached to it, so the original view second data source is no longer filtered by dataareaid. This may lead to a duplicate data when the same data of the original view's second data source exists in more than one company.

Delete duplicate rows

Recently I need to build an Axapta instance where the application has been modified while I only have a standard demo database.
Problem arises as the modified application has new indexes or change the table properties 'SaveDataPerCompany' to No instead of default Yes. Both may cause duplicate record problem. I don't want to just delete the entire record yet I need to synchronize the table with Axapta application.

It will be no problem if the table contains less than 20 records, I can just delete it manually. The problem is that I find a table has hundreds of records. Then I write this script to do this job less tedious.

--drop table #mytemptable -- This is needed only if run more than once
select distinct
module,type,groupid --specify as many as key fields
into #mytemptable
from pricediscgroup --this is the table name

--set variable s correspond to the key fields
declare @module int
declare @type intdeclare @groupid varchar(20)
declare @numberofrows int
declare mycursor Cursor for
SELECT * from #mytemptable
open mycursor

fetch next from mycursor into @module,@type,@groupid

while @@fetch_status=0
begin

select @numberofrows = count(*) from pricediscgroup where
module = @module and type= @type and groupid = @groupid
select @numberofrows, @module,@type,@groupid
if @numberofrows > 1
begin
set @numberofrows = @numberofrows - 1 -- leave one row

set rowcount @numberofrows --the select within the subquery will return this number of rows,
delete from pricediscgroup where recid in (
select recid from pricediscgroup p2
where
p2.module = @module and p2.type= @type and p2.groupid = @groupid)

end

fetch next from mycursor into @module,@type,@groupid
end

close mycursor

deallocate mycursor

It does take time to change the table name and adding some variables depend on the duplicate fields found. But it takes less time rather than deleting many rows manually.

Role centers and enterprise portal on Ax 2009

During installation of role centers and enterprise portal I got this error:

Setup cannot connect to the Application Object Server instance (AOS) by using the Business Connector. Confirm that the AOS is running, and that your account (domain\username) is a valid Microsoft Dynamics AX user. See the log file C:\Documents and Settings\All Users\Application Data\Microsoft\Dynamics AX\Dynamics AX Setup Logs\2008-09-11 11-27-45\DynamicsSetupLog.txt for more information.

I have checked that the user already exists in Axapta. Then I tried changed the service account with the same user and it works.
The service account is in Administration > Setup > Security > System service account. Put the username and domain in the alias and network domain fields.

I post this with hope to save time just in case others get the same problem.

Trace Parser for Dynamic Ax

I am about to install Trace Parser to see how it can help solving performance issue. This tool can be downloaded here

Axapta : unable to load page error

Recently I got an error "unable to load page" when opening report. After googling I found this . The solution is to use newer client kernel.
However weird that I don't experience the problem using diferent pc as client, using old kernel.

Sharepoint service log files grow rapidly

Recently I noticed that I run out disk space in my virtual machine where I install Windows sharepoint services.
I see that the log folder grows very big in size. It is already 2 GB now.
The default log folder is located here :
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS\
To avoid it consumes space in C, I change it to different folder which is achieved through:
Sharepoint central administration > Operations > Diagnostic logging.
Then I also change timer job definitions by disabling them (maybe I will enable them once I know more about sharepoint).

Installing Dynamic Ax 2009

After some years working with Axapta 3 and 4, today I tried installing Dynamic Ax 2009.
The first impression with Ax 2009 is that the software contains more options than Ax 4, and the installation guide is more detail.

I download the installer here and the demo data
here. Seems weird because usually all are packed into one installation source (DVD).
Note that partner source login is required to download.

I run the installer and create a new blank database. After installation checklist finished, I restored the demo data (which comes as database backup) and point the AOS to the demo data instead of blank database created during installation.
When I started the AOS service I got this error in the event viewer :

The version of the stored procedures in this database is different than that expected by the Application Object Server (AOS). You must set up a new instance of the AOS that points to this database to update the version of the stored procedures


Seems that the demo data is created using AOS of earlier version from the latest download. Looking at the SQL Server profiler, I found that the table SQLSYSTEMVARIABLES is accessed during service start. Comparing two databases (one created during installation and one from demo data), contents of this table are different, and the one from installer is newer.
So I run this statement into the demo data database :

delete from sqlsystemvariables;
insert into sqlsystemvariables select * from DynamicAx5Blank.dbo.sqlsystemvariables;

Then restart AOS and it works.

The backup set holds a backup of a database other than the existing database

This error message appear when I tried to restore an SQL 2K SP4 db into SQL 2005, using SQL Server Management Studio.
Although I have specified overwrite existing database, the error message still appear.
So I run transact-sql statement instead :

restore filelistonly
from disk = 'D:\SQL Backup\sourceDB.bak'
GO
--This will show the logical file name needed in the operation below
restore database targetDB
from disk = 'D:\SQL Backup\sourceDB.bak'
WITH MOVE 'sourceDB_Data' TO 'D:\SQLDATA\targetDB_Data.MDF',
MOVE 'sourceDB_Log' TO 'D:\SQLDATA\targetDB_Log.ldf',
REPLACE,STATS
GO
-- The option with replace is mandatory as it will overwrite existing database. With stat will show the operation progress