Help

Rollup field: IF all records = "Approved", output "All Approved"; ELSE output "Approval Pending"

Topic Labels: Formulas
Solved
Jump to Solution
212 2
cancel
Showing results for 
Search instead for 
Did you mean: 
AUA
4 - Data Explorer
4 - Data Explorer

I have a table "Products" and a table "Artworks". The Product table has a lookup field to the Artworks table. In this lookup field, I assigned multiple Artworks to each Product (e.g. "Product 1" has assigned "Artwork A", "Artwork B", "Artwork C", and "Artwork D").

Product 1 can only go into production once all Artworks assigned to Product 1 have approval status "Approved".

In the Products table, I added a rollup field that shows the records from the "Approval Status" field from the "Artworks" table. By default, the values appear like this in the rollup field: "Not submitted, not submitted, Approval pending, Approved"

Instead of listing all the records in the rollup field, I want the rollup field to show:

  • IF all records = "Approved": output "ALL ARTWORKS APPROVED"
  • ELSE: output "APPROVAL PENDING"

or alternatively:

  • IF any record is not = "Approved": output "APPROVAL PENDING"
  • ELSE: output "ALL ARTWORKS APPROVED"

I'm thankful for any tips!

 

1 Solution

Accepted Solutions
AUA
4 - Data Explorer
4 - Data Explorer

With the help of Chat GPT, I was able to solve it with an additional formula field. It had to correct and redirect Chat GPT a couple times, but eventually it was able to write the correct code. But Chat GPT wasn't able to come up with a solution that could be done directly in the formula settings of the rollup field. Is it right, or is there a way to do it with only one field?

Chat GPT solution:

Formula settings for "Approval" Rollup field:

ARRAYJOIN(values, ", ")

Add a formula field "Final Approval" with this formula:

IF(

{Approval},

IF(

COUNTA({Approval}) = SUM(FIND("Approved", {Approval})),

"ALL ARTWORKS APPROVED",

"APPROVAL PENDING"

),

"NO ARTWORKS ASSIGNED"

)

See Solution in Thread

2 Replies 2
AUA
4 - Data Explorer
4 - Data Explorer

With the help of Chat GPT, I was able to solve it with an additional formula field. It had to correct and redirect Chat GPT a couple times, but eventually it was able to write the correct code. But Chat GPT wasn't able to come up with a solution that could be done directly in the formula settings of the rollup field. Is it right, or is there a way to do it with only one field?

Chat GPT solution:

Formula settings for "Approval" Rollup field:

ARRAYJOIN(values, ", ")

Add a formula field "Final Approval" with this formula:

IF(

{Approval},

IF(

COUNTA({Approval}) = SUM(FIND("Approved", {Approval})),

"ALL ARTWORKS APPROVED",

"APPROVAL PENDING"

),

"NO ARTWORKS ASSIGNED"

)

Hmm, you could try giving the rollup a conditional to only show records that are not "Approved", then using an IF to display what you want like so?

Screenshot 2024-03-25 at 11.36.51 PM.png

Link to base