text field Assignment looking up a sub table to populate another text field

[Former Member]

I have a sub table called "User Data" it contains several fields like User ID, User Name, User Email

I want to write an assignment that populates the users email address into a text field:

The field called Last Updated by User id type = User Stamp (this is the loggin id) for example jdoe, look-up/find the matching value in the user data table and return the User EMail field value

User Data Table:

User ID User Name User Email

jdoe John Doe John_Doe"at"company.com

I can not get it to work...any ideas?

[Former Member · Accepted solution]

Sudhanshu, I have seperate fields. Hopefully this clears it up.

Fields in Main Table

Last Updated By User ID Type = User Stamp

Last Updated by Email Address Type = Text

Last Updated By User Name Type = Text

Last Updated User Data Type Look-up to User Data Table (I only added this to try and solve the issue)

Fields in sub table titled "User Data"

User ID Type =Text (matches loggin ID and Last Updated by User ID)

User Name Type = Text

User Email Type = Text

The requirement is to populate the Last Updated by Email Adddress and Last Updated by User Name.

The approach I was taking was to have an Assignment look-up the of the email address in the "Last Updated User Data" field and the same for the User Name...This all works

However I need a method to keep the "Last Updated User Data" updated...I can not figure out how to do this..or if it is possible.

I am open to making any changes inlcuding adding more fields to make this work. The basic requirement is to display the email address and NAme of the person who last updated the record in the main table.

[Former Member · Accepted solution]

Hi Brad

Please try this-

1. Define a field Last Updated By type User Stamp and select all fields other than AutoID in console. This will capture the user name who has last updated the record.

2. Add one more field as Email ID , type text.

3. Define a Java application/Project which can read the Last Updated by field for the record and pass it to ECC/MDM custom table where email IDs are maintained against the User Ids. the output email ID can be populated in field Email ID

4. Add the link for project in Data Manager as web tab. User has to manually execute the project in web tab.

thanks-Ravi

[Former Member · Accepted solution]

Hi,

The approach I was taking was to have an Assignment look-up the of the email address in the "Last Updated User Data" field and the same for the User Name...This all works

what i understood is u were manually doing this updation and now you want through assignment.

Please also mention what are the dispaly field of lookuptable.

I am not able to understand what you were trying to do and now what you want.

Sorry but Please describe more clearly.

Thanks,

Sudhanshu

[Former Member · Accepted solution]

Hi Brad,

The basic requirement is to display the email address and Name of the person who last updated the record in the main table.

Just try it out: Create a workflow, Start>Assign>Stop. you can set the Property of Workflow Trigger action on Record Update, Record Import and Record add as per your requirement. so whenever any field of your main table updates/create(by some user) this workflow will trigger and thus will populate fields Last Updated by Email Adddress and Last Updated by User Name (who updated/create record) through Assignments, these Assignments you should add in the workflow.

For writing Expression in your Assignments, it should be like this:

For Populating main table field (Last Updated by Email Adddress) has expression as: Last Updated User Data.User Email Type

For Populating main table field (Last Updated by User Name ) has expression as: Last Updated User Data.User Name Type

Regards,

Mandeep Saini

[Former Member]

Agreed, I have done something similar in the past with a java listener. I plan on proceeding with having the portal update this one field and triggering a WF to run the other assignements.

Thanks for all your help and ideas!

[Former Member]

Hi Brad,

In addition to approach suggested by Ravi, Just Check for Enrichment Controller Concept. I am not quite sure but i think it can help you where you need to syndicate 3 fields (out of MDM through MDSS(syndication Services) using MDM workflow on field Last Updated By User ID) Last Updated By User ID, MDM Last Updated User Data and Last Updated by Email Address to third-party dispatcher(from here you will get the corresponding e-mail addrr of User's), it will call the enrichment service and waits to receive the result (email address) i mean MDM Last Updated User Data, Last Updated by Email Addres( other two fields get populated) . After receiving the result, the enrichment adapter extracts the u2018envelopu2019 and passes the data back to the enrichment controller. MDM enrichment controller calls MDM API to import the results via a server port. Once the import is complete, the enrichment controller receives an event.

Please have a look on flow of Enrichment

http://help.sap.com/saphelp_nwmdm71/helpdata/en/44/ab743565d37455e10000000a11466f/content.htm

Complete Guide: http://help.sap.com/saphelp_nwmdm71/helpdata/en/44/aa4122e9d57454e10000000a11466f/frameset.htm

Hope it helps.

Regards,

Mandeep Saini

[Former Member]

Mandeep, yes that would technically work, however I need to do this for creation, update, approval and deactivation. Also we have thousands of users. Also it would be difficult to maintain as each new user is added.

Please let me know if you have any other ideas....I am willing to try anything.

Thanks

[Former Member]

Hi Brad,

I think whatever mandeep mentioned in last reply is the only possible way to achieve that functionality.

Because we cannot assign a field of type lookup other than lookup value and also there is no way to find in lookup table which particular record satisfy username comparison.

So you have to go by the this approach only.

Thanks,

Sudhanshu.

[former_member205403]

Hi Brad,

I doubt, Your requirement can not be fulfilled using Standard MDM data manager (* other than using approach suggest by Mandeep). But that approach can be very uncomfortable and tedious as you have to hard code values.

I strongly feel you can use MDM Java API and can achieve your requirement. You can also write stand alone Java Application and can schedule it to automatically update data in the repository. I never tried it myself, but feel it should work.

Best Regards,

Shiv

[Former Member]

Mandeep, I changed the display field and created the assignment however, this is what I tried in the begining, I get this error message when running the assignment ( Assignment Expression as Last Updated By User ID and here your Assignment field = Last Updated User Data )

Error message - Assignment operation failed: A Runtime error occured while evaluating an aissignment, validation or calculated field.

Note: I have no calculated fields and all the Validations are set to "none" for message type so they are not running.

Maybe it is a problem with my system...But other assignments work with no issue.

When I first saw this I thought it was because the Dispaly field in User Data did not = Last updated by User ID so I changed the dispaly field with the same result.

[Former Member]

Hi Brad,

As you are getting error message, there is another way of doing it but in this case your Expression would be quite lengthy if you have lot of User's.

I am assuming maximum length for a user 20, you can set it accordingly to your requirement.

I am taking example with 2 user's say (Brad & Mandeep) you can similarly do it for all the User's.

In assignment write Expression in below manner.

IF(LEFT(Last Updated By User ID), 20) = "Mandeep", Last Updated User Data (Mandeep), IF(LEFT(Last Updated By User ID), 20) = "Brad", Last Updated User Data (Brad))

Note: 1 ."Mandeep" and "Brad" within double quotes should be case Senstive according to value maintained for Field in Last Updated By User ID.

2. Last Updated User Data (Mandeep) and Last Updated User Data (Brad) should be selected from Lookups tab of Assignment Expression. Select the lookups tab-->Select the field Last Updated User Data -->A pop-up window will come, from Selection Choose lookup as Mandeep, as soon as you select you will get value in assignment expression as Last Updated User Data (Mandeep) not Parenthesis but in actual Mandeep will come in Square brackets as i am not able to write it down so i used Parenthesis. Just check and revert with result. This expression is working fine at my end.

Regards,

Mandeep Saini

[Former Member]

Thanks for your quick replies!

Sudhanshu, the display field in the User Data Table is User ID (value is the same as the users loggin)

[Former Member]

Thanks for your quick replies!

Mandeep, yes I have everything you described working, However the field on the main table (XXX called Last Updated User Data (look-up to User Data) is not populated, and when I write an assignment to populate it with Last Updated by User ID it fails.

I need to know how I can populate a look-up field from a value from a text field (The value is the same). This will allow the other fields to be updated properly in the assignments.

I fear that I might have to do what Ravi suggests however I would complete it on the Last Updated User Data Field and let the standard assignments update the email and name fields.

[Former Member]

Hi Brad,

The basic requirement is to display the email address and Name of the person who last updated the record in the main table.

1stly you should have two fields in your sub-table titled "User Data"

User Name Type = Text (DF)

User Email Type = Text

This subtable have all required populated UserName and UserEmail.

e.g. This table has values

User Name User Email

Brad Brad@xyz

Mandeep Mandeep@xyz

For this requirement> you should have MDM workflow Start>Assign-->Stop. you can set the Property of Workflow Trigger action on Record Update, Record Import and Record add as per your requirement. so whenever any field of your main table updates/create(by some user e.g. Mandeep) this workflow will trigger. The Assign Step of this workflow should have Assignment Expression as Last Updated By User ID and here your Assignment field = Last Updated User Data

Note: For testing you can run this assignment manually to check whether field Last Updated User Data populates or not

So in this manner your main table field Last Updated User Data which is lookup to User Data Table will be populated by the assignment used in workflow.

So in main table

Last Updated User Data Type Look-up to User Data Table will be populated using Assignment either manually or thru WF.

Last Updated By User Name Type = Text, you can even delete this field as Last Updated User Data has the same meaning as Last Updated By User Name since now Last Updated User Data field automatically contains the UserName her e( Mandeep)who create/updates the record.

Now, for your 2nd field

Last Updated by Email Address, you can populate this field using Assignment, Expression should be as below:

Last Updated User Data.User Email

Note: Don't type anything manually in Assignment Expression, select the field from Fields tab of Expression

This will populate your email corresponding to User Mandeep that is Mandeep@xyz which is required by you.

You can further add this assignment in your Workflow for automation.

Regards,

Mandeep Saini

[Former Member]

Sudhanshu, Yes I have a field in the main table that is a look-up to the User Data Table, however I can not write an assignment to keep it updated.

The Business Requirement is to display the last Updated user name and email address on the Main record level. However since MDM only tracks the user loggin ID, I am trying to meet the requirement by using another table....maybe there is a better way of meetign the requirement.

Thanks

Brad

[Former Member]

Hi ,

Ya i understand your requirement.

Just want to know you want to update the same field that is lookup to usertable with email address of the user that login to data manger.

Thanks,

Sudhanshu

[Former Member]

Mandeep, the piece of your proposed soltion that I can not figure out is how do I keep the field XXX updated?

Initally I was thinking of an assignment from the field Last Edited By (User stamp type) however that does not work.

[Former Member]

Hi Brad,

Is this flat lookup table atached with maintable or not.If not attached then in that case we can't refer to lookuptable.

Please describe the structure.

Thanks,

Sudhanshu

[Former Member]

Hi Brad,

As you haven't cleared you requirement, i am assuming following things:

1. You text field is in Main table which you want to populate using Email Addr of User Data subtable.

2. You should have a field in main table which is look up to this Subtable User Data e.g. field name is XXX

Now In main table , Assignments tab -->write Assignment Expression as given below.

XXX.User Email

Kindly let me know if you are looking for something else.

Regards,

Mandeep Saini

[Former Member]

Hi ,

I am not able to understand the actual requirement.

The field called Last Updated by User id type = User Stamp (this is the loggin id) for example jdoe, look-up/find the matching value in the user data table and return the User EMail field value

Please describe clearly what actually have to do.

Thanks,

Sidhanshu

[Former Member]

Hi ,

in MDM Assignment will not execute to subtable(Qualified Table) or tuple (Special Table) only executes in main table

Thanks,

Jignesh Patel