Model and Query Data
Create and Display One-to-Many Relationships
This lesson is part of the Developer Online Class for OutSystems 9 course.

in this lesson we'll see how to create and display
one-to-many relationships using Foreign Keys. It will allow us to define the
of each purchase order. Let's start by creating the
Order Entity and right-click here
add an Entity and call it 'Order'
and we will have to add a couple of Attributes to this Entity
the first one will be the SubmitDate
and before I and the next attribute let me just
create here diagram and show
that entities we have so far on this diagram so the Client
the Product and also the Order
now the next attribute that we're going to create for the order
is the one that will allow me to specify
the client for each order so
let's add here a ClientId
and when I press Enter, take notice of what happens here
in the diagram. Now, see
this created the relationship between the Client
and the Order Entity. In the platform
relationships are created by defining an Attribute
that references another Entity this is usually called a Foreign Key
and in these case, this Attribute ClientId
references the Client Entity because the data type of this
attribute is actually Client Identifier
okay so let me set the
attributes as mandatory so the SubmitDate
is mandatory, the ClientId
also mandatory and finally let me define the default value
for the SubmitDate. Whenever a new record is created
this Attribute will have this value by default
and you can type here a value that matches the datatype
of this attribute and for dates for instance for the date datatype
you can use here the function to return the current date
when the record is created. That's what they're going to use
okay so we finished defining the data model
let's publish before we continue to create the screens
so back to the interface and
let's change here the homepage
and we're going to create the Orders
screen where we're going to list the Orders. And don't forget to
tick the Anonymous role. Remember we don't want to handle security
at this point, we will deal with this later on. Now to create the list screen
let's open the screen let's give it a title 'Orders'
and we will need to put a widget here to populate the orders but before we do
we need to fetch them from the database and for that we will need to add a
Preparation to the screen and
fetch the Orders using an Aggregate. Now
notice that while I'm dragging the Order Entity
into the Aggregate, the Client Entity is
also brought along so when
to related Entities are added to an Aggregate
a join condition is created to define
how the records from both entities are combined
since the ClientId is a mandatory attribute
you'll see here in the sources menu you will see
both the the entities and you see the join condition
and as I was saying since this is a mandatory attribute the join condition that was
will return the results where we'll find
Orders only we Clients. Okay
so we're done for this let me
sort the results that we'll get from this Aggregate
and I want to sort the results by the OrderIdentifier
we are actually going to show the OrderId
in the interface so let me
unhide the order ID which was hidden
by default so usually you don't show identifiers
in the user interface but in this case we are going to show
the OrderId and let me sort
the Orders from A to Z
okay so we're done fetching the data let's get back to the screen
and we will need to add a Table Records to the screen
bind this Table Records to the result
of the Aggregate that we have on the Preparation so here
GetOrders - List, that's the one we want
and define the structure of what we want to show. And what we want to show on the
Table Records are
Orders so drag-and-drop the Order Entity
and this will bring along
also the Client Name. So again the OutSystems Platform
determines that since this is a Foreign Key since the ClientId is the Foreign Key
to the Client Entity
this is going to get the name of the Cient and put it here
in the table instead of showing this identifier
now as I said I want to show the OrderId
in the table so let me drag-and-drop the OrderId there
and that's it we're done for now for the Orders screen
let's go back to the main flow and let's create
the Order Detail so that we can create orders in our application
we are going to use this Order Detail also to edit
the order so we will need to have an input parameter
OrderId and
we will need a preparation to fetch this OrderId
from the database so I'm going to show you here a trick. Usually
what you do is that
you drag an Aggregate into the flow
you drag the Entity into the Aggregate and you create the filter
to get the order where the identifier is
is this input parameter. A shortcut for you to do
all of this in only one operation is to drag-and-drop
the input parameter into the flow
and it will create the same Aggregate
to get the Order
with the filter already created
okay so this is a neat trick for you to use and to save some time
implementing your features. Okay so we're done with the Preparation let go
back to the screen
and let's add the title, so remember that
we need to test if the OrderId
is Null or not
let's add an UI If, set the condition
when our input parameter
is Null then we will show here
'New Order' when it's not Null
let me just say that we are watching the
order number and add an Expression
with the OrderId, so double-click here
select the OrderId from the Aggregate - the Current element
okay so we're done for the title we will need to add a Form to the
main content area so drag-and-drop a Form
bind the Form to the Aggregate
in the Preparation to the results of the Aggregate actually so the Current
element again and
drag-and-drop the
Order Entity into the Form
Okay so Submit Date, the Client
we will need a button to send this information back to the server
'Save', double-click the create the action in bind it to the button
and here we are going to use again the CreateOrUpdate
set the Source Parameter to
use the Record property of the Form
done, and now redirect the user
back to the Orders screen
Okay, the save action is done, let's add a Cancel button as well
here, label it
'Cancel' and again when the button is pressed
redirect the user to the Order Screen
and we're done for the Order Detail
and don't forget the tick the Anonymous role, I was
almost forgetting. Let me go back into the main flow
we need to be able to navigate into the Order Detail
and we will do this to edit
the Order so need to create a link here
in the Table Records
so link to the Order Detail
and set the OrderId parameter
and also add a new
order link here on the actions. 'New Order'
link to - Order Detail. And in these case, since we don't have
the OrderId we want to create a new one, we will pass the
NullIdentifier() special value. Okay so we're done
let's publish and create a couple of orders in our application
okay so here we have the Orders list, we don't have any orders yet
let's create a new one and as you can see
the Submit Date was filled with the default value
and now I want to set the client - Alexa
and save, there we have
the first Order, we can edit this order
its Alan for instance and save it's okay
let's try to create a new order and
let's try not to define the client in
and save this order
and as you can see this returned an error
and this returned an error because we set the client
we set the client as a mandatory attribute and we didn't fill-in
later on we will see how to handle valuation in this form but for now
it's just to
assure you that when you set a Foreign Key as Mandatory
this will be validated and the order was not created
by this operation. That's it.