Checkbox Formula between related tables

  • 1
  • 1
  • Question
  • Updated 10 months ago
  • Answered

Hi there. This is the first time I've used this forum, so please excuse me if I'm violating any etiquette! And, thanks for trying to understand my poorly-worded question!

I’d like to create a formula for a checkbox field that looks into a related table to determine if it should be checked or not.

Table 1 – “Merch Elements”

            Table 1 field: Checkbox Formula called “Active Merch”

Table 2 – “CTAs”

            Table 2 field: Checkbox called “Active CTA”

Table 1 has a one to many relationship with Table 2: Each “Merch Element” has many “CTAs”

I’d like to put a formula in “Active Merch” checkbox formula field in “Merch Elements” that would look at all the related “CTAs” in Table 2 and if ANY of those CTAs have the “Active CTA” checkbox checked, then check “Active Merch” field in “Merch Elements”.  And, of course, if there are NO related “Active CTA” checkboxes checked, then the “Active Merch” checkbox should be unchecked.

In other words, I have merch elements (they look like boxes) on my website and there are several Calls to Action (CTAs) in them, like “buy,” “try,” “learn more” etc. When I remove one CTA from the merch element, I don’t want to lose the data from it, I want to make it “inactive” so I can filter it out. But, the merch element is still active since there are other CTAs still active in it. Now, If I make ALL the CTAs from that one merch element inactive, then I want the merch element itself to become inactive automatically.

I am sure I’m not being clear enough, sorry about that. Hope someone can help me.

Thanks a ton.
Photo of H.B. Mok

H.B. Mok

  • 82 Points 75 badge 2x thumb

Posted 10 months ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,046 Points 50k badge 2x thumb
Very descriptive question. Thank you for tak8ng th3 time to do that.

No problem.

Just go to the relationship and make a summary field to count the number of CTA subject to the condition that the Active CTA is checked.

Ok, so now the Parent Merch Ekement Record knows the number of children CTAs were active.

So just make a formula field for the Active Merch checkbox field ( you may need to change the field type to Formula checkbox.

IF([# Active CTAs] > 0, true)
Photo of H.B. Mok

H.B. Mok

  • 82 Points 75 badge 2x thumb
Awesome! That worked perfectly. Thank you very much.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 49,572 Points 20k badge 2x thumb
Thx for letting me know.  I see that my typing was messy but I was on my iPhone. :)