Skip to main content

Hi,

 

I have about 50 task fileds that each have a status. They can be marked as Assigned, In Progress, Complete, Question for Supervisor,  Question for Administrator. Each field is a multiple select field, because sometimes there is a question for both the supervisor and the administrator. 

I have a view for the supervisor and a view for the administrator that are each filtered to only show tasks that have a question for them. I have a formula field for each them that pulls out which task has the question.

The formula for the supervisor is as follows: (The admin has the same formula, just with ADMIN instead of SUP in the formula)

CONCATENATE(

 IF({Task A Status}="Questions for SUP", "Task A"),

 IF({Task B Status}="Questions for SUP", ", Task B"),

 IF({Task C Status}="Questions for SUP", ", Task C"),

 IF({Task D Status}="Questions for SUP", ", Task D")

 )

 

This works great. My question is, what if I have a question for both the supervisor and the administrator. Right now, if both those options are selected, the task does not get flagged by either formula, and both the SUP and the ADMIN don’t know that there is a task waiting for their attention. How do I put in the formula for the SUP to flag any task that is either Questions for SUP or Questions for SUP and Questions for ADMIN?

You could do that sort of a formula by using the FIND( ) function, which you can learn more about on Airtable’s formula reference page.

However, this is not the proper way to setup your database.

Whenever you have something that repeats multiple times, such as your situation where you have 50 different tasks, you should not create 50 different fields to represent those 50 tasks.

Instead, you should create a new tasks table, and each task should be its own record.

Then, you can manage each task individually in that separate table with its own status.

Back in your original table, if you need to see which tasks are associated with a particular record, you can use a linked record field to link the appropriate tasks to that particular record.

Hope this helps!

If you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

I know that this might be the ideal way to set this up, but at this point I’m not planning on changing my configuration. Do you have any ideas for working with my current formula / current setup?

That is not how you set up workflows in a database for exactly this reason. It might seem simple to duplicate a single select field when setting up, but the moment you need to generate a report or trigger a workflow, you’ll end up with a more complex solution. Putting you in the very situation you wanted to avoid.

All tasks should be in a Tasks table and assigned to users which are responsible for them. If tasks happen in sequence, you’d link them in chain where the completion of any previous task marks a task as ‘ready to work on’. If you set it up correctly, you could set up an interface for each user, where they see the tasks which are in the relevant status and disappear from that status once the user is done with them, and move it to the next user’s interface.

Hey ​@at-com,


I must agree with answers above, and would highly recommend you revise your setup as that will probably be the easiest. 
 

However, if you are looking for a quick solution using formulas I’d encourage you to ask this exact same question to ChatGPT and provide further context on the conditional logic. Chatgpt is pretty good at formulas :D

 

Hope this helps. 

I know that this might be the ideal way to set this up, but at this point I’m not planning on changing my configuration. Do you have any ideas for working with my current formula / current setup?

Your current formula is looking for an exact match, but you would need to use a FIND( ) function to search the multiple select field for what you’re looking for.

Something like this:

IF(

FIND("Questions for SUP",{Task A Status}) >0, "Task A")

)

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Reply


Terms & Conditions