Simple Inventory Management App – Part 1/3

“Hi, do you have any more of these in stock?”

Sure, let me check my inventory management app!

Hey PowerAddicts! Welcome back to NextStep Creations. This post marks the first of three instalments, in which we’ll guide you through creating a simple, but powerful inventory management application.

First off, let’s look at what you’ll be building over the coming tutorial!

Finished Inventory App

At the end of this series, you’ll have a nice, pretty inventory app, that will allow you to:

  • Scan barcodes and enter a product description and quantity
  • Save these barcodes,  descriptions and quantity to SharePoint or SQL
  • Display a list of all your inventory
  • Search your inventory and order the results by different criteria

Here’s a screenshot of the finished layout:

<———————–SCREENSHOT OF FINISHED HERE———————–>

Building the inventory front end

Let’s start by creating the basic layout of the application. We’ll have a home screen with 2 buttons, labeled “Enter Item” and “Search Items”.

Initial inventory home screen

Straightforward so far. In this part of the project, we’re going to be focusing on creating the Enter Items section.

Building the “Enter Items” screen.

Create a new screen in PowerApps, and set it up like so:

Enter items setup

There’s a few little things here that you’ll have to set up.

The “Scan” button is actually a barcode scanner object in PowerApps. You can add this by going to “Media -> Barcode scanner” in the insert menu at the top:

Add barcode scanner

In the “Product Description” text input, set the Mode value to:

TextMode.MultiLine

In the “Quantity” text input, set the Format value to:

TextFormat.Number

Also set the “VirtualKeyboardMode” to:

VirtualKeyboardMode.Numeric

Text Format quantity input

Perfect! The rest are just standard buttons and Labels, so lets move on.

Handling barcodes – pulling the scan data (the critical part of inventory management)

Time to get to the fun part. Reading the barcode numbers from your camera.

Note: You will have to use the application on your phone/tablet, as currently PowerApps does not support barcode scanning in the web version.

Alright! Select the “Scan” button we set up before, and find the “OnScan” action. Set the value of it to the following code:

Set(barcodeNum, barcode_enterItems.Value);

This will set a variable called barcodeNum to the value of the barcode scanner object we inserted before.

Now, the label you have that says “Barcode number: “. Time to append the barcode number to this. Set the “Text” property of this control to:

“Barcode number: ” & barcodeNum

Now go ahead, save your app and give it a test! Remember to add a Navigate() function to your home screen so you can get to the enter items page.

When you click the scan button, you should be presented with the camera on your phone, and a nice green square, like so:

Barcode camera scanner

When you hover over a barcode, you’ll hear a “BEEP” and then it’ll take you back to the app. Everything going to plan, you should see your screen with a barcode number on it:

Simple Inventory Management App - Part 1/3 1

You can fill out the description of the product, and add in the quantity of the product you have.

And that’s it for part 1! In the next part, we’ll look at setting up the back end so that we can save our scanned items to SharePoint or SQL. In part 3, we’ll look at searching the database and adding other cool functionality.

Hopefully by now you can start to see the power of this application, and your mind is racing with ideas. Leave a comment below or reach out to us on twitter at NextStep Aus, we’d love to see what you made!

Check out our other blog posts here:

Running SQL Queries in PowerApps

Attention class! Jacob here, and today we’re going to be talking about running SQL queries in Flow.

But why would we do that when PowerApps already has LookUp, Search, and Filter?
Because PowerApps doesn’t like doing the heavy lifting. And PowerApps will also use your device resources to handle these. So if you have a slow device…….you’re going to have slow functionality.

Now it’s no good making a claim like that without supporting it… Here’s a link for some Performance Comparisons in PowerApps: PowerApps Performance

That’s where SQL comes in. In other blogs, we talk about using SQL as a database for your app. Think of this like the backbone, and PowerApps is the pretty front end. So, why not use SQL for what it was designed for?

What is SQL good for?

    • Querying existing data based on changing conditions
    • Providing quick results from dynamic queries in PowerApps

What is PowerApps good for?

    • Providing the “good looks” of the app
    • Manipulating data already loaded inside the app

So, putting 2 + 2 together, we end up with A PowerApps front-end with an SQL Backbone.

 

SQL Query Structure

First of all, this isn’t a masterclass on SQL. I’m no SQL wizard by any means, but I will show you some entry level SQL that will help you manipulate your existing data without a massive performance hit.

For some more in-depth reading on SQL, see this link: SQL Tutorial – W3 Schools

An SQL Query looks similar to this:
“SELECT * FROM Customers WHERE Name LIKE ‘NextStep Creations'”

Let’s break this down a bit.

  • SELECT: The SQL statement used to select data from the database
  • *: All items. This can be changed to specific column names (customerName, customerID) if you only require certain data
  • FROM: Which table/source we are going to be selecting data from
  • Customers: The name of the table we’re pulling data from
  • WHERE: Used to start defining a set of condtions
  • Name LIKE ‘NextStep Creations’: Finds records where the Name column is LIKE the text in quotes

Here’s what that query looks like in PowerApps (with a custom added flow, which we’ll get to soon!):
Running SQL Queries in PowerApps 2

As you can see, we’re passing the entire SQL query from PowerApps, and using a text input as the dynamic content for our search.
Now, let’s create our own from scratch!

Step 1 – Creating the Flow

Create a new flow, and lets start with the PowerApps trigger. Add a step, find “Execute a SQL query (V2)” and add it.

Running SQL Queries in PowerApps 3

Fill in the blanks with your server name. Once you add your server name, it will let you pick from the databases you have (provided you’ve authenticated correctly):

Running SQL Queries in PowerApps 4

Once you’ve selected your database, add the “Ask in PowerApps” option into the “query” section.

After this, add a “Response” step, and set the “Body” to the following expression:

body(‘Execute_a_SQL_query_(V2)’)?[‘resultsets’][‘Table1’]
Leave the “Response Body JSON Schema” section blank for now.
Running SQL Queries in PowerApps 5

Name this RunSQL and save your flow, we’re almost there!

Step 2 – Adding to PowerApps & getting data

Create a new PowerApp, and create a text input (this will be our search field). Add a button below that, and then finally a gallery underneath the button. You should have a layout like so:

Running SQL Queries in PowerApps 6

Great work! Time to start setting the functionality up.

Button

Add the PowerAutomate flow we just created to this button:

Running SQL Queries in PowerApps 7

Set the OnSelect property of the button to the following:

ClearCollect(sqlSearchData, RunSQL.Run(“SELECT * FROM company WHERE companyName LIKE ‘” & TextInput1.Text & “‘”))

Replace “company” with the name of the table you wish to search against, and change “companyName” to the field in that table you want to search by.

Now, set the Gallery’s items to the collection we defined before, “sqlSearchData”.

Running SQL Queries in PowerApps 8

Awesome! Now you can test your app. Type something in the search box, and click “Search SQL!”.

Oh no! I got an error! Lets have a look why:

Running SQL Queries in PowerApps 9

Running SQL Queries in PowerApps 10

This may look confusing, but never fear! All this is telling us is that no data was returned from the search we made.

(in another blog we will cover error handling and how to deal with this. For the purpose of this tutorial, lets move on.)

Step 3 – Displaying the Search Results

Okay, time to do a test search with a company that I know exists, my own!

Running SQL Queries in PowerApps 11

Hmm…..no error, but we still can’t see anything in the gallery. Let’s check our collection and see if it’s empty!

Running SQL Queries in PowerApps 12

Huzza-WAIT. This isn’t the data we wanted……

Remember that field we left blank in our Flow earlier? Time to fill it out.

Step 4 – Providing the correct data back to PowerApps

Go to your flow, and open up the successful run. Expand the “Response” section, and you will see the data:

Running SQL Queries in PowerApps 13

This looks like exactly what we want! Grab a copy of the text in the “Body” section.

The data you see is in a format known as JSON. More information on JSON can be found in other blogs, or by visitng this link: W3Schools JSON.

Go back into your flow and edit it, and select the “Generate from sample” button in the Response step. You’ll be greeted with a lovely box. Go ahead and paste your body content we copied earlier into here:

Running SQL Queries in PowerApps 14

Select “Done” and your Response will now be filled with a JSON schema. Ensure that there is no keys that don’t have a data type. This will cause registering issues. More can be found about these issues in this blog post: Creating a HTML Report

Save your flow, and head back over to PowerApps.

Final Steps: Displaying data in PowerApps

First of all, we’ve made some changes to our Flow, so we need to remove and re-add it to our button.

Select the button, and then delete the OnChange section (this will remove the flow from being associate with this button).

Add the flow back in again, with the same value for the OnSelect:

ClearCollect(sqlSearchData, RunSQL.Run(“SELECT * FROM company WHERE companyName LIKE ‘” & TextInput1.Text & “‘”))

Running SQL Queries in PowerApps 15

Let’s run that search again and check our collection!

Running SQL Queries in PowerApps 16

VOILA! Great Success!

Now, go ahead and add some labels to your gallery, and set their properties to ThisItem.companyName (or whatever columns you have in your collection).

Running SQL Queries in PowerApps 17

Congratulations! You’ve successfully searched data in SQL using PowerApps, with minimal workload on the client-side required!