Qrew LegendI have an idea for an approach.
The basic concept here would be to have the ability to calculate the # of arrivals and # of departures say by house, but just for a Single Focus Day. Then have a Pipeline loop through to recalculate for each Focus Day say back 365 days ago and then write those to a history child table under each House (one House has many House-Dates).
The key field of the House-Dates table would be a concatenation of the Related house and the date. By having a custom Key field you can have a Pipeline create an upsert and not have the possibility of duplicate History records.
To get it working for a single day, I would Create a Table called Focus Date with a single record in it which will be Record ID# = 1. Then create relationships to any other tables in our app with a reference formula field with a formula of 1 and lookup the Focus Date. If you can calculate the Arrivals and Departures by House for that single date then you will have broken the back of the problem and then it will just be a matter of setting up a Pipeline to run each night to populate the history going forward and then a one time pipeline to loop through all the dates going backwards Note that the historical pipeline will not work with a FOR EACH loop as those fire asynchronously (ie all at once) . It would have to be a callable pipeline which calls itself.
