AnsweredAssumed Answered

Populating a field in a related table based on a condition

Question asked by MichelleBenes on Jul 4, 2013
Latest reply on Jul 9, 2013 by MichelleBenes

Title

Populating a field in a related table based on a condition

Post

     Hello,

     I'm hoping there's a relatively easy way to do this, but I haven't been able to so far.

     I'm creating a database of training courses. One requirement is that we keep a history of price changes for the courses. I have a table for the Courses, and a related (1-to-many) table of Course List Prices. The Course List Prices table contains the following fields:  Course ID, List Price and Date.

     How can I retrieve the current list price (the list price with the latest date) to populate a field in another table?

     One idea I had is to use the "GetNthRecord" function, but I would need a way to ensure that the current price is the first one. Is there a way to sort the records by the Date field when doing a calculation?

     Thank you,

     -Michelle

Outcomes