Hello everyone

I’m looking for advice on an automation…  I’m developing with Google Drive, and Microsoft Outlook, utilizing data reports extracted from MyCase. Each week, I upload CSV files containing client, case, and clinic information, and I want to use this data to send follow-up emails to medical clinics regarding the treatment status of our clients.

Objective:

Every Monday, I need Zapier to perform the following steps:

1. Pull CSV Files: Access the Client information report, Company report, and Case report from a specific Google Drive folder.
2. Filter Clinics: From the Company report, filter for clinics categorized under "Medical Provider" and with "Chiropractic" as their specialty.
3. Match Cases: In the Company report, the clinic’s case names are listed together in one cell. I need to split these case names and compare them with client names in the Client information report.
4. Extract Client Details: If a match is found, extract the following details from the Client information report:
   • Full name
   • Date of birth (DOB)
   • Phone number
   • Address Extract the accident date (D.O.L.) from the Case report.
5. Generate Emails: Use Microsoft Outlook to send personalized follow-up emails to the clinics, including the extracted client details and requesting updates on their treatment status. If no active cases are found, no emails should be sent.

Challenges:

• The Company report has cases listed together in one cell, requiring a method to split and accurately match them with the client names from the Client information report.
• MyCase will be used to identify the cases once the relevant information has been extracted, but I need to ensure that the automation pulls the correct case information based on the extracted data.

My Questions:

1. Feasibility: Is this approach achievable using Zapier’s built-in tools and functionality, considering the CSV structure and data matching requirements?
2. Alternative Solutions: Are there any alternative tools or methods within Zapier that could simplify this process or improve efficiency?
3. Process Reevaluation: Should I consider restructuring the CSVs or adjusting my approach to better fit the automation capabilities of Zapier?

I would really appreciate your help in this matter.

Thanks in advance