Today I was facing a strange problem. I use ledger entry for customer code which was freely available on different blog. You can find the same code here, which I used it two years ago.

http://tech.alirazazaidi.com/create-and-post-vendor-invoice-journals-x-dynamics-ax-2012/

This code will work perfectly fine. We create ledger entry for different customer and our customization is working fine. But when we create ledger entry for newly created customer. Ledger entry successfully created but customer account was not populated. More interestingly if we create or manually update same ledger entry for same customer account, the rest of customization works fines for same customer.

When I struck and no way out, I queried to NDA AX group, Only André Arnaud de Calavon

Replied with hint. Problem was that Customer account must exist in DimensionAttributeValueCombination.

When we create a ledger entry first time for customer, Ax create first customer entry in DimensionAttributeValueCombination table. Then go for Ledger entry.

After writing code, searched finally I found that following statement works for me.

DimensionStorage::getDynamicAccount(_CustTable.AccountNum, LedgerJournalACType::Cust);

 

This statement, search Ledger dimension, if found it will return otherwise will create and return.

Why I love Dynamics Ax, Because, ERP is so vast that every day new challenge and every day something new for learning.

{ 0 comments }

Dynamics AX 2012 R3 CU9 Update : Step-By-Step

by alirazazaidi on July 12, 2015

Download from partner source or customer source.

  • AXUpdateInstaller
  • DynamicsAX2012R3-KB3063879-SlipStreamOnly

Extract AXUpdateInstaller.

image001

Now also extract the silpstream  in same folder where AX 2012 AxUpdateInstaller extracted.

Extractioned folder

 

If you did not download slipstream, then during setup it will ask and download it at run time.

Now before running setup perform following steps to

Environment preparation:

  1. Backup business and model store databases. Backup the database that is being updated.
  2. Make sure that you are an Admin on local computer and System Administrator in Dynamics AX.
  3. Make sure that you are a member of “SecurityAdmin” server role on the SQL Server instance.
  4. Make sure that you are a “db_owner” role in model database.
  5. Make sure the system runs in single-user mode while installing this update (or down-time).

 

 

From extracted folder Click on axupdate.exe with administrator rights.

2015-07-12_11-49-57

 

Click on next

Next

 

 

 

 

Click on Accept and continue.

2015-07-12_11-52-56

It is my testing own Virtual machine, So select Model store According to your environment.

 

Click on it and it will take time, not more then 5 to 10 minutes.

 

2015-07-12_12-03-39

Click on next button

 

2015-07-12_12-50-39

Select the component you want to update , for current article I select all.

 

Let it install it.

2015-07-12_12-56-37

After that following screen appears describe the successful update

Installation

2015-07-12_15-33-52

 

 

Post Installation:

sss

 

Perform initialization steps

ss

 

 

 

{ 0 comments }

 

Consider a scenario that we are listing  sale line for  customers on RDP based report. Data is huge on server and report time out occur.

In Dynamics Ax 2012 R3 provide new class “SrsReportDataProviderPreProcessTempDB”  use this class instead of SRSReportDataProviderBase

For base for this report we please follow below link

http://tech.alirazazaidi.com/rdp-or-business-logic-based-ssrs-reports-in-dynamics-ax-2012-r3/

 

If you report is already developed you have to require three changes

Change 1:  RDP report always return table of type TempDB, If not then change it.

 

TempDbChange 2-Extend the class SrsReportDataProviderPreProcessTempDB instead of SRSReportDataProviderBase.

[

 

SRSReportParameterAttribute(classstr(CustomerSalesDataContract))

]

 

class CustomerSalesDataProvider extends  SrsReportDataProviderPreProcessTempDB

{

CustomerSaleTemp _CustomerSaleTemp;

}

 

Change 3.  Set report connection to temp data table.

The temp table connection string statement will be look like

 

  _CustomerSaleTemp.setConnection(this.parmUserConnection());

 

Rest of the logic remains same on report.

{ 0 comments }

RDP or Business logic based SSRS Reports in Dynamics Ax 2012 R3.

 

Logic based report in MS Dynamics Ax 2012 can be develop in following steps

 

  1. Create a temporary table
  2. Define the report parameters
  3. Add business logic for the report
  4. Create a reporting project
  5. Bind a report to a report data provider class

 

In graphical shape RDP reports will be as

Code Based Report

Image Inspiration http://dynamics-ax.blogspot.com/2011/12/ax-2012-ax-ssrs-report-design-concepts.html

 

 

Now consider a scenario, where we have to display list of Item, quantity, Price and total amount sold to customers. It is relatively simple report but have to build this report based on RDP or Report Data Provider framework.

 

First step to open an Ax client. When Ax client open press Ctrl + shift +W keys to open Dev environment or AOT.

 

For all artifacts for report development will be a single place and we did not move to node to node in AOT we have to create a AX project.

 

You can find projects at View=>Projects => Public project.

Create a new project at and rename it with “CustomRDBReport”

ss

 

 

 

Step 1 create a temp table.

The major step in RDP report is decision the fields require in report, create a temp table and add these field in temp table. For current example what fields we required on report are as follow

 

  • CustomerAccount
  • CustomerName
  • ItemId
  • ItemName
  • SalesPrice
  • SalesQuantity
  • SalesAmount

 

If we see these fields exists in SalesLine Table. So we drag them into our temp table, and rename them accordingly

Right click on project and create at table with Name “CustomerSalesTemp”,

TableCreation

 

From property window rename the table as “CustomerSalesTemp” and set  TableType to   tempDb

TempDb

 

Now close all window, open AOT and opens salesLine table. From top menu click on windows => Tile and both tables comes in parallel to each other

Tiltle

and start drag and drop fields in temp table

Drag fields

Now save the table and rename the fields accordingly if required. Also add a new field with Name CustomerName with extended data Type with Name.

 

 

Right click compile and synchronize table.

 

 

 

 

Now create a AOT Query with Name QSalesLine. Add data source on SalesLine and Add following fields on Salesline table

QSalesLine

 

For Date Fileter, we will use ShippingDateConfirmed on Date.

Safe this query.

 

Step 2 define the report parameters

For current report we required three parameters, Customer, From date and To date.

In Report Data Provider framework which is based on WCF, we have to create a data contract class.

Create a new class in, rename it, CustomerSalesDataContract.

In its declaration section create three variables

 

[DataContractAttribute]

class CustomerSalesDataContract

{

CustAccount CustomerAccount;

TransDate FromDate;

TransDate ToDate;

}

 

 

 

Now Create three data method

[

DataMemberAttribute(identifierStr(CustAccount)),

SysOperationLabelAttribute (“Customer Account”),

SysOperationHelpTextAttribute(“Customer Account”),

SysOperationDisplayOrderAttribute(“1″)

]

public CustAccount  parmCustomerAccount(CustAccount  _CustomerAccount = CustomerAccount)

{

CustomerAccount = _CustomerAccount;

return CustomerAccount;

}

 

 

[

DataMemberAttribute(identifierStr(FromDate)),

SysOperationLabelAttribute (“From Date”),

SysOperationHelpTextAttribute(“FromDate”),

SysOperationDisplayOrderAttribute(“2″)

]

public TransDate  parmFromDate(TransDate  _FromDate = FromDate)

{

FromDate = _FromDate;

return FromDate;

}

 

 

[

DataMemberAttribute(identifierStr(ToDate)),

SysOperationLabelAttribute (“To Date”),

SysOperationHelpTextAttribute(“To Date”),

SysOperationDisplayOrderAttribute(“3″)

]

public TransDate  parmToDate(TransDate  _ToDate = ToDate)

{

ToDate = _ToDate;

return ToDate;

}

 

 

 

 

Step 3 Add business logic for the report.

In Report data provider framework we have to write Data provider classes, which contain business logic to populate temp table. For this we have to add create a new class “CustomerSalesDataProvider”

Extend this class SRSReportDataProviderBase

 

 

[

 

SRSReportParameterAttribute(classstr(CustomerSalesDataContract))

]

 

class CustomerSalesDataProvider extends  SRSReportDataProviderBase

{

CustomerSaleTemp _CustomerSaleTemp;

}

 

 

This Class two method, first is return the temp table, and second one is which contains the logic to populate  temp table.

 

[SRSReportDataSetAttribute(“CustomerSaleTemp”)]

public CustomerSaleTemp getCustomerSaleTemp()

{

select * from _CustomerSaleTemp;

return _CustomerSaleTemp;

}

 

 

 

public void processReport()

{

TransDate _FromDate;

TransDate _Todate;

AccountNum _CustAccount;

CustomerSalesDataContract dataContract;

 

Query query;

QueryRun queryRun;

QueryBuildDataSource queryBuildDataSource;

QueryBuildRange queryBuildRange;

QueryBuildRange ShippingDateConfirmedFilter;

SalesLine querySalesLine;

 

 

query = new Query(queryStr(“QSaleLine”));

dataContract = this.parmDataContract();

_CustAccount = dataContract.parmCustomerAccount();

_FromDate = dataContract.parmFromDate();

_Todate= dataContract.parmToDate();

 

queryBuildDataSource = query.dataSourceTable(tablenum(SalesLine));

if (_CustAccount)

{

queryBuildRange = queryBuildDataSource.findRange(fieldnum(SalesLine, CustAccount));

if (!queryBuildRange)

{

queryBuildRange = queryBuildDataSource.addRange(fieldnum(SalesLine, CustAccount));

}

}

ShippingDateConfirmedFilter = SysQuery::findOrCreateRange(query.datasourceTable(tableNum(SalesLine)),fieldNum(SalesLine,ShippingDateConfirmed));

ShippingDateConfirmedFilter.value(SysQuery::range(_FromDate,_Todate));

 

queryRun = new QueryRun(query); ttsbegin;

while(queryRun.next())

{ _CustomerSaleTemp.clear();

querySalesLine = queryRun.get(tablenum(SalesLine));

_CustomerSaleTemp.SalesPrice =  querySalesLine.SalesPrice;

_CustomerSaleTemp.ItemId =  querySalesLine.ItemId;

_CustomerSaleTemp.ItemDescription =  querySalesLine.Name;

_CustomerSaleTemp.SalesQty =  querySalesLine.QtyOrdered;

_CustomerSaleTemp.CustAccount =  querySalesLine.CustAccount;

_CustomerSaleTemp.CustomerName = CustTable::find(querySalesLine.CustAccount).name();

_CustomerSaleTemp.insert();

 

 

 

 

}

ttscommit;

 

}

 

 

 

 

 

Now compile the class, generate Incremental CIL.

Step 4 create a reporting project

Now open Visual studio and create Model project say “CustomSalesLineReport”.

VisualStudio

From solution explorer, create a new report rename it RDPSalesLineReport

RDPSalesLineReport

 

 

Step 5 Bind a report to a report data provider class

 

Now double click on report and open it in

ExpandDataSet

 

 

Add new DataSet and rename it “DSSalesLine”. On right click and from property window set Data Source Type to “Report Data Provider”

 

RDPSettings

 

And click on Query and from browser window select The data provider class we created in previous step

CustomerSalesDataProvider

 

Click ok to create fields

FieldsDetails

 

Now drag and drop data set to Design node in report to create AutoDesign.  Rename it “RDPSalesLine”

 

Drag and drop

Expand “RDPSalesLine” design and drag and drop CustAccount field from Data Set to Group and sort nodes

SortAndGroup

 

Expand parameter of report and open the property of CustAccount parameter and set its allow blank to true and nullable to true, so if no customer is selected, report will run for all customer in legal entity

CustAccountSales

Save the report compile it, deploy it and add to AOT

 

Now switch back to AOT.  Create a new menu Item under Display node.

Mnu

 

And set menu item Name as “mnuRDPSaleLine” and set its properties as follow

MnuSettings

 

Save it and right click on menu item and open it

 

Report Dialog

 

Set values for From Date and To date and run the report, Report will work with business logic as follow

 

Sales

 

 

{ 0 comments }

Today I got this strange error “An item with the same key has already been added “, Very interestingly I got no error during compilation or deployment of report

when I run the report I got this error

An item with the same key has already been added.

 

On investigate I found this error is due to meta data of report. Report is based on Query. On Exploring Query I found that I added the same field two times, At report run time I got error due to duplication of same field. If you see the below screen shoot you  will found that “CustGroup” appears two times in Query, I removed the Duplicate field. compile the Query, Refresh the report data source, compile, deploy and add to AOT. Report run successfully.

 

AllFields

{ 0 comments }

In Microsoft Dynamics Ax 2012, we can create less complex reports with AOT Query or Static Query.

Simple or model based report is as follow.

QueryBased Report

http://dynamics-ax.blogspot.com/2011/12/ax-2012-ax-ssrs-report-design-concepts.html

 

The steps are as follow  We can create simple or model based report with following steps

  • Step 1: Create a Microsoft Dynamics AX query
  • Step 2: Create a new report in Visual Studio
  • Step 3: Apply Layout template
  • Step 4: Add Column sorting
  • Step 5: Group report data
  • Step 6: Filter report data
  • Step 7: Add a dynamic parameter
  • Step 8: Save and deploy the report
  • Step 9: View the report in Microsoft Dynamics AX

 

We can see these steps below

 

Now consider a scenario, where we have to display sales item detail with respect to its customer. As this report is not much complex and on exploring Dynamics Ax 2012 default tables we found that Sales order detail at Item level can be found on “SalesLine” table. Complexity is less so we decide to create this report with Static Query or AOT query based Report.

This Article is based on Contoso demo data, and Dynamics Ax 2012 R3 on demo licences

 

 

Open MS Dynamics Ax 2012 Client And press Ctrl+Shift+W to open dev environment or open the AOT.

When AOT open expand it and at AOT node add new query

CreatingAOTQuery

Rename Query to simple “QSalesLine”.

Expand its data Source Node, right click and add new data Source

Add DataSource

Rename the Data Source to SalesLine and select SalesLine table

SalesLineDataSource

Now expand the field Node and set its dynamic property to no

DynamicPropertyToNo

Right Click on Field and add new field and select salesId

 

SelectField

 

SalesId

Similarly you can add following fields

  • CustAccount
  • SalesId
  • OrderQty
  • SalePrice
  • LineAmount
  • ItemId,
  • Name,
  • CustGroup

QSales

 

Now save the Query.

 

Open Visual studio and create a new report project

ModelReport

 

 

Open Visual studio environment and create a new Report and Rename It to SalesLineReport

 

From solution Explorer add new report

SolutionExplorer

Now expand report and right click on data Source to create a new data source Rename It to DSSaleLine

SalesLineDS

 

Right click on “DSSaleLine” it Edit it or click on properties and open property window

propertyWindow

From Property window select

 

Click on Query and from Brower window select Required Query

 

Click on next window

SelectionOfQSales

 

Select all fields and click on save

FieldSelection

 

 

ReportDetails

 

Now drag the data set and drop on designs node, It will create auto Design

AutoDesign

Right click on report designer and do the following

Rename AutoDesign to as “SalesLineDesign”

Select ReportLayoutTemple to ReportLayoutTemplate

Title to “CustomSalesLineReport”

and click on save.

ReportTitle

Now expand “SalesLineDesign” design and drop and drop CustAccount in Group and Sort node. This will create the records group and line according to CustAccount

SalesLineReport

 

Now Compile the report, Deploy it and At to AOT.

 

 

Now open the AOT. Change the legal entity to “USMF” expand the menuItem node, and create a new display menu item

NewMenuItem

 

 

 

Change its Name to mnuCustomSalesLineReport

Change object Type to SSRS report and select the object is SalesLinereport and Report Design to SalesLineDesign

MenuSalesLine

 

 

Now save the menu and let’s run the report

ReportDailog

Click on ok to run the preview

Reports

Adding Dynamic Parameter

Create another query in AOT name it QCustomer, and add AccountNum is in field. You have to follow the same step which we used to create QSalesLine Query. Save the Query

DynamicQuery

Now switch back to model project in Visual studio and add new Dataset and Name it DSCustomer and pointed to QCustomer query the same way we select the Query.

DsCustomer

Now expand parameter node of report

And add new Parameter with Name “CustomerParm”

Expand Values and set

Customer

DynamicParameterparamet

Now expand Report designer and under filter node add new node

AccountFilter

Right Click on AccountFilter and from property window

Click on expression  To select = Fields!CustAccount

Operator select “Like”

And Value select =Parameters!CustomerParm.Value

FilterProperties

 

 

 

 

 

Save the report and deploy it, and then Add to AOT.

 

Open Aot and from item display menu to run report

 

You will found a new drop down for parameter in

CustomerSelection

 

 

Form this parameter You can select and run the report for specific customer

CustomerSelectionReport

{ 0 comments }

Here is very interesting development, Now Microsoft offer, Demo Dynamics Ax 2012 on azure can be deployed, test, learn and train. Previously only partner source and customer source can only login at lcs.dynamics.com. Where they can deploy preconfigured Dynamics Ax 2012 on Azure. From 29-Jun 2015, Microsoft allow very one with live / Hotmail / outlook account can login in lcs.dynamics.com. Accept agreement and deploy, learn, test, explore and enjoy world’s leading ERP.  Idea of special demo laptop gone, enjoy world’s leading ERP on Azure.  Now everybody is welcome to Dynamics AX.

Lcs

{ 0 comments }

Now we start development of form. First form we will build for Simple list form for Diagnostic table

For this open AOT and right click on Form Node and click on simple list form template

SimpleListSelection

 

Drag newly created form in Project and rename it “DiagonasisticTable”.

DiagonasisticTableForm

Expand DiagonsisticTable form and Right click on data source node to new Data source. Update name of name and Table name as DiagonsisticTable.

DiagnonsticDataSource

Now expand Designs=> Design Expand=> group Container and select grid

 

Grid

Right click on grid, click on properties window and set these properties

Data Source: DiagonasisticTable

DataGroup: overView

GridProperties

 

Save the form and right click on form open it. The form will look like similar

NewDiagonaistic Form

 

If you try to enter duplicate values in Diagnostic number field. Due to unique index on Diagnostic number it did not let you do it.

DiagonistiIndexWorking

 

 

 

 

New create a display menu with Name “MnuDiagonisticTable” and set it object Type to form and object Name as “DiagonisticTable”.  Save it.

diaganostic

 

Now expand Diagonaistictable right click on it and set its “FormRef” property with mnuDiagonisticsTable

DiaganosticViewMnu

This property provides us option of “View Details” on lookup field. We see its function in Master detail form.

 

Detail form:

 

Now we create a detail from for patient table. Create a new form and add data source and set its Name and DataSource value to Patienttable.

patientTableForm

Right click on PatientTable data source and set “InsertIfEmpty” to false

AdmissionSource

 

Now expand Designs and add new action tab, inside button group add three command buttons.

Rename them into “btnNew”, “btnEdit” and “btnDelete”. And from property window set their command to new, edit and delete.

NewButton

 

You can also select button Image by update following Properties

 

New Img property

Now expand Designs=>Design under ActionTab add a new Tab. Set Tab style property to fast tab.

Add new tab page in Tab and set is width and height value as follow

 

Tabs

 

 

Add new group control set its width and height like above one and column property to 2.

And set is data source to patientTable and Data group property to overview

PatientGroupProperties

Now form structure will look like

PatientDetailsStructure

Now open the form let’s see how it look like

PatientDetailsgroup

Test the form and click add remove entries

PatientFormWithData

 

Parent child form:

 

Now we expand patientTable form to Parent child form.

Now expand DataSource of node of PatientTable form and add a new data source. And set its table property to AdmissionTable, Name to AdmissionTable, and Set its JoinSource to PatientTable

 

AdmissionSource

 

 

PasteActionTabl

 

 

Now expand the form designer and add a new tab Page under patient tab and add new action pane There Set DataSource Property of ActionTab to AdmissionTable so all control inside this action tab works for AdmissionTable. In Button group add three command buttons and set their name “BtnNewAdmission”, “BtnEditAdmission” and “BtnDeleteAdmission”. After this set  there command property  to “New”,”Edit” and “Delete”.

AdmissionButton

Now add a grid in this tab and set its data Source to AdmissionTable.

AdmissionGrid

 

 

Drag and drop fields from AdmissionTable data Source to grid

AdmissionGridFields

 

Now open the form lets how it look like

Our parent Child from

You can see that here admission Date is greater than Discharge Date, for this we have to add some validation rules, which are not part of current post. We let it go.

AdmissionTableGridWorks

If you right click on diagnostic number field in grid, from pop up menu view detail option leads us Diagnostic table form.

ViewDetail link

 

This option comes due to we add menu Item in table properties in one of above step I am again adding its picture here

DiaganosticViewMnu

 

Now we create a new Display Menu Item “MenuPatientTable” and set its object property to

PatientMenu

 

Now we create a List page for Patient and attached Patient detail form with it.

 

For list page template we cannot attach table in data source for this we can create a Static Query Object. This Query Object will use as data Source in Form

 

First we have to create a Query. Re Name it to PatientQuery and form its DataSource add Patient table

PatientQuery

 

Expand PatientTable source in data table and right click on fields and set its field property to yes

QueryFieldDynamicsYes

 

After saving you will find all fields in Query

QueryFieldDetails

 

 

Now open AOT and right click on Form Node. From pop up menu select form template and then select list page.

ListPage

 

Now search form with similar CopyOfSysBPStyle_ListPage Name.  Drag it to your project and rename it to PatientListPage

PatientListPage

Now expand Patient List page and set data source and sets property to PatientQuery. This query we created in previous step

PatientListPageQueryDataSource

 

This will result in all tables in query in forms data source. By default these table have create and edit to no.

PatientListPageTablesList

Expand Designer  node of Form and select grid and set its dataSource property to Patient and drag and drop required fields from PatientTable_1 data Source to form.

 

Patient list Page grid

 

Now drag and drop required fields from data source to grid

PatientListPageGridFields

 

PatientListPageWorking

 

Now we are going to attached PatientDetail form with Patient

Now we add the Add, edit, delete and view functionality which leads the PatientDetail form

Expand the action tab in form and right click on menuItemButton  “newbutton”

PatientListPageAddNew

Set following properties

 

PatientAddNewButton

 

Expand next button group “MaintainedGroup” and and Open property window of EditButton

 

PatientlistPageEditButton

From Property window set following fields

DataSource = “PatientTable_1”

OpenMode =”Edit”

CopyCallerQuery=”Yes”

MenuItemName =”mnupatientTable”

PatientListPageEditButtonProperty

 

Now select mnuItemButton “ViewButton”

patientListPageViewButton

 

From Property window and set

DataSource = “PatientTable_1”

OpenMode =”View”

CopyCallerQuery=”Yes”

MenuItemName =”mnupatientTable”

 

PatientListPageViewButtonProperties

 

 

 

 

 

Now expand grid in form and set following property to MenuItemButton “ViewButton”, which we set in above step, so when we click on grid, detail form opens

patientListPageGridProperyToOpenListViewPage

 

 

Now run the form and lets try all functions.

 

Now create a new Display menu Item Say “MnuPatientListPage”. Set its form Property as follow

Menufor ListPage

 

Suppose That is hospital registration development is part of Accounts Payable module.

MnuList

Expand the menu Accounts Payable module expand common.

Add a new menu Item or drag drop existing menu item Common Set its following properties

Menufor ListPage

 

Now Open Ax client and open Account Payable area page

AccountsPayableAreaPage

 

PatientListPageINClient

 

{ 0 comments }

Consider a scenario where we are going to build small Hospital Admission System in Dynamics Ax 2012.  This Add-on is based on small database design based on

 

http://dhdurso.org/articles/ms-access-database-ad.html

 

db

 

 

We divide this task into two posts

For this post, we are cover following points to develop this small add-on

  • Ax Project
  • Patient, Diagnostic and admission table.
  • Simple list page for diagnostic table.
  • Entry form for Patient table (Detail form).
  • Parent Master detail form based on Patient ,Diagnostic table and patient.
  • List page for Patient table.
  • Integrate Patient List page with Patient Detail form.

 

 

 

AX Project:

 

AX Project for collecting Ax artifacts in single location.

When we create any Ax object they have to create under certain node. For example tables, forms, Classes, menu all stored in their respected node.

In Dynamics Ax we can create a Project which is not more the logical grouping of all Ax artifacts.

In these project, we create object groups and then placed our required object either drag and drop from AOT or create them here.

Advantages of Projects are

  • Logical grouping, it’s easier to locate required artifacts one location.
  • When we export project as Xpo, All objects are exported as single XPO.
  • We can export whole project into model, all object in project moved from one model to other model in single steps.

 

 

Lets make a new project

 

From top menu Click on View=> tools=> Project

Ax Project

Following Screen will open

Shared and Private Project

Right click on Shared folder and create a Project and renamed it to HospitalManagementSystem.

 

Hospital Management System Menu

 

New Project Managment

 

Click on Project and open it. Right click on it new => Group to create new groups

 

Details

For example we create Form group and set it Name and Project Group type to forms

Project name

 

Now Project is look like

Project Details

 

Similarly create other groups.

menu

 

 

Table structure design.

 

 

db

So our table structure look like

Diagnostic table

Column Extended data Type Primitive Data Type
DiagNo DiagnosticNumber Str10
Desc Description
Cost AmountCur Real

 

 

Patient table

Column Extended Data Type
PatNo PatientNumber Str 20
Fname Name
LName Name
BirthDate BirthDate
Address T_Address Str 20
City T_City Str20
State T_State Str20
Zip T_Zip Str20
Gender T_Gender

 

 

AdmissionsTable

 

Column Extended Data Type Primitive Data Type Foreign Key
AdmitNo AdmissionNumber Str20
PatNO Patient Number Patient table
Diag_code Diagnosticnumber

 

Diagnostic table

 

Adminationdate FromDate date
DischargeDate ToDate date
CoPay Notes Notes

 

First we create a extended data type required in three tables.

First one is DiagonisticNumber extended Data Type

Extended Data Type

Rename it and set its name as “DiagnosticNumber” and its string set its size to 10

ExtendedDataTypeSize

Similarly create all extended data type

List of Extended Data Type

Rest of we use out of the box extended Data Type

When you save any Extended Data Type, Ax ask for Synchronize database cancel it when all extended data Types created let it complete

DataBase Schyrni

 

 

 

 

 

In current example only gender is enum type, we create new base enum T_Gender instead of using out of box Gender enum.

 

Base enum menu

Create a new Enum and name it “T_Gender” t stands for training.

 

Right click on it and create a new element

Base Enum new Element

 

 

Set its name and label

 

SubType

Base Create one more element and it will look like

Base enum look like

 

 

Now create a click on tables group and right click and create a new table

 

 

Rename it to “DiagnosticTable”.

TableProperties

 

Now create a new field with Name “DiagNo” of string type and set its extended data type to “DiagnosticNumber”

 

FieldName

eeee

 

Similarly create all other fields in table

Diagnonstic

 

Now expand field Groups node in Table and Create a new Field group with Name “OverView”.

Overview

Drag all fields in it.

DiagnonsticDetial

 

Now want to make the Diagonistic and unique and make it primary key. For this right click and create unique index on it

DiagnonsticIndex

Rename it to DiagNoIx and set its AllowDuplicate Property to No and Alternate key to Yes

IndexDetails

Now drag DiagNo from fields node to DiagosticIdx.

Diagonisticss

 

 

Now right click on DiagnosticTable and set following Properties with newly created Index

 

DiagnonsticProperties

 

Perform same steps to create PaitentTable, create Index on “PatNo” similar way.

PatientTab

 

Create a new field group with overview and arrange field in a way that you want to see them in form

FieldsDetails

 

Create unique index on PatientNumber

PatientDetails

Similarly create AdmissionTable,

AdmissionTable

 

 

Now right click on Relation node in AdmissionTable and create a new relation and rename it to “PatientRelation” and sets its table property to “PatientTable”

PatientRelationDetails

Now Right click on relation click on new =>ForeignKey=>Single field AlternateKey based

AlterNativeField

 

 

A new field added against  relation, rename it to PatientNumber

PatientKey

 

Similarly create another key based DiagnosticTable

AdmissionTableWithRelation

 

 

Save and compile all table and synchronize each table.

 

Form development will be on next post

{ 0 comments }

Today I got very this error on deployment of customized SSRS report. The customization code was written in Dynamics AX 2012 R2 and it was successfully migrated to Dynamics Ax 2012 R3

 

System.Web.Services.Protocols.SoapException: Error while loading code module: ‘Microsoft.Dynamics.Framework.Metadata.AX, Version=6.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’. Details: Could not load file or assembly ‘Microsoft.Dynamics.Framework.Metadata.AX, Version=6.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35′ or one of its dependencies. The system cannot find the file specified.

at Microsoft.ReportingServices.Library.ReportingService2005Impl.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, Guid batchId, Warning[]& Warnings)

at Microsoft.ReportingServices.WebServer.ReportingService2005.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, Warning[]& Warnings)

 

ReportError

Solution is open the report In Visual studio, compile it, deployed it and add to AOT.

{ 0 comments }