I have a Table [UNIT] with relationship a table [PRICE] (prices change but units do not)
I would like to be able to sort the records in UNIT based on the last (most recent) record in [Price]
how do I best accomplish this??
Simply sort from UNIT the PRICE::price field. But I think more information is needed. What are you trying to accomplish with the sort? Is this for a list or portal? How do you see this working or being used?
I have tried that, however the sort does not have the desired result (not sure if it sorts by last entry only)
The portal sort is fine.
I have my Unit records in a list, with a portal to PRICE (single record/sort to show most recent) and I am trying to sort the list of UNITS based on the most recent PRICE of each UNIT
Can you post an image of what the sort looks like? Are you sorting a number or text field?
This is an image of the screen,
the field for "price" (number not text) is showing the correct "current price" from the PRICE table,
however although the sort order is set to Descending, you can see how price are not sorted correct.
You need to do one of the following:
In the definition of the relationship (not portal) between UNIT and PRICE, sort the records from PRICE by date, descending. Then you can use the PRICE::price field when sorting your report produced from UNIT;
Leave the relationship unsorted, and define a calculation field in the UNIT table as Last ( PRICE::price ). Then use this field for sorting. This is assuming prices are entered in chronological order.
Retrieving data ...