Thursday, July 21, 2011

AX 2012 RecIds and UnitOfWork Foreign-Key Magic

AX 2012 features the new UnitOfWork class. One of the reasons this class exists, is to handle new issues that have come up because of one major shift in thinking in AX.

We now should try to link records together based on RecIds.

I can hear you scream WHAT all the way to Denver. Yes, you heard me right, it's now considered GOOD practice to link records together based on RecIds. Most developers I know that have any kind of experience with AX hate tables that link on RecId (although in some cases you have to), and usually consider it bad practice.

Well, get with the program! :-) It is now considered a good practice. Key reasons are performance and avoiding data duplication. Lots of tables in AX use(d) long strings as primary keys, and in some cases you need to start linking on more than one field to keep linking unique, etc. Well, since RecId is always unique within a table, and it's "just" a number, the duplication of multiple field values, and the performance on long string primary keys is now "solved".
Yes, I agree, this will not be pretty when doing data migrations and company copies (which for all intents and purposes are near impossible in 2012 - more on that later). But I'm sure somehow we will all get over our preconceptions and embrace this new paradigm (I'm using it, but I'm not a big fan just yet).

In any case, it is what it is. There are some new things around this RecId linking, to facilitate both developer as well as end-user (can you imagine a user having to pick a RecId from a dropdown instead of a Customer number? Before you freak out - customers still have account numbers and that's still the primary key).

Let's create a new table called AX2012Features, which uses RecId as its primary key, and a Name field. This name field will be contained in a unique index as well, and be designated as the "Alternate Key" (more on this later). On the table's properties, set this key as the "ReplacementKey".





Next, we create a new extended data type for our RecId (yes!). So this data type we'll call AX2012FeatureRecId, and it has to be an Int64. As it so happens, this data type has a new property called "ReferenceTable", where we fill in our AX2012Features table.



I'm skipping the creation of a form for this table. Well just add some records using the table browser, we're amongst developers here, right?
So, on to the next table. We'll add an AX2012FavoriteFeatures table, where we can add an integer field "Ranking" for our favorites features from the other table. Also, we will drag and drop our new AX2012FeatureRecId extended data type on the "Fields" node of the table, which will then release some magic. I will rename this field to "FeatureRecId".




When you now open this table's table browser, nothing appears out of the ordinary (besides the fact the table browser in 2012 is now finally no longer an MDI child). There is an integer ranking field, and a FeatureRecId field with a dropdown with recids.
Let's create a form for this, shall we? Now, for the fields, either drag them from the datasource onto the design, OR, when you add a control for the FeatureRecId, select the "ReferenceGroup" control instead and set the referencefield property to "FeatureRecId". If you drag & drop, it will take referencegroup automatically.




A picture says more than a thousand words, so open up the form, which now has the FeatureRecId field (an Int64) in a ReferenceGroup control.



Yes indeed. The dropdown features your alternate key with your name. If you select a value, the supposed RecId field actually shows the Name, not the RecId. Please try this out with more than one field in the AlternateKey, instead of just one like we did here.
Ok, so this is pretty cool actually. We only store one RecId field, but the user sees and picks from a list of values from the alternatekey (however many fields there may be in that key). This begs the question, what do we do in code? Traditionally, I know going in, what my name will be, and so I can add that name when I insert a record in the favorites. Now with this, I cannot get my inserts going, until I have added the favorites, then I need to get that recid and put it in the favorite.

Blast! Just when you started getting used to using RecordInsertList, you need to stop using it because you need the auto-generated value to insert another record. Well, have no fear, the UnitOfWork class is here. And even better, if you have complex structures, it will figure out the sequencing of inserts for you! How you say? Let's create a class, sporting our two tables and a UnitOfWork class. We create an instance of the UnitOfWork class, and we set the name of our empty feature to "UnitOfWork". Instead of calling insert, we call unitOfWork's "InsertOnSaveChanges", to indicate we want to insert this record when we call "savechanges" on unitofwork. We use a class to test this, and not a job, because UnitOfWork always needs to run on server. So, when you create a main method to run the class, make sure to declare it SERVER!



Next, the magic happens. Go back to your FavoriteFeatures table, and check the relations. The RecId data type prompted you to auto-create the relation. If you open the relation, you'll see the FeatureRecId field linked to the RecId of the Features table. Open the properties of the relation and set the "CreateNavigationPropertyMethods" to YES.



In the code, we'll say our number one feature (at least for this blog post)is the one we're trying to insert. Because of the ForeignKey relationship, and setting the navigation property methods to YES, AX 2012 has added a method to the favorites table, carrying the name of your relationship, in the case of this example, AX2012Features. We pass in the (not yet created!) record buffer of feature. Next, we add the favorites buffer to the unitOfWork as well.



Alright. So now, the unit of work class knows we want to insert both records, and it also knows which one depends on which other one, so it will figure out in what sequence the inserts need to happen (granted, in this example there's not much to it… but feel free to experiment with more records, or changing up the order of calling "insertonsavechanges").

Since UnitOfWork also takes care of transactions, we don't really need to do anything more than call the SaveChanges method (because of this transaction model, if you want to use savechanges to make updates, make sure to select your records for optimistic concurrency!).

So, your full code should now look like this:



So… ready to run it? GO! And yes, if you open the favorites form, you'll see your unitofwork ranking number 1 in there, properly linked on RecId (but of course sporting the alternatekey "Name" field on the form instead!).

11 comments:

  1. Great post! I was wondering however if there are any specific guidelines on when to use this new method of relating tables to each other and when to just use the "old-fashioned" way. In the past forms could just show the foreign key field from their own table but now an extra join will be needed for this purpose, which might have a negative impact on performance. Another aspect that you mentioned yourself is data migration, exporting/importing companies etc. Do you have any more details on this?

    ReplyDelete
  2. Hi Marc,

    I can appreciate the question about the guidelines. However, currently there are none, and I haven't given it enough thought yet to give you a well-formed opinion. I think data migration would be the biggest concern, otherwise you might as well use it everywhere. Performance-wise though, there is underlying technology for the joins on the key fields. Of course, this is indeed an extra join, but it is handled optimally on the SQL side. If you have partner or customer source access, I encourage you to watch the technical conference videos, where they explain this from a high-level. I believe it's the "programming model" series videos.

    As far as data migration goes, this will get interesting. Company copies are no longer supported (the "duplicate" button is gone - and export/import within the same environment is not recommended), and the explanation to that is it's due to the sharing and/or no longer company-specific data tables, and I'm sure RecId comes into play here as well (if nothing else with export/import of a full company).

    ReplyDelete
  3. Hi Joris,
    are there any updates on 'guidelines' from Microsoft?
    Thanks,
    Ivan

    ReplyDelete
  4. No word on actual guidelines, but the consensus seems to be to use RecId linking where possible.
    We have noticed some issues with data migration on some standard AX tables that don't have the alternatekey properties setup properly etc, which gives errors using the office add-ins.

    ReplyDelete
  5. Hiho Joris,

    nice tutorial. I did evrything as you did - i think.
    But, i have a problem: Typing the line favorites.AX2012Feature(feature) results in an Error here - there's no AX2012Feature - method here on the Class UnitOfWork.

    Excuse me, i am a total newbie in Axapta/AX2009/2012.
    But i am sure i dont have to write that method by myself, right?
    Any suggestion wich could help me?

    ReplyDelete
    Replies
    1. That method will exist if you set the properties on the favorites table correctly. You must have missed the "CreateNavigationPropertyMethods" on the favorites table which needs to be set to "Yes". Try that and your code should work.

      Delete
  6. Your hint was right. Method found.
    Thx a lot for that help.

    greetz

    ReplyDelete
  7. I've spent the last couple of weeks playing with RapidStart (available as a service through Customer Source) and from what i've heard this is the method they suggest of using instead of copy company in future.

    The idea is that you set up the base config for each functional area in RS and then deploy to different legal entities, isntances as required.

    It does work but the obvious overhead is the duplication required to keep a template set of configuration in the cloud for use in copy company.

    It also uses Silverlight which is a pain...

    Also seen a dem of the migration tool which does look pretty good but didn't go into details on the RecID insertion and also dimensions which will be a further headache.

    ReplyDelete
  8. Hi Joris and group,
    I am knew to AX, but was recently asked by my employer to write a sql query for an SRS report that would extract gl transaction debits and credits. In addition to the debits and credits I need to pull the projectid and itemid associated with any transactions.

    I believe the related itemids are in the INVENTJOURNALTRANS table, but I don't see a way to link this table of subledger distributions to its counterparts in the GENERALJOURNALACCOUNTENTRY distributions.

    If anyone can help me understand this linking I would greatly appreciate it. I will check back for any replies or you can email me at system7matrix@yahoo.com. Thanks

    ReplyDelete
  9. Could you please elaborate on your statement "Blast! Just when you started getting used to using RecordInsertList".
    What's the issue if I do in this way..
    ttsbegin;
    features.name = "recordInserList";
    features.doinsert();
    recordInsertList = new recordInsertList(tablenum(AX2012Favouritefeautures));
    for (i= 0; i <= 3, i++)
    {
    favourites.Rank = i;
    favourites.AX2012FeatuteRecId = features.RecId;
    recordInsertList.add(favourites);
    }
    recordInsertList.insertdatabase();
    ttscommit;

    ReplyDelete
    Replies
    1. In your example you only have one insert into features table. The point is if you have multiple records to insert in both tables, you want to use record insert list to minimize database calls and traffic. The point of the example is to show that you don't have to worry about the RecId (which is only assigned after insert) if you use the unit of work features.

      Delete