Color field by condition

[Jennifer_Robin1]

Hi all,

I’m fairly new to airtable, but loving it so far. I manage a program that requires property inspections and reporting on conditions. I’m using Airtable for tracking the properties and various related aspects (contacts, etc).

I just discovered the “color by condition” option, and though that would help give me a quick overview of my inspection statuses. So, i thought setting up conditions for properties inspected within the past year, 18 mo, and 2 years would work. But, this only seems to work for those exact number of days, and I thought I was going to be getting something more like “within the past 365 days”

Thoughts on how I should modify my conditions, or if there’s another functionality I should be using entirely?

Thanks!

[Justin_Barrett]

Welcome to the community, @Jennifer_Robinson! :grinning_face_with_big_eyes: You should be able to get things set up as you wish. Here’s how I set up a quick test:

[Jennifer_Robin1]

Thanks so much @Justin_Barrett… this helped a lot!

[Jennifer_Robin1]

OK, now i have one more question related to this. I was looking for a way to summarize these color coded fields on another table. I think I can use the rollup function, but I can’t select the parameters I used for the color coding that I can tell. I tried following the instructions at the “Conditional Rollups” page of the help section but I’m missing some step it seems. Is what I’m trying to do possible?

[Justin_Barrett]

Jennifer_Robinson:

I think I can use the rollup function, but I can’t select the parameters I used for the color coding that I can tell.

Correct. Rollups only collect the raw data, ignoring display extras like record coloring, grouping, etc.

What you’re trying to do is probably possible. When you say you want to summarize those color-coded fields, do you simply want a count of each color group? Or do you want a different kind of summary? I couldn’t quite tell from your description.

[Jennifer_Robin1]

Yes, a count by color group. I used the model you showed above and now have my properties organized. I’m just looking for a summary of the number of properties in each category (color field).

[Justin_Barrett]

In that case, the setup to generate the summary will actually give you a slightly different, slightly easier way to do the color marking as well.

In this modified version, I created three formula fields that simply put an “X” into the field when the date matches specific criteria:

Notice that the marks match up with the colors set previously. With this in place, you can change the color marking system as follows:

The formula for {1 Year} is:

IF({Last Inspection}, IF({Last Inspection} > DATEADD(NOW(), -1, "years"), "X"))

{18 Months} is:

IF({Last Inspection}, IF(AND(NOT({1 Year}), {Last Inspection} > DATEADD(NOW(), -1.5, "years")), "X"))

And finally {2 Years}:

IF({Last Inspection}, IF(AND(NOT({1 Year}), NOT({18 Months}), {Last Inspection} > DATEADD(NOW(), -2, "years")), "X"))

In the first image above, notice how all records were linked to a single record in the [Inspection Totals] table. This is where you’ll do the rollups.

Here’s a screenshot of my first rollup for 1 Year. The rest are virtually the same, just pulling from the other appropriate fields.

To break down the aggregation function, values is an array containing data from the specified field—in this case, {1 Year}—across all linked records. Because of the formula we made, only those records matching that formula’s criteria will contain the X, and the rollup operation will ignore empty fields, so for this example rollup values will be an array of five Xs. Concatenating that with an empty string using the & operator turns the array into a string of five Xs, and then the LEN() function returns that string length as a number.