Forum Discussion

BarryDolan1's avatar
BarryDolan1
Qrew Member
5 years ago

I want to create a list from a field on a form

This seems like it would be common knowledge, but I can't find any threads that address it specifically. I would be grateful for any assistance. Sometimes my biggest struggle is asking the right question. Hopefully I am making sense in this description.

I have a House, a House Mgr, and a Bed assignment.  New Bed records are added when a client is assigned to the bed, and the Bed record become inactive when the client leaves the bed.
The House has many beds. The House has many House Mgrs. But there is no relationship between the Beds and the House Mgrs.

The House doesn't change, but the House Mgr can. Whenever I open the Bed record I see the House, but only see the current House Mgr of the House. If I'm looking at a closed Bed record it will show the current House Mgr and not the House Mgr who was assigned to the House at the time the Bed record was closed. 

I need to be able to pull up closed Bed records and see who the House Mgr was at that time, not who it is currently. Additionally I want to see all House Mgrs who were assigned to that House during the time span the Bed record was active and occupied.

I think I'm looking for a way to write the House Mgr into a static field so it doesn't change whenever the House Mgr changes. 

I'm at the limit of my limited knowledge. 
Thank you

------------------------------
Barry Dolan
------------------------------
Tables and fields
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Is there only 1 House Manager for a given House at a time?  So they may change from time to time but at any time there is only 1 House Manager for a House?

    Can you tell me which way you have your relationship between Houses and House Managers?  Which is the One and which is the Many in your current setup.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • BarryDolan1's avatar
      BarryDolan1
      Qrew Member
      5 years ago
      Yes, only one house manager at a time. But there could be multiple house managers during the lifespan of a bad record. Never simultaneously though.

      There are 2 relevant relationships;
      Many Houses belong to one House Mgr assignment 
      Each House has many House Mgr assignments





      ------------------------------
      Barry Dolan
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        There is a feature of Quick Base called a snapshot field. Read about it here 
        That will allow you to freeze the value of the House Manager at the time the Bed is assigned.  That will be that you will have two values for the house manager, one would be called the current house manager and the other would be called house manager at time of the bed assignment.

        As for seeing the history of all the House Managers over the course of the dates of the bed assignment, There are a variety of approaches to that. 

        It appears that in your design you have a table where one house has many house managers and therefore you have a history of the house managers overtime. Presumably those records have dates on them as to when they started and ended up being the active house manager.

        So one approach to showing a history of all the house managers between the dates of the bed assignment would be to create a button which would run a report. The report would have <ask the user> questions For the start date and the end date and of course the related house. So the user would click a button and it would run the report and answer it's own questions and provide a result on a button to click.

        If you did want to try that approach then create that report with those three questions and then post back and I can help you with the URL formula which will run that report and answer the questions. 


        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------