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

in this lesson we'll see how to manage the many-to-many relationship
to add, edit, and remove items
from an order. Basically we want to create
records of this
OrderItem here and we want to create these records
in the context
of the screen that we are on which is the Order Detail
so here we are on the Order Details screen
remember that we received as an input parameter to this page
the OrderId
we have here the order details and the list
of items in that order and now we want to
add will need to add a link here to
add an order item to this list. And this link
means that will need to navigate to a new page
where we are going to create
an order item
and we also
are going to use this page to edit
the order item
that we have here so this means that this will also link
to this page and since this is a page to
edit and to create a new order item
we will need to pass here in this link the OrderItemId
and this means that
on the Preparation of this screen we will need to fetch with an Aggregate
we will nee to fetch from the database
this order item so that it is
rendered here in the Form
now here in the Form
we are going to have to select
what is the product that we want to add
and what is the quantity
of that product that we want to add to that Order
but we also need to fill-in the OrderId
and the total price. Now the OrderId
we already had here on this screen because we receive it as an input
so we can send the OrderId into this new screen
as an input parameter also so here
these links will need to provide also the OrderId
so that when we create these element here
wee can fill-in the OrderId, the total price
that TotalPrice we are going to calculate when we save
when we save this Order Item
so this means that here on the action of this Order Item we will need to
to the form the OrderId
and the TotalPrice. And we will need to calculate the TotalPrice
with the price of the product we have here
so we'll need to get the product price, times
the quantity that the user selected
so the user selects a product we will need to get the price
of that product and will need to multiply that price
with the quantity that the user selected as well and we will assign
to the TotalPrice attribute
of the OrderItem and afterwards we can use
that CreateOrUpdate Order Item to store
these element in the database. Okay so
let's do all of this in
our development environment. Okay
so we'll need to create the new screen here
which will be
the OrderItem Detail
don't forget to tick the Anonymous role and for this screen
as we said we will need to input parameters
we will need the OrderItemId
and we'll also need to receive the OrderId
now with the OrderItemId on the Preparation
we will fetch, and I am going to use here this shortcut to drag-and-drop input
parameter to create the Aggregate
we will fetch the OrderItem from the database and
with this data that we fetched from the database we are going to
bind it to the Form on the screen
let's add here a Form and bind it
to the result of the Aggregate
Okay now as we said we only need to request the user to select
the product, let's drag-and-drop the Product into the Form
and the quantity
the other attributes we are going to programmatically set them
on the Save action and now we will need to
add a button to our page
which will save this Order Item
let me double-click to create the action
and before I implement the save action let me just finish here the screen
by creating a title
I'm going to have here a condition on the title
and this will be OrderItem
when matches Null
than we are actually
'New Order'
adding an order item, otherwise we are
'Edit Order Item'
and lets complement this information with
the title with information that we are
doing these for the Order, and the number of the order
that we receive as an input parameter on the page
so here I'm adding an expression with OrderId
okay so this is done remember that
we first need to fetch the Product
the user selected so that we have the price. So to do this will use again an
Aggregate to fetch the Products from the database
so let me add here the Product
and I need to filter these products I need to filter with the one the
user selected so
add a filter and the condition is that
the ProductId must match
the Product the user selected. I am going to get
that from the record
in the form so here this
ProductId holds the value the user selected
okay so this narrows down the products to just
that single product, but I want to do one more thing here because
since I already have here the price and I have access to the quantity the
user selected
I can add here another attribute to calculate the total price
so in an Aggregate we can manipulate the data
to extract more information one way of doing this
is to create calculated attributes. In this case we will calculate the
using the Product Price at true that we have here
and the Quantity that we have on the for
so let's go ahead and do this this will be
that Total Price and let me
add here the expression that calculates the total price, so again
this is the price of the product
times the quantity
the user selected, here so
on the Form, this attribute
and that's it we're done. Now we can use
these attribute in the Save action
to assign that value to the form. So let's go back here
to the Save action and now that we
fetch the information from the database about the price of the product
we can assign that value to the Form
will actually if you recall will need to assign also the OrderId so let's first
select here
the OrderId attribute, this one and we will assign to that
attribute the input parameter
we receive on the page OrderId
and we will assign to the total price
this one, we will assign the output
of the Aggregate. Now notice that this
aggregate that we created not only returns the Product Entity
but it also returns the calculated attribute
we added to the Aggregate so here we're going to use the Total Price
and now we can use the CreateOrUpdate
Order Item
the source will be the record of the form
and we're almost done, all we have to do now
is redirect the user back
to the Detail Screen in this case remember that we are
navigating from the detail into the order item well
actually we are not yet I'm going to create those links
in just a second. So go back here into the
Order Datail and we will need to fill-in the OrderId
and we will fill-in this with the input parameter
and finally go back here into the OrderDetail
and let's add a couple of links here to edit
the order item so these will link to
the OrderItemDetail
and we will need to pass the Order Item Id
we will get from the Table Records this one
and also the OrderId. And these we will get
from the input parameter
and we need to add a new link here after
the table to add a new product so 'Add Product'
and this text will link
also to the OrderItemDetail
in this case the OrderItemId will be Null
so we want to create a new Order Item, and the OrderId
will be the input parameter of the page
one final thing that we're missing here in terms of managing this many-to-many
relationship is the ability to remove
an order item so let's add here a column to
to our Table Records and create here a link 'Delete'
and this text will link
in this case this will link to a new screen action
where we are going to delete
you're going to delete the Order Item
here, DeleteOrderItem
and we will need to indicate the identifier
and we will get that identifier from the Table Records
OrderItemId and that's it
we're done let's publish and test adding products
and managing this many-to-many relationship
okay let's go into our order and
we don't have we don't have any order items let's add a new product to our order
so new order item in order number one let's add
this product and let's say that we have
only one of these, save
and there have it. Mac Book with
this total price and let's have another one
let's say that we want to add also a couple of
USB cables and there we have it with that total price
let's edit this and say that actually it's
three, not two and notice that
the total price is being calculated with the price of the product
and it's updated here, and we can also test here
the Delete and it's working now let me try to
show you one other thing remember that we created the
index to prevent the user from adding the same product
more than once to the order so let's try to
add again the Mac Book Air to
our order let's save and notice that
we will get an error we will see how to implement valuations later on but
this is just to assure you that this
rule is validated you will get no
duplicated products in the same order
and that's it