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

Question

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 Answer

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?

Link to base

TheTimeSavingCo · Answer

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?

Link to base

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded