Expand all | Collapse all

Display Data from Multiple Related Tables on Form

  • 1.  Display Data from Multiple Related Tables on Form

    Posted 12-06-2018 14:44
    I am hopeful that this question is not too elementary, as I can't help but feel like I'm missing something simple.

    I am creating an app that, among other things, will be used to manage various tasks tied to learning administration. There are three tables involved in what I am attempting to accomplish:

    Course -- Think of this as the overarching learning unit. For example, we might have a course entitled "Introduction to Quick Base".
    Class -- This would be a specific instance of a course being taught. So, there might be an "Introduction to Quick Base" class taught in December in one location, in January in another location, and so on.
    Course Tasks -- Whenever a class is offered for a given course, these tasks will always have to be performed, regardless of any other details surrounding the class.
    (I also have a fourth table, Class-Specific Tasks, to differentiate anything that might only need to transpire within the confines of a specific class offering.)

    What I am hoping to have happen is, whenever someone creates a new class for any course, that the appropriate details from the Course Tasks table flow into the data entry form in the Class table, with two particular features:  First, I would like for the Course Task to appear with an automatically calculated due date based on the "Days" field from Course Tasks and the "Start Date" field from the Class table. Second, I would like for users to be able to change the "Status" of a given Course Task to "Completed" once the task has been performed (but, obviously, only for the specific class being administered).

    So far, I have the following relationships set up:
    Each Course has many Course Tasks
    Each Course has many Classes
    Each Class has many Course Tasks

    While this sort of works in terms of getting the Course Tasks to flow into the Class form, I can't seem to get a Due Date to calculate (or, at least, populate), nor can I set each respective task's completion status for the given class. I feel like the displayed Course Tasks is a straight copy from the master Course Tasks table as it pertains to the course, but not really tied in any particular way to the class, which is why the Due Date fails to appear. 

    Does anyone have any ideas on how to get this sorted out? Am I even remotely on the right track?

    Many thanks in advance for any and all assistance.

  • 2.  RE: Display Data from Multiple Related Tables on Form

    Posted 03-20-2019 21:02
    If I am understanding you right, try adding a relationship - A Class has many Courses. this should allow you to create a lookup field (start date) from the class table for the due date calculation (formula date field)..