Find function and sequence check logic

[weoim]

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.

[bruceconsulting]

Hi,

I think this will work:

IF(RIGHT({field name},3)="Yes","Active",IF(FIND("No, No",{field name}),"Inactive","Active"))

Please note that the labels in quotes are case sensitive, so if your responses are not capitalized, you will also have to change "Yes" to "yes" and "No" to "no".

If you want to talk about this further or if you ever need additional help, please schedule some time with me.  Here's my Calendly link: https://calendly.com/d/ymz-w26-jb7/30-minute-help-meeting

Jody

[weoim]

Hi @bruceconsulting the Find function doesn't work in our case (it doesn't recognize "No, No" but the Right function worked perfectly fine in our case!!

So I updated the function to a super simple case 

IF(RIGHT({field},6)="No, No","abandoned"," ") basically reading the last 2 responses (with space and period) and this is exactly what we are looking for. 

Thanks so much for your help!!!