Finding orphaned Records in tables using RefRecId

Dynamics AX makes us the RefRecId, RefTable and RefCompany data pattern in a number of tables the system to provide flexible linking from a common/generic table such as WorkflowWorkitemTable to a variety of specific tables such as PurchReq or PurchTable.

Examples of this include Workflowtables, DocuRef, SpecTrans,  etc…

From time to time you may want to identify which of the records in these tables have become “orphaned” i.e. the documents that they refer to no longer exist. To do this in a fairly generic way you can make use SysDictTable’s makeRecord functionality as below (Using DocuRef as an example)

static void Check_Orphans(Args _args)
{
SpecTrans specTrans;
DocuRef docuref;
counter i;
Common record;
SysDictTable table;

while select forupdate * from docuref// where SpecTrans.RefTableId == 865 && SpecTrans.RefCompany==’an’// 866 for Debtors
{
table = new SysDictTable(docuref.RefTableId);
changeCompany(docuref.RefCompanyId)
{
record = table.makeRecord();
select firstonly * from record where record.RecId == docuref.RefRecId;
if(!record.RecId)
{
info(strFmt(“Record %1 has been orphaned”, docuref.caption()));
//Delete if necessary over here…
i++;
}
}
}
info(strFmt(“%1 orphaned records found”,i));
}

I hope you find this a useful trick in your AX wanderings.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">