May 15, 2013 by Dick Wenning Category: AX2012, AX2012 R2 0 comments Tags: datamodeling

As we all know the default primary and cluster index in AX is the recid index. When we add a primary key based relation to a table we get a new column with the refrecid, an index for that column and a relation to the related table. There is nothing wrong with that. That is unless users want to change query ranges by pressing the select button of a runbase class or a developer adds ranges to that refrecid column. From that point the surrogate key is not your best friend anymore.

When a developer adds a range on a query of a column of type refrecID, the user is displayed a pull-down with the first column being the recid, besides the fields from the replacement key. The user then selects a 64 bit recid number as a query range. Next time the user opens the batch again, he has no idea what the purpose of this refrecid was.

However, if a user adds a range to the query by using the sysQuery form, he gets nice replacement keys. But sometimes he is not able to do this at all.

Example:

rp001

If there is a primary key based relation fields on the custtransopen table, the user cannot select them.

So let us explain how the SysQuery code works in case of replacement keys. The option replacement key column name is only there if the code below returns true. (code is part of the lookup method of the table fields in the sysQuery form.)

rp003

So, why these conditions?

The replacement key results in an additional inner join data source in your query. If the lower data source has a 1:1 fetch mode, the combination of the new data source and the lower data source acts like an ‘OR’ condition in your query while the user expects that it will work like an ‘AND’.

Therefore when user complains that they cannot add a range in a query, you know what to do. In the example of custcollectionlettercreate the custtrans has 1:n fetch mode.

What should the developer do in such a case? Well it is easy, he needs to simulate the process described above. Add the related data source with inner join relation and add the range on that table (the replacement key) .

Leave a Comment!

Your email address will not be published. Required fields are marked *