Best Of

This sounds like it would be a great item to submit to the Product Idea page on the Community. I’ve also created many Reports, and like yourself, often struggle when the list of columns is sorted alphabetically instead of the way I’ve intentionally ordered them in a sheet. I don’t think there’s a workaround for us (other than pulling up the underlying sheet on one monitor and building your report on another, which still requires you choose the lesser of the two evils you describe in your original post).

Hi @johnna.young,

I think this will do what you're after:

=IF(Status@row = "Complete", "Gray", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(15)), "Green", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(4)), "Yellow", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row <= TODAY(3)), "Red", ""))))

Sample:

The only thing it doesn't cope too well with is if you have an At Risk/In Progress status with no target end date - these will show up as Red.

Hope this helps, if you've any problems/questions then just post! 🙂

Hi @YCH_Employee

Try to check if there's an identical workflow that is causing the alerts. If not, you can raise a support ticket to identify the root cause.

Ok @Jeremy W., here's what I did:

I had an "auto number" column in my sheet already, and it counts by one and assigns a new number to each row. What I added for my solution was an Alert ID column that, with a formula, just pulls the right final digit from the auto-number column, so I've ended up with a column that has the single digits 0 thru 9.

Then, in my automations, I simply duplicated my alert 10 times and added a conditional filter to each one to send if the Alert ID is equal to 1, using a different number for each of my duplicated alerts.

I believe this will result in Alerts only being combined if two rows are updated at the same time AND there are 9 rows between the two rows that are being updated. This should greatly reduce the risk of combined alert emails for my specific situation, since I have very few updates happening as the entries age.

If you have a lot of rows being updated daily, you may be able to break them up in a similar way, but using a different identifier that would give you a span 20 or 30.

One drawback is if I need to update the custom message, now I have to do it 10 times. 😕