Skip to main content

Hi there,

I am trying to create an action to send a gmail email reminder (not relevant for this question), whenever a Clio Calendar Event is about to start within 24 hours, whenever Zapier determines that “one” of the attendees associated with the Clio Calendar event is one of the named attorneys listed in Column A of a Google Sheet.

The problem is that there are usually at least two attendees per Clio event: the client and the attorney, and Clio puts them together into a single value: Attendees: Joe Schmo, Mary Foe

Joe Schmo is an attorney for this example. All attorneys and their email addresses are listed in a Google sheet. Attorney Name = Column A. Attorney Email Address = Column B. Thus:

Column A              Column B

Attorney Name   Attorney Email

Joe Schmo           joes@abc.com

Curly Schmo        curlys@abc.com

Larry Schmo         larrys@abc.com

My problem is this: How do I successfully “Lookup” and find “Joe Schmo” in the Google Sheet, when my “Lookup Value” in Clio event is not “Joe Schmo,” but is “Joe Schmo, Mary Foe.” Obviously that is not a match.

I’m trying to figure out how the Zap can determine that “Attendees: Joe Schmo, Mary Foe”  (the “Lookup Value”) actually includes/contains just the name, “Joe Schmo”, so that it finds there is a match in the Google Sheet Column A.

Right now, if I put Column A as the Lookup Column in the sheet (“Joe Schmo”), and then for the Lookup Value, I choose the Clio “Attendees: Joe Schmo, Mary Foe” value, and run the test, the test fails because the Zapier lookup is looking for a value in the Lookup column that is exactly “Joe Schmo, Mary Foe.” It is NOT looking only to see if one of the names (Joe Schmo) is in the lookup column. This is what I want it to do.

I want to set up the Google Sheets Lookup with a formula of some sort so that the zap will search for the Column A name (Joe Schmo) within the entire Lookup VAlue from Clio (e.g. Joe Schmo, Mary Foe), and upon finding that the Clio Attendees at least “contains” the attorney’s name, then Zap will consider it a scucess and will allow me to proceed with sending an email reminder to Joe Schmo at joes@abc.com.

I’ve tried Contain(), Search(), ISNUMBER (SEARCH” formulas, not knowing if any of these actually are supposed to work, and not even knowing if I am setting them up correctly. Apparently not, because they are not working the way that I’m trying to use them.

Any ideas? 

Thank you.

Bert

 

 

 

 

Hi @Accesslegalcare 

Try this…

Use the action step Formatter > Utilities > Text to Line Items: https://zapier.com/help/create/format/create-line-items-in-zaps

Then you can use the Looping app to handle the line items (aka array): https://zapier.com/apps/looping/integrations


Ok, thank you. That tells me that I can do it, but my tech knowledge is limiting me.

Would I be able to hire you to set up this zap for me by chance?

If not, I will try to slush through it and understand it all. 

Thank you for giving me the answer! I really appreciate it! That was fast.

Bert