Streamlining Qualification: Creating TradeTapp Workflows Using Power Automate

BRETT JACKSON: Hello, and welcome to the AU 2024 session, "Streamlining Prequal using TradeTapp and Power Automate." My name is Brett Jackson, and I'm a data engineer at Fortis Construction.

In this technical instruction, we'll be using primarily two services-- TradeTapp and Power Automate. TradeTapp is Autodesk's pre-qualification tool, which is part of the BuildingConnected service. Vendors and trade partners submit their company details and financial information securely through TradeTapp, which TradeTapp can process automatically for a contractor to determine project eligibility based on specified risk criteria.

Power Automate is a low-code platform for creating automated workflows. It can perform many actions such as parsing email attachments, notifying team members, or as I'll go over today, making API calls to retrieve and write data. So why are we using these together?

Well, TradeTapp manages the pre-qualification process, but if we want to extend that functionality, like automatically creating a vendor profile in an ERP or database once a vendor is qualified, we need to use TradeTapp's API to retrieve data. Power Automate simplifies this by enabling us to create the connection and perform the necessary actions to complete the workflow. By extending TradeTapp's functionality with Power Automate, we can streamline the entire pre-qualification process for a team, saving time and effort.

And here's what we'll create step by step. This is a Power Automate flow designed to retrieve qualification data from TradeTapp's API and write it to a database. So from top to bottom, we start by defining the trigger and any necessary variables. Then we use a loop to fetch qualification data from TradeTapp. After formatting the data as needed, we create or update rows in SQL, based on whether the data is new or existing.

So on the agenda, we'll start by creating an app in Autodesk Platform Services to obtain API credentials. Next, we'll use those credentials to configure a custom connector in Power Automate. Then we'll design a flow in Power Automate, which uses our custom connector to capture TradeTapp data. From there, we'll write the data to our destination database. We'll complete the flow by adding a step to update data as any changes occur. Lastly, I'll recap the process and discuss resources related to the solution. So let's get started.

So I have two open browser windows here. On the left, I have my TradeTapp account. I have Autodesk Platform Services ready to create a new application. I have documentation for our Autodesk Platform Services, otherwise known as APS authentication process. And I have documentation for TradeTapp's API. And on the right, I have Power Automate.

So we'll start by creating a new app in Autodesk Platform Services. We'll call that TradeTapp demo. We're going to keep the traditional web app and hit Create. Apps is then going to issue us a client ID and client secret to use for our connector. So this is where we'll go to Power Automate. We'll open the side menu here and click Custom connectors. We're going to create a new custom connector from blank. That connector is going to be called TradeTapp demo as well.

I'll hit Continue. So on the first page here, we need to define where Power Automate will connect for each action. So we'll enter a host and base URL from our documentation. So we'll go to the qualification section. And what we're looking for here-- sorry, what we're looking at here is the qualification's action. What this does is it retrieves a list of qualifications data from TradeTapp when we call it. So we're going to go ahead and copy the host here, which is everything up to the end of the dotcom section. We'll paste it in the host part of Power Automate. And then we're also going to copy everything after that up until our actual qualifications endpoint. And we'll paste that in the base URL section.

Next, we'll go ahead and define the security. For apps authentication, we're going to use [? OAuth, too. ?] So we're going to go back to our new app that we just created, and we're going to copy over our client ID and client secret. So I'm going to click to copy here and paste it in the client ID section, and I'll do the same with the client secret.

Now what we're setting up here is a three-legged authentication process. It's called this because it involves three parties-- the client, which is Power Automate; the server, which is Autodesk APS; and the user, which is me. It is used when clients need to access resources on behalf of the user-- in this case, Power Automate.

So next, we need to add our authorization URL. So we get that from our authentication documentation. So the URL we're going to copy from our authorization section here. So go ahead and copy that and paste it over. So the authorization URL is where the user is directed to log in and grant permissions to the application to access their data. Oh, a typo here.

So next, we need to grab the token. So after the user grants permission, the application sends a request to this URL to get an access token. The token URL is this. We're going to paste it in our token URL section in Power Automate. The access token is like a key that will allow Power Automate to access our TradeTapp data.

We also need to copy over the token URL in the refresh URL section. So our TradeTapp access tokens will expire after one hour. When they do, Power Automate will use our refresh token URL to get a new access token. And it lets us fetch data without needing to sign in again.

So one more thing that we need to define before we save this connector is our scope. So the scope for qualifications is [? data ?] [? colon ?] [? read. ?] So what this means is we can only read data from the qualification endpoint. So we'll go ahead and paste that here.

If we were going to perform multiple scopes like [? data ?] [? write, ?] we would just add a space and add any other scopes we needed here. But for the sake of this demo, we can only call the qualifications endpoint by reading data. So we'll go ahead and hit Create connector here in Power Automate, and we'll jump back to our app in APS.

So what Power Automate is going to make for us is a redirect URL. This redirect URL is unique to our Power Automate connector. So we're going to click to copy that, and we're going to paste it in the URL section of our TradeTapp APS app, and hit Save changes. So the redirect URL is the location where the authorization server sends Power Automate the access token.

So next, we're going to move on to the definition section. In the definition section, we're going to click New action. And this is where we're going to use our documentation for TradeTapp for our qualifications endpoint that we're going to connect to. So for summary description and operation ID, we're just going to paste qualifications in here. These don't have to be very descriptive, and they're just for future reference when you're looking through this connector.

The request section is actually where we define the API call. So we'll go ahead and click Import from sample, and our verb is going to be [? get. ?] And we'll paste qualifications into the URL section here. There's a couple query string parameters we'll want to add. So we need to add cursor state and limit. To do that, we add a question mark and add in cursor state. And then we use an ampersand and limit.

If we wanted to add multiple query parameters, we would just continue to add an ampersand between them. So there's one more step that I need to do for the sake of the demo that lets us connect to our sample demo trade type account, and that's adding a custom header parameter. So I'll go ahead and click Import here. And in our custom parameter, I'm going to edit this to add a default value. This ensures that when we use this connector, we're only reaching our demo trade type account.

All right. Now that this is loaded, we're going to click back, and the next thing we want to do is look at the response that we're expecting to see when we call this qualification endpoint. So the type of response that this is, it is a JSON object. And JSON stands for JavaScript Object Notation. It's a lightweight format that's easy for both people and computers to read. Our response is composed of both objects and key value pairs within each object. This is a key value pair. This part is the key, and this part is the value.

All nested values-- all nested objects belong to the object above it. So for example, status, internal single limit-- all of these under here-- summary, denial reason all belong to the qualification object. So what we're going to do is go ahead and copy this response example and add it to the Power Automate response, and we'll pass that into the body section here and click Import.

All right. By copying the response from the documentation, we can tell Power Automate what details it can expect to receive when retrieving qualification data. Doing this allows us to reference any specific object or value later in the process. You can see we're going to have one of these values for each of the objects from our JSON output. So we're going to jump forward to the test section here, and we'll click Update connector to save it.

All right. So now that our connector is saved, we'll go ahead and make a new connection. So this pop-up window is what happens when we call our authentication endpoint. And what this is saying is, by allowing this, I'm saying I want Power Automate to connect on my behalf to TradeTapp and read data. So I'm going to go ahead and say Yes and allow this. So now that our connection is made, we can try calling our newly added qualifications endpoint. So we'll click Test operation.

Awesome. So we have a response here that looks very similar to the sample output response from the documentation. With that, our connector is complete. So now that our connector is set up, we're going to go ahead and create a flow to use it. So I'm going to go to the My flow section of Power Automate. I'm going to click New flow and Instant cloud flow. We'll call this TradeTapp demo. And for our trigger, I'm going to choose to manually trigger it.

All right. So when we first start Power Automate flow, we have a blank canvas here. And to start, we'll add an action. Actually, before I go ahead and add an action, I want to be sure and refresh Power Automate to make sure that our newly-added connector pulls through here.

All right, so you'll create a new flow again. And I'll add an action. And here, I want to select the custom runtime. And this is what we want to see. Our newly created TradeTapp demo connector has pulled through. So we're going to select the command that we set up, the Qualifications command. And we'll go ahead and start by just saving and testing this.

Now, Power Automate flows run in sequence from top to bottom. So when I trigger this, it's going to go ahead and run the qualifications call next. So I'm going to hit Test flow and choose manually and click Test. So the first time I test this, it's just going to confirm that I want to use my TradeTapp demo connector. So I'll say continue and run flow.

All right, let's check the outputs here. So when we make a request to TradeTapp, our results are paginated. What this means is that instead of TradeTapp sending us all records at once, we get them in small chunks, and we need to make subsequent requests to retrieve all the data. In this demo, it's easy to retrieve all the data because we only have 19 vendors in our TradeTapp account. But a business using TradeTapp could have hundreds, if not thousands of vendors.

This is where the query parameters, cursor state, and limit come into play. Both are used in conjunction to enable us to retrieve all data. By default, we return up to 20 records when we make a qualification request. If there are more than 20 records, TradeTapp includes a cursor state token with the results.

This token can be used as a query parameter to retrieve the next set of 20 records. A new cursor state token will be returned with each subsequent set of results until all records have been retrieved. So if we had 100 vendors, using the default limit of 20, we would have to call the endpoint five times to retrieve all our data.

With 19 vendors, by setting our limit to 5, we can force TradeTapp to include a cursor state token. This way, we can test what this process would look like with more data. So I'll go up to the Edit section. And in our Qualifications command, I'll show all our parameters here, and I'll set the limit to 5. I'll click Save and test.

So I'll scroll down to the outputs here. So we can see that this time, when our limit was set to 5, TradeTapp included a cursor state token indicating we have more records available. So to retrieve all our data, we'll design a do until loop. So I'll click Edit, add an action, and search for do until.

We'll also need a variable to store our cursor state token. So I'll add an action, search for variable. And we'll choose initialize variable. We'll call the variable Cursor state. The type is going to be string. And we'll label our steps here.

So we want to set the cursor state variable-- sorry, we want to pass the cursor state variable into our cursor state query parameter. So I'll click this little lightning bolt here to grab our recently-added variable, Cursor state. I'm going to go ahead and drag this qualification step into our do until loop.

Next, inside the loop, we need to add an action to update our variable based on the results from our request. So we'll insert another action here, search for variable again. Click See more, and I'll choose Set variable. So I'm going to choose the Cursor state variable, and the value I want to pass into this is going to be the cursor state token from our qualification request. So I'll go ahead and click here.

Now that we've defined what we want the loop to do, we need to specify how long we want it to run for, and we want the loop to continue running until the cursor state variable is empty. So in our do until action, we're going to add a function here. The function is called Empty. And we can read about the function here. So it returns true if an object array or string is empty. So we'll go ahead and pass in our cursor state variable.

So we want the loop to end when our cursor state variable is empty. So since the Empty function returns true, when that happens, we'll set is equal to and then enter true. So this process will be a little more clear after we run it. So I'll go ahead and save and test.

All right, so our loop ran four times. The first time it ran, we received a cursor state token. That cursor state token was then passed into our variable called cursor state, which is then used on the subsequent loops. So our variable here-- sorry, our token here is then used-- I'll put it in the search bar just so we can keep track of it-- on as the input for the second qualification call.

When we get the results from that qualification call, that is then passed into our cursor state variable. This happens till the fourth step, when the call is made, and we don't receive a cursor state token, indicating there's no more results to retrieve. In that case, the variable cursor state was set to null, which made our do until function result in true, ending the loop.

So even though we have to grab all this qualification data in chunks, we want to combine it into a single array to allow us to write to our database in one step. So we'll click Edit, and we'll create a new variable. This variable is going to be called Qualifications. And the type is going to be array.

Now, each time the do until loop runs, we want to append the results that we get to our array. More specifically, we want to append this Results section, which will have one for each of our 19 vendors. So we're ignoring the top part of the body of the response, and we're just pulling out this Results section. So we do that by adding an apply to each action inside our loop. So we'll search for apply to each, and we'll go ahead and add that.

So in reply to each, we want to pass in the body of our qualification array. But we don't want to include the pagination. So actually, I'm going to make a change here and say-- no, sorry. We want to select the output of the body from our qualifications. So we'll click See more. And I'm going to search for body.

So from each response body, we want to-- we want to update our qualifications array variable. And we want to append to it. So we'll search for variable, and we'll click See more. And we'll say append to array variable. And the value here is going to be-- are applied to each value.

We need to make a quick edit here, so I'll search for body. And then I want to pull out the Results section of the body response. So that means we're just going to grab this section down. So go ahead and hit Add. That didn't update. I'll try that one more time. OK. So now, we'll add a Compose step so we can look at the output of that array. In the Compose, we're going to add our qualifications variable as an input. So we'll go ahead and hit Save and test.

All right. Now that we have our vendor data in the structure we want, we need to describe the Power Automate what data is in the array. So to do that, we'll go ahead and copy the outputs. And we're going to add a new action. And that action is called parse json.

So we're going to pass in our qualifications array and use a sample payload to generate schema. And that's where we paste our copied output from our array. When we hit Done, Power Automate infers the schema. The schema tells Power Automate what types of data are in our output. So it looks like we have types that are string, integer. We have array and Boolean in here as well. Before I proceed, I'm going to label some of these steps so we don't lose track.

So now that we have our data described, we're ready to write it to our database. So let me pull up my database here. Right now, it's empty. But there are four different attributes that we're going to be storing from it-- ID, qualification status, company name, and federal tax ID. For your business, you may want this data to be sent directly to your ERP. This is possible as long as you have the ability to write to a SQL endpoint.

So I'll go ahead and hit Save here. Oh, I forgot to pass in content for our qualifications right here. So now, we'll add our step to write to our database. So we'll search for SQL, and we'll choose Insert row.

For server name, I'm going to enter the name of my server. I'll specify the database, and I'll choose that table. So for the ID, we're going to be writing the output of our parsed-- we'll be writing from the output of our parsed qualifications array. We'll choose the body ID, and we'll notice that Power Automate automatically nests this in a for each action.

So I'm going to search for qualification status. So in this case, we know that qualification status is a nested value of the qualification object. So we actually have to add a question mark and then specify status here. If we didn't do that, we would-- Power Automate would try to write all these values to our qualification status column. So I'll hit Add. And then we'll find our company name. And then we'll find our tax ID.

All right, so let's save and test this. So the first time we run this, Power Automate just wants to confirm that we're using these two connectors together. So we'll say Yes, we want that to happen. Click Continue and Done. It's going to take a few seconds to write through each of these 19 records. OK, perfect.

So sometimes, in order to view some of the more granular results of a flow run, we need to go back and then check out a specific run history. So we'll scroll down, and we can see 19 rows have been added. So let's go ahead and run a query on our table here, and we can confirm yeah, awesome. 19 rows have been added. Go ahead and sort that by descending.

So the next time this flow runs, we don't want to write the same values again. So we need to add controls which allow us to update vendors that already exist in our database and create new entries for those that don't. To do that, we'll need to determine what data is already in our database so we can process the qualification output accordingly. So we'll add another AQL action, this time to run a query. So click Edit. Add an action. Search for SQL, and click Execute a SQL query. I'm going to go ahead and copy my database details in here again. And we're going to enter a query.

So for the query, we want to select the ID column from our table qualifications, and we're going to order it by descending. So it's going to come through in this order. In this case, the order doesn't matter, but I'll just do it anyway. So before I run this, we're going to temporarily add a terminate action to stop Power Automate from trying to write new rows into our SQL database. So we'll go ahead and add that here. There's a few different statuses, but we'll go ahead and pick succeeded. So I'll click Save and test.

All right, so let's look at our outputs from the SQL query. Just like our qualification outputs, our SQL query comes in a JSON response as well. But as you can see, we received one object for each of the 19 vendors in our database. So let's go ahead and click to copy this because we're going to add a parse json action for this as well. So click Edit, add action, and parse json.

The content we want to create the schema from comes from our SQL query, and it's going to come from the body section of it. So we'll click Add here, and like we did before, we'll use the sample payload to generate the schema. That sample payload is the output of our previous run from SQL.

So now that we have our data type defined, I'm going to go ahead and make sure to label this properly. We want to select just ID out of this. So what we're going to do is add a select action. And the array we're going to pull from is going to be the body of the table. I'm not sure how that happened. And we're going to map the body ID.

So I'll go ahead and hit Save and zoom out here. We can see-- actually, I didn't want this to be in a for each loop. So let me drag this out and delete that for each. Save it one more time, and then we'll run it.

All right. So what we've done here is taken our JSON object and created an array of vendor IDs which we can use to compare with what we retrieve from TradeTapp. And this is our output here. So to compare those to what we have in TradeTapp, what we're going to do is add another action. So inside for each loop, we want to both insert and update our SQL database. So I'm going to go ahead and add one more SQL command, which is going to be update a row. Again, I'll copy over the server details.

So the row-- the configuration here is going to look very similar to what we did for the Insert row. So from our parse json for qualifications, we add the body ID. We'll add the qualification status, like we did before we select the status out of the qualification body-- or qualification object. And we'll search for company name.

OK, so next, we need to add a condition inside our for each loop. So we'll search for condition. And inside this condition, we need to define-- we need to check our qualification ID to see if it's already in SQL. So what we'll do is we'll choose a value here. And for our value, we'll pass in our select output, which is where we have that array of just the IDs from our database. And we're checking to see if it contains the ID that we're pulling from TradeTapp, TradeTapp API.

So since we're already in a loop, looking at the-- looking through each of the outputs from our parse json, we can use these items for each, and then we'll just specify that we want to pull out the ID from it. All right, so if our condition-- if we run through this condition, and the items do exist in our SQL database, what we want to do is update them. So we'll put update under true. If they don't, we want to insert a new row, so we'll put that under the false output.

I can see that this doesn't want to drag like it should, so I'm just going to go ahead and copy it and paste it in. And the configuration should still be the same. And I'm going to go ahead and delete this action and save.

So now that we have our loop defined, and we know what conditions we want to either update or insert rows, let's make some changes to our data so we can test this out. So first, we'll remove a few vendors from our database. This will let us test how new vendors are added with the flow. So I'm going to copy this command, delete from qualifications. We're going to choose a few IDs here and paste them in.

We'll go ahead and run that. And now, when we select, we're only going to see 16 rows. Now let's make some updates to our vendors and our TradeTapp account. So in TradeTapp, we actually have a few vendors here where their qualification has expired. So let's go ahead and change a couple of these. We're going to set the expiration date out so that the qualification status is now qualified rather than expired. And we'll do that for this one as well.

Now that we've made some changes to our data, we're going to go ahead and rerun our flow. This time, we should expect two of the vendors to have their updated qualification status, and we should expect three new rows to be replaced for the rows that were deleted. So let's make sure this flow has been saved and go ahead and test it. Quick run flow.

Oh, I forgot to remove our term termination step, so let me delete that real quick and save and test. This time, we should see it. Go past our select statement and start to run in this loop. Awesome. Let's look at what happened in more detail.

So our loop ran 19 times. And we should see some of these were updated for insert row. Yes, perfect. So insert row once, twice, and three times. Let's go ahead and rerun our query for our table here. And as we expected, we now have 19 rows again. And two of the statuses that we changed in TradeTapp have been updated.

So the last step is, up until now, we've been triggering this flow manually. We're going to change that. So we'll go ahead and delete our manually triggered flow action. We'll add a trigger and search for schedule, which is under the recurrent section. We'll set the interval to one and frequency to daily, day. So I've set it to run daily. But how often you want this flow to run will depend on your business needs. Go ahead and hit Save. And with that, we've finished a flow to sync data from trade tab to our database automatically.

Now that our flow is complete, let's recap. We've been able to learn about TradeTapp's API functionality by reviewing the documentation. We've covered how to use custom connectors to extract data via API by defining both authentication and actions we want to perform. We've also learned to add and update rows in a SQL database automatically by configuring a flow. You should now have a better idea of how web APIs work, how to retrieve data from TradeTapp, and how to design flows in Power Automate. Thanks for watching.