[MUSIC PLAYING]
CHRISTIAN SCHALK: Hello, and welcome back
to the Building with AppSheet series.
I'm Christian Schalk, a Google developer advocate.
And in this episode, we'll build on what
we started in our last episode, by showing
how to use multiple tables together in AppSheet.
We'll also cover some other useful new concepts
along the way.
Let's get started.
So in this next demo, I'll be extending the Tasks application
from the prior episode.
I'll show how to connect the Tasks table to a new Owners
table using an AppSheet Reference.
Additionally, to provide better visibility to the Owners data,
I'll add and customize a new Owners View.
Before jumping into the demo, though, here's
some more information on AppSheet references
and relational data.
AppSheet references allow your apps
to support relational data.
In database terms, relational data
simply means that records in one table
refer to one or many records in another table.
This is important because business apps pretty much
always have relational data.
AppSheet references are implemented with a special Ref
data type, which can be applied to a column in a table that
refers to another related table.
For the Ref column to work, though, it
needs to have the same name as the other table it's
referring to.
You'll see in a moment that AppSheet can automatically
recognize relations in data and apply references automatically.
Incidentally, this works the same with either Sheets or SQL
databases.
Let's now return to our Tasks demo app
and extend it with a new Owners table.
So here's the underlying Tasks sheet
that we used in our prior episode,
but you'll also see that I've updated it.
I've added a new Owner column here.
And this Owner column now also refers
to this new Owners table.
So they're essentially the same name, the Owner column
referring to the Owners table, except for this Owners
table now has the full information for each owner.
And because it's the same name, AppSheet
will then recognize that these two tables are related.
So let's go ahead and test that.
We'll generate a new application.
So you select the AppSheet, Create an App option, directly
from Google Spreadsheets.
And we'll now set up a new application in AppSheet.
And so here we are.
We have our new app.
It's fully functional, but you'll
see that it only has the Tasks table.
So it gives us the option to add a new table for Owners.
But before I do that, I want to show you something
very quickly.
Let's take a look at the columns.
And so here's the columns for the Tasks table.
It all looks as we'd expect.
And we can see the data type for the Owner
column is of type Email.
So now, I'm going to create a new table for Owners.
So let that take a few seconds, and it
will generate the new table.
And now, we have both Tasks and Owners together.
And before continuing, I'm going to take a quick look
at the columns again for the Tasks table.
You'll see that the Owner column, the data type
has been updated to Ref.
Whereas previously, it was Email, but now it's a Ref.
And it's a Ref data type because it's now
referring to a Owners table.
And here's the Owners table that the column is referring to.
And these columns all pretty much match up.
But you'll notice that there's a new related Tasks column,
and this is actually a virtual column.
So let's open that up to explain this a bit more.
So first off, it's a virtual column,
meaning that it's calculated on the fly,
and it's using this app formula, which is essentially just using
this REF_ROWS function to link the Tasks and Owners
table together.
And so that is what allows these tables to work together.
All right.
So now, I'm closing up the tables.
And let's take a look at the app.
So the app itself, it has, of course, the task information,
but now I can see the owner of the tasks,
and I can even drill down on individual owners
and see their related tasks.
So this all works perfectly good,
although I think I might want to update the app a little bit.
I want to make it so that I can start from an Owner's View,
meaning that I can see all of the owners in one view
and be able to drill down on each owner individually.
So how would I do that?
Well, it's actually quite trivial.
All I have to do is click on the UX tab
here and then click on New View.
So now I have a new view, and I'm going to name it Owners.
And for this data, I'm going to, of course,
select the Owners table.
And now, it's just a matter of customizing this new view.
In this particular case, I'm going to use the Deck view,
so that gives me a nice UI that I can see
all the owners in one glance.
I can also change the position of the link.
So in this case, it's set to the left side.
I could try it out on the right side
or just switch it back over the left side here
and where I want it.
And finally, if I wanted to maybe toggle
a few other features, like, for example,
changing the image shape, I could select the round image.
But in this case, actually, I'm going to go ahead
with the square image.
So that's basically it.
Those are all the updates I'm going
to do for this particular app at this time.
And let's go ahead and try out the app a little bit.
So starting with the Owner view that I just added,
I can, say, click on a particular owner
and drill down on the respective tasks that this owner owns.
And maybe I want to update a particular task--
complete budget proposal, that is.
And so let's go ahead and change a few things here.
So I'll go ahead and click on the Edit link.
And maybe I'll set the due date to a later date.
And then I'm going to go ahead and reassign the owner
to be someone else.
In this case, I'll select Susan Spencer to be the owner.
So then I'll click Save.
And now, it's momentarily thinking back
to the spreadsheet.
So let's take a look at the data inside the spreadsheet.
So here, we have the same task.
And you'll see that it's got the updated date and it's also
got the updated owner.
So you could see in rather just a few trivial steps,
I was able to create a fully relational database web
application without much effort at all.
So for more info on AppSheet, Google Cloud,
and Google Workspace, check out these links and also
in the description below.
And for more videos like this, make
sure you subscribe so you don't miss out.
Thanks for watching.
[MUSIC PLAYING]