Adding Tables

Santiago Uribe Montoya
A free video tutorial from Santiago Uribe Montoya
VP Product at AppSheet
4.4 instructor rating • 1 course • 12,260 students

Learn more from the full course

Create Business Applications with AppSheet

A Basic Guide to Build Data Driven Mobile Apps & Web-Apps in Minutes

57:09 of on-demand video • Updated August 2019

  • You'll learn how to get started and publish data-driven mobile apps with AppSheet
  • You'll learn the design principles of data-driven apps
  • You'll learn how to manage mobile apps, starting with data structure, applying different behaviors in the app, and then manage publishing and access
  • You'll learn how to add maps to your apps
  • You'll learn how to add image galleries to your apps
  • You'll learn how to create a database mobile app
English [Auto] The most essential part of every app sheet app is the data actually absolute is a structured interface that goes on top of your data that allows you to interact more specifically securely and mobility with that data. So when you're thinking about building your first app sheet app you want to think first about the data. So I've got a Google Sheet here that has four main tables and I've got a little example table over here that we're gonna use to just teach a few lessons. But this app has four main tables facilities which are the different properties that we're inspecting inspection points which are different points of interest that we're going to be performing inspections in and then inspections which are the actual data capture form entry that we're going to be doing that's going to result in you know added business value and then we have our staff table which contains information for all the people who are going to be either performing inspections or overseeing and managing those inspections. So to add a new table you're going to make sure you're in the app editor you have it open you go to the Data tab on the left and then we're going to be in the tables section and you'll notice here the top app sheet is smart is already looked at my Google Sheet. Notice there's two tables in my Google Sheet that I'm not using inside my happened so it's it's giving me a suggestion to add one of those so you can do. You can click right there and I would add it nice and easy for you. So we're going to add staff back in now that staff is back in I have a few controls that are really important. So I get to choose one but I call the table. So I want to call it staff someone to leave at the same time and then I'm going to get to control how people interact with the sheet inside the app so whether they have permissions to add new records to update existing records or to delete any record that's in the table or I could limit all of those and give them read only permissions which means they can see the data but they can't really do anything to it for the staff table. I'm going to leave it at read only because we only need to see the data. We don't need to add or update or delete so I'm going to make that change and I'm in a press safe. Now let's dig into a little bit of the specifics of what makes a data table proper and good and usable inside of our app. So a spreadsheet is a great canvas for putting all sorts of data and doing calculations. But when we start to build an app on top of our spreadsheet we need to follow some specific guidelines to get the most value from it. So you'll notice for instance in the facilities table in the first row we have what I call headers which are names of names which are names for each column. Let's look the facilities table the facilities table in row one has a name describing each column so each individual row is a record of data so many rows of data is considered a collection. So this is a collection of many different records and we have tables that each contain a collection of many different records. So this is some of the terminology that we're going to use to describe what we call database formatted table an important element to every table that you create is something called the Primary Key or the identifier I.D. column. The key column helps actually distinguish this row from every other row in the table. This is very similar to something like Social Security numbers how every person born in the United States is given a unique number to identify them because their names might be too similar the key value your idea column is extremely important especially as you begin to build more advanced apps and start tapping into a feature called references. So our sample app here takes advantage of references and we'll explain it in a later video more in depth. Let's take a look at three or four common errors that people run into with their database tables so let's start with what's good. We'll look at this table structure and we'll kind of look at the other versions of this table that will introduce problems when we bring in an app sheet. So in this table we have an order idea. And this is just a list of fruit orders or fruit picks from a fruit farm. In some state that's that's being shipped out and we have an order I.D.. So this is our unique identifier and you will notice there are unique values in every single row. So we know this is a good stable key value. And then we have one row with unique column headers where every column header is different from the other one and all rows are fitting within the structure of the column headers. So our category has all the right categories quantity. We have all numbers in here shipping date all dates states all states. Let's look at the non distinct Rose error for this example. There's no stable key or I.D. If we look at so commonly people try to use dates as keys or identifiers for their data but it seems that two people have added a row on the same day. And so now we have two of the same key and this becomes a problem when actually tries to go and retrieve you ask it for one eleven 2019 and it doesn't know which one to grab. And so it's going to grab this row one time and it's going to grab this row another time. So we have to make sure there's always a stable key. Now this issue non distinct columns doesn't actually cause an error inside of app sheet right away. But as you continue to build on your app it will make it unstable so this error the double headers and merge cells error is common when we kind of take advantage of the open canvas of spreadsheets. So we have a row here with good identifiers but we had this kind of second hetero that groups some of these and it actually merges we're using merge cells here. So this isn't going to be helpful for our app sheet app and could cause problems. So what we want to do is just delete that row right there and stick with a good stable one hetero our final error is a pivot table error so a pivot table is not really a stable set of data. It's more of a report or an aggregate statistic or calculation. The reason this is a problem is because we have a row down here that isn't related directly to our column headers and in fact it's it's doing calculations on many of the rows above it which is not an operation that actually necessarily supports you can extract this type of information through the actually platform using expressions and different type of table setups but we'll go into that in another time. So you don't want to use. You don't want to try to bring in pivoted tables into app sheet. In conclusion how information is collected modified or viewed in your applications will depend on the structure of your data tables and will influence the ease of app updates in the future as well as ease of use. So to build a great app you have to have a good solid and sturdy database Foundation.