I 100 % agree with you @ChristopherCounsell about getting this into the Field Maps Form. Great spot to have this functionality.
Although I'd say that this isn't unleashing more power to the ArcGIS Online user as this concept is almost already there. A user today can navigate to the hosted feature service, click the data tab, click the attribute field of a parent table (say the LatestInspection field), click "calculate" and then write an arcade expression to update this value to the latest related inspection record. All of this is available and working and accessible with no MAKE, enterprise sde, FME etc
To accomplish this task, I wouldn't expect one someone would need to implement an entire enterprise environment or automated nightly scripting or webhooks from survey123.
I'm thinking that a local bushcare group should be able to collect tree data in a simple AGOL environment, 1 feature service with 1 point layer and a related table. When they add an inspection record to the related table, then the point layer gets the Latest Inspection Record. They just set this up on the hosted feature service (or like you mention in a form). IMO, this isn't complicated data management concepts or high compute load that requires a mindset of a RDMS on an enterprise setup.