Should GetNthRecord recalculate when referenced field changes?
I have a problem trying to create a return-delimited list from a set of related records, using GetNthRecord functions to find each of these related records.
I am creating a Search screen which starts off showing a long list of all records, which can be whittled down through a series of free-text filters (through multi-criteria relationship) and a portal filter. I need to filter fields from two different (related) data tables. (This is because the database contains survey records for possum nest boxes, and when people send in new survey data they identify the nest box surveyed by any one of many criteria, including site name (in parent table), nest box number (in child table) or GPS (in child table).)
I have created a relationship between the Search table (containing global fields) and the parent data table directly; and, to create a relationship between the Search table and the child data table I've created a field in the parent table that calculates a return-delimited list of all the related records in the child table (luckily there are exactly 3 nest box records per site). The global search field then links to the return delimited list.
It works beautifully... Until I add new records.
New records do not show up on the Search layout. This seems to be because the return-delimited list for the new record is not happy. Does it need to be promted to recalculate? Or is there a better way to set this up?