Tip:
Highlight text to annotate it
X
In this lesson we will see how to model many to many relationships
and display them on the screen.
We will extend Purchase Management application to allow the input
of multiple purchase products.
So let me open the entity diagram right now,
and what we want is to add a relationship between product
and purchase so we can have in the database the purchased products.
This will be the many to many relationship that we'll model with a third entity
or with a new entity that will be Purchase Product.
Name it Purchase Product
that we have it, and let me drag and drop it
to the data diagram and let's start adding the attributes to it.
So that the first one will be a foreign key to product so Product ID
and the second one will be a foreign key to purchase, Purchase ID.
We are not done yet.
We need of course to add the quantity
and the total price.
Let's set all these as mandatory.
This one, the purchase id as well, the quantity, and the total price,
and since there are no composite keys in service studio, we will need to create
a unique index on top of the Product ID
and Purchase ID to prevent duplicates.
Let's just double click the purchase product entity.
In the indexes tab right here, we can create new index
by clicking New and then setting the name of the index,
let's say ID X Unique, something like that, and then set it as unique,
and then this index will be on top of the Product id, and Purchase id,
so whenever you try to insert a duplicate, a combination where these two attributes
already exists in the database, the database will throw an exception
and will not allow the insert. Let's go and have create the show page.
Interface, Main flow and create a new screen,
that will be the Purchase Show.
Let's double click on the Purchase Show and we will as always add a new input parameter,
that will be the Purchase ID that we want to show.
We will create a title in this page, Purchase, and of course let's add the number
of the purchase that in this case is the ID of the purchase.
Let's add the show record to the page to display the purchase.
So we need to go through to the data and drag and drop the purchase
on to the show record, and now we need to add a query
in the database to get the purchase.
So let's add a preparation to the show page
and simply drag and drop the Purchase ID.
We also need to get the client,
there's the join automatically added
and we need a client because we're displaying the client's name.
That's why.
Let's select the show record and bind the query in the preparation
to the show the source record, actually the show record,
then we're good to go.
Let's add a link to edit to allow the user to edit
this purchase
and right click on the link
or on the text of course and link it to the edit page.
Purchase edit and don't forget to supply the Purchase ID parameter
of the show page. We're done with this.
Now let's go to the list and now that we have the show page
we can just right click, we can just link this to the show page instead of the edit
Link to another destination, purchase show.
All right, let's take a look at the main flow. Everything seems to be working.
Now go back to the show page and complete this screen.
We now need to add a master detail
with the list of the products in this purchase.
So let me drag and drop the table, table records here,
and we are going to show the purchased products.
The purchased products drag and drop. Of course we need a query
in the preparation to get this data from the database, so let's just do that.
Purchased products
and we will need to get the purchased products for,
and it's important that you stop a second to think about it,
for the purchase id.
So if you leave this as it is, we will get all purchased products in the database,
but we need to filter this by this ID.
So I am going to use the shortcut to query.
Simply select the Purchase ID parameter which is this one and notice how the query
is automatically updated to have a parameter and a condition
so the filter is done.
Let's go to the show page and bind the source record list property
to the output of the query, to the list and that's it.
By the way, we forgot to add an entity
in the query so that can get the product name as well, so let's open the query,
right click, and select the product.
Going back to the show page,
we are going to add another column so we can have a delete link
that will allow us delete products from the purchase.
Type delete, link to new screen action, and this delete will basically execute
the delete purchased products entity action.
We need to supply of course the ID coming from the table.
List current purchased products ID and this way we allowing the users
to delete.
Okay, we're almost there.
All we need to add right now is a way for users to add products
to this particular purchase and that's what I'm going to do now
with an edit record.
So let we drag and drop the edit record here and this edit record
will basically allow me to add a purchase product to this list.
We will need of course a button
and let me label this add product.
Double click the create the screen action and this is the tricky part,
let's start by adding an assign note that will assign the Purchase ID
to the edit record variable.
Let me just double click open the Purchase Product Edit Record
and check the record variable and we will need to assign the Purchase ID
because it's not in the form for the user to assign it.
Let's double click and assign it to input parameter of the page.
Next, we will need to query the product that user selected
to get the price and then compute the total price.
So let me drag and drop the product
and we want to filter this by product id, so I'll add a parameter to this query,
product ID.
The ID we're going to supply comes from edit record.
Product ID and after this we have the product,
and now we need to compute the total price.
So let's just say that
the total price equals
quantity times the unit price that we get from this query.
Product
price.
After this we need to add the record to the database using an entity action
that will allow us to create a purchased product
based on the variable from edit record.
If we remember we don't have products in the database, so I'll just bootstrap
the database with a few products.
Create bootstrap action from Excel,
here is the product Excel file proceed.
Let's publish and see this in action.
Well just before doing this, let's take a look at the purchase show really quick.
We need to remove total price from the form.
Well, we should be good to go, publish.
Open in browser, okay, we have two purchases.
Let's start with the first one and it seems I forgot to set the show page
as anonymous, let me do that real quick.
The show page set as anonymous, publish.
Okay, open in browser again.
All right, let's click the first purchase and select a product.
Let's say this screen and we want three of these, add product.
Three of these. We can delete it, we can added it again,
and let's say Finding Nemo 2.
And if you try to add Finding Nemo again, we will get the database exception
that is not allowing us, that is caused by the unique index
that we added in the beginning.
We will later change this combo to only show the products
that are not already added to the purchase.
All right that's it.