Our PowerApps canvas app currently reads customers from a SharePoint list and also provides a way to contact customers with a “Send E-Mail” screeen. In this post, we expand the app once again so that it now includes a ticket feature. Topics in this blog focus specifically on handling Append Text content for the ticket log and building a relationship between SharePoint and SQL data.

PowerApps and SharePoint Append Text Fields

Presently, PowerApps does not provide a way to display SharePoint append text field content and associated “Created By” and “Created On” metadata. This is important to a ticket scenario because tickets often include a running log of activity completed by ticket owners which, when implemented in SharePoint, are logically stored in a multi-line text field with append text support.

If you try to use a SharePoint append text field as part of your PowerApps data source you will not succeed displaying all associated data (i.e. text, created by, and created on) without some sort of “hacky” tricks, using linked fields and dealing with large SharePoint lists, piecing together workflows, etc.

In my opinion, using SQL to store ticket activity logs is a good solution to providing this critical ticket system functionality…avoiding a convoluted design and difficult to maintain implementation.

Use SQL to Store Your Ticket Log Entries

One of the great features of PowerApps is that it allows you to easily combine data from multiple data sources together in a single interface. Design-wise, it’s a non-issue to build an app which creates, reads, updates, and deletes content in both SharePoint and SQL simultaneously. However, there are some things to consider with the design I outline below:

• The first side-effect of using SQL instead of SharePoint (i.e. to store log entries) is the cost overhead. You need to have a SQL or SQL Azure instance available.
• Your log entry content won’t be natively available in SharePoint Search without some extra configuration.
• Deleting a case in SharePoint won’t delete the logs in SQL. You may want to build an SSIS, CSOM executable, or other clean-up utility.

I propose that the above drawbacks are outweighed by:

• The unlikelihood that one will search log content outside of a ticket’s context; logs don’t need to be available in SharePoint Search. Also, should you need the SQL content in SharePoint Search, there are  couple of ways to make that happen.
• SQL is designed to hold large sets of records. If you try to join two SharePoint lists (e.g. Tickets and Logs) together through a linked column then you will likely  run into list view limit issues after just a few hundred tickets are created.
• The design and implementation of joining SharePoint and SQL records are completely straightforward using out-of-the-box PowerApps functions. That’s opposed to the work-around you will end-up building trying to keep everything in SharePoint.

For this ticket system, a table such as the following works:

CREATE TABLE [dbo].[CaseLogs](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[SPItemID] [int] NOT NULL,
	[CaseLog] [nvarchar](256) NULL,
	[CreatedDate] [datetime] NOT NULL DEFAULT (getdate()),
	[CreatedBy] [nvarchar](50) NOT NULL DEFAULT (''),
	[CreatedByEmail] [nvarchar](50) NOT NULL DEFAULT (''),
 CONSTRAINT [PK_CaseLogID] PRIMARY KEY CLUSTERED ([LogID] ASC)
)

Note: I also added an Index on SPItemID as that will be our primary filter. That is not shown above. Admittedly, I’m not 100% certain how PowerApps transforms filtered SQL queries on the back-end. But, SPItemID is the SharePoint list item ID against which all CaseLogs will be associated with a specific ticket, so an index on this column makes sense. Also, it optimizes the table for the likely SQL query “…WHERE SPItemID = n” in most scenarios where SQL data is being searched directly.

With this key decision covered (i.e. how to store work logs for tickets), the post will now move into the rest of the design and functionality.

Use SharePoint to Store the other Ticket Fields

It makes sense to store the remaining fields in SharePoint. Having the main ticket content stored in SharePoint will make it easy for users to set alerts, create views, build search-oriented pages, export to Microsoft Excel, and wrangle tickets in all the many ways that SharePoint provides. My schema is:

• Title: holds a basic description of the ticket issue
• CaseDescription: holds a detailed description of the ticket issue
• CaseStatus
  • In Progress
  • Closed
• CasePriority
  • Low
  • Medium
  • High
• CaseOwner: specifies an O365 user; the person working the ticket
• CaseCustomer: stores the e-mail address of the customer who opened the ticket

PowerApps Case and Ticket Navigation

I designed this to work as:

1. Select a customer, then list all associated tickets in a gallery.
2. Either select a ticket from the gallery to edit it, or create a new ticket.
3. If selecting a new ticket, then create the ticket and navigate back to the ticket gallery.
4. If selecting an existing ticket, then go to the ticket’s edit form to update any details and add new logs.

Let’s first look at a demo, and then I’ll explain in more detail how everything is built.

* I didn’t spend much time cleaning up the UI. This is a prototype for a blog…my goal was just to get the functionality and basic navigation in place.

PowerApps Functions

The main functions used to build this interface are: Filter(), User(), and the Now() function..

PowerApps Filter() Function

The filter function is used to:

• Filter tickets to the currently selected customer when clicking the “Manage Cases” button.
• Filter logs to the currently selected ticket.

The first instance looks as follows, filtering a SharePoint ticket list data source, “Cases”:

Filter(Cases,CaseCustomer = CustomersGallery.Selected.EMail)

* Every design is different – you could filter by a company name, customer ID, company ID, regions, etc.

Next, when selecting a specific ticket, filter a SQL data source “[dbo].[CaseLogs]” on that Indexed field “SPItemID”:

Filter('[dbo].[CaseLogs]',SPItemID = CaseGallery.Selected.ID)

PowerApps User() and Now() Functions

Looking back at the SQL schema, you see that a created by name, created by e-mail, and created-on timestamp are stored with every log. For the name/e-mail, you could also store the user principal ID and do look-ups on the round-trip, but storing this small amount of text (i.e. name and e-mail) in two fields will barely exceed the number of characters of just storing the principal ID. In my opinion, storing a bit of extra text outweighed the overhead of running a lookup function.

The display name and e-mail are pulled from the following:

User().FullName

User().Email

They are tied to the [dbo].[CaseLogs] data source by adding the SQL data source fields to the app definition and setting those fields’ default values:

*In production I wouldn’t actually add these fields to the canvas…but for demo purposes:

We can see the same technique with the LogCreated field and the use of:

Now()

Finally, when clicking “Save All”, the following chain of functions are executed:

SubmitForm(CaseEditForm); If(Len(NewLogText.Text) > 0, SubmitForm(CaseLogNewForm)); EditForm(CaseEditForm);NewForm(CaseLogNewForm); Refresh('[dbo].[CaseLogs]')

You could also use the PowerApps Unsaved property instead of the Len() function to detect whether or not there is a new log to submit.

Conclusion

PowerApps makes it simple to join multiple data sources together in a single app. Once integrated, it is straightforward to build a foreign key style relationship between the data sources with the use of the Filter() function. In our ticket system, this was critical to working around the fact that PowerApps currently does not support the display of SharePoint append text fields.

Categories: Apps, Microsoft PowerApps, Office 365 and O365, SharePoint

Tags: Apps, Office, Office 365 and O365, PowerApps, SharePoint, SQL