This blog walks you through how you can build your own Audit app where a user can add input and save it to multiple SQL tables and build a relationship between the tables. This blog also focuses on creating relationships across three Azure SQL tables.
Scenario:
In this scenario an Audit App is created when users have to fill in necessary information plus take pictures if needed be.
Description:
The back-end contains three SQL tables where data is saved for each audit. Here is the breakdown of the tables:
Audit Table
This table saves the users names, email address, department and the data.
Here are the column names:
| Column Name | Type |
| ID | Int |
| Auditor | Nvarchar(1000) |
| AuditorEmail | Nvarchar(1000) |
| Department | Nvarchar(1000) |
| Created | date |
SQL Query:
CREATE TABLE [dbo].[AuditDemo]( [ID] [int] IDENTITY(1,1) PRIMARY KEY, [Auditor] [nvarchar](1000) NULL, [AuditorEmail] [varchar](1000) NULL, [Department] [nvarchar](500) NULL, [Created] [date] NULL )
Audit Answers Table:
This table allows you to save all the results/answers to the questions as separate rows. This table has an Audit ID column which maintains the relationship between itself and the Audit table.
Here are the column names:
| Column Name | Type |
| PriID | Int |
| AuditID | Int |
| Question | Nvarchar(1000) |
| Answer | Nvarchar(1000) |
| Comment | Nvarchar(1000) |
| ID | Int |
| NumberOfImages | Int |
SQL Query:
CREATE TABLE [dbo].[AuditAnswersDemo]( [PriID] [int] IDENTITY(1,1) PRIMARY KEY, [AuditID] [int] NULL, [Question] [varchar](1000) NULL, [Answer] [nvarchar](500) NULL, [Comment] [varchar](1000) NULL, [ID] [int] NULL, NumberOfImages [int] NULL )
Audit Answers Image Table:
This table allows you to save an image for any or all the answers. This table has an AnswersID column which maintains the relationship between itself and the Answers Table.
Here are the column names:
| Column Name | Type |
| ID | Int |
| AuditAnswersID | Int |
| Picture | Image |
| AuditID | Int |
SQL Query:
CREATE TABLE [dbo].[AuditAnswerImageDemo]( [ID] [int] IDENTITY(1,1) PRIMARY KEY, [AuditAnswerID] [int] NULL, [Picture] [image] NULL, [AuditId][int] NULL )
In addition we are also adding a Question table where all the questions are stored. This gives the end user to add, edit and remove the questions thereby updating the app dynamically instead of manually editing the app.
Here are the column names:
| Column Name | Type |
| ID | Int |
| AuditID | Int |
| Question | Nvarchar(1000) |
| Answer | Nvarchar(50) |
| Comment | Nvarchar(1000) |
| NumberOfImages | Int |
SQL Query:
CREATE TABLE [dbo].[AuditQuestionsDemo]( [ID] [int] IDENTITY(1,1) PRIMARY KEY, [AuditID] [int] NULL, [Question] [nvarchar](255) NULL, [Answer] [varchar](50) NULL, [Comment] [nvarchar](1000) NULL, [NumberOfImages][int] NULL )
Even though this table has only the Audit Questions, it is important they have the other columns as well because we’ll be using the Collect formula.
Video: This video walks you through how to create the SQL tables and then build the Audit app.
Formulas:
OnSelect formula for the Submit Button:
Set(IdAuditVar,Patch('[dbo].[Audit]',Defaults('[dbo].[Audit]'),
{
Auditor:AuditorNameTextInput.Text,
AuditorEmail:AuditorEmailTextInput.Text,
Department:Text(DepartmentDropdown.Selected.Value),
Created:NowVar
}).ID);
ForAll(Gallery2.AllItems,
UpdateIf(AuditCol,ID=Value(IDGal.Text),{AuditID:IdAuditVar,Answer:ARadio.Selected.Value,Comment:CommentGal.Text,NumberOfImages:Value(ImageCountGal.Text)})
)
;Collect('[dbo].[AuditAnswers]',AuditCol)
;ForAll(PictureCol,
Patch('[dbo].[AuditAnswerImage]',Defaults('[dbo].[AuditAnswerImage]'),
{
AuditAnswerID:Question,
Picture:Image,
AuditID:IdAuditVar
}))
;ClearCollect(AuditCol,'[dbo].[AuditQuestions]')
;Clear(PictureCol);Reset(DepartmentDropdown)
OnSelect formula for the AddMediaWithImage control
If(
PictureColVar=1,Collect(PictureCol,{Image:UploadedImage2.Image,Question:1}),
PictureColVar=2,Collect(PictureCol,{Image:UploadedImage2.Image,Question:2}),
PictureColVar=3,Collect(PictureCol,{Image:UploadedImage2.Image,Question:3}),
PictureColVar=4,Collect(PictureCol,{Image:UploadedImage2.Image,Question:4}),
PictureColVar=5,Collect(PictureCol,{Image:UploadedImage2.Image,Question:5}),
PictureColVar=6,Collect(PictureCol,{Image:UploadedImage2.Image,Question:6}),
PictureColVar=7,Collect(PictureCol,{Image:UploadedImage2.Image,Question:7})
)
Items formula for the AnswerGallery
Filter('[dbo].[AuditAnswers]',AuditID=AuditGallery.Selected.ID)
Items formula for the ImagesGallery
Filter('[dbo].[AuditAnswerImage]',AuditID=AuditGallery.Selected.ID && AuditAnswerID=AnswerGallery.Selected.ID)
Important links:
- Collect formula: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearcollect
- For All formula: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-forall
- Patch formula: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch
- Update If formula: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-update-updateif
***
Want to hear more from MVP Daniel Christian? Check out his other blogs Adding Responsive Design to PowerApps and User-based Security in PowerApps all here on the Valo blog!
***
Stay tuned for more invaluable content from thought leaders in the Microsoft community by following us on social!
