on 11-17-2008 6:38 AM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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 _
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
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.