List Rows Based On Expand Query In Power Automate

Introduction

In order to get the details of a related entity for the selected entity without a separate retrieve call we can leverage Expand Query. As a business scenario for the same vaccination is a use case of extracting details of accounts that are present in list of contacts who took the vaccination.

Step 1

Login to the required Power Apps environment using URL make.powerapps.com by providing username and password and click on Flows on the left-hand side as shown in the below figure.

Step 2

After Step 1, Click on New Flow and select instant cloud flow and provide the trigger as Manually trigger a flow and click on Create as shown in the below figure.

Step 3

After Step 2, name the flow as List Rows - Expand Query take another step and take initialize variable and name it as Initialize variable - Set ConsolidatedDetails Array and provide inputs like below

Name : ConsolidatedDetails
Type : Array
Value :

BASIC

as shown in the below figure.

Step 4

After Step 3, select new action and under Data verse Connector and select action List Rows and name it as List rows – Contacts on the contact entity and provide the following inputs as below

Table name : Contacts
Select columns : fullname,telephone1,contactid,_parentcustomerid_value
Filter rows : (cr5bc_vaccinationcompleted eq true) and (parentcustomerid_account/accountid ne null)
Expand Query : parentcustomerid_account($select=address1_city,address1_composite,websiteurl,name)

BASIC

as shown in the below figure.

Step 5

After Step 4, Take Apply to Each Action and provide value as

outputs('List_rows_-_Contacts')?['body/value']

BASIC

And take Set Variable name it as Append to array variable and provide the following values as

Name: ConsolidatedValues[Select Initialize Array variable from Drowdown]
Value: {
    "Customer Name": items('Apply_to_each') ? ['fullname'],
    "WebSiteUrl": items('Apply_to_each') ? ['parentcustomerid_account/websiteurl'],
    "TelePhone": items('Apply_to_each') ? ['telephone1'],
    "AccountName": items('Apply_to_each') ? ['parentcustomerid_account/name'],
    "Account City": items('Apply_to_each') ? ['parentcustomerid_account/address1_city']
}

BASIC

as shown in the below figure.

Step 6

After Step 5, save, test, and run the flow and observe the details as shown in the below figure.

Note

One can easily form expand query by the use of XRM Tool Box, Fetchxml Builder and form fetchxml with link-entity and then click on View -> Power Automate Parameters -> Power Automate List Records Parameters popup and under Expand Query click on the content link where query is there so that you can copy the query and go to Step 4 and provide Expand Query as shown in the below figure.

Make sure to save and run flow.

Microsoft Documentation Details about expand query can be found here.

Conclusion

In this way, one can easily extract details of related entities linked to the base entity using expand query.

BPF auto movement in dynamics crm - Using javascript ( please refer next blog using plugin)

 Moving to next stage : 

Consider the below JavaScript code which is fired during OnSave event of the form record. 

function BPFMove(executionContext) {

    //Initiated Form Context.

    var formContext = executionContext.getFormContext();

    //Getting Value From Field Account Closed.

    var AccountClosed = formContext.getAttribute("accountclosed").getValue();

    //Condition If Account Closed Is True.

    if (AccountClosed === true) {

 //Moving Chevron To Next Stage.

formContext.data.process.moveNext();

    }

}

Moving to previous stage :

function BPFMove(executionContext) {

   //Initiated Form Context.

    var formContext = executionContext.getFormContext();

    //Getting Value From Field Account Closed.

    var AccountClosed = formContext.getAttribute("accountclosed").getValue();

    //Condition If Account Closed Is False.

    if (AccountClosed === false) {

        //Moving Chevron To Previous Stage.

        formContext.data.process.movePrevious();

    }

}

Moving BPF Stages from Plugin

 Method 1:

https://community.dynamics.com/365/b/mfasih365crm/posts/moving-bpf-stages-from-plugin-method-1

Method 2: 

Introduction

During some scenarios we must automatically move BPF Stages based on server-side code. This can be achieved by CRM Plugin. As an example, on update of a title field for a selected contact record BPF will automatically moves to next stage.

Step 1

Login to the required environment and go to flows and select Business process flows – Vaccination and observe whether BPF is active or not, if not then activate it as shown in the below figure.

Step 2

After Step 1, open Business process flow and note down the logical name of the Business process flow by opening the above selected BPF which we will be going to use in next steps as shown in the below figure.

Step 3

After Step 2, we have to go to our plugin code [C# Class Library] under execute method on update message of contact for jobtitle field change and write the logic inside the code block as

public void Execute(IServiceProvider serviceProvider) {
    IPluginExecutionContext context = (IPluginExecutionContext) serviceProvider.GetService(typeof(IPluginExecutionContext));
    IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory) serviceProvider.GetService(typeof(IOrganizationServiceFactory));
    IOrganizationService crmService = serviceFactory.CreateOrganizationService(null);
    try {
        string logicalNameOfBPF = "cr5bc_vaccination";
        if (context.MessageName.ToLower().Equals("update") && context.PrimaryEntityName.ToLower().Equals("contact")) {
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity) {
                Entity entity = (Entity) context.InputParameters["Target"];
                if (entity.Contains("jobtitle") && entity["jobtitle"] != null) {
                    // logic here
                }
            }
        }
    } catch (InvalidPluginExecutionException ex) {
        throw ex;
    }
}

C#

Which is shown in the below figure.

Step 4

After Step 3, we need to get active BPF details that is present on the selected contact record which was open, to get this we write a separate method and call this method to retrieve details passing contact entity which was obtained in Step 2 and by using RetrieveProcessInstancesRequest present in CRM SDK messages we can form the request and pass this to RetrieveProcessInstancesResponse to get the list of BPF process instances and select the first instance using below code

public Entity GetActiveBPFDetails(Entity entity, IOrganizationService crmService) {
    Entity activeProcessInstance = null;
    RetrieveProcessInstancesRequest entityBPFsRequest = new RetrieveProcessInstancesRequest {
        EntityId = entity.Id,
            EntityLogicalName = entity.LogicalName
    };
    RetrieveProcessInstancesResponse entityBPFsResponse = (RetrieveProcessInstancesResponse) crmService.Execute(entityBPFsRequest);
    if (entityBPFsResponse.Processes != null && entityBPFsResponse.Processes.Entities != null) {
        activeProcessInstance = entityBPFsResponse.Processes.Entities[0];
    }
    return activeProcessInstance;
}

C#

As shown in the below figure

Step 5

After Step 4, we need to write another method and call it inside execute method to get all the stages details of the selected BPF on contact record so that we can extract next stage ID details by using RetrieveActivePathResponse present in CRM SDK messages and we have to pass parameters like Active Stage Id, Active BPF Id, IOrganization service object as input and an output parameter with int type to extract current Stage Position also retrieve active path request using the below code

public RetrieveActivePathResponse GetAllStagesOfSelectedBPF(Guid activeBPFId, Guid activeStageId, ref int currentStagePosition, IOrganizationService crmService) {
    // Retrieve the process stages in the active path of the current process instance
    RetrieveActivePathRequest pathReq = new RetrieveActivePathRequest {
        ProcessInstanceId = activeBPFId
    };
    RetrieveActivePathResponse pathResp = (RetrieveActivePathResponse) crmService.Execute(pathReq);
    for (int i = 0; i < pathResp.ProcessStages.Entities.Count; i++) {
        // Retrieve the active stage name and active stage position based on the activeStageId for the process instance
        if (pathResp.ProcessStages.Entities[i].Attributes["processstageid"].ToString() == activeStageId.ToString()) {
            currentStagePosition = i;
        }
    }
    return pathResp;
}

C#

As shown in the below figure

Step 6

After Step 5, we need to call the methods obtained from Step 4 and Step 5 from the main method and then based on active path response and current Stage Position and looping through the stages and get the next stage id and set the next stage as active by forming an object of current BPF with selected BPF Instance Id and passing next stage id as activestageid of BPF with IOrganization service’s update method BPF Active stage will gets changed to next stage with the below code

Entity activeProcessInstance = GetActiveBPFDetails(entity, crmService);
if (activeProcessInstance != null) {
    Guid activeBPFId = activeProcessInstance.Id; // Id of the active process instance, which will be used
    // Retrieve the active stage ID of in the active process instance
    Guid activeStageId = new Guid(activeProcessInstance.Attributes["processstageid"].ToString());
    int currentStagePosition = -1;
    RetrieveActivePathResponse pathResp = GetAllStagesOfSelectedBPF(activeBPFId, activeStageId, ref currentStagePosition, crmService);
    if (currentStagePosition > -1 && pathResp.ProcessStages != null && pathResp.ProcessStages.Entities != null && currentStagePosition + 1 < pathResp.ProcessStages.Entities.Count) {
        // Retrieve the stage ID of the next stage that you want to set as active
        Guid nextStageId = (Guid) pathResp.ProcessStages.Entities[currentStagePosition + 1].Attributes["processstageid"];
        // Set the next stage as the active stage
        Entity entBPF = new Entity(logicalNameOfBPF) {
            Id = activeBPFId
        };
        entBPF["activestageid"] = new EntityReference("processstage", nextStageId);
        crmService.Update(entBPF);
    }
}

C#

As shown in the below figure.

Step 7

After Step 6, the final code looks like this

public void Execute(IServiceProvider serviceProvider) {
    IPluginExecutionContext context = (IPluginExecutionContext) serviceProvider.GetService(typeof(IPluginExecutionContext));
    IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory) serviceProvider.GetService(typeof(IOrganizationServiceFactory));
    IOrganizationService crmService = serviceFactory.CreateOrganizationService(null);
    try {
        string logicalNameOfBPF = "cr5bc_vaccination";
        if (context.MessageName.ToLower().Equals("update") && context.PrimaryEntityName.ToLower().Equals("contact")) {
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity) {
                Entity entity = (Entity) context.InputParameters["Target"];
                if (entity.Contains("jobtitle") && entity["jobtitle"] != null) {
                    Entity activeProcessInstance = GetActiveBPFDetails(entity, crmService);
                    if (activeProcessInstance != null) {
                        Guid activeBPFId = activeProcessInstance.Id; // Id of the active process instance, which will be used
                        // Retrieve the active stage ID of in the active process instance
                        Guid activeStageId = new Guid(activeProcessInstance.Attributes["processstageid"].ToString());
                        int currentStagePosition = -1;
                        RetrieveActivePathResponse pathResp = GetAllStagesOfSelectedBPF(activeBPFId, activeStageId, ref currentStagePosition, crmService);
                        if (currentStagePosition > -1 && pathResp.ProcessStages != null && pathResp.ProcessStages.Entities != null && currentStagePosition + 1 < pathResp.ProcessStages.Entities.Count) {
                            // Retrieve the stage ID of the next stage that you want to set as active
                            Guid nextStageId = (Guid) pathResp.ProcessStages.Entities[currentStagePosition + 1].Attributes["processstageid"];
                            // Set the next stage as the active stage
                            Entity entBPF = new Entity(logicalNameOfBPF) {
                                Id = activeBPFId
                            };
                            entBPF["activestageid"] = new EntityReference("processstage", nextStageId);
                            crmService.Update(entBPF);
                        }
                    }
                }
            }
        }
    } catch (InvalidPluginExecutionException ex) {
        throw ex;
    }
}
public Entity GetActiveBPFDetails(Entity entity, IOrganizationService crmService) {
    Entity activeProcessInstance = null;
    RetrieveProcessInstancesRequest entityBPFsRequest = new RetrieveProcessInstancesRequest {
        EntityId = entity.Id,
            EntityLogicalName = entity.LogicalName
    };
    RetrieveProcessInstancesResponse entityBPFsResponse = (RetrieveProcessInstancesResponse) crmService.Execute(entityBPFsRequest);
    if (entityBPFsResponse.Processes != null && entityBPFsResponse.Processes.Entities != null) {
        activeProcessInstance = entityBPFsResponse.Processes.Entities[0];
    }
    return activeProcessInstance;
}
public RetrieveActivePathResponse GetAllStagesOfSelectedBPF(Guid activeBPFId, Guid activeStageId, ref int currentStagePosition, IOrganizationService crmService) {
    // Retrieve the process stages in the active path of the current process instance
    RetrieveActivePathRequest pathReq = new RetrieveActivePathRequest {
        ProcessInstanceId = activeBPFId
    };
    RetrieveActivePathResponse pathResp = (RetrieveActivePathResponse) crmService.Execute(pathReq);
    for (int i = 0; i < pathResp.ProcessStages.Entities.Count; i++) {
        // Retrieve the active stage name and active stage position based on the activeStageId for the process instance
        if (pathResp.ProcessStages.Entities[i].Attributes["processstageid"].ToString() == activeStageId.ToString()) {
            currentStagePosition = i;
        }
    }
    return pathResp;
}

C#

As shown in the below figure.

Step 8

After Step 7, build and sign the assembly and then using Plugin registration tool register this assembly and then create an update step on contact entity with update message, filtering attributes on jobtitle on preoperation with mode as Synchronous and click on create/update step as shown in the below figure

Step 9

After Step 8, now go to the contact record and observe the current stage as Contact Details before changing title as shown in the below figure

Step 10

After Step 9, update job title to Sri and save the record you should see automatically stage moves to Vaccination Status as shown in the below figure

Note

1. I have concentrated more on the logic part that’s why only execute and other related methods were shown.
2. Other custom logic like restriction based on roles and to perform other operations and transactions can also be written inside the execute method.
3. In exception block we can write generic as well as specific exceptions like invalid exception.
4. I have included screenshot outline of the methods in Step 7 because of constraint of taking screenshot with total code in a single image.

Conclusion

In this way, one can write CRM Plugin code to move automatically BPF Stages based on the given business requirement.

OLd Process: 

Steps 1:- Create a workflow in processes: