Find all tables that have a relation to a primary key in another table.

Finance and Operations/AX Developer Forum
1

Hello,

We are migrating from an old ERP system to AX2009 and are having some issues with the unit table (that holds the units of measure).

This old ERP system had many unwanted unitID’s, but did not have the ability to clean them up. Therefore we now have many unwanted unitID’s in AX2009 as well (I know, it should have been done differently, but that’s the current situation).

We are now ‘ready’ for the cleanup, but are having some issues like this one:
We have the unitID Box that is correct, but there are also four ‘misspelled/ misused’ ones:
Bx
Little box
Big box
Bxo

We want them all to become ‘Box’, but we cannot ‘rename’ them to ‘Box’, as that already exists. (there is no conversion in qty needed)

I’ve tried this:

  1. Rename ‘Bx’ to ‘Box’ (does not work, since there is already a unitID ‘Box’
  2. Use the method ‘RenamePrimaryKey’ on the ‘Unit’ table and call that from a Class WITH BREAKPOINT to try and find the logic behind this renaming method. (AX did not show that logic, it seems as if these methods are ‘black boxes’).
    That way I would do it in SQL.
  3. Find the relations in the AOT (there were none on the unit table)
  4. Use the SQL profiler while renaming a unitID. (That gave many many many results, even in a TEST environment with no activities, and it seems like a lot of the action is not converted into readable SQL statements…)

So my questions:

  1. Can I merge unitID’s in AX2009?
  2. Is there a way to find all tables that have a ‘foreign key’ - like relation to the unit table? (like ‘renamePrimaryKey’ seems to be using)?

Thanks a lot.

Peter

2

PS, this is the X++ code I’ve used for the second attempt:

static void main(Args args)

{

unit unit;

Counter upd = 0;

;

select firstonly unit

where unit.UnitId == ‘Bx’;

if(unit.RecId)

{

unit.UnitId = ‘Box’;

unit.renamePrimaryKey();

upd++;

Info(strFmt(’%1 regel(s) gewijzigd.’, upd));

}

else

{

Info(‘Foutje’);

}

}

Sorry for the Dutch info lines.

3

The DataReferenceSearcher might help you to find the references.

4

Thanks, I’ll try that one.

5

That did the trick for me.

Nice work there :slight_smile:

Thanks.