Discussions

 View Only
Expand all | Collapse all

Compare values based on Pre-Requisites

  • 1.  Compare values based on Pre-Requisites

    Posted 03-16-2020 13:16
    Hi, 

    I need help in figuring out how to validate if the user currently registering for the training has attended the corresponding pre-requisite training. 
    To elaborate, 

    Trainings - Registrations (One-to-many)
    Courses- Trainings (One-to-many)
    Courses- Pre-Requisite Courses(One-to-many)

    User will be able to "Register" for a training session, with the "Add Registration", for a specific training. 

    How can i implement the logic to check whether the user registering for the current training has attended a pre-requisite course training?

    ------------------------------
    Divya P
    ------------------------------


  • 2.  RE: Compare values based on Pre-Requisites

    Posted 03-16-2020 13:32
    Divya,
    I do not have a lot of time right now, but here is a quick thought.

    If you make a combined text summary field on the Course record to summarize the course codes for all the Pre-Reqs, then the course will "know" its Pre-Reqs.

    Then that can be lookup up down to Trainings.

    Then that can be looked up down to Registrations.

    Similarly, I assume that 1 User has Many Registrations (you did not show that relationship), so you could summarize all the Registrations where the User has passed the course.

    Then you would need to have a formula to calculate if each Pre-Req was contained in a string of course codes that the user has passed.


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



  • 3.  RE: Compare values based on Pre-Requisites

    Posted 03-16-2020 14:38
    Thank Mark. Will try and keep you posted.

    ------------------------------
    Divya (OSSE-Contractor) Pogaku
    ------------------------------



  • 4.  RE: Compare values based on Pre-Requisites

    Posted 03-16-2020 14:44
    When you get to the point where you have a field with a string of the pre Reqs and then a String of the User's courses taken, I can help you think though that formula if you get stuck.

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



  • 5.  RE: Compare values based on Pre-Requisites

    Posted 03-16-2020 17:50
    Mark, 

    I am at a point in creating relation ship between user and registrations. 
    Currently, all the registrations lie in the same table- Registrations. 

    Can i use quickbase actions to create a record in User table and copy the email address, when ever a new registration record has been added? Email address will be the key for the user table. 
    I did this. but not able to derive the number of registrations tied to the user email.

    ------------------------------
    Divya (OSSE-Contractor) Pogaku
    ------------------------------



  • 6.  RE: Compare values based on Pre-Requisites

    Posted 03-17-2020 08:31
    Divya,
    as Don's diagram suggests, you will need to have a table for the Students, with the key field of email.  I guess, in theory you can have an Automation create that Student field if. Registration is entered and the student email record did not exists, but unless this is a very small school, you will have other information about the students aside from their basic email address.  Although, I suppose that the student name can be added later.

    Is this an EOTI app (Open to everyone on the Internet) or do these users each have a userid?

    For the next step in your question, if you make a relationship where one student email has Many registrations, then you can make a combined text summary field of the course codes of the registrations where the student successfully completed the course.

     U will also need a similar field where the course has a combined text summary field of the pre req courses.

    once you get that far, post back and we can move on to the next step.




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



  • 7.  RE: Compare values based on Pre-Requisites

    Posted 03-17-2020 13:35
    Yes Mark, this is an EOTI app and anyone can register with the link.

    ------------------------------
    Divya (OSSE-Contractor) Pogaku
    ------------------------------



  • 8.  RE: Compare values based on Pre-Requisites

    Posted 03-16-2020 22:26
    Divya,

    I am assuming that you have an architecture that looks like this:


    How many Courses are going to be in the DB? 

    If you are dealing with a small number that can be easily managed by a single person, you can build Formula Check Box fields in the Students table that will determine if the requirements for a specific course have been met.   This is a manual process and requires data base maintenance in the Students table every time a Course has its Prerequisites changed.

    Further assume in the Course Table that

    Record ID# 5 is Course Number 300 and that it has two prerequisites of the Courses with Record ID #1 and Record ID#2
    In the Students Table create Summary Fields from the Registrations Table using the [Related Course]

    # of Courses RID 1     your filter is Related Course =1
    # of Courses RID 2       your filter is Related Course =2

    In the Students Table create the field [Course Req 300 FC}  
    if (   [# of Courses RID 1] >0 and [# of Courses RID 2] >0, true, false)

    Create a Formula User field in the Course table called Related Student User and set the value to User()
    Add another relationship to your app between the Course and Students table.  Use the Related Student User field you just created as the reference.


    Add the formula check box from Students to the Course Table

    Create a new Formula Checkbox field in the Course Table called Student Qualified FC.   This will become a long If Statement but it begins like this

    If (   [Record ID#]=5 and [Course Req 300 FC]= true, true, false)

    Now your Course record knows if this Student is allowed to Register for it.

    Expand your current Record Picker report in the Trainings table to also include 
    [Student Qualified FC]=true

    This will work if you are managing the Courses for a corporate department with a relatively small number of Courses and lots of Trainings.

    If you are building this for a University with 10,000 courses, then this is a terrible idea and you should build a custom interface that uses traditional development tools to query the students past registrations, the current Course prerequisites and builds the Registration options for that student.




    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 9.  RE: Compare values based on Pre-Requisites

    Posted 03-17-2020 13:34
    Thanks Don. 
    But the issue is there is no separate student user table. This application is open to "Everyone on the internet" and any user can register with the link.

    ------------------------------
    Divya (OSSE-Contractor) Pogaku
    ------------------------------



  • 10.  RE: Compare values based on Pre-Requisites

    Posted 03-17-2020 13:39
    Divya,
    You will need to have a table of Student emails.  There is not a way to determine if an email address has registered for the Pre Reqs unless you have a table of student email addresses and a relationships where 1 Student email has many Registrations.

    If necessary, the student email address records can be Auto created using an Automation.

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



  • 11.  RE: Compare values based on Pre-Requisites

    Posted 03-17-2020 14:59
    Divya,

    Reinforcing what Mark said.  Regardless of the database, you have to have a Student record where the past registrations and course work exists.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------