Here is my situation.
I have a table that stores Bookings, and a related table that stores one or more correction records (that table is called Production Cycles) for each booking.
Our customer services manager needs to be able to search for Bookings based on the Despatched date against the most recent correction record.
In order to facilitate this I set up the following (Each correction gets assigned a correction number, 1 for the first correction, 2 for the second etc...).
So I added a field to the Booking table:
CountCorrections Calculation Unstored, from Bookings, = Count ( Production Cycles::CorrUID )
I then set up a relationship between Bookings and Productions Cycles as follows:
I then tried adding the following field to my Booking layout:
Newest Production Cycle::ProofDespatched
But when trying to search on that field the following error occurs:
"This Operation cannot be performed because one or more of the relationshops between these tables are invalid."
So I added another unstored calc to the Booking table that picks up the Newest Production Cycle::ProofDespatched field.
After adding that field to the layout, the searches have been working fine for the last few weeks, but now they have stopped working.
Anytime we search on that field it says there are no records found that match, which is not true:
When I perform the find it says there are no records matching.
Does anyone know why this has stopped working?
Is there a better/more reliable way to set up this functionality?