Price based on JoinTable issue

[Devin]

 

I've got 4 tables.  Product, Vendor, VendorProducts and Product Price.

A Product can have multiple Vendors. Each Vendor has their own price for a product.  My issue is I want to also Track the Price changes a vendor makes but also show the latest pricing when looking at a product. 

When setting up the Portal to list the VendorProducts on Products is not an issue but returning the latest Product Price based on a timestamp is the issue. I could create a script to place the latest price in the VendorProduct but that seems like the wrong approach.. The other thought is maybe create a Calc Field in VendorProducts to return the price is thats possible? Or maybe there is even a better way....   Thanks

 

 

[comment]

If you define the relationship between VendorProducts and ProductPrice to sort the records on the ProductPrice side in reverse chronological order, then the Price field placed in a portal to VendorProducts will show the most recent price.

Alternatively, you could define a calculation field in VendorProducts as:

Last ( ProductPrice::Price )

This is assuming that either the relationship is not sorted and prices are entered in chronological order, or the relationship is sorted in chronological order.

[Devin]

Sorting that Portal worked perfect.. 

Thanks

[comment]

13 minutes ago, Devin said:

Sorting that Portal worked perfect.. 

I hope you mean the relationship, not the portal. Sorting the portal should have no effect.

[Devin]

Yes.. I did mean relationship..Sorry about that.