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

[AUA]

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!

[AUA · Accepted solution]

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"

)

[TheTimeSavingCo]

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?

Link to base