How to create an array with values from a field based on a conditional query?

[Andre_Souza]

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’.

[JonathanBowen]

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

[Andre_Souza]

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 [of user who suggested] (Single line text)
Email [of 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.

[Andre_Souza · Accepted solution]

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(),
‘ :red_circle: ’,
IF(FIND({Email}, {ArrayRecurrentUnique})>0,
IF(FIND({Email}, {ArrayApprovedUnique})>0,
‘ :green_circle: ’,
‘ :red_circle: ’
),
‘ :red_circle: ’
)
)

[JonathanBowen]

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