I was recently building a Power App for a work project and needed to have a combo box display the primary text field from one data source, while having the secondary text field display from another data source. Here’s how to display 2 data sources in a Power Apps combo box:
We will be using the AddColumns and LookUp functions to essentially find a record that matches our criteria, while joining two tables together.
According to the documentation, here are the descriptions for each function respectively:
The AddColumns function adds a column to a table, and a formula defines the values in that column. Existing columns remain unmodified.
The formula is evaluated for each record of the table.
The LookUp function finds the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria.
To break it down: AddColumns(Table1, “New Column Name”, LookUp(Table2, ‘Column We Want to Look Up’ = Table2[@Table We Have to Compare Against], ‘Column We Want To Return’))
In the example below, I’m adding a column named “Account Name” to the Projects table. In order to find my “Account Name”, I am using the LookUp function to search the Accounts table for a record that matches “Account ID = Projects[@Account]”. The last argument of LookUp means I only want to return the “Account Name” column from the Accounts table.
There are three main parts to creating a resume builder in Power Apps. You will be learning PowerApps, SharePoint, and Power Automate (formerly Flow) which allow the user to create, edit, clone and delete resumes, as well as email a resume in PDF format. For the PDF version of the resume, you will need to know some HTML because we will use Power Automate to convert our data into HTML then to PDF.
The final product looks something like this.
Before we begin, I want to stress the importance of naming elements and organization. I prefer to use camel case for most of my elements. This ensures you can reference your code as easily as possible, so please come up with a naming convention and stick to it!
Part 1: SharePoint
First, let’s create a new list in SharePoint to use for storing the resume data. I named my list “Resumes” and added some basic columns to start.
Once you have a SharePoint list created, you are ready to move on to Power Apps!
Part 2: Power Apps
Create a new Power Apps “canvas app from blank”.
Your first screen should be added for you already. This page will consist of a gallery, some text labels, text inputs, buttons, and images. For the main elements, I have some text, a search box with filter icon, company logo and a button to create a new resume.
The “Create New Resume” button’s “OnSelect” property sets a global variable named “NewResume” to true, creates a new form called “ResumeForm”, and navigates to the Edit Screen. We will get to this further in the tutorial.
For the sort button next to the search box, we simply toggle a variable named SortDescending1 between true/false. Note that this is not a global variable and will only work on the current screen.
Once you create your gallery, it will ask for a data source. This will be the SharePoint list you created in step 1. For my gallery, I chose to create a 4-column layout consisting of an image, three text labels and three buttons.
User Image
For our user image, we will be using the Office365Users connector. The “Image” property of our image will find the user by email and if the user has a photo, display it…otherwise show a default image.
Below is the “Text” property of the two text labels, respectively. “Title” is a default column, so I will be using it as the resume title. Even though my example image above shows the users name, this is really the title column. This ensures that when we clone resumes, we can set a title and distinguish between resumes.
ThisItem.Title
ThisItem.'Job Title'
Update Button
The following code is for the “OnSelect” property. If NewResume is true (i.e. we clicked the “Create New Resume” button), then set NewResume to false, then reset the form and navigate to the Edit Screen.
This completes the main screen. As mentioned before, I like to name files descriptively, so that referencing them is easier. An example is shown below.
Edit Screen
Now we move on to our edit screen (which also acts as our create screen). This screen consists of a text label, four buttons (1 hidden), and a form.
The “Create Resume” label at the top will dynamically change based on if we are editing or creating a resume. Set the “Text” property to change the text based on the boolean value of NewResume.
If(NewResume, "Create Resume", "Edit Resume")
Now create a new form and set the DataSource to your SharePoint list. You can pull in all the fields you previously created and choose which to include in your form. For any multi-text fields, you will want to edit the field and select “Edit rich text” as the control type.
Buttons
There is much to look at here, so I highly suggest reading the documentation to learn more about these functions. The Save/Send button will do the following: 1) Patch to update record data in our SharePoint list 2) Set to set the value of a global variable named “LastRecord” (which gets the ID of the last record entered so that we can send the ID to Power Automate) 3) Run the “SendResume” Flow using the last record ID 4) Reset the form if creating a new resume 5) Navigate back to the main screen and refresh the data.
The cancel button is simple, as it just navigates back to main screen.
Navigate(MainScreen)
For the hidden delete button, we want it to navigate to the main screen and refresh the data set after removing the current record from the SharePoint list.
We are now ready to setup our flows inside of Power Automate. Start by clicking on the “Action” tab at the top of Power Apps, then Power Automate, then “Create a new flow”. Follow along in the next part.
Part 3: Power Automate
We will be creating two separate flows: one for sending/emailing our resume and the other for cloning our resume.
Flow #1: Send Resume
Here is a quick overview. We previously set the trigger inside Power Apps. The flow then gets the fields from our SharePoint list, as well as the user profile photo. It sends this data to a string variable (in which I have three). A plugin, Encodian, will also be used to correctly convert HTML to PDF, as the default provided by OneDrive doesn’t work the way we want. Afterwards, it stores that PDF on SharePoint so that we can view it later, and finally emails it to the user.
Get SharePoint List Fields (Get item)
Get User Photo
Convert User Photo (Base64) (Initialize variable)
Header Image (if you have one) (Initialize variable)
Create HTML (Initialize variable)
After pasting your HTML code, you can use the user profile photo or header image like so (CSS and HTML examples)
Here is another overview. We set the trigger in Power Apps, get the SharePoint fields, duplicate the record, and report back to Power Apps.
Get item
Create item
Make sure to fill in any fields you might want to duplicate. Then change the Title field to append ” – Copy” to the end.
Final Notes
Back in Power Apps, edit the “OnSelect” property of the Clone button, replacing “NameofYourFlow”.
NameofYourFlow.Run(ThisItem.ID); Refresh(Resumes)
And finally, edit the View button to launch the web URL of your SharePoint file that was created, making sure to include your naming convention so it knows which resume to point to.
Creating email templates are often a time-consuming task, especially if these templates need to have the same overall look but be repeated with different content for each recipient. When you consider the number of variables and data that change, the task can seem a bit daunting without some kind of automation. In this article, we show you how to create dynamic email templates using MySQL and the popular Handlebars.js templating engine. If the data already exists…there is no reason to recreate it. Instead, we can utilize a database to pull in the data we need, and Handlebars to populate our fields dynamically.
To begin, you can learn more about Handlebar.js and how it’s implemented here. Let’s start by grabbing the data we need in our MySQL database and outputting to JSON (JavaScript Object Notation) format. This is the format that your data must be in for Handlebars to accept it.
We will be creating 4 files for this project:
index.php (Main file)
data.json (Generated JSON file)
config.js (Configuration file)
template.html (Template file)
Procedural Method
index.php
// Connect to database
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';
$conn = mysqli_connect($host,$user,$pass,$db);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Get event data
$events = mysqli_query($conn,"SELECT * FROM events");
// Initiate empty array
$data_array = array();
// Loop through event data
while($row = mysqli_fetch_assoc($events)) {
$data_array["event"] = $row;
}
// Output JSON to file
$fp = fopen('data.json', 'w');
fwrite($fp, json_encode($data_array));
fclose($fp);
mysqli_close($conn);
Object-Oriented Method
index.php
// Connect to database
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';
$conn = new mysqli($host, $user, $pass, $db);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $con->connect_error);
}
// Get event data
$sql = "SELECT * FROM events";
// Return results from query
$events = $conn->query($sql);
// Set empty array
$data_array = array();
// Set counter
$counter = 0;
$counter2 = 0;
// Loop through event data
while($row = $events->fetch_assoc()) {
$data_array["event"] = $row;
}
// Output JSON to file
$fp = fopen('data.json', 'w');
fwrite($fp, json_encode($data_array));
fclose($fp);
$conn->close();
Here is an example of what our JSON file (data.json) might look like:
The above expression {{event.date}} would return “January 1, 2019” from our JSON file.
That’s about all there is to it! Now you can pull dynamic data from a MySQL database and insert those variables into your template.html file. I highly recommend reading more about the features of Handlebars.js and what it’s capable of doing. If you have any questions, please feel free to leave them in the comments!
