Hi guys, I need help building formula automation and seem to have trouble with the Find function.
So we have an outreach database for our customers and for each outreach, we track a response as "yes" or "no," Ideally after two constant rejections we would mark the customer inactive. If later on they respond or reach out then we will mark them active again.
With this in context, I'm trying to build an automatic status update function in Airtable. If one customer's status is "No, No" or "Yes, No, No," the customer's status shall automatically change to "inactive." If a new yes is recorded, like "No, No, Yes" then it shall change to "active,"
I tried a few different ways but none seem to work perfectly. The closest I could think about right now is to use the "Find()" function which in my understanding returns the last position of the string being looked up. However, when I tried it, this is what I got:
- only one string "yes" or "no" is in the cell, return 1";
- Two "no" in the cell, Find("no") return 2, Find("yes") return 0;
- Two "yes" in the cell, Find("no") return 0,Find("yes") return 2;
- but when "yes, no" in the cell, Find("no") return 2, Find("yes") also return 0.
Does anyone know what's going on or have a better way to build our solution? Attaching the current issue.