Forum Discussion

KarenWondergem's avatar
KarenWondergem
Qrew Trainee
8 years ago

Confusion about Status across multiple tables- Project Management

Total newbie, I am learning but I apologize for the fairly easy question I am sure. I have 3 tables in my app: 
  1. Courses
  2. Projects
  3. Change Log
Each table has a multiple choice Status dropdown: 

Course Status (Main Record: Course ID)

  • LIVE
  • ARCHIVED

Projects (Major Projects)

  • Major Project Pending
  • Major Project in Progress
  • Major Project Completed

Change Log (Minor Changes)

  • Minor Change Pending
  • Minor Change in Progress
  • Minor Change Complete

All projects and changes have launch dates. A project or change can be Complete, but will launch on a certain date (the dates are in a connected Calendar table)

I would like all three to relate to each other, but in the following manner: 

  • All Courses have the default value of LIVE unless we move to ARCHIVED
  • If a Major Project or Minor Change is started for a course, the user changes the Status to Pending or In Progress. The Status then shows up in the main Course table so if the main record is accessed, it shows the course being worked on in a project or minor change.
  • When the Major Project or Minor Change is marked as Complete, the Status in the main course table will automatically change to LIVE when the Course Copy date occurs. 
Is this possible? If so, how can this be accomplished? 
Ask about Quickbase functionality
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    One approach to this is to calculate the Status on the Course table.  There can be two status fields on the course record.

    [Status Manual] and then [Status] with the latter really being [Status (calculated)] but with just a clean name of [Status].


    You can roll up a Summary count of the # of child records in various statuses and then based on those, then calculate the final status.  The alternative is to get into "Actions" to actually try to update the Course status field when certain conditions are true, but my first suggestion is to calculate the Status as a formula-text field
  • KarenWondergem's avatar
    KarenWondergem
    Qrew Trainee
    8 years ago
    The Status multiple choice field should be centralized, am I correct? Right now there are three status fields- one in each of the table. Do I make another table with a list of all the statuses? Would it then be a lookup field in each of the tables, and from there I apply the formula you mention above? 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    No, we are not on the same wavelength.

    The status fields for thew two child tables would not be changed at all.  They remain normal drop down multiple choice fields.

    I am assuming here that in fact you do have relationships setup right? Like 1 Course has many Projects?

    It is just the Status for the Contract which is depending on the Statuses of the children, so that is the only one which would end up being a formula field.