It’s been a wild ride but we are now starting the last (number nine!!!) in our long series of articles about relationships. For those of you who are coming to this post but who would like to know about all the others in the series, the end of this article has all the links that you would need. So this time out, we are going to discuss Iterative Entity Instances.

In reality the concept of iterative entity instances is kind of related to work that we discussed earlier when looking at inferring instances. But this is a particularly common case in certain industries and business scenarios so it got it’s own chapter. It uses a technique seen earlier called inductive logic – you can deduce something about an instance based on the instance that came before.

Iterative Entity Instances are exactly what they sound like – a series of instances inferred for some logical reason or to reach some business goal. The first and most well-known example of iterative entity instances is the loan payment scenario. Let’s look at it in these terms. You have a loan of 10,000 currency units and want to pay it back. You know the interest rate and you need to know how long it is going to take to pay it back.

For those of you with a good memory back to the article about creating a string of text from entity instances, you will probably have thought about one key term – this is going to be a loop. We need to loop through the amount to pay and reduce it each time. And, as looping in Oracle Intelligent Advisor is always something that needs to be controlled properly, we are going to set an upper limit – 2 years, 5 years or whatever to ensure that if someone enters some very large numbers we don’t spend all day wondering what happened.

So we can look at it like this, ready to use our Iterative Entity Instances:

Collect the loan amount, the yearly interest rate and the amount you want to pay per month. Consider that on day one, you will have the entire amount outstanding. Then, as you progress through the months, we can deduct the payment amount from the remaining amount – taking into account the interest you pay – and repeat until either we reach a remaining amount of zero – you have paid off the loan – or you have taken longer than however long we decided was the upper limit – 2 or 5 years or whatever. This is where the Iterative Entity Instances come in.

So just as in previous examples we create two relationships. One, to handle the first month to kick off the sequence. Then a self-referential relationship to cover all the others, counting down to the point where either there is nothing left to pay or we have reached our time limit.

So our relationships look a bit like this:

The first month is just a typical relationship to identify one month, which we already infer in the first line of the rules shown above.

The second relationship is the cool one, since it is a self-referential relationship and it uses a reverse relationship as well to help us implement the Iterative Entity Instances.

So now the rule designer can define the payment iteration thus. The payment month amount means “how much is left to pay”:

In the first month, we owe the entire start amount. In the other months, use For(the previous payment month, the payment month amount * ( 1+ the monthly interest rate ) – the monthly repayment amount. So let’s break that down.

Assume a loan start amount of 100, and that I want to pay back 10 a month, with 0.03 annual interest rate (3 per cent, in effect).

In month zero, we have 100 to repay. The annual interest rate is 0.03. So the monthly interest rate is 0.0025.

In month one , we have 100 * (1 + 0.0025) – 10 which gives us a value of 90.25 still to pay.

In month two, we have 90.25 * (1 + 0.0025) – 10 which gives us a value of 80.4756 still to pay.

And so on…

The amount is rounded up to two decimal places for neatness, and the value assigned will be the maximum of either the value or zero, so that when we reach the last month of the loan we don’t start generating a negative amount since the last payment will not be 10, but whatever is left over.

Credit where credit is due, this example is a slightly modified version of the Worked Example 5 that you can find in the online documentation for Oracle Intelligent Advisor.

To finish here is a walk-though of both the example and some custom Input and custom Entity Container extensions to get the result working on a single Screen, so that it looks like this, complete with dynamic scrolling entity container and an auto-update of the Screen when you change the monthly repayments:

The video (don’t forget to subscribe to our YouTube Channel as well!):

Previous Articles in this learning journey

Containment Relationships were discussed here.

Inferring Containment Relationships was discussed here.

Reference Relationships were introduced in this article.

Inferred Reference Relationships were discussed here.

Self-Referential Relationships were videoed and discussed in two parts : an introduction, followed by a more advanced example.

Creating Mirror-Image Instances was discussed here.

Reverse Relationships were videoed and discussed in this article.