Hi Henrik -

I'm guessing your VACC table tracks each vaccination given to each horse. So you probably categorise the schedule of vaccination given, e.g. basic, 30day, 150day, yearly. If you do then that makes it easy to create a field in your Horses table that can tell you what coming up.

I did a test db that has two tables, Horses, and a child of Horses, vaccSchedule. I have two fields in vaccSchedule beside the relationship filed to Horses, date and schedule. Date is a date field and schedule is a choice field with 4 choices (Basic, 30day, 150day, Yearly).

Then in the Horses table I add a field called Vaccination Due and put the following formula in:

let curRec := this; <--grabs the data of the current record
let dueDate := last((select VACC where Horses = curRec) order by Date);<--finds the most recent by date vaccination record for the horse
let vacDue := days(dueDate.Date, today()); <--gives you the number of days between the last vaccination and today
switch dueDate.Schedule do<--this takes the simple choice field and depending on the choice selected does the following
case 1:<--if the most recent record is basic then it tells you when the 30 day is due or if over due
if vacDue < 30 then
"30 day due in " + (30 - vacDue)
else
"30 day over due"
end
case 2:<--if the most recent record is 30 days then it tells you when the 150 day is due or over due
if vacDue < 150 then
"150 day due in " + (150 - vacDue)
else
"150 day over due"
end
case 3:
if vacDue < 365 then
"Yearly due in " + (365 - vacDue)
else
"Yearly over due"
end
case 4:
if vacDue < 365 then
"Yearly due in " + (365 - vacDue)
else
"Yearly over due"
end
end

So you end up seeing something like: