Automatic assignment of hierarchy field in the main table

[Former Member]

Hi,

We are upgrading from SRM-MDM catalog 2.0 to SRM-MDM catalog 3.0 sp07 patch 10.

In the main table, we have the standard field Hierarchy, that is a lookup on table Hierarchy.

In order to value this field, we have a specific field in the Main table, the UNSPSC code, that is a lookup on a specific reference table. This reference table makes the link between UNSPSC codes and nodes of the Hierarchy table.

After importing a new record that contains the UNSPSC code, we want to automatically assign a value to the hierarchy field in the Main table by the assignment's expression "[main table]UNSPSC code. [specific table]name of hierarchy. [Hierarchy table]name of hierarchy".

When executing this assignment, we get the error "Assignment operation failed: A runtime error occurred while evaluating an assignment, validation or calculated field".

Do you know if this kind of assignment is possible (it worked in MDM 5.5 but not anymore in 7.1) ? Or if there is a workaround ?

Thanks for your kind help.

Catherine

[Former Member · Accepted solution]

Dear Catherine,

Possible reasons are clearly mentioned in the error message itself.

1. As you imported the records so there is a possibility that you record contains a validation error. First check that record for all validations on which you are running the assignment operation.

If you upgraded to 7.1 SP04, your look-up reference values which are being used in Validation needs to reassigned/mentioned in validations again as per the SAP Note 1385073.

2. Any Calculated field expression in not properly executed.

3. As this Assignment is field value dependent then check whether the field on which basis, hierarchy is being assigned has a value or not. Or there are any other assignment which are being Failed.

BR,

Alok Sharma

[Former Member · Accepted solution]

Hi Alot,

Thanks for your kind answer.

To answer to your suggestions :

1 - we do not have any validations, automatically executed ; and the assignment we are running does not refer to any other validation. And I have re-written the expression.

2 - the calculated fields are properly calculated.

3 - if we run the assignment on a text field, instead of the Hierarchy field, that is on lookup on the Hierarchy table, the name of the leaf node is properly assigned to the text field.

So I think that the problem comes from the assignment field itself, which is on lookup on the Hierarchy table.

Has anybody ever tried to assign a value to the hierarchy field in the main table, in 7.1 ?

Thanks in advance for your help.

Best regards,

Catherine

Edited by: Consultation Domaines on May 31, 2010 1:45 PM

[Former Member · Accepted solution]

Hi Catherine,

Has anybody ever tried to assign a value to the hierarchy field in the main table, in 7.1 ?

it tried it with MDM 7.1, it is working fine..

Make sure that the leaf node which you are giving in the assignment expression should be selected from Lookups tabs of your Assignment Expression. when you click on lookups tabs, Select the field here of main table which is lookup to Hierarchy table. Now a pop up window will open Choose Lookup Value, from Values select the leaf node. You will get field name followed by value in brackets in your assignment expression. This will solve your Problem.

Kindly revert with result.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi,

I am on MDM 7.1 SP04, and using assignment expression IF(Country.[Record]=Country[IN, India],Customer Hierarchy[1000001, SBI HEAD OFFICE], Customer Hierarchy[1000006, ICICI BANK HEAD OFFICE]).

It is working fine at my end.

Let me know your expression, i will try and let you know.

BR,

Alok Sharma

[Former Member · Accepted solution]

Hi Mandeep,

Thanks for your answer.

But, I do not want to assign a specific value to the Hierarchy field in the Main table. I want to determine it via a specific table.

Here is a sample of our data model to try to explain :

Main table :

item reference [Display field]

hierarchy --- lookup on Hierarchy table

UNSPSC code --- lookup on Specific reference table

Hierarchy table :

hierarchy name [Display field]

hierarchy code

Specific reference table, type flat :

UNSPSC code [Display field]

hierarchy name --- lookup on Hierarchy table

After import, in the Main table, item reference and UNSPSC code are valued.

In order to value the hierarchy field in the Main table, we would like to use the specific reference table :

[Main table]UNSPSC code >>> [Specific reference table]hierarchy name >>> [Hierarchy]hierarchy name.

This assignment works if the assignment field is a plain text field but not if it is the Hierarchy field of the Main table, which is on lookup on the Hierarchy table.

Thanks for your help !

Best regards,

Catherine

[Former Member · Accepted solution]

Hi Alok,

My expression is : UNSPSC . classification . name of hierarchy

"UNSPSC" is the field in the Main table, "classification" is the field in the Specific reference table and "name of hierarchy" the field in the Hierarchy table.

In the specific reference table, we have around 2600 distinct UNSPSC codes, which means that we cannot treat them value by value.

Thanks for your help !

Best regards,

Catherine

[Former Member · Accepted solution]

Hi Catherine,

As the Data model given by you.. Try with Below expression, it should work...

IF(IS_NOT_NULL(UNSPSC code.UNSPSC code), UNSPSC code.hierarchy name.)

Note: Make sure that for Specific reference table, type flat :has both of its field populated UNSPSC code and hierarchy name, when you select current table as Specific reference table in record mode of data manager. So that whenever for a item reference record your field UNSPSC code is populated then after executing the assignment expression your Hierarchy field of main table will also get populated. Make sure that you select . as shown in above expression rather than hierarchy name. i used here { } these brackets but in expression it would be square brackets.

Kindly revert with result. It is working fine at my end.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Mandeep,

Your expression does work with my Data model.

Thank you very much for your kind help !

Catherine