Skip to main content

First of all I would like to say thank you for everyone contributing to the Airtable Community, I have been saved hundreds of time by the ideas and solutions posted here. Now, I would like to pose a problem I’ve been dealing with:



I’m creating a data base to keep record of project ideas pitched by a team.



I want to be able to prioritize ideas from people who have already suggested something before AND had their idea chosen for further development.


I managed to indicate all people that already submitted an idea thanks to this: Can I find unique values and put them in a column?


Now I would like to have a field indicating if that person has had an idea picked for developement before, but I’m stuck and can’t find a solution.


I have an array with all the people who have submitted an idea before, now I need to know the status of these ideas.


I believe the way to do this is create an array for each recurrent user with all values from the idea status column so I could verify if any status is ‘in development’.

Hi @Andre_Souza - can you share some screenshots of your base and/or description of the current base structure?

Hi @Andre_Souza - can you share some screenshots of your base and/or description of the current base structure?


My base is kind of big and in Portuguese, so I will try to explain the tables and field I’m actually using for this.


I have two tables:


InProduction


ID (Auto number)


Idea (Single line text)


Idea Status (Single select: new idea, in development, launched, dropped)


Name eof user who suggested] (Single line text)


Email lof user who suggested] (Email)



Calc


Name (single text)


Link to InProduction


E-mail Array (Rollup)


Unique Email Array (Formula)



Then I have 2 views set that show me only new ideas and ideas in development of launched.



Some extra info:





  • I don’t want the people responsible for developing new ideas to be able to see whats is being suggested.


  • There is a form for inserting new ideas on the table, every records gets inserted through this form.


  • And I want to make the life of the person choosing new ideas easy by indicating people who have already suggested something that was picked for development before. I was able to indicate when the person has already suggested something using the topic I mentioned on my first post. I adapted to check if the email is already in any record on the table.






What I’m thinking now is that I need to pick the E-mail and see all Idea Status of all records that have that e-mail, so if any of them is in development or launched I can signal this record.


I keep thinking this would be solved with a multidimensional array comprised of e-mail and idea statuses, where I could query if said e-mail was in the array and if it was if there where any statuses with the wanted values. But I know Airtable doesn’t support it and I’m clueless on how to adapt this.

Ok, while trying to explain I came across the solution.



I created a conditional lookup field on Calc table to return all the e-mails where Idea is in development or launched. This gave me an array with all e-mails where ideas where picked for development before. Then, I created formula field on Calc table to give me a an array with unique values.



On InProduction table I created a lookup field with the unique array from Calc table. Next, a new formula field to give me the results I was looking for:



IF({Email}=BLANK(),


‘ 🔴 ’,


IF(FIND({Email}, {ArrayRecurrentUnique})>0,


IF(FIND({Email}, {ArrayApprovedUnique})>0,


‘ :green_circle: ’,


‘ 🔴 ’


),


‘ 🔴 ’


)


)

Ok, while trying to explain I came across the solution.



I created a conditional lookup field on Calc table to return all the e-mails where Idea is in development or launched. This gave me an array with all e-mails where ideas where picked for development before. Then, I created formula field on Calc table to give me a an array with unique values.



On InProduction table I created a lookup field with the unique array from Calc table. Next, a new formula field to give me the results I was looking for:



IF({Email}=BLANK(),


‘ 🔴 ’,


IF(FIND({Email}, {ArrayRecurrentUnique})>0,


IF(FIND({Email}, {ArrayApprovedUnique})>0,


‘ :green_circle: ’,


‘ 🔴 ’


),


‘ 🔴 ’


)


)


Sorry, didn’t get chance to reply but great that you found a solution!

Reply


Terms & Conditions