help with an assignment

[Former Member]

HI All,

I need your help to figure out how to build an assignment for the following purpose: we have 1 maintable MT and inside 2 fields which are lookup[flat]: Field1 and Field2 with approximatelly 400 values(all of them 3 symbols long) in each lookup table.

Now we need to introduce a new field (boolean) which should contain 2 values: "Y" or "N" and it should be automatically populated with assignment with "Y" only when combination(concatenation) from both fields are in a predefined list of 150 values.

I have created new lookup table with those fields and my aim was to create an assignment which concatenate both fields and put the value in this new field which is lookup against my new table with values, but the assignment does not work. I just cant figure it out how can we check if the concatenation of 2 fields exists in 1 table - that's all we need to check.

Did somebody have deal with such a case?

Thanks&Kind regards,

ILIN

[former_member188878]

Dear ILINI

you can also try out

if(IS_NOT_NULL(CONC(F1,F2),Y,F)

Regards

shankar

[Former Member]

Hi Sudhanshu, Christian and Shankar,

Thanks for your help.

I am not sure if I explained it correctly so I will try again with example.

1. We have 1 maintable MT and inside 2 fields which are type lookup(flat): F1 and F2 with approximatelly 400 values in each lookup table behind: LT1 and LT2.

2. When user maintain main table record he/she need to choose sepparatelly F1 and F2 values from the corresponding lookup tables: LT1 and LT2. Sometimes the field can be blank.

3. Now there is a new business requirement to add 1 new boolean field: BF, which should contain 2 values: TRUE or FALSE. It should be TRUE only if the concatenation of F1&F2 is equal to a pre-defined list of values (150) - in all other cases = FALSE.

This is easy achivable with simple assignment:

IF(F1&F2=123ABC,TRUE,FALSE)

, but in which we need to list all 150 pre-defined list of values(in the example 123ABC was just one of them), which is the problem as they are too many and also I am affraid of performance problems (the assignment will be executed in workflow).

The assignment will have to looks like:

IF(F1&F2=123ABC OR

F1&F2=123ABC OR

F1&F2=123XYZ OR

F1&F2=456ABC OR

F1&F2=456FGH OR

F1&F2=789ABC OR

F1&F2=777ABC OR

F1&F2=666ABC OR

...

F1&F2=123ABC,

,TRUE,FALSE)

That's why I was thinking of any other possible way to achieve this. We can't change the repository structure to combine F1 and F2 as they are completelly sepparate and not connected, but only for this business requirement there is connection between them.

Any other ideas how we can implement that?

Thanks&Kind Regards,

ILIN

[Former Member]

Hi Ilin,

from my point of view, i.e. if you cannot alter the data model, I only see it as a web service called from the enrichment step out of the workflow.

Could you provide some more details about the purpose of the flag? Maybe, based on that information there are other ways. For instance, if you need it only as a syndication filter, you could also put the logic into a search expression in the syndication map (just to give an example).

Best regards

Christian

[Former Member]

Hi,

I just want a clarification.

How you are maintaining data in the third lookup that contain 150 lookup values .During Import using F1 and F2 fields.

Please clarify this.

My understanding is the table only contains concatenate values of LT1 and LT2.So tehre is no need to check whether

F1&F2 contains in this lookup table LT3 as both F! and F2 are lookup fields and only contains values that already existing in LT1

and LT2.

Instead of this just check if any of the fields is blank then make it TRUE otherwise FALSE.

If you want to correct me then please go ahead

Thanks,

Sudhanshu

[former_member205403]

Hi llin,

Unfortunately there is no other Possible way using Standard MDM and Assignment.

May be you can give a try using Programming APIs like MDM Java API and can check on Performance.

Best Regards,

Shiv Prashant Dixit

[Former Member]

Hi Christian,

This is a normal boolean field which will be also syndicated to R/3 field from MARA table of Material Master record. Business will use it for other purposes, but they need it to be maintained in the described above way.

Sudhanshu,

I have created the third lookup table just for testing purpose and once I uploaded all concatenated records inside. Also I have there the F1 and F2 values and the Boolean field = TRUE (which I added to use it in additional assignment to the desired Boolean field). So my aim was if possible when user maintain F1 and F2, somehow automatically with an assignment to add the concatenation of these 2 fields F1&F2 into this third lookup table and then to use third assignment which puts TRUE in the Boolean field when there is a record(concatenation value) into this third L3 pointing to my third lookup table.

If I am not able to populate values into my F3 as a concatenation of F1 and F2 so I cant go on and check if the record is null or not null, which would be nice.

Shiv,

Unfortunatelly we do not want to add API programing as this will slow down the maintenance process, that's why I was wondering if there is any workaround.

Maybe listing all possible values in 1 big assignment will be alot of work to do but simplest way.

If somebody have any other ideas will be very much appreciated.

BR,

ILIN

[Former Member]

Hi Ilin,

one more idea, this time involving PI:

- Create a lookup field for the boolean values Yes and No.

- Import the 150 values as remote key for the Yes value (or respective No value).

- Create a syndication map, that exports the mat number with the two lookup field values.

- Create an import map, that imports the concatenation of these two values (use partitioning) for the newly created lookup table.

- Modify your workflow by adding a syndication step.

- Configure PI to listen to the new port and copy files from this port into a new inbound port for immediate re-import.

The idea is now, that whenever a user picks to values, then sends the workflow to the next step, these two values are being exported, then re-imported as remote key to the new boolean field. You may need to create a little delay in your workflow. Voilà, you will get your appropriate Yes/No values.

Not the most elegant approach, but it'll work.

Another idea, involving PI: If you need this value only in your ERP system, why not let PI do the actual work during the transport from MDM to PI? As far as I remember, you syndicate the ERP mat number back to MDM for remote key mapping. Here you could also include then the flag to be written back to MDM.

Best regards

Christian

[Former Member]

Hi Ilin

Mainitaing all the 150 possible combinations in 3rd table may not work as it will not be linked to the main table and will be a hanging table. Assigments and Validations always work for a single table and not across multiple tables and they always work at a record level. Are you also linking this 3rd table in the main table?

You can try another workaround-

1. Define a Text large field linked to the main table which contains all possible 150 combinations of F1 and F2.

2. Define a calculated field F3 with logic as F1&F2. Write an assignment to find if the calculated field exists in the Text large field to derive the Boolean value.

hope this helps-Ravi

[Former Member]

Hi Ravi,

The same thing i suggested .

But i think the only problem is that we maintain same information in all records.

If that is feasible then i think this is good option top go ahead.

Thanks,

Sudhanshu

[Former Member]

Hi Ilin,

If I am not able to populate values into my F3 as a concatenation of F1 and F2 so I cant go on and check if the record is null or not null, which would be nice.

Approach1: You can give a try for WebDynpro Application. You need to give the URL to the application within the MDM Console and need to test this application using Data Manager option WebPane URL for Selected records.

In this application, your F1 and F2 are input display fields and F3 as output field which you can populate using this application.

Please check this Article for creating and using [WebDynpro Application|http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70260b78-ff89-2a10-5eb0-d2f51467560b?quicklink=index&overridelayout=true] for some other requirement.

Approach2: You can also use MDM workflow on record Add, Import or Update etc as per your requirement. Once the workflow triggers, You have Process step in WF which is assigned to some Role/User. This user will check the existing values filled in F1 and F2. F3 lookup field has 151 values (150 actual values which is combination of F1 and F2 and 151 value as FALSE) and then fill the field F3 manually from drop down list of 151 values based on the combination of these two fields F1 and F2 if exist. If the combination of F1 and F2 does not exist, it will set F3 values as FALSE and then move to next Assignment. At this Assign step Boolean field value automatically populates as per the logic written in Expression and give output as either TRUE/FALSE. After this there is Approve step. Approver will approve it after checking F1, F2 and F3 and Boolean field logic filled correctly or not and then move to last step Stop.

Start>Process>Assign>Approver>Stop

assignment Expression: IF(F1&F2=F3, TRUE, FALSE )

Else the simplest way as you suggested, Maybe listing all possible values in 1 big assignment will be alot of work to do but simplest way.

Regards,

Mandeep Saini

[Former Member]

Hi Ilin,

if your 150 values are to dynamic to have them in the assignment expression (I'm not sure of the impact of performance with so many values anyways), maybe you could rethink your data model:

1. option: Create a lookup table to the main table which itself has to lookups to your other two lookup tables and one additional field with the Yes/No value. On the main table the user would then select only a combination of these two values instead of two separate values. In this case you would assign the Yes/No value already during import.

2. Create a 2-level hierarchy table which again combines the two values (if the logical linkage allows that). Again, the user would select only a combination of the two values instead of two separate values. And again, you could control the Yes/No value directly during the import.

Just another idea. If you need more detail info, let me know.

Best regards

Christian

[Former Member]

Hi,

Please correct me if i my understanding wrong.

Instead of checking whether the concatenate value present in lookup table i think yr requiremnet may be is to check that

the the newlooup filed contain concatenation value or not and based on that value you set true or false.

This is my understanding because as all three fields are lookup values so there is no need to check the values in lookuptable as we can select only the values that already present in lookuptables so there is noo need to check in lookuptables.

If your requirment is to just check whther the record contain concatenate value or not.

for example for record X lets suppose values

lookupfield1 : a

lookupfield2 : b

lookupfield(concatenate value of 1 and 2) :ab

booleanField : true

lookupfield1 : a

lookupfield2 : b

lookupfield(concatenate value of 1 and 2) : xz

booleanField : false

If this is the case then this is very straight forward.

if(lookupfield.[record]==lookupfield&lookfield2,true,false)

If my understanding is wrong then please get back to me.

Thanks,

Sudhanshu

[Former Member]

Hi,

*I have created new lookup table with those fields and my aim was to create an assignment

which concatenate both fields and put the value in this new field which is lookup against my new table with values*

This is not possible .You cannot assign value to lookup field other than the value it already contains ie.e you cannot assign a string value to lookup field.

To check value in a lookup is not possible .

what you can do is create a longtext field and put all lookup values there and then you can check whether that field contains concatenation value or not using has_any_values.

May be through workflow it is possible.I have to check for that.

If the solution is feasible for you then please go ahead .

It is working fine at me end.

Thanks,

Sudhanshu