Set up Microsoft Power Automate with Sage 200cloud

This feature is only available from Sage 200cloud Professional Winter 2018 version onwards.

Microsoft Power Automate (formerly called Microsoft Flow) is a cloud-based service that makes it practical and simple for people to build workflows that automate business tasks and processes across applications and services.

You can set up Power Automate with Sage 200cloud to use our supplied flows, and create your own flows.

We provide some example flows, such as:

  • Purchase requisition flow for email notifications.

  • Supplier bank details flow for email notifications.

Note: We also provide some additional example flows. See Sage200cloud - Flow Demonstration Application (Ask Sage article 44411).

What do I need to use Power Automate?

To use Microsoft Power Automate, you'll need:

  • A Microsoft 365 subscription: Microsoft 365 Business Standard (formerly Office 365 Business Premium), Enterprise (E1, E3 or E5).
  • Your Microsoft 365 subscription must include Azure Active Directory Premium P1/P2.
  • A Power Automate Per user plan.

    To choose the Microsoft Power Automate plan that's right for you, see Power Automate plans and pricing.

How to connect Sage 200cloud and Power Automate

Follow the steps below to set up Sage 200cloud with Power Automate:

  • Connect Sage 200cloud and Microsoft 365 to use Power Automate.
  • Create an Azure Active Directory (AAD) application.
  • Create the Sage 200 Custom Connector.
  • Set up the provided flows for email notifications.

Connect Sage 200cloud and Microsoft 365

You will need to connect Sage 200cloud and Microsoft 365 to use Power Automate.

You may have already connected Sage 200cloud with Microsoft 365, if you have set up Sage Contact, Power BI, or the API.

Create an Azure Active Directory (AAD) application

First you need to create an Azure Active Directory application.

  1. Go to portal.azure.com and sign in using your Microsoft 365 account.
  2. Select Azure Active Directory.
  3. Select App registrations.
  4. Select New registration.

    1. Set the Name for the application, for example Sage 200 application.
    2. Leave the supported account types set to Accounts in the organizational directory only.
    3. Leave the Redirect URI blank, as you'll enter that later when you create a Custom Connector.
    4. Select Register.

      Once the application has registered, you'll see an overview with details of the application.

  5. Copy the Application (client) ID to the clipboard and keep this somewhere. You'll need this later when you create the Custom Connector.
  6. Select Certificates & secrets.

    1. Select New client secret.
    2. Enter a Description, such as Sage 200 client key.
    3. Set Expires to Never.
    4. Select Add.

      A new client secret will be displayed.

    5. Copy the Value of the new client secret to the clipboard, and save this somewhere. You'll need this later.

      You must keep a note of the client secret Value now, as you will not be able to return to this page once you leave.

  7. Select API permissions.

    1. Select Add a permission.
    2. Select APIs my organization uses.

    3. Select Sage 200c API from the list.

      Tip: To find this, type sage in the Search box to filter the list.

    4. In Delegated permissions, select (tick) all the permissions.

      Note: The permissions in each group may not be visible, so to display them you must open each group, or select Expand all.

    5. Select Add permissions.

Download the custom connector and flows

This feature is only available from Sage 200cloud Professional Summer 2019 version onwards.

You will need to download the files required to set up your custom connector and flows.

  1. Download the Custom connector and flows.

  2. Open and unzip the *.zip file.

    This contains:

    Custom Connectors\Sage 200.PowerApps.swagger.json OpenAPI (Swagger) file that you'll use to create the custom connector.
    Purchase Requisition Email Flow\PurchaseRequisitionAuthorisationFlow.zip Used to set up the purchase requisitions flow (email notifications).
    Supplier Bank Email Flow\SupplierBankDetailsFlow.zip Used to set up the supplier bank details flow (email notifications).

Create the Sage 200 Custom Connector

You will need to create a Custom Connector for Microsoft Power Automate to access your data in Sage 200cloud.

To create a Custom Connector, you must have first created your Azure Active Directory (AAD) application.

Note: If you have already created a Custom Connector for Sage 200cloud before the 2020 R1 release, you will need to update your Custom Connector first before you create new flows. See Update an existing Custom Connector.

Get your subscription key

To set up the Sage 200 Custom Connector you will need your subscription key (also known as your primary key for the Sage API).

Create your Custom Connector

  1. Go to flow.microsoft.com and sign in using your Microsoft 365 account.

    Note: The instructions here will show you how to create a Custom Connector in Microsoft Power Automate, but you can also create it in Power Apps.

  2. Select Data > Custom connectors.
  3. Select New Custom Connector > Import an OpenAPI file.
    1. Enter the Connector name, for example Sage 200 Connector.
    2. Select Import.
    3. Select the file Sage 200.PowerApps.swagger.json that you downloaded, then select Open.
    4. Select Continue to create the connector.
  4. Once the connector has been created, enter the connector's settings.

    1. On the General tab:

      1. Do not select Connect via on-premises data gateway (this is not selected by default).
      2. Set the Scheme to HTTPS.
      3. Set the Host to api.columbus.sage.com.

      4. Set the Base URL to /uk/sage200extra/accounts/v1.
    2. Move to the Security tab:

      1. Set the Authentication type to OAuth 2.0.
      2. Set the Identity provider to Azure Active Directory.
      3. Set the Client ID to the Application (client) ID for your application.

        You will have copied this earlier when you created the Azure Active Directory application.

      4. Set the Client secret to the client secret value for your application.

        You will have copied this earlier when you created the Azure Active Directory application.

      5. Set the Resource URL to https://SageCSPEU.onmicrosoft.com/6ce3bbc5-d381-4d11-b079-50accd62241a.

      6. Do not change the Login URL, Tenant ID, and Scope.

    3. Select Create connector.

      It might take a minute for the connector to be created.

    4. Once the connector has been created, copy the Redirect URL to the clipboard.

      You'll need to enter this in your Azure Active Directory application.

  5. Go to portal.azure.com and sign in using your Microsoft 365 account.

    1. Select Azure Active Directory.
    2. Select App registrations.
    3. Select the application that you created.
    4. Select Authentication.
    5. Enter the Redirect URI.

      1. Set the Type to Web.
      2. Set the Redirect URI to the Redirect URL that you copied from your Custom Connector.
      3. Select Save.
  6. Go back to your Custom Connector at flow.microsoft.com.

    1. Move to the Test tab.
    2. Select New Connection (in the Connections section).
    3. Select Create to create the connection.

      • You may asked to sign in with your Microsoft 365 account, and Accept the permissions requested.
      • The new connection to the Custom Connector will be displayed in the Connections list.
    4. Select Custom Connectors.
    5. Select Edit (pencil icon) for your Custom Connector.
    6. Move to the Test tab.
    7. Select GetSites from the list of Operations.
    8. Set ocp-apim-subscription-key to your subscription key for the Sage API.
    9. Select Test operation.

      It might take a minute for the test to complete.

    10. Check the test Response:

      • The Status will show OK (200) if the test was successful.

      • The Body will show details of your companies in Sage 200cloud.

        Copy the company_id and site_id for each of your companies. You'll need these details later when you set up a flow.

        Note:

        If the status is OK (200) but you don't see any company details in the Body, this might be because:

        • Your Sage 200cloud server is not running.
        • You haven't yet set up Sage 200cloud to connect with Microsoft 365, and assigned your Microsoft account email address with a user account in Sage 200cloud.
    11. Select Close.

Share your Custom Connector

If other people in your organisation will also be setting up Power Automate or Power Apps, you can share your Custom Connector with them.

You can skip this step if you don't want to share the Custom Connector, or want to do this later.

Update an existing Custom Connector

If you have already created a Custom Connector for Sage 200cloud before the 2020 R1 release, you will need to update your Custom Connector first before you create new flows. See Update an existing Custom Connector.

Your existing flows will continue to work with the updated Custom Connector, so you don't need to update your flows too.

Set up the provided flows

This feature is only available from Sage 200cloud Professional Summer 2019 version onwards.

We provide some example flows for email notifications:

  • Purchase requisition flow for email notifications.
  • Supplier bank details flow for email notifications.

Note: To set up these flows you will need your subscription key for the Sage API, and the site_id and company_id for each company. You can find your site_id and company_id when you run the GetSites test with your Custom Connector.

Note: We also provide some additional example flows. See Sage200cloud - Flow Demonstration Application (Ask Sage article 44411).

Purchase requisitions flow (email notifications)

The purchase requisitions flow automatically sends emails to people to notify them during the authorisation process.

  • Authorisers will receive email notifications when they have new purchase requisitions that require authorisation.
  • The person who submitted the purchase requisition will receive an email notification when it is authorised or rejected.

This flow will run once per day and will send email notifications emails at 4pm (16:00), but you can configure the flow to change this frequency and timing.

Note: If you use multiple companies, the flow must be set up for each company.

Set up the flow

  1. Go to flow.microsoft.com and sign in using your Microsoft 365 account.
  2. If you have more than one environment, check you're using the correct environment.
  3. Select My flows.
  4. Select Import.
  5. Select Upload, then select the PurchaseRequisitionAuthorisationFlow.zip file that you downloaded.

  6. Set the import options in Review Package Content.

    1. Purchase Requisition Authorisation Flow - Flow:

      1. Select the Action icon.
      2. Set Setup to Create as new.
      3. Select Save.
    2. Sage 200 Custom Connector - Connector:

      1. Select the Action icon.
      2. Select the name of your Custom Connector from the list (so it is ticked).
      3. Select Save.
    3. (email) - Sage 200 Custom Connector Connection:

      1. Select the Action icon.
      2. Select the email address of your Microsoft account from the list (so it is ticked).
      3. Select Save.
    4. (email) - Office 365 Outlook Connection:

      1. Select the Action icon.
      2. Select Create new. This will open a new page with a list of connections.
      3. Select New connection.
      4. Select Office 365 Outlook from the list, then select Create.

        Tip: To find this, type off in the Search box to filter the list.

      5. Go back to your flow, and select Refresh list to display the new connection.
      6. Select the connection from the list (so it is ticked).
      7. Select Save.
    5. Notifications - Notifications Connection: Select the Action icon, then select the email address for your Microsoft account from the list (so it is ticked). Select Save.

      1. Select the Action icon.
      2. Select Create new. This will open a new page with a list of connections.
      3. Select New connection.
      4. Select Notifications from the list, then select Create.

        Tip: To find this, type not in the Search box to filter the list.

      5. Go back to your flow, and select Refresh list to display the new connection.
      6. Select the connection from the list (so it is ticked).
      7. Select Save.
  7. Once you've set up each import option, select Import to create the new flow.

    Note: You can only do this once each import option is complete.

  8. Select My flows. The Purchase Requisition Authorisation Flow is displayed.

    Tip: If you can't see your flow, try refreshing your browser.

  9. Select Edit (pencil icon) for the new Purchase Requisition Authorisation Flow.
    1. If you want to change the frequency of the flow to send email notifications, select Trigger. By default, the flow runs once per day at 4pm (16:00).
    2. Select Initialize SubscriptionKey, then set the Value to your subscription key for the Sage API.

    3. Select Initialize SiteID, then set the Value to the site_id for the Sage 200cloud company.

    4. Select Initialize CompanyID, then set the Value to the company_id for the Sage 200cloud company.

    5. Once you’ve entered the properties, select Save.

  10. Check that your flow works:

    1. Go to My flows, and select Run for the flow.
    2. Select Run flow to start the flow.
    3. Once the flow is running, you can check the Run history for flow.

      If the flow is working, its Status will show Succeeded when it is complete.

      Tip: Try refreshing your browser to update the status.

Set up email addresses for users

Once you've set up the flow, enable notifications for each user:

Open: System Administration.

  1. Select Users.
  2. For each user, double click the user's System name name, or right-click the user and choose Properties.
  3. Enable Receive Notifications by Email.
  4. Move to the Details tab, then enter the user's Email address to receive notifications.

Supplier bank details flow (email notifications)

You can send email notifications for changes to supplier bank details by using the supplier bank details flow.

The supplier bank details flow automatically sends emails to people to notify them of any changes to the bank details for supplier accounts. The emails are sent to everyone that has user access to the Audit Log, so that they can check the changes in the log.

This flow will run at 15 minute intervals each day, but you can configure the flow to change this frequency and timing.

Note: If you use multiple companies, the flow must be set up for each company.

Set up the flow

  1. Go to flow.microsoft.com and sign in using your Microsoft 365 account.
  2. If you have more than one environment, check you're using the correct environment.
  3. Select My flows.
  4. Select Import.
  5. Select Upload, then select the SupplierBankDetailsFlow.zip file that you downloaded.

  6. Set the import options in Review Package Content.

    1. SupplierBankDetailsFlow - Flow:

      1. Select the Action icon.
      2. Set Setup to Create as new.
      3. Select Save.
    2. Sage 200 Custom Connector - Connector:

      1. Select the Action icon.
      2. Select the name of your Custom Connector from the list (so it is ticked).
      3. Select Save.
    3. (email) - Sage 200 Custom Connector Connection:

      1. Select the Action icon.
      2. Select the email address of your Microsoft account from the list (so it is ticked).
      3. Select Save.
    4. (email) - Office 365 Outlook Connection:

      1. Select the Action icon.
      2. Select your Office 365 Outlook connection (if you've previously created a flow), or click Create new to add the connection.
      3. Select Save.
    5. Notifications - Notifications Connection: Select the Action icon, then select the email address for your Microsoft account from the list (so it is ticked). Select Save.

      1. Select the Action icon.
      2. Select your Notifications connection (if you've previously created a flow), or click Create new to add the connection.
      3. Select Save.
  7. Once you've set up each import option, select Import to create the new flow.

    Note: You can only do this once each import option is complete.

  8. Select My flows. The Supplier Bank Details Flow is displayed.

    Tip: If you can't see your flow, try refreshing your browser.

  9. Select Edit (pencil icon) for the new Supplier Bank Details Flow.

    1. If you want to change the frequency of the flow to send email notifications, select Recurrence. By default, the flow runs every 15 minutes.
    2. Select Initialize SubscriptionKey, then set the Value to your subscription key for the Sage API.

    3. Select Initialize SiteID, then set the Value to the site_id for the Sage 200cloud company.

    4. Select Initialize CompanyID, then set the Value to the company_id for the Sage 200cloud company.

    5. Once you’ve entered the properties, select Save.

  10. Check that your flow works:

    1. Go to My flows, and select Run for the flow.
    2. Select Run flow to start the flow.
    3. Once the flow is running, you can check the Run history for flow.

      If the flow is working, its Status will show Succeeded when it is complete.

      Tip: Try refreshing your browser to update the status.

You'll also need to set up email addresses for all users that will receive notifications. See Set up email addresses for users.