If field contains

[CAHerrmann]

I am using a series of nested ifs to set a formula field to some value based on an adjacent lookup field pulling in drop down selections (yes, no, null) from related records (one to many).

I want my formula field to evaluate to one of three options:

“Rejected” if ALL records return “No”
“Awarded” if ANY record returns “Yes”
“Pending Proposal” if ANY record returns “Null”

Any ideas?

[CAHerrmann]

IF({Count Proposals}>0,IF(SUBSTITUTE(LEN(ARRAYJOIN(Lookup),",","")/{Count Proposals})=2,“Rejected Proposal”,“Maybe?”),“Needs Proposal”)

So…this is what I have so far. I’m hoping there is a simpler/more elegant solution out there.

Basically, when the count (count field of a linked record field…) is greater than zero, this evaluates if the length of the joined array, stripped of delimiters (yesyesno vs yes, yes, no) divided by the proposal count is 2.

If it is, then that means all proposals have selected “no” and it evaluates as rejected. Otherwise, it evaluates as “Maybe?” as a placeholder. Finally, if the proposal count is 0, it evaluates as “Needs Proposal”

The needs proposal was an additional requirement I hadn’t included, but figured it would make sense to do so in this.

The whole reason for this is related to the work I do – I bid on jobs, sometimes to several contractors, and I need a way to track the proposals individually, rather than as a cluster on one job. Each proposal is related to a single project, while each project can have many proposals.

Thanks in advance for any help!

[CAHerrmann]

IF({Count Proposals}>0,
IF(LEN(SUBSTITUTE(ARRAYJOIN(Lookup),",",""))/{Count Proposals}=2,
“Rejected Proposal”,
IF(FIND(“Yes”,{Lookup})>0, “Accepted Proposal”, “Pending Proposal”)),
“Needs Proposal”)

This worked perfect, FYI. I had seen the FIND() function, but was not clear on how best to use it.