Discussions

Expand all | Collapse all

Need formula to update 2nd record based on 1st record status

  • 1.  Need formula to update 2nd record based on 1st record status

    Posted 01-22-2020 19:59
    I have an app where users can register for schedules courses, with a Scheduled Course table (scheduled course offerings) and a Reservation table.  There are 2 classes per week to choose from, but we only have trainer resources available to teach 1 class per week.  So once a reservation is made for one of the offerings, the 2nd offering (of that same week) needs to change to an unavailable status.  I do have a formula field to calculate the week # based on scheduled course date.  Is there a formula that could be used in a custom table rule or an automation whereas I can change the status​ on the 2nd offering once the 1st option is chosen for that particular week?

    ------------------------------
    Colleen Burch
    ------------------------------


  • 2.  RE: Need formula to update 2nd record based on 1st record status

    Posted 01-23-2020 07:50
    Edited by Don Larson 01-23-2020 07:57
    Colleen,

    I have got a solution for you but it requires the Triggers tool from Juiced Technologies.

    https://www.juicedtech.com/triggers  

    It is like an Automation but on Steroids

    Here is one potential architecture:



    • When you add the Courses to [Scheduled Courses]  they will have different Dates but the same Course Week
    • Have a Trigger add a record to the Course Status Change table for each Course with Related Course Status = 1  the proxy value is New
    • When a student is added to the Reservations table and the Max RID Related Course Status =1  have another Trigger add a record to the Course Status Change Table   Related Course Status =2.   
    • You then have a Trigger fire from the Course Status Change Table that will Search the Scheduled Courses table and give you all the courses where both the Related Course Status is New AND the Course Week is the same as the Course just Scheduled.
    • That Trigger now adds a record to the Course Status Change Table for the search results   Related Course Status = 3  which is "Unavailable"

    The thing that makes this solution nice is you could have three Scheduled Courses one week. Once the first one gets a Reservation then you make the other two Unavailable.

    Maybe in the future you have two instructors and you only want to make Unavailable the 2nd Course they are teaching and not any of the Courses for the other guy.  You include table for Instructor and make it the parent of Scheduled Courses and make the Related Instructor part of the Trigger.

    Call Juiced about their tool set that does this.  It is very inexpensive and super powerful.  I use it with every client now.  


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



  • 3.  RE: Need formula to update 2nd record based on 1st record status

    Posted 01-23-2020 16:03

    Thank you for your quick reply.  Unfortunately, I cannot use or download any 3rd party solutions.  I must rely solely on the functionality available in QuickBase for this solution.