Trouble with FIND formula


#1

I’m having some trouble figuring out why a FIND formula isn’t being triggered in a particular instance. I may be giving too much information, but I don’t know what might be relevant to the issue, so I’m erring on the side of over-sharing. :slight_smile:

THE BACKGROUND:

I work for a law firm that does consumer class actions. We need to sign up plaintiffs for each state where we are bringing claims in a lawsuit (we have databases of people who contact us about an issue, and we reach out to them based on their interest in the case). A person can represent a state where they purchased a product, which may be different from the state where they reside. They also may have purchased products in multiple states, in which case, they could represent each state where they made a purchase.

THE (RELEVANT) STRUCTURE OF MY BASE:

Table: People
• Contains a single-select field called “Plaintiff Status,” with options:
“Active”
“Former Plaintiff”
“Potential (Rejected)”
• Contains a linking field called “Link to Products,” which links to the “Product” Table and allows a person to be linked to multiple products purchased.

Table: Products
• Contains a linking field called “Link to People,” which allows only one person to be linked per product.
• Contains a lookup field to pull up the “Plaintiff Status” field from the “People” Table.
• Contains linking field called “Purchase State” field which links each product to the state where it was purchased, on the “States” Table.

Table: States
• Contains a linking field called “Link to Products,” which allows each state to be linked to multiple products.
• Contains a lookup field to pull up the “Plaintiff Status” field from the “Products” Table.
• Contains a formula field “Has at least one active plaintiff?”, which contains the formula: IF(FIND(“Active”,{Plaintiff Status})>0,“YES”)

THE PROBLEM:

I’m trying to create a view that groups the States Table according to whether or not there is at least one “active” plaintiff in the state.

For simplicity, I have the following:

  • Alaska: “Plaintiff Status” field shows “Former Plaintiff” • “Has at least one active plaintiff?” field is empty.
  • Arizona: “Plaintiff Status” field shows “Active” • “Has at least one active plaintiff?” field shows “YES”
  • California: “Plaintiff Status” field shows “Active, Active, Active, Active” (there are 4 active plaintiffs in that state) • “Has at least one active plaintiff?” field shows “YES”
  • Florida: “Plaintiff Status” field shows “Potential (Rejected), Active” • “Has at least one active plaintiff?” field shows “YES”
  • Texas: “Plaintiff Status” field shows “Active, Former Plaintiff” • “Has at least one active plaintiff?” field is empty.

So here’s the problem: Why is Texas showing up as not having any active plaintiffs, even though the Plaintiff Status field contains the term “Active”???


#2

Hi Celeste

I’ve tried using Find on a Rollup field and have another suggestion for you.

In your People table, have a formula field which calculates to 1 for Active records:

IF(Status=“Active”,1,0)

You can now use Rollup fields which can add up how many Plaintiffs there are per product or state. You could group by this number in States - or have a formula in Has at least one plaintiff? of:

IF({Number of Plaintiffs}>0,“Yes”,“No”)

I hope this helps?


#3

@Julian_Kirkness, that did work, thank you!

It’s funny, though: I originally had a structure similar to that, and I thought, “This seems overly complicated. Why can’t I just refer directly to the Status field on the People table?”

I’m still a little concerned that there’s something about the FIND function that I’m not understanding… I don’t know why it didn’t work my original way (although I’m very happy to have a solution that works, thank you!).


#4

You’re welcome @Celeste_Boyd