Skip to main content
Question

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

  • January 23, 2020
  • 0 replies
  • 14 views
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
------------------------------
This topic has been closed for replies.

Forum|alt.badge.img+15
  • DonLarson
  • Quickbase Alumni
  • January 23, 2020
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
------------------------------
Terms & ConditionsAccessibility statement