Populating a Word document with Microsoft Power Automate

Dynamics 365 has long had the ability to populate Word templates, but not without limitations. Now with Power Automate, there is an easier way.

Microsoft Power Automate has a huge range of functionality and with the help of the native Word connector, it is possible to take a prepared Word document and populate it with data from Microsoft Dynamics 365. The main advantage of this method over using traditional Dynamics 365 templates is that the data can come from any tables that you reference in your flow and can also be combined with additional data along the way.

This tip has steps that are required to create your customized Word document and create a multi-page report using Dynamics 365 data.

A one-time step that needs to be carried out is to enable the Developer tab in Word’s ribbon.

1.   Go to the File Tab and select Options/Custom Ribbon, choose the Developer Option and Click OK.  Your window should look as shown:

Next start a new Word document and format as required. In this example, we’re creating a simple table of contact information:

First Name	Last Name	Email Address	Address	City	State

 For this tip we will have repeating rows to populate the table within the document, so you only need to create one blank row for now

For each field you want to populate, you will need to add a placeholder reference via the Developer tab as shown in the following figure.

1 – Developer tab

2 – References

3 – Design Mode

1.     Click the Insert Text Control button

2.     Enter a name of your choosing in the ‘Title’ box and click OK. Keep this simple – lower case with no spaces.

3.   When  all the fields are in place and with their titles set, you will need to select the entire row and add a Repeating Section Content Control, as seen below:

Now that the Word document is ready, you need to upload it so that it can be accessed by Power Automate. The simplest place for this would be OneDrive for Business, but SharePoint is another straightforward option as well.

You are now ready to create the flow to populate your template with data from Dynamics 365. Head over to Power Automate begin creating your flow, either from the Flows option in the menu or creating the Flow.  In this tip, you have kept things simple, with the flow being triggered manually and listing all contacts:

The key part to note is the initialization of an empty array that we will later be populating with Dynamics data.

Next, add an Append To Array Variable action, select your array variable initialized earlier and enter your text as shown:

You will note that as soon as you add the first field (First Name in our example), an Apply To Each loop is automatically created for you; this enables your Flow to automatically loop through all of the records found in your List Records action. Ensure that ‘Value’ text is entered cautiously in the same format as above or you will get an error message.

Now that you have prepared your data, you are now ready to populate your uploaded Word Document. When you add a Populate a Microsoft Word Template action, you will be prompted to browse for the location of the file and once selected, the references you added earlier will automatically be displayed:

Although each individual reference is displayed, by clicking the Switch To Input Entire Array button, you can populate the entire row at once using your array variable:

With the Word document now containing data, it’s ready to be used; in this case, you are just sending a simple email with the Word file as an attachment:

And as you can see, the received document is now populated with all the requested Contact data:

  

First Name	Last Name	Email Address	Address	City	State
Carol	Ray-Holmes	Carol.rayholmes@ladyraycomputer.net	1234 Anywhere St	Sacramento	CA
Joe	Jones	Joe.jones@sampleemail.com	3949 Freed Street	Cleveland	OH
Jean	Stinnett	Jean.stinnett@sampleemail.com	4848 St John Road	Atlanta	GA
Remme	Smith	Remme.smith@sampleemail.com	2920 Steward Rd	Houston	TX

Now with a  few simple steps, Word documents can be created that can be populated with your Dynamics 365 data. Once a Word file has been created, edits to this file can easily be made and the original file overwritten, making it much easier to maintain than a traditional Dynamics 365 template.

For more tips and tricks follow updates, workshops and consulting services at www.ladyraycomputer.net

 

Using Data Validation and VLOOKUP in Excel TIP

 

Doesn’t it get redundant when you use VLOOKUP function in Excel with a large data table  and you have to type in the Lookup value each time you need a new one to look up? 

Try using Data Validation List option in Excel along with your VLOOKUP function.

For this tip I am using a sample data table that lists SKU data information.  Let’s say this is a very large table and I need to look up SKU specific number information. 

The headings from the table has been typed starting with cell I2:M2.

1.      In cell I2, I typed  the function =VLOOKUP(Table1[@SKU],Table1[#All],1)

2.      Select the Data tab and choose the Data Validation option. Select Data Validation from the list.

You should see the following as shown in this window:

3.      Choose List from the Allow drop down menu.  Select the SKU list A2:A19.

4.      Now you can repeat the VLOOKUP formula for each column heading.  With the drop down you can now select the SKU number and populate all the columns with the correct data from your lookup table.

Using Microsoft 365's To Do App with Flagged Emails from Outlook

If you have trouble setting and reaching goals, making decisions about what to focus this  digital tool can keep you productive and motivated.

Use a Microsoft Office 356's to-do list to create and store all your individual tasks in one place—in essence using it as a smart daily planner. Create your tasks and sub-tasks in the app and make them meaningful by adding descriptions, photos, and files for reference. Simply open the app at the beginning of your day and it will help you map out everything that must be completed. Over time, this app will help you track your existing behavior patterns to create more productive habits, providing personalized suggestions on the most critical priorities for the day. Set up reminders for certain tasks, which will keep you accountable and more intentional about prioritizing your workflow. As well as adding flagged emails to your to do list.

To manage your flagged email directly in Microsoft To Do, sign in with the same work, school, or personal Microsoft account that you use for email.  

Note: This feature is only available if you’re using an account that’s hosted by Microsoft, such as an Outlook.com, Hotmail.com, or Live.com account. It’s also available if you’re using an account hosted by Microsoft but using a custom domain. 

1. To see your flagged email tasks, navigate to the list menu, then select Flagged email > Create. 
2. Alternatively, you can turn the list on in Settings. 

Once turned on, email flagged in Outlook will appear as tasks in Microsoft To Do. The task's name will be the subject of the flagged message and will include a preview of the email's text in its detail view. To open the original email, select the option to Open in Outlook from the task's detail view.  

Items in the flagged email list can be renamed, assigned due dates and reminders, added to My Day, and marked as important. 

Note: The flagged email list only shows tasks from messages flagged in the last 30 days. It’ll show a maximum of 100 of your most recently flagged messages.

Flagged calendar events won’t be converted to tasks in To Do and won’t appear in the flagged email list. 

Good Luck! 

 

Directly Create Drop Down List In A Word Document

Please do as follows to create drop down lists in a Word document.

1. In the Word document you want to insert drop down list, click File > Options.

2. In the Word options window, you need to finish the below settings.

• Click Customize Ribbon in the left pane;
•  Select Commands Not in the Ribbon from the Choose commands from drop-down list;
• In the right main tabs box, select a tab name (here I select the Insert tab), click New Group button to create a new group under the Insert tab;
•  Find and the Insert Form Field command in the commands box;
• Click the Add button to add this command to the new group;
•  Find the Lock command in the commands box;
• Click the Add button to add this command to the new group too;
• Click the OK button. See screenshot:

Now the specified commands are added to a new group under the certain tab.

3. Place the cursor to where you want to insert drop down list, and click Form Field button.

4. In the Form Field dialog box, select the Drop-down option and then click OK.

5. Then a form field is inserted into the document, please double click it.

6. In the Drop-Down Form Field Options dialog box, you need to:

• 6.1) Enter a drop down item into the Drop-down item box;
• 6.2) Click the Add button;
• 6.3) Repeat these two steps until all drop down items are added into the Items in drop-down list box;
• 6.4) Check the Drop-down enabled box;
• 6.5 Click the OK button.

7. Click the Lock command to enable it. Then you can choose item from the drop down list now.

8. After finish selecting, please turn off the Lock command in order to make the whole document editable.

Note: Every time you want to choose item from the drop-down list, you need to turn on the Lock command.

 

Setting up Calendars in Microsoft Project

Why it is so Important to Set up Calendars First in Microsoft Project

Although Microsoft Project’s base calendars are an excellent starting point, you will likely need to make customizations for your project. Luckily, the Change Working Time dialog box provides a central location to modify the calendar, set holidays, and more.

Understanding the calendars

There are three different calendars in MS Project

1. Project calendar - defines default work days and hours
2. Task calendar - used when the timing of a task must be different than the project calendar allows
3. Resource calendar - defines when resource is available

MS Project has rules how the calendars interact:

• if a task has no resources and no task calendar, it will use the working days and times in the project calendar
• if a task has a resource, it will use the working days and times fro the resource calendar for that task's resource(s)
• if a task has a task calendar, it will use the working days and times from that task calendar
• if a task has a resource AND a task calendar, it will use working days and hours the task and resource calendars have in common (unless the Ignore resource calendars option has been set for the task)

If the working day is adjusted on the standard calendar, be sure to adjust how many hours is in a day in the calculation MS Project uses.

Define the Project Start Date

When you create a new project, Microsoft Project inserts the current date as the start date by default. If your project is going to start on a different date, you should change it .

You can define a project finish date instead of a start date. This is useful if your project has a stringent finish date; however, You should use the default start date if you are new to Microsoft Project. Refer to figures I and II below, which illustrates changing a project’s start date.

Figure I

Figure II

Define Your Project Calendar

All tasks and resources in Microsoft Project follow a calendar. A Microsoft Project calendar can be used for specifying holidays, as well as working time.

Microsoft Project includes three base calendars by default:

1. Standard: Defines working time between 8 AM and 5 PM, with a one-hour break at 12 PM.
2. 24 Hours: Defines working time between 12 AM of first day and 12 AM of next day, with no breaks.
3. Night Shift: Defines working time between 11 PM and 8 AM, with a one-hour break at 3 AM.

You should define your own project calendars, although you can base them on one of the above provided calendars. Include your organization’s holidays and working time in your calendar(s) and assign them accordingly to project resources.

Refer to the figures III and IV below, which show how to define a project calendar.

Figure III

Figure IV

It is a good idea to take the Microsoft Project classes at Lady Ray Computer Services LLC.  Please check out the schedule for the Microsoft Project at our website, www.ladyraycomputer.net.

 

For more information, please contact us at contact@ladyraycomputer.net