Filtering via relationship and current date

[madman411]

Hi all

I'm implementing the ability to control cost pricing via separate records so there's a record history of changes. Currently the price table is related to the asset table and filtered via the relationship (Creation time stamp) to show the newest price for that asset and in turn being used in any transactions. It works just fine, however now I'm wanting to allow entering future pricing by adding a 'beginning' date field. For example:

PRICING FOR ASSET X

$450 (beginning) 10/01/2021
$400 (beginning) 01/01/2021

The price field for Asset X on the asset layout will display $400 until October 1 2021 rolls around despite there being a "newer" record with a value of $450, if that makes sense, and stay at the most recent price until a newer price record is created with a later/newer date. I'm assuming this will now become a calculation referencing the 'beginning' date and the current date to determine which record should be shown, but I'm not sure where I should start. Any ideas appreciated!

[comment]

If you want to keep it simple, then leave the relationship unfiltered as:

Assets::AssetID = Prices::AssetID

and use a one-row portal filtered to show records where:

Prices::EffectiveDate ≥ Get ( CurrentDate )

This is assuming that price changes are not too frequent (say no more than a few hundred prices per asset) and that prices are entered in chronological order (or that the relationship is sorted by date, ascending).

You will need a filtered relationship for looking up the price into a table where you record transactions, though.

 

[madman411]

1 hour ago, comment said:

If you want to keep it simple, then leave the relationship unfiltered as:

Assets::AssetID = Prices::AssetID

and use a one-row portal filtered to show records where:

Prices::EffectiveDate ≥ Get ( CurrentDate )

This is assuming that price changes are not too frequent (say no more than a few hundred prices per asset) and that prices are entered in chronological order (or that the relationship is sorted by date, ascending).

You will need a filtered relationship for looking up the price into a table where you record transactions, though.

 

Thanks Comment. This got the ball rolling for me.