I suppose that you use FM12, otherwise…
The solution is to use the ExecuteSQL:
ExecuteSQL ( "SELECT Price FROM Table_Course WHERE Date = ?" ; "" ; ""; ExecuteSQL (" SELECT MAX (Date) FROM Table_Course ";"";"" ) )
But unfortunately it does not work because the second SQL query returns the date in the format yyyy-mm-dd and the Date in the WHERE clause is in the format dd-mm-yyyy.
This is for sure a bug (I will report it)
So, do it in this way:
max_d = ExecuteSQL ( " SELECT MAX (Date) FROM Table_Course ";"";"") ;
max_d2 = Right(max_d;2) & "-" & Middle( max_d; 6; 2) & "-" & Left(max_d;4);
p = ExecuteSQL("SELECT price FROM Table_Course WHERE Date = ?" ; "" ; ""; max_d2) ];
Now it works
Usually the method is to create a Rates Table and have your Course list Prices table lookup the price in the rates table. The history is then preserved in the original table (Course List Prices). When the price for a course changes, update it in the Rates table. The new price will only apply to records you create after this. Lots about lookups in the online help.
Gianandrea, I've tried your solution and it's returning blank. Do you know if there's a way for me to test/debug what I have?
Rick, I'm not sure I understand your suggestion. I still don't see how to make sure to lookup the most recent rate/price.
You would have only one rate record per course...not the many records in your current structure.
The lookup would have only one record to look up from.
The historical rates would be accesible in the course table since each course would have looked up the rate from the rate record in existence at the time the course record was created.
Actually, you can have many rate records for the same course in the same table. The trick is to sort the relationship in descending order by a date field in the table that records the date when the rate goes into effect. Sometimes, you then have to filter that a bit more to omit future dates, but this still allows for a relationship where the first related record is the one you want and then standard auto-enter options access the correct related record.
PhilModJunk - That's exactly what I'm trying to do. Can you tell me how to sort the relationship in descending order by the date field so that I can just select the first one?
Open Manage | Database | Relationship.
Double Click the relationship line to open a dialog box of relationship details.
Click the Sort option for the pricing table.
Select your descending sort order.
As long as you don't enter a price record for a price that has not yet gone into effect, that's all you need do. For cases where you create the records before they go into effect, you need to use an unstored calculation field with Get ( CurrentDate ) as an additional match field matching with an inequality operator to the effectiveDate field as an additional pair of match fields in the relationship:
CoursesSelected::CourseID = CourseRates::CourseID AND
CoursesSelected::cToday > CourseRates::EffectiveDate
That worked! Thank you!
I knew there had to be an easy way.