Skip to main content

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. 🙂


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

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?

@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

Reply


Terms & Conditions