In the following image, there is a portal to the Training table and user notification controls at the bottom. What I want to do is include a merge field in the space next to each of the radio buttons at the bottom that reads “Renew training in ___ days” and is entered as “Renew in <<Training::Renew>> days”.
To clarify, the Defensive Driver course, for example, was completed on 6/28/2018 and needs to be retaken in four years. So, the space below Defensive Driver on the bottom should read “Renew training in 1461 days”.
My calculation field called Renew in the Training table is:
If (Training::Course ID = "100261";
Max (Training::Course End Date) + 1461 - Get(CurrentDate)
The problem is it is only looking at the first row in the Training table to see if the Course ID = 100261. The calculation works if I change the If statement to search for 100014 because, as you can see in the image, it is the first entry.
My question is: how do I get it to look through all of the rows to find the most recent entry with the Course ID of 100261?
Also, once four years has past and this employee needs to retake the Defensive Drivers course again, the fields for the Defensive Driver course will not be updated, a new entry will go into the table. This allows us to keep a history of the classes taken.