cancel
Showing results for 
Search instead for 
Did you mean: 

Question on Calculation expression with a lookup [Hierarchy] field

Former Member
0 Kudos

Dear all,

This is Angela works on another MDM project.

I got a problem when I try to use calculation field in below scenario:

+++++++++++++++++++++++++++++++++++++++++++++++++

Three fileds in main table:

1> First Name (text)

2> Family Name (lookup [Hierarchy])

3> Full Name (text, calculation field, display field)

One field in the hierarchy lookup table,

4> Family Name

++++++++++++++++++++++++++++++++++++++++++++++++++

I want to display the Full Name by using the 'concatename' expression like:

CONCAT(FAMILY NAME.[Record]&", "&FIRST NAME)

The expected results should be like Bush, Geoge, but actually it turns out to be like 1.42975422, Geoge. Any one can give me some advise on this problem? Thanks in advance!

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Angela,

The reason for your output is Record i.e. FAMILY NAME.Record instead of this select FAMILY NAME.Name i.e. the field inside the lookup table containing the actual values. Say in your example Bush may be the value for field Name present inside Lookup table.

When you select Family Name from the drop down list you must have got two or three options depending on number of fields present in Family Name lookup table. Select the actual field name instead of record.

Regards,

Jitesh Talreja

Former Member
0 Kudos

Hi Talreja,

Thank you for your answer! But I'm still not very sure what changes should I made - are you suggesting that I should use some Java function somewhere? But how?

You see, below are all the possible selections I could make to the Hierarchy lookup field in the calculation expression window:

++++++++++++++++++++++++++++++++++++

1> CONCATE(FAMILY NAME.[Record]&FIRST NAME)

2> CONCATE(FAMILY NAME.[Depth]&FIRST NAME)

3> CONCATE(FAMILY NAME.[Parent].[Record]&FIRST NAME)

4> CONCATE(FAMILY NAME.[Parent].FAMILY NAMES&FIRST NAME)

5> CONCATE(FAMILY NAME.[Child].[Record]&FIRST NAME)

6> CONCATE(FAMILY NAME.[Child].FAMILY NAMES&FIRST NAME)

+++++++++++++++++++++++++++++++++++++

But none of them could give me what I want...

Former Member
0 Kudos

Dears,

I find a solution based on Talreja's reply.

1> Changed the property of multi-Lingual for the Family Names field in the hierarchy lookup table from Yes to No.

2> in the calculation expression, one more selection could been made when choosing the lookup field 'Family Name', as below:

CONCATE(FAMILY NAME.FAMILY NAMES&", "&FIRST NAME)

3> I got the expected full name in data manager as Bush, George...

But I don't understand why the 'multi-lingual' setting would effect the lookup field selection in the calculation expression. Anyone could kindly explain?

Many thanks!

____________

Angela _

Former Member
0 Kudos

Hi Angela,

Consider the below table Countries:

Code Name

100 India

200 US

300 Germany

Whenever you use FAMILY NAME. Record it will return some code. Now say we want to display Country name then the expression would be Countries.Name if you refer the above table. In your case you may not get the field name because there will be only one field present in the lookup table. Create one more field and populate the data in it. Now while writing expressions you will get the option FAMILY NAME. New Field. Use this and check the output. It may append some characters to the exact value like US;3 in this case use the function LEFT(FAMILY NAME.Name,FIND(FAMILY NAME.Name,";")-1)

After this you should get the desired output.

Regards,

Jitesh Talreja