Tag Archive for: Dynamics 365 Sales

Managing teams and their members is a common requirement for organizations using Dynamics 365. If you’ve ever needed to retrieve the list of members in a specific team from the Dataverse, you’ve likely found that it isn’t as straightforward as you might expect. After a bit of research and trial-and-error, I’ve put together a simple solution using Power Automate, and I’m excited to share it with you. This blog post will walk you through the process step by step.

The Challenge

The Dataverse has a many-to-many relationship between teams and system users, managed through the hidden teammemberships table. This table links users (systemusers) to teams, but since it isn’t visible by default, you need to use Power Automate to query it effectively.

The Solution

By leveraging Power Automate’s Dataverse connector, we can build a flow to retrieve team members. Here’s how it works:

Step 1: Get the Team

The first step is to identify the team you’re working with. To do this:

  1. Add a Get Row action.
  2. Set the Table name to Teams.
  3. Specify the Row ID. If you’re dynamically retrieving this (e.g., based on the owner of a record), you can use the Owner (Value) field.

Step 2: Retrieve Team Memberships

Next, we’ll query the teammemberships table to find all users linked to the team:

  1. Add a List Rows action.
  2. Set the Table name to teammemberships.
  3. Use a Filter Query to specify the team ID:
    teamid eq '<TeamID>'

    Replace <TeamID> with the dynamic value retrieved from the previous step.

This will return all system users associated with the team.

Screenshot of list teammemberships in power automate

Step 3: Loop Through Team Members

The teammemberships table gives you the systemuserid values for all team members. To retrieve detailed information about each member:

  1. Add an Apply to Each loop.
  2. Set the input to the value array from the previous step.
  3. Inside the loop, add a Get Row by ID action:
    • Set the Table name to Users.
    • Set the Row ID to the systemuserid from the current item in the loop.

Looping through the list output from Team Membership

Step 4: Process the Results

Once you have the user details, you can process them further. For example:

  • Aggregate the results into an array.
  • Format the data into an HTML table for an email notification.
  • Save the data to a SharePoint list or Excel file.

Practical Use Case

Let’s say you want to send an email to a manager listing all the members of a specific team. Here’s how your flow might look:

  1. Retrieve the team and its members as described above.
  2. Collect each member’s name and email into an array.
  3. Use the Create HTML Table action to format the data.
  4. Add a Send an Email (V2) action and include the table in the email body.

Why This Solution is Helpful

Finding a way to retrieve team members in Dataverse is a common challenge, and there’s limited documentation on how to achieve it effectively. By using the teammemberships table in Power Automate, you can overcome this limitation and build powerful workflows tailored to your organization’s needs.

Final Thoughts

I hope this guide helps you streamline your Dataverse workflows. If you’re managing Dynamics 365 teams and need to automate team-related tasks, this approach can save you time and effort. Feel free to share this with your team or let me know if you have questions—I’d be happy to help!

AccessOrange is a leading Microsoft Partner that specializes in Microsoft 365, Dynamics 365 and Azure to help our customers work smarter.
If you need help with building this flow in your own environment, leave your contact information below, and we will contact you as soon as possible.

"*" indicates required fields

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
This field is for validation purposes and should be left unchanged.

In this blog post, I’ll show you how to create a solution in Dynamics 365 that automatically ensures the Opportunity currency matches the Account currency.

I wrote this solution as I got so frustrated that when you create an opportunity in Dynamics 365 Sales, that the currency is not automatically set to the correct currency. Instead it goes to the default currency as set in the system, which will create all sort of problems when you are working in a multi region sales team. (selection of wrong currencies, quote then cannot use the correct currency, etc)

This solution will use JavaScript to retrieve and compare these values, and if they don’t match, update the Opportunity currency to reflect the Account’s currency. Additionally, we’ll publish this solution and configure it to run seamlessly within Dynamics 365.

Prerequisites

Before you begin, ensure you have the following:

  • Administrator access to Dynamics 365.
  • Basic understanding of JavaScript and Dynamics 365 customization.

Let’s break down the process step-by-step.

Step 1: Create a New Solution in Dynamics 365

The first step is to create a solution to contain your custom JavaScript logic and publish it into Dynamics.

  1. Navigate to Power Platform Admin Center:
    • Go to your Dynamics 365 environment.
    • Click on the gear icon and select Advanced Settings.
    • In the settings area, click on Solutions.
  2. Create a New Solution:
    • Click New to create a new solution.
    • Enter details such as:
      • Display Name: Opportunity-Account Currency Sync.
      • Publisher: Select or create a new one.
      • Version: Start with 1.0.0.0.
  3. Add the Opportunity Entity to the Solution:
    • In the solution editor, click on Add ExistingEntityOpportunity.
    • This allows us to add custom logic to the Opportunity entity form.

Step 2: Write the JavaScript for Currency Sync

Next, we’ll write the JavaScript code to compare the Opportunity and Account currency and update the Opportunity currency if they don’t match.

  1. Create a Web Resource for JavaScript:
    • In the solution, navigate to Web Resources and click New.
    • Select JavaScript (JScript) as the type, and name it currencySync.js.
    • Paste the following JavaScript code into the web resource:
    • function getDetailsFromOpportunity(executionContext) {
      var formContext = executionContext.getFormContext();
      
      // Check if the Opportunity has been saved (i.e., it has an ID)
      var opportunityId = formContext.data.entity.getId();
      if (!opportunityId) {
      console.log("This is a new opportunity. The record has not been saved yet.");
      return; // Exit the function if it's a new, unsaved Opportunity
      }
      
      // Proceed with the currency check if the Opportunity is not new
      opportunityId = opportunityId.replace("{", "").replace("}", "");
      
      // Get the Account (Customer Field) from the Opportunity
      var accountLookup = formContext.getAttribute("parentaccountid"); 
      if (accountLookup != null && accountLookup.getValue() != null) {
      var account = accountLookup.getValue()[0]; 
      var accountId = account.id;
      
      // Retrieve the statuscode and compare currencies only if the opportunity is open
      Xrm.WebApi.retrieveRecord("opportunity", opportunityId, "?$select=statuscode").then(
      function success(result) {
      var opportunityStatus = result.statuscode; 
      if (opportunityStatus === 1) { 
      console.log("Opportunity is open. Proceeding with currency check.");
      
      // Retrieve the currency from the related Account
      Xrm.WebApi.retrieveRecord("account", accountId, "?$select=transactioncurrencyid&$expand=transactioncurrencyid($select=currencyname)").then(
      function success(result) {
      if (result.transactioncurrencyid != null) {
      var accountCurrencyId = result.transactioncurrencyid.transactioncurrencyid.toLowerCase(); 
      
      // Get the Currency field from the Opportunity
      var opportunityCurrencyLookup = formContext.getAttribute("transactioncurrencyid"); 
      if (opportunityCurrencyLookup != null && opportunityCurrencyLookup.getValue() != null) {
      var opportunityCurrency = opportunityCurrencyLookup.getValue()[0]; 
      var opportunityCurrencyId = opportunityCurrency.id.replace("{", "").replace("}", "").toLowerCase(); 
      var opportunityCurrencyName = opportunityCurrency.name; 
      console.log("Opportunity Currency Name: " + opportunityCurrencyName + " | Currency Id: " + opportunityCurrencyId);
      
      // Compare the Opportunity currency with the Account currency
      if (accountCurrencyId !== opportunityCurrencyId) {
      console.log("Currencies do not match. Updating Opportunity currency to match Account currency.");
      
      // Update the Opportunity currency to match the Account currency
      formContext.getAttribute("transactioncurrencyid").setValue([{
      id: accountCurrencyId, 
      name: result.transactioncurrencyid.currencyname, 
      entityType: "transactioncurrency"
      }]);
      
      // Save the form after the currency is updated
      formContext.data.entity.save();
      } else {
      console.log("Currencies match. No update required.");
      }
      } else {
      console.log("No currency is associated with this opportunity.");
      }
      }
      },
      function(error) {
      console.log("Error retrieving account currency: " + error.message);
      }
      );
      } else {
      console.log("Opportunity is not open. No currency update will be performed.");
      }
      },
      function(error) {
      console.log("Error retrieving opportunity status: " + error.message);
      }
      );
      } else {
      console.log("No account is associated with this opportunity.");
      }
      }
  2. Save and Publish the Web Resource:
    • Click Save and then Publish the web resource.

Step 3 : Add the JavaScript to the Opportunity Form for the Account Field onChange Event

In addition to loading the script when the form is loaded, we’ll also trigger it when the Account field is changed by the user.

1. Go to the Opportunity Form Editor:

  • In the solution editor, select the Opportunity entity, and click on Forms.
  • Open the Main form for editing.

2. Add the JavaScript Web Resource to the Form (if not already added):

  • If you’ve already added the currencySync.js web resource in the previous steps, you don’t need to do this again.
  • If not, click on Form PropertiesForm LibrariesAdd, and select the currencySync.js web resource.

3. Attach the Function to the Form Load Event (if not already done):

  • In the Event Handlers section under Form Properties, add the function as before for the OnLoad event.
  • Make sure to set the correct event handler details:
  • Do the same for On Save, so when the opportunity is saved, the function will check if the currency needs to be updated.

4. Attach the Function to the Account Field onChange Event:

  • In the Form Editor, locate the Account field (which is the parentaccountid field in Dynamics).
  • Double-click on the Account field, or select it and click Change Properties.
  • Go to the Events tab of the field properties.
  • Under Field Events, click Add.
  • In the Library dropdown, select the currencySync.js library (if not already added).
  • In the Function Name field, enter getDetailsFromOpportunity (the same function name as used for the onLoad event).
  • Uncheck the box that says “Pass execution context as the first parameter,” if it’s checked.
  • Click OK to save the event handler for the Account field’s onChange event.

5. Save and Publish the Form:

  • Save the changes made in the form editor.
  • Click Publish to make the changes live in Dynamics 365.

Testing the onChange Event

Now, your JavaScript function will be triggered both when the Opportunity form loads and when the Account field is changed.

Test the Functionality:

  1. Open an Opportunity in Dynamics 365.
  2. Change the Account value, and ensure that the script compares the Opportunity currency with the new Account currency.
  3. If the currencies don’t match, the Opportunity currency should update automatically.

Have any questions on this script, or happy that it worked for you? Leave a comment below!

AccessOrange is a leading Microsoft Partner that specializes in Microsoft 365, Dynamics 365 and Azure to help our customers work smarter.
If you need help with building this solution in your own environment, leave your contact information below, and we will contact you as soon as possible.

"*" indicates required fields

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
This field is for validation purposes and should be left unchanged.