Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Trouble with FIND formula

1622 3
cancel
Showing results for 
Search instead for 
Did you mean: 
C_Boyd
6 - Interface Innovator
6 - Interface Innovator

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. :slightly_smiling_face:

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”???

3 Replies 3

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?

C_Boyd
6 - Interface Innovator
6 - Interface Innovator

@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!).

You’re welcome @C_Boyd