Summary Field Problem not giving me accurate info

I 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.
 

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

I'd need to re-read this one when I have more time, but it sounds to me like you'd want to consider a pivot table (many-to-many) between Beds and People.

So assuming your Beds don't move between Houses, the relationships may be something like:

- A House has many Beds. And a Bed belongs to one House (one-to-many)
- A Person has many Beds. And a Bed has many People (many-to-many)

That leads us to consider the following tables:

- Beds
- Houses
- People
- Occupancies (Bed Person pivot table)

Then, in the Occupancies table, you would store the Related Bed and the Related Person, as well as the Check In (Datetime field) and Check Out (Datetime field). That'd set you up where you could have a summaries on Beds to determine their Availability (max Check In and Check Out datetimes). 

Conversely a summary on the People table could show you where the Person is currently "Bedded."

You may want to consider a formula field to house the Status on the Occupancies table to leverage in conditional summaries. For example,

// Occupancies::Is Available (Checkbox) 
If (
    not IsNull([Check Out]),
    true,

    // Default to unchecked
    false
)

Or maybe you'd need to another table to track Cleanings that relates to Occupancies too or another datetime on Occupancies. Anyway, if you delegate the Status or "helper checkboxes" logic to the pivot table, that may make summarizing a bit easier.

We're getting off topic a bit, but hopefully, that gives you some ideas!

Quickbase is great for rapidly prototyping, so sometimes, I'll build a separate "playground QB app" using the free QB Builder Program account and try various data modeling and different ideas. Then, once dialed in, build out in the "production QB app."

It's important to really try to poke holes in your model. Try "searching for the nouns" and avoid overriding data if you need to retain history, as it seems like the case here. Maybe study hotel booking systems too as that seems similar!

------------------------------
Brian Seymour
------------------------------