Getting wrong values on calculated fields

[Former Member]

Hi Experts,

We are facing one problem that the calculated fields are getting wrong values.

Here below is the Expression for one calculated field : "IFRS Hierarchy level"

IF ( IS_NULL(IFRS Hierarchy parent.Account No)

, IF( IS_NULL(IFRS Hierarchy child.Account No)

, "" , 1)

, SUM(IFRS Hierarchy parent.IFRS Hierarchy level) + 1

)

In my case in MDM Data Manager:

account 10051 has IFRS Hierarchy parent S1_10030

account S1_10030 has IFRS Hierarchy parent IA_00031

account IA_00031 has IFRS Hierarchy parent IA_00030

account IA_10030 has IFRS Hierarchy parent BS_01100

account BS_01100 has IFRS Hierarchy parent BS_01000

account BS_01000 has IFRS Hierarchy parent BS

BS is the top node is in hierarchy level 1.

The correct result for hierarchy level for account 10051 is 7 but I got only 1. And the wrong value 1 applied to all accounts.

Currently we have updated patch 04 from MDM 5.5 SP06 patch02 on Sandbox. Don't know if this is perhaps from the updates. From verifying repository has no detail informtion about the calculation expression but clear to know there is no problems on patch02.

Could anybody help me with this problem ?

Thanks a lot for your help

Kind regards,

Wei Dona

[Former Member · Accepted solution]

Hi Wei

The SUM function "Returns the sum of a multi-value or a list of values" it does not count the number of levels in a hierarchy. I therefore suspect that what is happening is that the "SUM(IFRS Hierarchy parent.IFRS Hierarchy level)" part of your expression always returns zero.

HTH,

Mark

[Former Member · Accepted solution]

Hi Mark and experts,

Currently we applied the MDM 5.5 SP06 patch04 hotfix16 which has been released since last month on 18.06.2009. I can also see that one of the bugfix in this hotfix looked very closing to our issue:

-

---

Bugfixes:

MDM Server

Fixed: In the Expression Builder the function SUM returns 0 in every case (O355186 2009).

-

---

But we are still facing this problem till now. What we knew from MDM Server Log:

......

EXPR calculateField: faild. Calculated Field Recursion detected.

......

Could you please tell me what it means ? Or is the problem still in Expression builder ? or other possible problems ?

Thanks very much for your help

Kind regards,

Wei Dona

[Former Member · Accepted solution]

Hi Dona

Calculated field expressions should not have IF statement, otherwise they can go in a loop.

Try with simpler statements. when we load the repository all calculated fields are populated based on the expression written. For the records which are not fulfilling this condition, MDM system cant populate values giving errors.

Instead wherever you need conditional calculations use Assignments o the same field. Only extra precaution to be taken is to execute these assignment either manually in data manager or use a workflow for this.

best regards

Ravi