Rental prices for articles

[j.s]

PROBLEM:

GIVEN 2 tables with the indicated fields:

Table "Articles"

- ArticleID

- Price

Table "RentalPeriods"

- RentalPeriodID

- PricingFactor

All Articles in table "Articles" can be rented for the periods listed in table "RentalPeriods". Rental price for a rental period is calculated by Articles::Price * RentalPeriods::PricingFactor.

WANTED: A portal in the article layout that lists ALL rental periods PLUS corresponding rental prices (as defined in the last sentence) for a selected article.

EXAMPLE:

GIVEN:

Article (1 record):

ArticleID = 1, Price = 100$

Rental periods (3 records):

RentalPeriodID = 1, PricingFactor = 0.3

RentalPeriodID = 2, PricingFactor = 0.6

RentalPeriodID = 3, PricingFactor = 1.0

WANTED OUTPUT:

Portal:

==================

Rental | Rental

Period | Price

==================

_ 1 _ | _ 30$ _

_ 2 _ | _ 60$ _

_ 3 _ | _100$ _

==================

Preparations taken in attachment. Thank you very much for your help!

rental.zip

[comment]

Here's one way to look at it - IF you can accept looking at a single article at a time.

rental1.zip

[j.s]

Here's one way to look at it - IF you can accept looking at a single article at a time.

Wow, that was quick, thank you very much! Yet, can you think of a solution without script triggers, so its compatible with fm<10? Sorry, i forgot to mention that.

Thank you again, i am kind of overwhelmed by your 'speed of help'!

PS: One article at a time is fine.

[comment]

You could use one of the script-triggering plugins, if you wish. For myself, I always preferred to restrict users to scripted navigation only - and include the update of the global in that.

[j.s]

You could use one of the script-triggering plugins, if you wish. For myself, I always preferred to restrict users to scripted navigation only - and include the update of the global in that.

Thank you again, Comment! Does that mean that there is no way to automatically load the correct portal values - without using script triggers nor any similar plugins?

Since the Articles table contains only a few hundred records and is not growing fast, i thought of a fancy hack, yet got stuck again during its implementation. My idea was the following:

1.) Add a calculation field cbCurrentRecord (boolean) to the Articles table that marks the currently selected article (in the current window).

2.) Then add a relationship from table RentalPeriods to Articles, such that all article records are selected (cartesian).

3.) Sort this relationship by Articles::cbCurrentRecord (descending order), such that it points to the currently selected article (in the current window).

4.) Through this relationship it should be possible to reach the current article's Price field that is necessary to calculate the rental price for each period.

PROBLEM:

I couldnt find a proper definition for cbCurrentRecord. Again, this calculation field should be '1' for the selected article in the current window, and '0' otherwise. Can this be done without plugins and in a FM<10 compatible manner?

Preparations made in attachment rental2.zip. Thank you so much again for your assistance!

rental2.zip

[comment]

I couldnt find a proper definition for cbCurrentRecord.

Me neither.

---

There is another option, and that is to use a joining table in order to create a true Cartesian product - i.e. one record for each combination of article and pricing factor. But maintaining such table also requires some scripting to be triggered when new records are created in either table.

Edited January 15, 2011 by comment

[comment]

On a belated second thought, perhaps you could do it this way:

rental3.zip

[j.s]

Holy cow! That's incredibly creative! Got to show this to my FM-friends! :-) Thank you so much! If you sent me an invoice, i'd pay it.. :-)

[comment]

I don't see an address... :tongue: