Streamlining the application process with Azure OpenAI and Power Automate
Katerina Chernevskaya
????Principal Microsoft Cloud Architect (Power Platform & Azure AI) ||?? AI unlocks opportunities, low-code provides tools, I transform them into business impact. ||??BizApps MVP || ??MCT || ??Speaker
The grant application process, or any kind of application process for that matter, can pose a significant challenge for any organization, particularly when applications must be submitted in multiple languages. Filling out a large number of forms with repetitive information, and generating marketing descriptions that stand out can be an administrative nightmare. That's where solutions based on #Azure #OpenAI and #PowerPlatform come in handy.
?
In this article, I provide a detailed guide on how to leverage the latest Azure OpenAI model - gpt-35-turbo (available in preview) to create a project description based on its name. Additionally, I cover how to fill out a Word document using Power Automate and dynamicFileSchema.
?? If you prefer to start with a template, the solution for download from my GitHub will be shared at the end of this article.
?
In this article I'm focusing on the automation part, so the UX part is out of the scope. But keep in mind - you can use various options for building the user interface - Power Pages, Power Apps, Power Virtual Agents, etc.
?
Prerequisites
?
Step 1. Prepare Azure subscription
You can use your existing Azure subscription or create one for free - https://azure.microsoft.com/free/cognitive-services.
The model gpt-35-turbo that we are going to use in this article is in preview. You need to apply for access in advance using this form: https://aka.ms/oai/get-gpt4.
Once you will be onboarded to the preview - create an Azure OpenAI service and deploy the gpt-35-turbo model.
You can follow instructions in my previous article for the resource creation and the model deployment. One different is - in current case please deploy gpt-35-turbo model instead of text-davinci-003.
?
Step 2. Prepare the .docx template
For this article I took one application form from one of European grants.
To allow Power Automate fill out the document for us, we need to map fields we want to be populated. To do so first of all make sure that the tab Developer is turned on in your Microsoft Word.
If not - go File -> Options -> Customize Ribbon and select Developer tab.
Click on an area where you want Power Automate put information, open the Developer tab and click on the Plain Text control.
After the control's area appear, click on Properties icon. In the pop-up window enter the field name in both Title and Tag fields, and click OK button.
Use self-explained names. We are going to use them later to match fields and information.
Repeat for all fields you want to be populated by Power Automate.
Step 3. Prepare document libraries
For this sample we will use 2 documents libraries.
The first library is for store templates. In my case I created a document library called GrantBuddyTemplates for this purpose.
The second library is for store completed forms that Power Automate generates. In my case I created a document library called GrantBuddyCompleted for this purpose. And for this second library let's add 2 additional columns: RequestorEmail and RequestID. Just in case for the future additional features building and matching documents with Dataverse records.
Once both libraries are ready - upload the template prepared on the step 2 to the library GrantBuddyTemplates.
?
Step 4. Prepare solution components
Sample that we are going to build today includes several components. So I recommend you firstly create a solution to build everything inside of this solution.
I created the solution called Grant Buddy.
Let's create solution's components.
4.1. A table
Create a table (I created a table called Grant) with following custom columns:
- NameEN (single line of text)
- GrantName (single line of text)
- Duration (whole number)
- ProjectLocation (single line of text)
- Email (email)
- ShortDescription (single line of text, set Maximum character count to 2000)
- ShortDescriptionEN (single line of text, set Maximum character count to 2000)
In our sample a user will provide information for 5 fields only:
- Name (this field was generated when the table was created)
- GrantName
- Duration
- ProjectLocation
For the rest of the fields information will be generated using Power Automate, Azure OpenAI and Microsoft Translator.
4.2. 3 Environment variables
To be more flexible and support deployment in different environment the best practice is to store dynamic values in the Environment Variables.
For our sample let's create 3 environment variables to store SharePoint site name, our template file name and Azure OpenAI api-key.
?
Step 5. Prepare the flow
Create an automated flow. Enter its name and select the trigger - When a row is added, modified or deleted.
Configure the trigger:
Add the next step. In the Choose an operation window in the search field enter translator and select Detect language action.
With this action we will identify in which language a user typed the name of a project. So we will be able to translate the description to this language later.
In this action click Add dynamic content and choose Name from the trigger.
Add the next action - Translate text. In the field Target Language choose English. In the field Text select the dynamic content as in the previous action and choose Name as well.
Add the next action - HTTP. With this action we will ask Azure OpenAI write a fabulous description for the project that a user submitted.
To prepare API request for this step I always recommend use Postman to test your request and be sure that everything is working as expected.
领英推荐
You can check how to configure API request in Postman in the Step 4 of my previous article.
But as we are working now with the gpt-35-turbo model the request parameters will be slightly different. Let's explore.
Method: POST
Params:
Headers:
Body:
{
??"messages":?[
????????{
??????????"role":?"system",?
??????????"content":?"<YOUR_INSTRUCTIONS_FOR_MODEL>"
????????},
????????{
????????????"role":?"user",?
????????????"content":?"<YOUR_REQUEST>"
????????}
????],
??"max_tokens":?400,
??"temperature":?1,
??"frequency_penalty":?0,
??"presence_penalty":?0,
??"top_p":?0.95,
??"stop":?null
}
One of my favorite thing of this model is that we can guide and train the model in the request directly. Explore here more options: How to work with the ChatGPT and GPT-4 models (preview) - Azure OpenAI Service | Microsoft Learn
?
Test your request in the Postman and when everything is ok - go back to the flow to complete the HTTP action.
Copy almost all values from the Postman to the HTTP action except the api-key value. Instead of hard-code this value, use the Environment Variable.
Add dynamic content to the body. In my sample I provide the project's name (in English) as a request.
Add the next action - Compose and add the following expression:
body('HTTP')['choices'][0]['message']?['content']
With this action we extract the response received from Azure OpenAI.
Add the next action - Translate. Now we need to translate the description received from Azure OpenAI from English to the language the user used when submitting the request.
For the Target language choose the Language code that we got in the action Detect language. For the Text field choose the outputs of the Compose action
We are almost ready to work with the template document ??. Just one step more before we go to action with Word.
Add another Compose action and add the following in the action's field:
Don't forget the / symbol before the Environment Variable.
With this action we prepare the file's path using Environment Variable.
Well done! We successfully prepared all pieces required for our template. Let's generate the document.
Add the next action Populate a Microsoft Word template and configure fields:
Looks good. But have a look, instead of fields that we configured in the template document with controls, the action shows us just one field dynamicFileSchema. This is because in the field File we are using dynamic content, not hard coded.
Let's save our flow for the moment and delve into the specifics of where and how we can locate information for this field.
?
The schema for this field is pretty easy:
{
"KEY": "VALUE"
}
The trick part here is where to find the key. You might thing that the key is the Title of Tag that we configured on the step 2. Unfortunately, not exactly. The KEY is a numeric unique id that we can find in the XML file. To do so first of all save your template somewhere on your PC and change the file type to zip.
After unzip the achieve, open the folder word, and open document.xml file.
Scroll down and find the name of your plain text control. What we are looking for is id.
Find and copy id for all controls you configured in the Word template.
?
Now we can back to our flow and add information to the dynamicFileSchema field. For KEY use copied from XML values. For VALUE use dynamic content.
?
Uhh! The most challenging aspect has now been overcome. We just need to save generated document to the document library, update information in columns and send the document to the requestor.
?
Add the next action Create file and configure it.
For the File Name field add the following expression to create the unique file name (and don't forget about the file extension):
concat('GrantRequest_',utcNow(),'.docx')
Additionally, we need to update several rows for the saved document - RequestorEmal, RequestID and Description:
Now let's update the Dataverse record. Add Update a row step and add dynamic content to 3 fields: NameEN, ShortDescription, ShortDescriptionEN.
?
And finally we can send the email to the requestor with the populated document using the action Send an email (V2). To configure the attachment use following values:
Attachment Name - 1: Title from the action Create file
Attachment Content: body from the Populate a Microsoft Word template action
?
Congratulations! ?? You did it! Everything is prepared and we ready to test it.
As the UX part is out of scope in this article, you can simply manually add a new record to the Dataverse table. Once you did it, the flow should be triggered automatically. After it will be completed successfully the requestor will receive the email with attachment and a new auto populated document will be created in the document library.
?
??Want a quick start?
I published the unmanaged solution with all necessary components to my GitHub: https://github.com/Katerina-Chernevskaya/Grant-Buddy
You can download the solution, export it to your environment, and start building on the top of preconfigured flow.
?
Conclusion
In conclusion, #AI and #PowerPlatform solutions offer many benefits in streamlining the grant application process, reducing administrative tasks, saving time, and minimizing costly errors. They can be utilized across various industries where grant applications are required, making the process more manageable, and saving organizations the stress of the process.
?
2x Microsoft MVP | PMI-CDBA | PMI-CDP | Manager of Business Applications Development | Microsoft Power UP Program Instructor
1 年Thank you so much for sharing. This is awesome!