How to change the validation status of member in MDS 2012 using Stored Procedure in Custom Workflow

Question

I'm trying to change the validation status in a custom workflow task to pass or fail for a member in MDS 2012. Are their stored rpcedures or maybe even a WF API method to update the validation status?

Answer 1

I wanted to do something like that recently after staging and wrote this.  It works for me, but obviously is "unofficial" so try it and see if it works for you:

DECLARE @ModelName NVARCHAR(50) = 'yourModel' 
DECLARE @Version NVARCHAR(50) = 'yourVersion' 
DECLARE @UserName NVARCHAR(50) = 'yourUser' 
DECLARE @Model_ID INT, @User_ID INT, @Version_ID INT, @Entity_ID INT, @MemberIdList INT, @MemberType_ID INT, @ProcessUIRulesOnly BIT
DECLARE @x mdm.IdList 

-- Lookups / parameter assignment
SELECT @User_ID = u.Id FROM mdm.tblUser u WHERE u.UserName = @UserName
SELECT @Model_ID = Model_ID, @Version_ID = ID FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName AND Name = 'yourModel'
SELECT @Entity_ID = ID FROM mdm.tblEntity WHERE Model_ID = @MODEL_ID AND name = 'yourMember'
SET @MemberIdList = 1
SET @MemberType_ID = 1 
SET @ProcessUIRulesOnly = 0

EXEC mdm.udpValidateMembers
	@User_ID = @User_ID,
	@Version_ID = @Version_ID,
	@Entity_ID = @Entity_ID,
	@MemberIdList = @x,	-- Empty TVP = validate all?
	@MemberType_ID = 1,	-- 1: Leaf	2: Consolidated	3: Collection	4: Hierarchy	5: Collection Member
	@ProcessUIRulesOnly = 0

Answer 2

Thanks, but I'm trying to only update one member. I believe this update all the member in the Entity. Also when you do update member how can you get a red "X" to appear beside the member as a fail validation?

Thanks

Answer 3

Hi, yes, my intention was to validate all members after a staging exercise.

If you want to validate one member, then you can populate the TVP which is called @x in my script with the member id you want.  You'll have to look that id up in the database.

I think the red X will appear if your member violates any of the business rules once you run this proc.

Let me know if you need some help and I'll try and script out and update for one member - although hopefully you can work it out from the info available to you?

Answer 4

Hi,

If you want to validate just one member, you can do it using custom workflow and .Net class library. Below link gives you more details on how to implement this.

http://johanmachielse.blogspot.co.uk/2011/07/master-data-services-implementing.html 

Hope this helps.

Answer 5

How do you change the "?" beside the members in the Explorer UI to a Green "check" or Red "!". I know once you appy the business rule which valid the member. I'm using custom workflow task (Business Rule) to valid the member to see if it pass or fail. How can I change the validation status to a pass or failure from within a Custom workflow Task.

Answer 6

How do you change the "?" beside the members in the Explorer UI to a Green "check" or Red "!". I know once you appy the business rule which valid the member. I'm using custom workflow task (Business Rule) to valid the member to see if it pass or fail. How can I change the validation status to a pass or failure from within a Custom workflow Task.



Answer 7

Hi Roger

In your custom workflow task, you could try to call MDS API, then use this code to process validation and get validation errors

 // Create the request object.
                MDSTestService.ValidationProcessRequest validationProcessRequest = new MDSTestService.ValidationProcessRequest();
                validationProcessRequest.ValidationProcessCriteria = new ValidationProcessCriteria();
                validationProcessRequest.ValidationProcessCriteria.ModelId = modelId;
                validationProcessRequest.ValidationProcessCriteria.EntityId = entityId;
                validationProcessRequest.ValidationProcessCriteria.VersionId = versionId;
                validationProcessRequest.ValidationProcessOptions = new ValidationProcessOptions();
                validationProcessRequest.ValidationProcessOptions.ReturnValidationResults = true;

                // Process validation and get a validation issue.
                MDSTestService.ValidationProcessResponse validationProcessResponse = clientProxy.ValidationProcess(validationProcessRequest);
                HandleOperationErrors(validationProcessResponse.OperationResult);

                // Show the validation issue's description. 
                if (validationProcessResponse.ValidationIssueList.Count > 0)
                {
                    ValidationIssue validationIssue = validationProcessResponse.ValidationIssueList[0];
                    Console.WriteLine("Validation issue: " + validationIssue.Description);
                }

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

Answer 8

Hi

you can try to use

for one member:

declare @p7 nvarchar(250)  
set @p7=N''  
exec mdm.udpMemberStatusSet @User_ID=1,@Version_ID=20,@Entity_ID=37,@MemberType_ID=1,@Member_ID=18,@Status_ID=2,@ReturnEntityName=@p7 output  
select @p7  
  
 SELECT * FROM mdm.tblAttribute WHERE DomainEntity_ID=37  
  

 or

EXEC mdm.udpMemberValidationStatusUpdate 1,9,7,1,2

[mdm].[udpMemberValidationStatusUpdate]  
(  
	@Version_ID				INTEGER,  
	@Entity_ID     			INTEGER,  
	@Member_ID				INTEGER,  
	@MemberType_ID			TINYINT,  
	@ValidationStatus_ID	INTEGER   

and

for many members

DECLARE @MemberIdList mdm.IdList;  
INSERT INTO @MemberIdList  
VALUES (1), (2), (3)  
  
EXEC mdm.udpMembersStatusSet @User_ID=1, @Version_ID=20, @Entity_ID=37, @MemberIds = @MemberIds, @Status_ID=2  
  

with

   @MemberType_Leaf               INT = 1  
        ,@MemberType_Consolidated       INT = 2  
        ,@MemberType_Collection         INT = 3  
        ,@MemberType_Hierarchy          INT = 4  
        ,@MemberType_CollectionMember   INT = 5  
  
        ,@Status_Active                 INT = 1  
        ,@Status_Deactivated            INT = 2  
  
        ,@ValidationStatus_AwaitingRevalidation INT = 4  
  
        ,@TransactionType_ChangeMemberStatus INT = 2  

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".