AX 2012 RecIds and UnitOfWork Foreign-Key Magic
Filed under: #daxmusings #bizapps
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!).
There is no comment section here, but I would love to hear your thoughts! Get in touch!