Update Checklist field in One Table When Linked Record in Another Table Enters View

[Kai_Soremekun]

I have a Scenes Table with records listing each scene (Scene Name) to be shot for a video (Video Title).

Once the Scene Names are all listed they go to a Shots Table where shots for each scene are created.

In the Shots Table each record is based on the Shot Number. I have a checkbox field (Shot?) that I check when that shot has been filmed.

Filmed scenes get sent to an Archive View when that checkbox field is checked.

What I’d like is an automation that checks my (Shot?) field in the Scenes Table when all shots for that scene have been checked in the Shots Table and entered the Archive view.

For example, in the screenshot below when the shots listed in the (Shots) field enter the Archive view in the Shots Table the (Shot?) checkbox gets checked.

If a trigger tied to ALL shots entering Shots Table archive view is problematic, I’d settle for when any shot enters the archive view for a scene, it triggers the checkbox for that scene in the Scenes Table.

Thanks for your help.

[Kai_Soremekun]

Bumping in hopes someone might have a suggestion.
Thank you

[ScottWorld · Accepted solution]

In your Scenes table:

1. I would create a field of type “count”, which will give you the count of linked shots.
2. Then, I would create a lookup field to lookup the completed shots checkbox.
3. Then, I would create a formula field to count the completed shots checkboxes in the lookup field using the COUNTA() function.
4. Then, I would create a formula field to compare the values in the linked record count field (#1 above) vs. the formula field that counts the checkboxes (#3 above)… and I would also make this formula field check to make sure that they’re both greater than 0.
5. When the 2 values match (#1 and #3), then the formula field (#4) would result in a value which would trigger your automation.

[Kai_Soremekun]

Well this is pretty awesome @ScottWorld. Thank you.
Automatic kudos for explaining this in a way that allows a non formula/tech person to understand.
I’ve completed 1-3 but not sure how to create the formula suggested in #4.
Going to continue to research and see if I can figure it out but should you see this would appreciate some guidance.
Names of fields from your instructions:
#1 Count (Shots)
#2 Shot? Checkbox Lookup
#3 Count Checks

Actually reading #5 not quite clear on that either at this point.
But I did have to read 1-3 like five times before wrapping my head around it. lol

[Kai_Soremekun]

I guess I haven’t figured out step number 3.
This is the formula I used.
COUNTA({Shot? Checkbox Lookup})
It’s showing the number of total shots instead of the checkboxes.

Ignore this part I figured it out.
Putting this here in case it’s helpful to anyone else.
I needed to have it meet certain conditions
Where Shot? is [checked]

Okay. I’ve got it working.
This is the formula I used:
IF({Count (Shots)}={Count Checks},“ :clapper: Scene Shot”, “ :movie_camera: To Shoot”)
I did not incorporate the check to make sure both are greater than 0 because I didn’t know how to.
I’m sure there’s a good reason for this part of the formula, I just can’t wrap my head around why and probably won’t until I come up on a situation where I go “oh that’s why”. lol
Anyway please suggest how to incorporate that into the formula if someone knows how.
Then I’ll mark as solved. :slightly_smiling_face:
Thank you @ScottWorld.
I enjoyed figuring it out with guidance.

[ScottWorld]

Hi @Kai_Soremekun,

Hey, that’s great to hear that you’ve got this figured out! :slightly_smiling_face:

Congratulations on your fantastic puzzle-solving skills! :cowboy_hat_face: :raised_hands:

The reason I wanted you to check if both fields are greater than 0 is because if you create a brand new scene from scratch, both “count of shots” & “shot checkbox lookup” will start off as 0, so it will say “ :clapper: Scene Shot” before you’ve even typed in anything about that scene.

So here’s how you could setup that formula:

IF(
AND(
{Count (Shots)}={Count Checks},
{Count (Shots)}>0,
{Count Checks}>0
),
" :clapper: Scene Shot", " :movie_camera: To Shoot")

[Kai_Soremekun]

Aah! Of course @ScottWorld . And I would have come up against that problem quickly and sat there forever trying to figure out what was happening.
:pray:t4: