Conditional Coloring for the Record "pills" in a lookup field

[seanpeter2]

Working on costuming for a film - have 4 main tables of Actors, Costumes, Scenes, and Characters.

There's a lot of table to table referencing going on w/ lookups...

But one thing I would LOVE to be able to do:

When looking at the COSTUMES table (characters have multiple costumes) - there is a lookup field that shows what SCENES that costume is used in.  (Scenes are described by a number).

In the SCENES table - I have a check mark field to keep track of what scene is wrapped (already shot)

Back in the COSTUMES table, the scenes a costume is used in all show up as a list of numbers in little grey pill boxes. I'd LOVE it if the color of those pill boxes could be colored differently if they have a checkmark in the "wrapped" field in the scenes table.

I created a conditional coloring of records in that SCENES table... but alas, it does not translate that record color over to the pills in the lookup field in the COSTUMES table.

 This image is from the COSTUMES table - pointing at a lookup field referencing the SCENES table

Any ideas??

[Josh_Colina · Accepted solution]

Hi Sean! What you're describing does sound like something super useful, but unfortunately as far as I know there isn't a way to accomplish this in a linked record field in a grid view at the moment. You might be able to achieve what you're looking for by using a List view instead. 

Lists are great for helping to visualize parent-child relationships between records, and come with a prefix field feature that allows you to easily check-off records. If you create a List view in your Scenes table and establish the Costumes table as the parent, then you can generate something like the below.

You could also build this into an Interface, which might be better for customizing where different information lives (especially if you're less keen on having this view live in your Scenes table as opposed to Costumes).

Here's a hyper-analog solution if you want to see this from the grid and don't mind a little extra overhead!

In your Scenes table, add a Single-select field and paste in your list of all the scene numbers. Switch all the colors of the options to the same color, then assign each scene the corresponding single-select option (if Scene 1, choose 1, Scene 2 is 2, etc. - see first image). In your Costumes table, add a Look-up field to the Scenes table and look-up the Single-select field you just created. Now, whenever a scene is wrapped, change the color of the option to a standard "done" color (for example, green). With some additional manual labor, you'll get a view like this in your grid (second image)

 Hope this helps!

[seanpeter2]

Love the analog version. I have to go in and put a check-mark in the "wrapped" field as it is... so it's just an extra click or two to change the color in the single select filed instead.
That will work. Thanks!

[seanpeter2 · Accepted solution]

OK, you inspired me.
I ditched the analog version... and made a formula field in the SCENES table that references the primary field (Scene) and the "Wrapped" field - and used an emoji checkmark:

F(x) = 

IF(Wrapped=FALSE(), Scene, Scene & "✅")

Then just did a simple lookup of that field in the COSTUMES table.

I sure wish you could format text (color, or even just bold) in a formula! But this works at a glance.

Thanks for sparking the idea! 

!