Mobile Development Basics
Retrieving Data in OutSystems
This lesson is part of the Developing OutSystems Mobile Apps course.

Welcome to Retrieving Data. In this session we like to discuss how we are
able to retrieve data in OutSystems and there are two major ways for us to
do that. We can use aggregates to retrieve data and we can use SQL
queries. When we use aggregates there are a number of things we need to define
about them and things that we can control to go ahead and get exactly the
types of data that we're looking for and when we use SQL queries obviously
they'll be inputs and outputs and then we'll execute that SQL to go ahead
and get the data that were interested in.
So let's get started looking at how we can define the types of data that we're
looking for through these two different approaches.
So when we think about retrieving data we mentioned that we do have aggregates,
and aggregates have an icon, the aggregates are available both on the
mobile and on the web side, and there's no SQL background really needed. All you
need to do is be able to think about the types of things that you want and the
editor will help you visually describe those and then it will attempt to go
ahead and build the request to get you what you're looking for. When we talk
about SQL queries really this is a server-side thing only
so when we think about having the SQL queries we can't run SQL queries on
the mobile devices themselves, we can only run them on the server side so the
SQL queries can run any type of custom SQL that you're interested in,
you can even have access to database specific so if you have an oracle
database and you want to run a particular command you can do that and
the bulk operations are all there as well. So we could go ahead and get
clients from a particular table and we could go ahead and get that information
using an aggregate or using a SQL query. SQL query has a little bit more
flexibility the aggregates are a little bit easier and quicker to build if you
get used to using the visual editing.
Once we run these queries whether it's an aggregate or SQL query there will
be output, and typically when we're asking for information from a table
there may be a list of those coming back, and from that list we should also get
the count of how many of those types of things that we were looking for came
back, so when we begin working with this in OutSystems and OutSystems visually
shows the aggregate as a variable and is something that exists, and then
right below it shows the list itself and then if we go further down you can see
that the count comes back, and then once we're inside the list, the list will have
many different items so whichever one of the list is the current one
so if we have a list of clients than the current client, in the list of clients,
will have an Id and have a Name and then we'd also be able to know if this is a
current one in the list what row number in the list are we
what's the length of the list or is the list currently empty so there are
different types of information that we're going to get as part of the output
and having this type of information can be important because we may have to loop
over the information that comes back from the database and we have a for each
capability that we can use in actions and then we'll be able to loop over
these lists to get the types of data that we need back. So inside of
aggregates we can create aggregates in a number of different ways. If we're on the
mobile side we can actually attach an aggregate directly to a screen so here
we can see the HomeScreen that is a mobile screen that's available and if we
right-click on the HomeScreen we can fetch data from the database because
this is a mobile device we could fetch data from local storage because we might
have a local storage entity and then we can fetch data from other places so once
we say we'd like to fetch data it will go ahead and create an aggregate for us
and it will give it a default name of Aggregate1 and that
get one will be attached to the HomeScreen. Once the aggregate is there it
says please drag-and-drop some sort of entity or attributes so we know what to
try to retrieve with this aggregate and we can go over to the data tab and we
could say give us the clients and then it would go ahead and create the
aggregate to retrieve clients for us so this is one way to go ahead and create
aggregates. Once we create the aggregate then the aggregate editor opens and you
can see that because we chose the clients entity and changed from the name
aggregate to GetClients and you can see over in the HomeScreen and changed it
to GetClients and right below it shows the Client entity and if we open it
shows its structure so that we can get all the information about the structure
of the data that is available. Until we publish it will give us this warning
saying hey we know you want clients and we know clients are the source of this
particular aggregate but we don't have the capability of bringing back any
preview data because we we don't have access it hasn't been published it so we
have to publish things to be able to to get the access to them now over here in
details when we look at the properties of a GetClient aggregate it will have a
server request timeout it will have a maximum number of Records to bring back
and if you want to see sometimes as we build aggregates they get more and more
complex see the type of SQL that will be executed to go ahead and run this
type of query to get this information back we can go look at the executed
SQL it's here and it's part of the properties, if we double click on it, it
opens and we can see it now not only can we attach aggregates to screens but a
lot of times we'll be using aggregates in flows, and to be able to do that we'll
go to the aggregate inside of the toolbox and when we drag and drop it
from the toolbox we can put it inside of any action flow in between the start and
the end of an action
and the same thing will happen it will bring up the generic aggregate one
editor will have to choose some entity or attributes and then it will be a
defined aggregate that will be used inside of this action flow. So let's look
at the aggregate editor in a little more detail.
It's basically an excel like display of the real data that's available in that
particular table. We can add more entities to any given entity that we're
currently working on and that will add different sources to this particular
aggregate at the moment, so this aggregate at the moment is getting clients but if
we wanted to do something a little more interesting like get clients from a
state or something like that we could add another source to go ahead and make
sure that we find and join some of the information about addresses and states
and other types of things. So we can have different sources, we can have different
filters, we can sort information, and we can use some test values. All this is
available inside of the aggregate if we click on sources here we can see that we
have clients and we have products. So we may have a ClientId inside of the
ClientId we may have different products that have been purchased and the Product
has a ClientId because we know who the product belongs to as far as that
particular Client so we could join these two tables and say I want the clients
with or without the products so I need all the clients back, and if their
products related to it great bring that information with it and if not I don't
need that information so there are joins and similarly to in databases we know
that their inner joins and outer joins left hand joins we have a number of
different descriptions here, such like we could have clients with products and
then we get both and we get all of it or clients with or without so there are a
number of different choices there and the types of different choices that are
available refer to or relate to some of the different types of joins that we
can do.
Now besides the sources once we have the sources we may want to filter some of
the data and filtering the data just basically becomes the SQL where
clause. So we can say give us these sources so maybe we're getting clients
with or without products and we want the clients with or without products when
the clientId for the Client entity that were working with is the input that came
in from the screen or the action or whatever else so we have an input ClientId
and that's what we're going to search for. So that will typically if it's a
Client with a single id it will return one instead of those and then once we
have the information back we could sort it. We can sort it by the clients Name, we
could do ascending sort descending sort. So lots of interesting things to
be able to to deal with and that really works on in the sorting the same type of
thing that we would do with order by. Now the sort that we just saw a second ago
was a regular sort and it would be on one of the attributes that's available
from the set of entities that were working with as sources but we may want
to be able to do dynamic sorting and dynamic sorting would be done with
some sort of information that was passed in as a variable so here you can see an
input variable and we can either say that we want to go ahead and sort this
according to a particular entity and it's attribute, and if we do that, we have
to use this structure to go ahead and format the string so that it knows that
we are talking about a particular entity and it's attribute, or if we have what we
call computed attributes you can just write the computed attributes name and
it will be fine there as well, and then we can choose whether we're going to
sort these descending or ascending and that can be defined here as well, so
there's a slight difference between just a sort of an individual type of
attribute and the dynamics sort that can come in from variables that we are working
with. Last but not least very interesting and useful thing is test
values, so we can define different types of information coming in and we can go
ahead and give it a test value, and then there's a test button and once we click
the test button it brings back the information that would be returned if
someone had the input that was this particular Id so that can help us kind
of test things before, and it's basically a preview but it helps us at development
design time know what we're looking for.
So this means that we now have aggregates that have lots of flexibility
and we can test them out which is which is kind of cool
this does give us sort of an Excel spreadsheet kind of look and feel so we
can see which entities which attributes they show up as columns what's available.
If we end up having multiple entities with lots and lots of attributes we may
get overwhelmed by the amount of information we have on the screen. At any
given time you can go ahead and right click on one of the entities and its
attributes, and say that you'd like to hide it, and once we hide it
it slides and becomes just a tab in between the other entities and
attributes that existed there so you can control the look of what's happening
inside of our aggregate editor so that you get the information that you need
and you don't get overwhelmed by the rest of the stuff. You also have the
capability in these aggregates to aggregate functions, so if we go back one
second and look at the high capability when we went to hide this attribute we
also have the capability of grouping by this ProductId and we could group by
any of these particular columns so we can group buy the ProductId, we can
group by the ClientId, and when we grouped by those things then we can also
use different types of functions like we could count them, and do some other
things, so when we go to aggregate the records or rows we could go ahead and
say yes i'd like to go ahead
and group all of the Ids, and when I group all of the ids I'd like to
count of those IDs so here for grouping by ClientId and we can see that there
are six Client Iss the count comes up as six so there are a number of different
things we can do we can some of them average them find the max and min do
a count so all of those things are available to us because we may need that
type of data from the tables that were working with especially when we're
joining tables and have more complex data, and then last, we have some computed
adhoc attributes so you can see that at the end of the list of attributes that
we have we always have this new attribute button, and if we click on the
new attribute button it will create a new attribute, we can give it a name and
then we can define the formula or the way in which this will be computed, so if
we were looking at certain types of products then we might want to create
the total price for those products and we'd like the amount and take
the amount of a particular product that's being ordered times its unit
price those maybe attributes that are available and we could go ahead and
create this extra attribute that's a calculated and computed attribute
because we already have all the information
that's being returned from the query that were running. So those are
aggregates that's the editor and that's what let's get our information back a
lot of times when information comes back from an aggregate it will be a list so
there will be lots of information so it could be just a list of integers or we
were looking at a list of clients sometimes it'll be a list of multiple
entities so because we did a join it will be a list and we'll have some
country info some date info and text info and all of those come back as part
of that query, so those will be created as part of the output and those outputs
will often be what we just mentioned as a list
so here we can see an action flow and we're creating an aggregate that says get
customers that might be named Michael so we create whatever that aggregate is by
listing the sources and the filters and the things that we do and then, when we
get the information back will see that when that aggregate runs it returns a
list of current so this will be the current selected row inside of the list
and the customer is available so the customer will you know in this case, will
have access to its last name so this is the way the normal information looks if
we run a simple aggregate on one type of data one entity and we're looking for
one set of information sometimes we're actually going out and say we're trying
to retrieve information about movies that are currently in different cinemas
and theatres. Well, to do that, we might have joined several different sources. We
needed all the cinema information, we needed the movie information and then, we
had a reference attribute that we called MovieSession.
Well if we have that type then what we could actually say GetMoviesWithCinemas
will return a list and that list will have all this information. So if we
wanted to see that type of information how could we see it or if we're
assigning that to an output variable maybe we wanted to add extra information
to it.
How could we do that? Well one of the things that we could do is we could come
in and say that we'd like to use the data type editor to go ahead and modify
what's coming back and in this particular case the output variable can
see that when the aggregate brings information back it had Cinema
information, Movie, and MovieSession information, and then we could have added
a new attribute so here it says add an attribute we can add this ExtraTextAttribute
and we can go ahead and define it
we give it whatever name we want it could be a different type but we do have
the capability of creating new types especially when they're going to be in
variables they can
then consume or hold all the information from the aggregate and
anything extra that we'd like to be able to go ahead and add to it. So that takes
care of the aggregates there are also SQL queries, and the SQL queries
are the things that give us a little bit more power because we can get whatever
we want. Iin the aggregates were listed were limited to the way we join the
sources and the types of filters and the types of sorting and that are available.
The aggregates really nice because it's visual and it's quick but if you need
something a little more powerful because you actually want to write your own
SQL statement you can go ahead and do that. So inside of the SQL queries
were basically manually writing our own SQL, and we can go ahead and do all
the things that you might be used to so you can go ahead and look for the
different entities and their attributes and if you know a lot about SQL
you'll be able to handle that.
Now when you work inside of the SQL editor you'll notice that there's a very
specific way of representing entities because we need the curly brackets
around them, and then any of the attributes have square brackets, and
there will be highlighting so that we know all of the SQL keywords are in
blue all of the entities or in black in the brackets and any of the attributes
are in red in the square brackets so we can come in and write whatever type of
SQL that we want. If we need input parameters we can define them up here at
the top and we need to be able to define what the output structure is, so here for
selecting Client and all of the rows by from the Client and we're ordering it by
obviously we're going to get a list of clients back so the output entity
structure is the Client entity that table that were actually bringing back.
So lots of information that we need to put into the SQL query to be able to
get that information back. So there may be input parameters and if there's an
input parameter and we want to use it in the query which if we have an input
parameter we should be using it somewhere, it will show up as green
and it has a @ symbol in front of it so that we can go ahead and know where
it's being used and again highlight exactly where some of the pieces are in
the actual query. Now right below the information at the top we have the
capability of not just looking at the SQL but doing test input so just like
we could test the aggregate we can test this SQL query. We can go ahead and
have the different parameters that are going to have test values put in the test
value because this was the input so here's the value that would be an input
and then if we clicked on test output and we ran the query which show us what
the output of that query would be. Sometimes it's nothing
so when we do hit the test button you know we have to be careful about the
types of input that we have because occasionally there will be nothing there
and if there's nothing here that will show you the the number of rows returned
so that you can tell it did actually run and there were any errors it's just that
you've got nothing returned. And then we define the SQL with all of those
entities, attributes, keywords and variables that are coming in underneath
the covers its having to make this call to the OutSystems server and you can
actually see the details of what that real query is going to be. This isn't
really a human-readable it's a lot of the generated code that OutSystems
creates for us but occasionally if you're debugging or trying to solve
complicated problems because there's some sort of error
you may use this we try to stay away from that and not have to worry about
this but occasionally it's something that you you may need to understand and
be able to work with. Now there's one other thing that you can do with the
SQL statement that is interesting and that is sometimes we want to to
create or use a SQL statement against the tables that were working with but, we
don't need it to be a query we just wanted to do something so say we wanted
to just delete the Product table
well we can go ahead and write delete from Product and
it will get rid of the product table but for us to be able to do that we do
always have to have an output entity structure so in this case we are
deleting things there really is no return or output but we do have to have
just a default dummy output here any kind of structure or entity would be
fine and then it will go ahead and run this and it will do what it needs to
do so not only are the SQL queries queries but occasionally we can use the
the SQL's sort of as commands or statements against the database to get
it to do what we want. So we spent a decent amount of time looking at
aggregates and how we can use the visual editor to set up the sources and filter
and sort and do some of the more advanced things with aggregates. So that
we can get data fairly quickly without having to know a huge amount about
SQL. If you know a lot about SQL ready then just go ahead and add the
input parameters that you need, define the outputs you'll be able to see the
executed SQL and have all the control that you're used to, so you have these
two different approaches and you'll be able to use those to get data on the
server side when we work inside of the mobile devices especially with local
storage we have to work with just the aggregates, so this hopefully give you a
good idea of how to retrieve data you're going to get a chance to use this a lot
as we begin building more applications.