0

How can we display the owner of the record automatically in MDS.

For Example, I have 2 groups called Group A and Group B.

Group A user entering the record as following:

NAME    CODE
Bike    1
Car     2

Similarly Group A user entering the record as following:

NAME    CODE
Bus     3
Lorry   4

What i want to display is

NAME    CODE    Owner
Bike    1       Group A
Car     2       Group A
Bus     3       Group B
Lorry   4       Group B

How can we achieve this? We need to write any custom code for this. Please help me out in this regard

TotPeRo
  • 6,561
  • 4
  • 47
  • 60
user3607647
  • 51
  • 2
  • 14

1 Answers1

0

You have the column [EnterUserName] in every subscription view which has been created. So what you can do is add a column to your entity and create an insert trigger which copies this information to the newly created column. The trigger has to be creeated for the "Base table" which holds the main information for your entity.

For information on how to create a subscription view see: http://msdn.microsoft.com/en-us/library/ff487013.aspx

If you want to examine what table contains information of your entity you can search for the view in MDSDB Database and perform "ALTER to" statement. The table name should be something like [tbl_...EN]

Now create a trigger on the table: 

CREATE TRIGGER [mdm].[SOME_NAME]
  ON  [mdm].[YOUR_BASE_TABLE]
  AFTER INSERT

AS 
BEGIN

  SET NOCOUNT ON;
  UPDATE [mdm].[YOUR_SUBSCRIPTION_VIEW]
  SET [Owner] = [EnterUserName]
  FROM [mdm].[YOUR_SUBSCRIPTION_VIEW]
  WHERE  ID = (SELECT MAX(ID) FROM [mdm].[YOUR_SUBSCRIPTION_VIEW])

END
Narti
  • 181
  • 7
  • Thank you so much for this code. It is working as expected. I want to know more detail about it. For example in my base table i could see only name and code. I couldn't see the user defined columns. when we updating the subscription view how it is automatically reflect in MDS. Thanks! – user3607647 Jun 16 '14 at 14:03
  • I'm glad it worked out for you. Unfortunately the MDS backend is not documented very well. But if you have a look at SP [udpCreateAttributeViews] you will notice the main steps which are performed to update table information when you create a view. – Narti Jun 16 '14 at 14:44