help with assignment expression

[Former Member]

Hi All,

I'd like to ask you for help regarding one assignment in MDM.

We have text field: "Material_Long_Description" (as a concatenation of 8 attributes) - the field length is unlimited, e.g.:

"attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8".

We need to have "Material_Short_Description" - limited to 40 symbols so I have created assignment:

LEFT(Material Long Description,40) and the result is: "attribute1,attribute2,attribute3,attribu",

but we also need not to have cut one of the attributes on the middle and half attribute and teh comma(",attribu") to be removed - the aim is in this case to have: "attribute1,attribute2,attribute3".

I've tryed with function FIND but didn't find solution.

This was implemented in SQL Database with the following query:

set Material_Short_Description = left(Material_Long_Description,(40 - locate(",", reverse(left(Material_Long_Description,40)))))

and it works perfectly well, but such a function "reverse" does not exists in MDM.

Any suggestions how we can implement this logic in MDM without API programing?

Thanks in advance,

ILIN

[former_member205403 · Accepted solution]

Hi Illin,

Why don't you use Attribute 1, Attribute 2... etc for populating values in Short Description field instead of trimming Long description Field value.

I mean to say, For Populating Value in Short description field you can use logic something like below

1. If length of concatenation attribute 1 to attribute 8 is less than 40 then assign this value to short description, else count length of concatenation from attribute 1 to attribute 7.

2. following step 1 till you get you attribute concatenation string less than 40 characters.

In this way you don;t have to look for comma separator. if this looks good, then you can implement this logic. Please let me know if you face any problem while defining such expression in assignment.

Regards,

Shiv

[Former Member · Accepted solution]

HI Shiv ,

thanks for your advise. I have implemented it in this way and it works, but it is really big code (as I need to check all possible conditions and it is about 30 lines of code:)

Anyway it works fine, but I wanted to know if there is other way arround (cuting to 40 and up to the comma) - if somebody have something in mind or it is simply not possible :).

Thank you for your help, Shiv,

BR,

ILIN

[former_member205403 · Accepted solution]

Hi Illin,

If you implement this assignment using LONG_DESCRIPTION field, then also i think it will be complex and lengthy. Because you have to fist check total length of this field. It it is more then 40 then, check length upto 6th comma (upto 7th attribute). If it is still more than 40 characters then upto 6th comma (6th attribute) and so on till you get length upto 40 characters.. i think executing assignment based Attributes will be better and easier to implement then on Long description field.

Regards,

Shiv

[Former Member · Accepted solution]

It is possible using LONG DESCRIPTION(LD)

I am assuming any of these attributes (1 to 😎 have maximum length of 10 char.

Attribute Max Length =10

IF(MID(LD,41,1)="," OR MID(F,41,1)=" ", LEFT(LD,40),

IF(MID(LD,40,1) = ",", LEFT(LD,39),

IF(MID(LD,39,1) = ",", LEFT(LD,38),

IF(MID(LD,38,1) = ",", LEFT(LD,37),

IF(MID(LD,37,1) = ",", LEFT(LD,36),

IF(MID(LD,36,1) = ",", LEFT(LD,35),

IF(MID(LD,35,1) = ",", LEFT(LD,34),

IF(MID(LD,34,1) = ",", LEFT(LD,33),

IF(MID(LD,33,1) = ",", LEFT(LD,32),

IF(MID(LD,32,1) = ",", LEFT(LD,31)))))))))))

If you have LD as ABCD,EFGHIJKL,SDFGHJ,QWERTYUI,WERTYUI,DWERTYUI,XYZ,ERT

It will return you ABCD,EFGHIJKL,SDFGHJ,QWERTYUI,WERTYUI returns total of 37 characters.

You can further extend this Expression, if you feel it is having max length of Attribute > 10 OR

Reduce this Expression, if it has attribute length < 10 as per your data maintained for Attribute values in MDM.

See in the below example carefully there is no attribute whose value is > 10 and in this Max Attribute length is 8.

ABCD,EFGHIJKL,SDFGHJ,QWERTYUI,WERTYUI,DWERTYUI,XYZ,ERT

I tried this at my end, It is working fine.

Just check and revert with the result, let me know if you have any issue.

Thanks and Regards,

Mandeep Saini

[former_member205403 · Accepted solution]

Mandeep,

This is really a nice logic. But it may fail if some attributes are NULL thereby making total length less then 30 characters.

But I think this can also be handled by writing some more IF conditions.

Regards,

Shiv

[Former Member · Accepted solution]

Shiv,

You mean to say in case of length less than 40 right, in that case just a minor change as such there is no need to write more IF conditions.

IF(MID(LD,41,1)="," OR LEN(LD)<=40, LEFT(LD,40),

IF(MID(LD,40,1) = ",", LEFT(LD,39),

IF(MID(LD,39,1) = ",", LEFT(LD,38),

IF(MID(LD,38,1) = ",", LEFT(LD,37),

IF(MID(LD,37,1) = ",", LEFT(LD,36),

IF(MID(LD,36,1) = ",", LEFT(LD,35),

IF(MID(LD,35,1) = ",", LEFT(LD,34),

IF(MID(LD,34,1) = ",", LEFT(LD,33),

IF(MID(LD,33,1) = ",", LEFT(LD,32),

IF(MID(LD,32,1) = ",", LEFT(LD,31)))))))))))

In 1st IF condtion, LEN(LD)<=40 will take care of it.

@ llin, Just check this above expression and revert with the result, It is working fine at my end.

Thanks and Regards,

Mandeep Saini

[Former Member · Accepted solution]

Thank you, Mandeep.

Your logic is great and works fine.

The only one thing which I couldn't find is how to trim spaces from the attributes in the middle - e.g. " 3 - 4 " - We want to have "3-4", but TRIM_ALL function remove the spaces only from beginning and end - the result is: "3 - 4" (but not the desired "3-4").

If you have any idea how to achieve it within assignment will be great.

Thanks&Regards,

ILIN

[Former Member · Accepted solution]

Hi llin,

You are retrieving these attributes values which you have given in Attribute text values for attribute name in taxonomy mode. Why don't you change these values there itself in taxonomy mode for Attribute values.

like you have Attribute A who has text values as 3 - 4 and 5 - 6, change it to 3-4 and 5-6 rather than using any logic in assignment expression.

If you still want to use assignment expression for the same then it would be really lengthy but it is working fine at my end.

I denote Attribute1 = A1, Attribute2 = A2 and so-on till Attribute8 = A8

This expression you will run on Material_Long_Description, i mean on text large field.

I understand you don't want to maintain space for Short description, but i would suggest as you populate concatenation of attribute values data on Material_Long_Description, why don't at this point of time you take care of space and populate your Material_Long_Description free from space character. Further when you populate this data in Short Description, you just need to use logic as given by me above and don't need to bother about space character for Attribute values as they are already corrected while populating Material_Long_Description.

Assumption:

I am assuming whenever "-" hyphen character comes it is always append and prep-end with space character " ".

I mean, you have always values as "3 - 4" or "3-4" in attribute not as "3 -4" or "3- 4"

Procedure:

Create an Assignment: give the Assignment field as Material_Long_Description.

Now right click on this assignment-->Add Branch.

Select the Branch Value of that category: Write Assignment Expression here in this Add Branch Assignment as given below:

IF(MID(A1,FIND(A1,"-")-1,1)=" " AND

MID(A1,FIND(A1,"-")+1,1)=" ",

LEFT(A1,FIND(A1,"-")-2)

&MID(A1,FIND(A1,"-"),1)

&MID(A1,FIND(A1,"-")+2),

A1)&","&

IF(MID(A2,FIND(A2,"-")-1,1)=" " AND

MID(A2,FIND(A2,"-")+1,1)=" ",

LEFT(A2,FIND(A2,"-")-2)

&MID(A2,FIND(A2,"-"),1)

&MID(A2,FIND(A2,"-")+2),

A2) and so-0n till 8th Attribute(A8)

Logic: 1stly it will look Attribute value which has space character " " before and after Hyphen "-", eg "3 - 4", then it will populate value as 3-4, else if it doesn't find space character " " before and after "-" Hyphen it will just put there same attribute value. e.g if it is already 3-4, it will just place it same as 3-4.

Just check this above expression and revert with the result, It is working fine at my end.

Thanks and Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Mandeep,

thank you for your help.

The need of this was because these are not taxonomy attributes, but standard text fields, because taxonomy attributes does not support "free text" and they are syndicated to classification structure in R/3, where for range of values the input should be: 1 - 2, but we don't needed this in teh description.

I have implement your logic (plus several additional conditions checking which attributes are not blank) and it works fine.

The strange thing for me is that such a trim internal spaces functionality is missing in MDM.

Anyway there are workarounds, thanks again.

BR,

ILIN