Build your own Audit app

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:

***

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!

 FOLLOW US ON FACEBOOK

Schedule your free demo!

Want to make your life much easier and work more effective? Yes! We’re more than happy to schedule a demo with you! Just contact us and we’ll get back to you.

We keep your information safe. Read more from our Privacy Policy.

Subscribe to our Newsletter

Get our news delivered right into your inbox. We won’t spam, promise!

We keep your information safe. Read more from our Privacy Policy.