I am getting a lot of questions now a days on DRM cross referencing nodes and how to achieve this.
let’s say we want a property to hold a comma-separated list of nodes in the Accounts hierarchy where a certain property value matches one in the current node.
For example, a list property has certain values, e.g. HR, Finance, Legal, etc. Nodes in the Services hierarchy need a list of nodes in Accounts where this value matches.
Any solution that traverses a whole hierarchy for each execution of a formula, i.e. for every node in another hierarchy, would have performance implications once the number of nodes rose above a few thousand.
Here we describe how to use a lookup table for the list. A property (e.g. ‘AccountLookup’) needs to be created with a lookup table pre-populated with the list of those nodes in the Accounts hierarchy that have each possible entry, e.g.
HR – 001,002,003,…
Finance – 004,005,006,…
Legal – 007,008,009,…
The cross reference property should be a string lookup property, with AccountLookup as the lookup property and the value the list property.
Populating the lookup table is not so straightforward. My proposal is this:
1. Create an export for each possible value of the list property (‘ListProp’). The first export, ExportHR, has the top of hierarchy Account as its top node, recurses, has a single column, NAME, and an in-line query filter: “ListProp Equal HR”. On the Target tab, export to client file with the Field Delimiter set to None and record delimiter set to comma. This produces a file with a single line. In the Header field enter HR. The result is this:
2. Copy this export to ExportFinance, replace the filter value with “ListProp Equal Finance” and put Finance in the Header field
3. Repeat for Legal and all other members of the list.
4. Combine these exports into a book, exporting to client file and selecting ‘Include Combined Export Output File’ with Combined.txt as the filename. This book now produces a zip file, which if you open it in Windows Explorer can be drilled down into to find Combined.txt, which looks like this:
5. If you use the Migration Utility to export the ISSrelatedCoA property, it contains a section like this:
It should be possible to merge Combined.txt with XML like above to create Migration Utility import. You might use a utility or XSLT for this. Note that the XML file must be complete, i.e. not only contain the above!
The lookup table must be refreshed whenever the Accounts hierarchy or its ListProp values change.
— Comments and questions welcome.