Conditional Lookup based on Checkbox field

[Cat_Crawford]

Hi friends! I am hoping this is easy, but I'm noob enough to not even know how to search up an answer in support/forums.

My specific use is retreat registration. I have one table for Retreat Participants and a second for the possible Retreat Rooms.

The "Retreat Rooms" table has a field for the rate for Solo Occupancy, and a field for the rate for Shared Occupancy.

In my "Retreat Participants" table, I have a checkbox to indicate "solo occupancy". I would like there to be a field in the Participants table that then returns the correct room rate based on whether or not this checkbox is ticked.

Let me know if you need further information or explanation, and if you have any tips! I don't know if this is an "If/Then" or "Switch" or what 😵‍💫 Thank you!

[Alyssa_Buchthal]

Is the participant record linked to the relevant retreat room record?

[Cat_Crawford]

Yes! Sorry, should have included that. The Retreat Participant selects which room they want which then links to the Retreat Rooms table.

[Alyssa_Buchthal]

This is simple to do with an automation! Rather than a look-up field, make a new $ field altogether. Then, when the linked field for the room is populated (using a "when a record is updated" trigger watching that field will likely be easiest), use conditional logic to populate the rate field in the participant table. If single occupancy is checked, use the single occupancy rate from the rooms table, else, use the double occupancy.

[Cat_Crawford]

Thank you—but I was hoping it was a little simpler than that😅 With an automation, I would have to create statements for each possible room rate (7 different room types, two rates per room), but I already have that data in my other table. Creating automations seems like it would have too much room for error, and would not be easily re-creatable for future retreats. I was hoping for a formula like "If [checkbox] is ticked, then pull [shared rate], if it isn't ticked, then pull [solo rate]"

[Alyssa_Buchthal]

If you have look-ups to both fields in the table already, you can definitely do that too, seems like you already have the logic all figured out. If you're making a new base for every retreat you will need to recreate any automation, using a script rather than the AT built-in conditional logic could make that simpler to replicate in each case.