IMO a boolean field is not of much use for this. I'd use a date field. If it's null, it's current. Anything with a date would not be returned in a query if you create it that way. As for the related records, there'd be no parent record in the main data returned by the query (if you set criteria to be null), therefore no child records would be returned anyway. Having archive field in the child tables is redundant and only adds an unnecessary level of complexity.
Plus, a date field tells you when the record was archived as well. A checkbox doesn't do that.
Last edited by Micron; 06-26-2023 at 09:06 AM.
Reason: clarification
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.