I am working on a project about making reservations. I have a reservation table that holds the enum field Status. And reservation lines table that holds the NoYes - Paid field.
The task is I have to iterate through all lines in res lines table and see if the NoYes Paid field is yes. If this field is yes on all lines in a given reservation, the field Status in res table has to change its status to Paid.
Any suggestions how should I implement this?
So the first thing I want to point out is that it is harder to update a parent record/row (i.e. reservation table) from multiple child records/rows. With that said, I can come up with 3 different ways to do this. Depending on your experience, some may be more appealing than others.
- Javascript/Plugin
- Power Automate Flow
- Combination of calculated fields, roll up fields, and business rules.
I am going to talk through this last one because you don’t need to know Power Automate or building custom plugins. Caveat: I tested this with the Account and Contact records in a development environment. The relationship type between your two tables will need to be a parental relationship. My field names are arbitrary. You can change them as you wish.
Step 1. You will need to create a secondary column on the reservation line table that is of type Whole Number. I would recommend that you put this on your form, but lock and hide it. Let’s call this “Status Int”.
Step 2. Create a business rule that sets “Status Int” based upon the Paid field value.
-Set “Status Int” to 0 for No and set it to 1 for Yes.
Step 3. Create two separate rollup fields on the Reservation table.
-Record Count – returns the count of Reservation Lines under the Reservation table.
-Status Int Sum – returns the sum every Reservation Line has for “Status Int” under the Reservation table.
Step 4. On this next step, you can either create a new calculated field on the Reservation table or use a business rule.
–Existing Field = Create a business rule that updates the field value based on whether the “Record Count” and “Status Int Sum” fields are equal or not. The idea is that if you have 10 reservation lines and only 8 are paid, then “Record Count” will say 10 and “Status Int Sum” will say 8.
–New Field = Create a calculated field that sets its value conditionally upon whether “Record Count” and “Status Int Sum” are equal.
I did have all fields added to the appropriate forms, but that might not be needed. Also, keep in mind that rollups only update once an hour after a 12-hour delay for the first run. You can change when the first calculation runs if needed, but otherwise you will need to wait.
Please let me know if you have any questions or if I can help at all.
–Jonathan