2012 Table Relationships

Table relations have undergone a drastic makeover in 2012. Let's take a peek.

Surrogate keys, Natural keys and Foreign key relations are the new concepts in 2012 tables. EDTs no longer support relations defined on them and Microsoft wants developers to avoid using Field fixed and Related field fixed relations and instead use Foreign key relations.

What's a Surrogate Key?

Surrogate key is

• A single column index on a table that uniquely identifies each record
• Also referred to as the Primary key, RecId index or the PrimaryIndex
• No business meaning
• System generated in Microsoft Dynamics AX 2012

Note: On new tables, the PrimaryIndex property will be set to SurrogateKey by default. Existing tables will NOT automatically have their PrimaryIndex property set to SurrogateKey.

And Natural Key?

• Defines a unique index that can be used instead of the SurrogateKey on lookup forms
• Optional Property
• User-friendly index with business meaning
• Also known as Alternate Key

Index properties
On table indexes, there is a new AlternateKey property. When set to Yes, this property allows for an index to be specified in the PrimaryIndex and NaturalKey properties on a table. The AlternateKey property can only be set to Yes on indexes with the AllowDuplicates property set to No since both the PrimaryIndex and NaturalKey properties require indexes that are unique.

Additionally on table indexes, a property called IncludedColumn has been added. A field with the IncludedColumn property set to Yes is added to a non-clustered index to improve the performance of a query by covering all of the fields that are referenced in a query using this index including the key and non-key fields. To set the IncludedColumn property to Yes, more than one field must exist on the index.

Relation properties

On a relation there is a property RelationshipType which can take 2 values. Composition and Association

In a Composition relationship, the parent table OWNS the child table. No records can be created in the child table without having a corresponding header in the parent table. For example, a sales line cannot exist without a sales header. A department can exist without an employee and an employee can be deleted without deleting the department, this relationship is an Association.

Foreign Keys: Parent/Child tables

Child table is one that has a foreign key column. Parent table is one that supplies the value for the foreign key column. Normal, Field fixed and Related field fixed table relations should be avoided going forward.

Try yourself.

Drag SalesId EDT on to your table fields. This will prompt you to add foreign key relation. SalesId field, SalesTable relation and SalesTableIdx index will be created for you.

Virtual Convergence

Microsoft Convergence 2012 is over. For all those like me, who couldn't attend, Microsoft is making available Virtual Convergence, which includes online keynotes and general sessions.

Non-attendees will need to signup on a simple form and you are good to go.

I especially enjoyed the opening song just before Kirill Tatarinov’s keynote, check it out.

Enjoy! After all, its a World of Opportunity ;)

Android Time

Some new flavour today. Checkout my Android App published on the Android Market. Something I do at my leisure apart from blogging. Try out and give feedback! :)

https://play.google.com/store/apps/details?id=com.amannain.android.missedcalls

SalesTableForm Class

Customizing a Sales Order is one of the most frequent tasks in any Dynamics AX Implementation.
Form SalesTable is anyways a heavily customized form and writing any direct code on it (or for that matter on any form) is not advised.

So what could you do? Well that depends on what you want to do? :)

Writing code on a table or a class is what is best practice. In case of Sales Order form, code could be written on either SalesTable/SalesLine tables or SalesTableType/SalesLineType classes. But then there is only so much you can achieve writing code in the above two places.

In a recent interaction with Mr Lars Jacobsen from Norway, I discovered another way.
There is another class called SalesTableForm Class, which is recommended for writing code that affects your Sales Order form, header as well as lines.
For eg, if you need to dynamically create a new sales line when you satisfy some condition, you can write your code in the method SalesLine_write(). Do explore the class to figure it out more.

XPO Import Error

Recently I spotted a small error with Facility management Sample for AX 2012 so sharing that with my readers.
When you import the Facility_Management.xpo, you get an error like below.

As it is self-evident, some query is having a wrong version number. On a closer look, all the queries in the xpo have a version number of 31, whereas standard AX designates 29.
I changed the number from 31 to 29 and reimported. As expected, the import was successful.

.auc file

Sometimes users receive error like below, which says

User 'UserName' is not authorized to insert a record in table 'TableName'. Request denied.
Cannot create a record in TableLabel (TableName). Access Denied: You do not have sufficient authorization to modify data in database.

Clearing the user's usage data won't help. What i tried (and worked :)) was to delete the user’s .auc file, which solved the problem.
Search for .auc file at Location : “C:\Documents and Settings\%USERNAME%\Local Settings\Application Data”

The purpose of the object caching file (.auc) is to store a local copy of the AOT objects which have been used by the client, so that in the future they do not need to be re-read from the AOS.

We can also change the table’s (EmplTable, in our case) properties like AosAuthorization and MaxAccessMode, which can solve the issue. But the issue was in production environment and I needed a quick fix.

Links: http://blogs.msdn.com/b/emeadaxsupport/archive/2009/05/07/user-username-is-not-authorized-to-insert-a-record-in-table-tablename-request-denied.aspx

http://www.axaptapedia.com/index.php/Object_caching

Hope it is useful.

Tackling dependent tasks in a Batch job

I am sure most of you must be familiar with Batch jobs. If not created one, you must have at least seen some in action. A simple batch job consists of a single task with no dependencies. On the other hand, a complex batch job can have multiple tasks having dependencies with each other.

Basic info on how to create and run batch jobs can be found here.
http://msdn.microsoft.com/en-us/library/cc636647.aspx

In my post, I will demonstrate how to define dependencies between the tasks. This has application when you want to have complex dependency hierarchies, allowing you to schedule tasks in parallel, and choose multiple execution paths etc.

In short, I will create 2 tasks where second task will be dependent on the first task. Let's go step by step.

1. I am using the Tutorial_RunBaseBatch class. Just open/run the class, check the Batch processing checkbox and click OK. This will create a new batch job named Tutorial with one task.

2. Open the Batch job form. Select the newly created batch job named Tutorial. Click on View tasks button. You can see the only task available named Tutorial. Rename it to TutorialTask1 so that it makes more sense. Also, I kept a copy of Tutorial_RunBaseBatch class named CopyOfTutorial_RunBaseBatch. (I won't rename it as I am a little lazy. :P) Since each task represents a class, I will create a second task named TutorialTask2 running on CopyOfTutorial_RunBaseBatch class. Press Ctrl+N to create TutorialTask2.

3. Now select TutorialTask2, click the Has Conditions grid in the lower section of the Batch Tasks form and press Ctrl+N to create a new condition.

4. Select the task ID of the parent task, in our case TutorialTask1.
5. Select the status the parent task must have before the child/dependent task can run. In our case, TutorialTask2 starts when TutorialTask1 becomes 'Ended'. Save the condition.

It works more or less like a workflow. :)