Hello All,
I have something that is absolutely stopping me in my tracks. Here is the situation:
I have a workflow process where we are going to use an Airtable form to add a new row to a sheet - this is a new job for us.
That in turn, using Zapier, makes an email that sends to my scheduling software called Service M8. Service M8 (SM8) converts this email into a job, and automatically converts the name of the customer from first name last name to last name, first name.
We have to use a set template for the email to be able to be ready by SM8 and therefore be able to convert it to a job with one click. And the tables for name are as the below example:
First Name: John
Last Name: Smith
When a job is automatically made from the email: Smith, John
Once the record is in Airtable, and the job is in SM8 we run workflow that constantly updates fields in Airtable. They look for the column we have called “Job Name” as that’s how we track everything. The workflow driven off of the SM8 is all driven off of forms we use in SM8 - and for whatever reason, the data available to Zapier is fields like both names together (John Smith) or it shows the name as two different fields (John or Smith). So ultimately, when we want to update the row in Airtable, we use a FIND ROW process in Zapier, and it can’t find the row because the job name in SM8 (Smith, John) is not the same as the job name in Airtable (John Smith).
Sounds easy maybe for some right now… just create a few new columns in Airtable to catch the first name and the last name, then run a formula to the “Job Name” column and make sure I reverse them. That works for some of my jobs, but a lot are different. Here is an example of where it works for me and where I stop understanding how to make it work:
How I know how to make it work: John Smith (that’s easy - if converted to “Smith, John” it all works using the following formula:
TRIM(CONCATENATE({First Name}, " ", {Last Name}))
How I don’t understand how to make it work: I have some jobs that are not first name last name - here are some examples:
Steris 2500 - #39
Target - Merryville
Chelsey’s
When I commercial account jobs they can look like the 3 examples above. When I send the email template out, I have to put it in like it shows - all as the 1st name - or it reverses it and looks weird. So these jobs would be make via the email template as follows where I would leave the last name blank.
First Name: Steris 2500 - #39
Last Name:
So I guess what I am thinking is to have my primary column be “Job Name” and be the last name, first name order to match the incoming workflow. Because the names come out of SM8 as individual names, I need a column for “First Name” and another one for “Last Name”. I then need a formula on my “Job Name” column to take the last name from the column and add a comma after it, then add the first name - while trimming to make sure there are no spaces where their shouldn’t be. Then… how do I deal with the non-typical ones that are are basically one name? How would I ever account for these variables in the “Job Name” column?
I am hoping that this all makes some sense and that someone may be able to offer some insight. Thanks in advance if anyone can do so.
Wayne Merlino
