Status icon formula help

Question

I need help generating a formula for a status field I am trying to create. Here is what I would like to accomplish...

On my Tasks report, all tasks show up for all projects. Lets say each project consists of 4 tasks to be completed by 4 people. Michelle is responsible for task1, Danya task 2, John task 3, and Bob task 4. The way the report is current setup, if michelle does not complete task1, the task still shows up under Danyas name, even though she should not sign off on her task until Michelle finished task 1. I understand there is something with "predecessor" rules but I have not been able to figure those out without rewriting the whole app. Instead I would simply like to display a green icon for when the task is due for that user, and a red icon for when other tasks still need to be completed before any action can be taken on that specific task. I have attached a photo of my task report and what I would like to see for icons. I already created a Test - formula field, I just need help with the formula.

Here is the base line for my formula - however I do not know how to write it correctly...

If the [PROJECT ID-Percent Complete] is equal to 0 and is task is assigned to Michelle, display src=\"https://images.quickbase.com/si/16/228-rect_green.png\">",

If the [PROJECT ID-Percent Complete] is equal to 0 and is assigned to anyone but michelle, display src=\"\"" rel="nofollow" target="_blank" title="Link https//imagesquickbasecom/si/16/227-rect_redpng">https://images.quickbase.com/si/16/227-rect_red.png\">",

If the [PROJECT ID-Percent Complete] is equal to 25 and is assigned to Danya, display src=\"\"" rel="nofollow" target="_blank" title="Link https//imagesquickbasecom/si/16/227-rect_redpng">https://images.quickbase.com/si/16/228-rect_green.png\">",

If the [PROJECT ID-Percent Complete] is equal to 25 and is assigned to anyone but Danya, display src=\"\"" rel="nofollow" target="_blank" title="Link https//imagesquickbasecom/si/16/227-rect_redpng">https://images.quickbase.com/si/16/227-rect_red.png\">";

If the [PROJECT ID-Percent Complete] is equal to 50 and is assigned to John, display src=\"https://images.quickbase.com/si/16/228-rect_green.png\">",

If the [PROJECT ID-Percent Complete] is equal to 50 and is assigned to anyone but John, display src=\"https://images.quickbase.com/si/16/227-rect_red.png\">";

If the [PROJECT ID-Percent Complete] is equal to 75 and is assigned to Bob, display src=\"https://images.quickbase.com/si/16/228-rect_green.png\">",

If the [PROJECT ID-Percent Complete] is equal to 75 and is assigned to anyone but Bob, display src=\"https://images.quickbase.com/si/16/227-rect_red.png\">"

eric_j_hansen · Answer

This is a very complicated problem to solve that needs a bunch of setup work to get you to the point where you can create your formula.

I can show you a demo of how we've set this up for other clients (we are a Quick Base development company) if you like. Visit my profile page or www.cirrusops.com

Basically the problem is this:

Tasks are not aware of any other tasks. Even with predecessors, they only become aware of the task that falls before it; though not any real information about the task, just the record id# of it, which is not helpful in your formula.

Now, using summary fields in the relationship between projects and tasks, you can summarize info about specific tasks up to the project and then back down to all tasks with lookup fields. If you have a set list of tasks everytime, this can be done fairly easily. You can summarize whether a certain task is complete or not, even when it was completed. The other tasks related to that project can utilize that information to determine where it specifically fits into the project plan and if it is next or not. If you do not have set tasks lists, this method falls apart. In that case, we use some coding to solve the problem.

Hope that is helpful - reach out to continue the conversation.

michellecoslet · Answer

Hi Eric -

Thanks for the quick reply. Sounds complicated. The task list is the same for every project so I have that going for me. It is all copied over from a Template each time a new project is added. Even better, the users who are assigned these tasks never change. Task 1 is always "task a" assigned to "michelle" for example.

Currently, the way the report is setup to show % complete is working as is. Looking at the photo I attached, Danya will not start on a task unless % complete is equal to 25%. She knows if it shows 0%, that Michelle still needs to complete her task. It works but is not as visually appealing and it still takes a tiny bit of thought to comprehend. Our users are 100% capable of doing this, I'm just a sucker for icons these days and thought there might be an easy way to reflect those percentages as icons so to speak.

In writing this response I may have thought of an idea. I will update if it works. :)