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