Forum Discussion

BobFaulk's avatar
BobFaulk
Qrew Member
3 years ago

Summary Field Problem not giving me accurate info

I run a clean & sober housing program where we have 28 houses with over 250 people living in our houses. Our parent table is Houses, the child table is "Beds". We have a summary field called "arrivals" that tracks the "Minimum bed start date" of people coming into our houses and another summary field called "departures" that tracks the "Maximum bed end date". The problem is we also have people that move from house to house sometimes and even within a house from bed to bed. I'm trying to find the number of actual arrivals and departures in our program by house, not the actual program. A person can move from house A to house B and not leave the program. A person can also move from bed 1 to bed 2 within the same house but that person may not have arrived in our program in that house at all. The person may have arrived in our program 1 or 2 houses ago, moved around within different beds in that house, left that house and moved to the new house, moved around that house x # of times and then departed our program. Each time a person arrives and becomes an owner of a "Bed" in our beds table, that record has a start date and end date when its closed. This gives us the history of the bed in the house but I cant figure out how to track the actual arrivals and departures because even though we may have a person start a bed then leave a bed on specific dates, that person may still reside in the house just in a different bed so my summary field is tracking an arrival and/or departure when the person may actually still be in the house. What can I do to get the correct numbers filtered through my summary fields. I have several summaries that I need I.E. arrivals last 30 days, last qtr, qtr 3 and qtr4 as well as departures for last 30 days, last qtr, qtr 3 and qtr4 as well. Thanks



------------------------------
Bob Faulk
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago

    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
    ------------------------------
  • BrianSeymour's avatar
    BrianSeymour
    Qrew Assistant Captain
    3 years ago

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