3.4 Delete Actions

Delete Actions

Use of Cascade, Restricted and Cascade +Restricted (Delete action property for a table)

Example of Cascade

Suppose we have two tables  (Customer & Order) and the relation is of One-To-Many i.e. Customer can have many orders

So on a parent table i.e.(Customer) if I set a delete action property to “CASCADE” for Order table. Then If I go and delete the record from a Customer table. It will also delete all the related records in Order table automatically

Example of Restricted

Suppose we have two tables  (Customer & Order) and the relation is of One-To-Many i.e Customer can have many orders

So on a parent table i.e.(Customer) if I set a delete action property to “RESTRICTED” for Order table. Then If I go and delete the record from a Customer table. It will first check the record in the child table and if exist that warning prompt saying that first we need to delete a record from child table.

Example of Cascade + Restricted

Suppose we have three tables  (Person ,Customer & Order) Now Person is a parent of Customer table, and Customer is a parent of Order table having (One-To-Many) relations

If I set a Delete action property on Person table to “CASCADE” for customer table  and If I set a  Delete Action property on a CUSTOMER table for Order table to “CASCADE +RESTRICTED”.

So if I delete a record from Customer table then It will first check the record in the child table(order table) and if exist that warning prompt saying that first we need to delete a record from child table.

But if I delete a record from Person table it will automatically delete a record in Customer table and all records related to customer table in Order table would also be deleted.

3.2 Tables [AX 2012]

3.2.1 Get table properties through X+

SysDictField dict = new SysDictField(tableId, fieldId);
str label = dict.label();

3.2.2 Table Contents

Specifies how setup/parameter data can be reused from one customer to another. The following values are possible:

Not specified – For most tables.

Default Data – Use for customer-independent data such as ZIP/Postal Codes, units, and time intervals.

Base Data – Use for customer-dependent data such as calendars, groups, and parameters.

Default+Base data – Use for data where the local perception varies. For example, Chart of Accounts is not customer-dependent in Germany, but is most other places in the world.

3.2.3 Cache LookUp

None

No data is cached or retrieved from the cache for this table.
This property value should be used for tables that are heavily
updated or where it’s unacceptable to read outdated data.

NotInTTS

All successful caching key selects are cached.
When in a transaction (after ttsBegin), no caches made outside

the transaction are used. When inside a transaction, the record
is read once from database and subsequently from the cache.
The record is select-locked when read in a transaction, which
ensures that the record cached is not updated while the
transaction is active.

A typical example of the NotInTTS property is on theCustTable
in the Microsoft Dynamics AX application. It is acceptable to
read outdated data from the cache outside a transaction, but
when data is used for validation or creating references, it is
ensured that the data is real-time.


Found

All successful caching key selects are cached. All caching key
selects are returned from the cache if the record exists there. A select forUpdate in a transaction forces reading from the

database and replaces the record in the cache.
This is typically used for static (lookup) tables, such as Unit,
where the record usually exists.


FoundAndEmpty

All selects on caching keys are cached, including selects that
are not returning data.
All caching key selects are returned from caching if the record

exists there, or the record is marked as nonexistent in the cache. A select forUpdate in a transaction forces reading from the

database and replaces the record in the cache.
An example of FoundAndEmpty record caching is in the

Discount table in the Microsoft Dynamics AX standard application.
By default, the Discount table has no records. By using a FoundAndEmpty cache on this table, the keys that are queried
for but not found are stored in the cache. Subsequent queries for
these same non-existent records can be answered from the cache without a round trip to the database.


EntireTable

Creates a set-based cache on the server. The entire table is
cached
as soon as at least one record is selected from the table.

Table Group	Cache Lookup
Miscellaneous*	See notes below
Parameter	EntireTable
Group	Found
Main	Found
Transaction	NotInTTS
WorksheetHeader	NotInTTS
WorksheetLine	NotInTTS
Framework	N/A
Reference	Found
Worksheet	NotInTTS
TransactionHeader	NotInTTS
TransactionLine	NotInTTS

 * All newly created tables default to a table group of Miscellaneous. Ideally don’t use this table 
group for custom tables.

Finally, bear in mind that in AX 2012, the cache limit is configurable for every table group in the 
server performance settings:

System Administration > Setup > System > Server Configuration > Performance optimisation tab

Entire table cache size determines in kilobytes how much data is cached in memory before 
spilling to disk. The defaults are 32KB for AX 2012 RTM and 96KB for AX 2012 R2/R3.

3.Tables [AX 2012]

3.1 Table Properties

3.1.1 Table Type

Basically in AX2012 there are two types of Temporary tables:

InMemory tables	TempDB tables
Holds data temporarily in client or server tier	Holds data temporarily in database till the scope is valid
These tables can't be stored in Database	These tables are stored in the database
Can't apply security	Can apply security
We cannot use InMemory table buffers	TempDB table buffer can be used in coding

      Capabilities of TempDB Tables:

• Can be joined with other AX tables 
• Can be either per company or global. 
• Can be used from Enterprise Portal by using .NET Business Connector. 
• Can have foreign key columns. 
• TempDB tables can be instantiated from the client or server tier. 
• Can have indexes columns. 
• Can have methods, but cannot override. 
• Usa as a query 
• Transaction support. 
• No configuration key is required. 

      Limitations of TempDB Tables:

• Cannot be a valid time state table. 
• Cannot have any delete actions. 
• No Record Level Security (RLS). 
• Cannot use the Table browser form. 
• Cannot be in a table collection. 
• No view support. 

InMemory tables:

Instantiated in the active memory of which ever tier the process is running on, either the client or the server tier. InMemory tables are never represented in the database management system.

An InMemory table is held in memory until its size reaches 128 KB. The dataset is then written to a disk file on the server tier. The disk file for an InMemory table has the naming convention $tmp<nnnnnnnn>.$$$.

Scope: An InMemory table is instantiated when the first record is inserted. The instantiated InMemory table continues to exist only while a record buffer variable that references the table exists. The memory or disk space for the InMemory table is de-allocated as soon as the record buffer goes out of scope.

[Important Questions]
1. How can you check whether table is a tempDB table or InMemory?
Ans: By calling the isTmpDb and isTmp record buffer method -Returns a value of true orfalse depending on whether the table is temporary.
2. How to convert regular table into In memory table?
Ans: Using setTemp() funtion

2.Architecture [AX 2012]

Dynamics Ax 2012 is based on  Three tier Architecture consists of

1. Database tier.
2. Application tier
3. Presentation tier.

Database tier:

If you saw standard Dynamics Ax databases with default installation you found. Three databases

MicrosoftDynamicsAX:

This database contains the data used in Dynamics Ax. AOS used this database for transaction.

MicrosoftDynamicsAX_model:

Meta data for forms, tables, and all other code are stored in model_store exists in MicrosoftDynamicsAX_model database. It contains the all application element data inside Microsoft Dynamics Ax.

MicrosoftDynamicsAXBaseline:

This baseline database stores the model store that is used to upgrade X++ code to Microsoft Dynamics AX 2012. The Baseline database is used to analyze application updates before they are applied.

It stored the old model store before applying the upgrade.

There more databases are installed which has no direct link Microsoft Dynamics Ax 2012. These are

SSRS database, Sql server analysis server database and if you installed the Interpise portal then data bases required for Share point also include installed.

Application Tier.

In Dynamics Ax 2012 AOS, Reporting server extensions, enterprise portal, Workflow, Application Interface Framework all consider to be application tier,

AOS Application object server.

AOS is responsible for communication between Client and Database. Services also deployed on AOS so external application can communicate with AOS. Dynamics Ax 2012. Application business logic resides at AOS.

Enterprise portal.

Enterprise portal is asp.net and share point porta also consider to be at application tier. SSRS reports, dashboards and other websites are hosted on IIS.

Workflow. Also consider to be application tier. These allow to user to create and automate some workflow activities.

Reporting and analytical services.  Reporting using SSRS and Analytical services used in Dynamics Ax 2012 also part of application Tier.

Application integration framework.

Services and integration framework that is used to communicate external and internal system is also part of Application tier.

Presentation Tier.

Microsoft Dynamics Ax 2012 client, Enterprise portal and external application that communicate with Dynamics Ax all are consider to be Presentation tier.

1.Layers [AX 2012]

From left to right, these are the most common layers available in a standard Dynamics AX system:

SYS > GLS > FPK > SLN > ISV > VAR > CUS > USR

Each layer is completely independent of the other layers.  You specify which layer to connect to when setting up the AX client profile.

The bottom layer will be the SYS layer and this is where the standard application code is located, and is only accessible by Microsoft.  Other layers like ISV (Independent Software Vendor), VAR (Value Added Reseller) can be accessed by entering keys that Microsoft provides for each layer except USR (User) which does not require a key.

Each layer has a corresponding patch layer above it.
The patch layers are designed to make it easy to incorporate updates in your application. The basic idea is that when a minor update or correction is made, it is distributed in a patch file.

Layer	Patch Layer
USR	USP
CUS	CUP
VAR	VAP
ISV	ISP
SLN	SLP
FPK	FPP
GLS	GLP
SYS	SYP

[Important]

The top layer will always be used

For Example:

If you are modifying any table in VAR and USR Layer,
It will not matter if you keep adding columns to the table definition in the VAR layer as those new columns won’t be available to the end user, as the USR layer takes precedence.

Interview Question:
1. Different types of layers (Ans above)
2.If you manipulate anything which layer would it affect?
Ans: The present layer where you are working and below of that layer.