IT Brief New Zealand logo
Technology news for New Zealand's largest enterprises
Partner content
Story image

Working with normalised SQL Tables – a guide

By Contributor
Wed 31 Oct 2018
FYI, this story is more than a year old

Article by Mobile Mentor mobile solutions architect Shane Sloan

Disclaimer: This blog post is technical. It speaks to normalisation of SQL data and will not be appropriate for non-developers. Second, the goal of this blog post is to explain a method for working with normalised SQL data in PowerApps. The SQL tables, views, and app I created for this blog post are educational in nature and are therefore contrived. This article assumes the reader to be proficient in PowerApps development.

Microsoft PowerApps allows connections with many different systems. Some of those are naturally going to be SQL databases. With PowerApps, connecting to a database is a cinch. Simply establish a connection to a cloud-based database within your Tenant by creating a new SQL Server connection. Enter the SQL server name, database name, then give credentials for an account on the server with the permissions you desire (read vs write).  No coding is necessary.

While this works well, in any enterprise environment SQL data is going to be normalised. This means that the tables themselves are going to be full of relational ID numbers.

Realising this, Microsoft has done a good job of allowing the SQL connector in PowerApps to read views within the database.

But, that’s all you can do with views. You cannot Update or Add a record to a view in PowerApps. Further, PowerApps does not leverage stored procedures (as of the date of publishing) to assist with adding or updating records. This creates real challenges when trying to add or update records in a normalised SQL database.

If your app only needs to read data, create a view that resolves the relationships and you are ready to go. But what should you do if you want to add or update records?

You can display a view, but you cannot update the view or add to it. How should you proceed? Your users will need to see item names, but the table is going to expect ID values.

Based on my experience the following are steps you can take to ensure a good user experience when working with normalised SQL data:

For the purposes of this post, let’s assume we want to create a Power App that allows employees to inventory items at our worksites. The inventory is tracked in a SQL database and the Power App needs to integrate with that database directly.

First, I recommend setting up views in the database that contain both the name fields as well as the ID fields. This is what the SQL tables might look like. For Worksites, Items, and Current Inventory.

The employee responsible for counting inventory is not likely to know that the ID for “Mops” is 1 or that the ID for “South Location” is 3.

The employee is going to need to see the names of these items and worksites. Again, this can be solved with a view. However, when the employee wants to update the Inventory Audit Table, the view cannot be used and the original ID numbers will have to be entered.

Let’s look at how the view should be setup. Below I’ve created a Current Inventory View that contains the names as well as the ID fields.

This view will allow us to show the employee the name of the worksite or item, while still retaining its ID. The Current Inventory view is based on the Current Inventory table and uses JOINs to add in the names for the worksites and items.

Armed with this Current Inventory view, you can now setup a Power App that displays the Current Inventory and facilitate entry into the Inventory Audit Table.

Let’s walk through an app that could use this data and how it would need to be setup. Here is my user interface. I am displaying names to the user but will be able to resolve the appropriate IDs later thanks to my view.

Ignoring that fact that this may not be the best user interface design, lets skip to how I’m able to make use of the view.

Here are the five key points:

  • On Screen1 I have set an expression in the OnStart. For performance reasons I am creating a collection which I will name, “colCurrentInventory”. This collection will contain the entirety of the Current Inventory view that I described above. Doing this minimises the amount of times that the app needs to query the SQL database. If I worked directly with the view instead of creating a collection, then each subsequent step would incur a delay as the database is queried again and again. You should always work with collections to minimise SQL queries and optimise performance.

Here is the expression: Collect(colCurrentInventory, ‘[dbo].[View_CurrentInventory]’)

  • I have created a dropdown control, named it ddlWorksite, and set an expression in its Items I am using the collection from step 1 and then using Distinct() to remove duplicates so that the drop down contains each worksite name only once.

Here is the expression: Distinct(colCurrentInventory, SiteName)

  • I have created a second dropdown control, named it ddlItem, and set an expression in its Items For this drop down we need to have a list that is based on the selection in ddlWorksite. For that, I am going to use the collection again and this time I’m going to use Filter() to ensure only inventory items at the selected worksite are displayed.

Here is the expression: Filter(colCurrentInventory, SiteName = ddlWorksite.Selected.Value).ItemName

  • I have created a text field, named txtItemCount, to hold the actual count observed during the audit. I set the Format property to Number. This isn’t strictly necessary, but its good design and takes a trivial amount of time to setup.
  • I created a submit button and set an expression in its OnSelect This is where it all comes together. Because I’m not using a form, when the button is pressed I am calling Collect() on the Inventory Audit table. I am then passing it all fields except for the table’s primary key (AuditID) as this is set in the database and auto increments. Note that to use Collect() on a table, the table MUST have a primary key defined.

Here is the expression: Collect(‘[dbo].[InventoryAudit]’,         {  AuditDate: Now(),               Employee: User().FullName,               SiteID: First(Filter(colCurrentInventory, SiteName=ddlWorksite.Selected.Value)).SiteID,               ItemID: First(Filter(colCurrentInventory, ItemName=ddlItem.Selected.Value)).ItemID,               ItemCount: Value(txtItemCount.Text)         } )

Digging into point 5 a bit more, there are two fields where I am resolving the ID based on the selected name in the appropriate drop down. For SiteID, I use the collection and filter it based on the value in ddlWorksite.

For ItemID, I do the same but use ddlItem instead. With both I need to use First() to obtain the correct data type – a variable (not a collection). Even though in this case Filter()is going to return one row for both of these dropdowns, there is no rule explicitly that says Filter() has to reduce a collection strictly to one row. So, I need that extra wrapper to ensure the data types match.

As a final note, I am wrapping the value in txtItemCount with Value() because the ItemCount field of the Audit Inventory table itself is ‘int’. Without Value()you will get an error.

OK so that’s it.

TLDR: When working with normalised data, create a view that contains both the IDs and the names of relevant fields. Use the view to display names to users while retaining the ability to resolve field IDs. When updating or adding records, resolve the correct IDs using the view and the names selected by the user.

Related stories
Top stories
Story image
Tech job moves - Forcepoint, Malwarebytes, SolarWinds & VMware
We round up all job appointments from May 13-20, 2022, in one place to keep you updated with the latest from across the tech industries.
Story image
Let’s clear the cloud visibility haze with app awareness
Increasingly, organisations are heading for the cloud, initiating new born-in-the-cloud architectures and migrating existing applications via ‘lift and shift’ or refactoring.
Story image
Remote Working
Successful digital transformation in the hybrid work era is about embracing shifting goalposts
As organisations embraced remote working, many discovered they lacked the infrastructure needed to support history’s first global load test of remote work capabilities.
Story image
Vectra AI
Understanding the weight on security leader’s shoulders, and how to shift it
Millions of dollars of government funding and internal budgets are being funnelled into cybersecurity to build resilience against sophisticated threats, indicating how serious this issue has become.
Story image
Cradlepoint expands its Cellular Intelligence capabilities
Cradlepoint has announced additional Cellular Intelligence capabilities with its NetCloud service.
Story image
Data backup plans inadequate, data still at risk - study
The Apricorn 2022 Global IT Security Survey revealed that while the majority organisations have data backup plans in place, data for many are at risk.
Story image
Artificial Intelligence
How to ensure ethical deployment of AI implementations
The increase in automation and machine technology such as AI and machine learning has unlocked a whole new level of scale and service to organisations. 
Story image
Lightspeed launches all-in-one marketing platform in A/NZ
ECommerce provider, Lightspeed has launched a new all-in-one marketing solution, Lightspeed Marketing & Loyalty in Australia and New Zealand.
Story image
Customer experience
Gartner recognises Okta for abilities in Access Management
Okta has announced it has been recognised as a Customers' Choice for the fourth time in a row in the Gartner Peer Insights "Voice of the Customer" report.
Story image
A10 Networks finds over 15 million DDoS weapons in 2021
A10 Networks notes that in the 2H 2021 reporting period, its security research team tracked more than 15.4 million Distributed Denial-of-Service (DDoS) weapons.
Find out how a behavioural analytics-driven approach can transform security operations with the new Exabeam commissioned Forrester study.
Link image
Story image
Digital Transformation
The Huawei APAC conference kicks off with digital transformation
More than 1500 people from across APAC have gathered for the Huawei APAC Digital Innovation Congress to explore the future of digital innovation.
Story image
Legrand unveils Nexpand, a data center cabinet platform
Legrand has unveiled a new data center cabinet platform, Nexpand, to offer the necessary scalability and future-proof architecture for digital transformation.
Story image
Apple previews new features for users with disabilities
Apple says new software features that offer users with disabilities new tools for navigation, health and communication, are set to come out later this year.
Story image
Maintaining secure systems with expectations of flexible work
Most office workers feel they've proved they can work successfully from home, and as much as employers try, things aren't going back to the way they were anytime soon.
Story image
Data Center
Preventing downtime costs and damage with Distributed Infrastructure Management
Distributed Infrastructure Management (DIM) can often be a lifeline for many enterprises that work with highly critical ICT infrastructure and power sources.
Story image
NCSC advisory highlights poor security configurations
The GCSB's National Cyber Security Centre (NCSC) has released a cyber security advisory identifying commonly exploited controls and practices.
Story image
Wireless Nation
Wireless Nation, N4L provide 4G network to remote NZ schools
Wireless Nation and Network for Learning (N4L) have rolled out the Rural Connectivity Group’s (RCG) new 4G network to better connect three Chatham Islands schools.
Story image
Cybersecurity starts with education
In 2021, 80% of Australian organisations responding to the Sophos State of Ransomware study reported being hit by ransomware. 
Story image
Revenue operations is taking centre stage
As the business world continues to evolve, new demands need to be met to keep up with the ever-changing landscape. 
Story image
Grasping the opportunity to rethink the metrics of a sustainable data centre
A data centre traditionally has two distinct operations teams: the Facility Operations team, and the IT Operations team. Collaboration between them is the key to defining, measuring, and delivering long-term efficiency and sustainability improvements.
Story image
Cloud Security
Aqua Security createa unified scanner for cloud native security
“By integrating more cloud native scanning targets into Trivy, such as Kubernetes, we are simplifying cloud native security."
Story image
Commerce Commission
ComCom appeals $2.25 million fine in Vodafone FibreX case
The Commerce Commission has filed an appeal in the High Court against a $2.25 million fine imposed on Vodafone NZ for its offending under the Fair Trading Act.
Story image
More than 40% of banks worried about cloud security - report
Publicis Sapient's new report finds security and the lack of cloud skills and internal understanding of business benefits are big obstacles for banks moving to the cloud.
Story image
Digital Transformation
How to modernise legacy apps without compromising security
At a time when digital transformation has become central to business, even the most important applications come with a ‘use-by’ date.
Story image
Data Protection
Barracuda launches new capabilities for API Protection
"Every business needs this type of critical protection against API vulnerabilities and automated bot attacks," Barracuda says.
Story image
New vulnerabilities found in Nuspire’s Q1 2022 Threat Report
“Threat actors are quickly adjusting their tactics and these exploits tend to get industry attention, but the threat posed by older and attacks still persists."
Booster Innovation Fund. A fund of Kiwi ingenuity – for Kiwi investors.
Link image
Story image
Rubrik Security Cloud marks 'next frontier' in cybersecurity
"The next frontier in cybersecurity pairs the investments in infrastructure security with data security giving companies security from the point of data."
Story image
Supply chain
Jetstack promotes better security with supply chain toolkit
The web-based resource is designed to help organisations evaluate and plan the crucial steps they need to establish effective software supply chain security.
Story image
Artificial Intelligence
AI-based email security platform Abnormal Security valued at $4B
"A new breed of cybersecurity solutions that leverage AI is required to change the game and stop the rising threat of sophisticated and targeted email attacks."
Story image
Vodafone NZ buys remaining stake in retail joint venture
Vodafone New Zealand has purchased the remaining 50% stake in the specialist joint venture (JV) with private equity company Millennium Corp.
Story image
Nozomi Networks
Nozomi Networks, Siemens reveal software integration
Nozomi Networks and Siemens have extended their partnership by embedding Nozomi Networks’ software into the Siemens Scalance LPE local processing engine.
Story image
Power at the edge: the role of data centers in sustainability
The Singaporean moratorium on new data center projects was recently lifted, with one of the conditions being an increased focus on power efficiency and sustainability.
Story image
Amazon Web Services / AWS
RedShield leverages AWS to scale cybersecurity services
"Working with AWS gives RedShield the ability to mitigate significant application layer DDoS attacks, helping leaders adopt best practices and security architectures."
Story image
Artificial Intelligence
SAS announces new products amid cloud portfolio success
Analytics and AI company SAS is deepening its broad industry portfolio with offerings that support life sciences, energy, and martech.
Threat actors are exploiting weaknesses in interconnected IT/OT ecosystems. Darktrace illuminates your entire business and takes targeted action to stop emerging attacks.
Link image
Story image
Digital Transformation
The impact of COVID-19 on healthcare environments and care delivery
The COVID-19 pandemic has revolutionised the healthcare industry while overcoming staff shortages, social distancing requirements, and lockdowns.
Story image
Application Security
What are the DDoS attack trend predictions for 2022?
Mitigation and recovery are vital to ensuring brand reputation remains solid in the face of a Distributed Denial of Service (DDoS) attack and that business growth and innovation can continue.
Story image
PwC NZ unveils new Cloud Security Operations Center
PwC New Zealand has unveiled its new Cloud Security Operations Center for the entire Microsoft technology stack.
Story image
Could your Excel practices be harming your business?
While Excel has been the de-facto standard for budgeting, planning, and forecasting, is it alone, enough to support organisations in the global marketplace that’s facing rapid changes due to digital transformation?
Story image
New Relic enters multi-year partnership with Microsoft Azure
New Relic has announced a strategic partnership with Microsoft to help enterprises accelerate cloud migration and multi-cloud initiatives. 
Story image
Sift shares crucial advice for preventing serious ATO breaches
Are you or your business struggling with Account Takeover Fraud (ATO)? One of the latest ebooks from Sift can provide readers with the tools and expertise to help launch them into the new era of account security.
For every 10PB of storage run on HyperDrive vs. comparable alternatives, an estimated 6,656 tonnes of CO₂ are saved by reduced energy consumption alone over its lifespan. That’s the equivalent of taking nearly 1,500 cars off the road for a year.
Link image