Personalized SMS Text To Multiple Phone Numbers

AppSheet AppSheet Q&A
,
1

I haven’t found a way to make this work, if it’s possible. Could someone please advise?

I send out a text to all new crew members added to a job, but I’d like to personalize the text message with the receiver’s name, if possible.

Thanks

Phone Numbers SELECT:

SELECT(CSI Employees[Main Phone], CONTAINS(
[_THISROW].[New Crew], [Employee ID]))

Current Text Message Body:

You’ve been assigned as crew to Ticket #<<[Ticket Number]>>.
Date: <<[Ticket ID].[Ticket Date]>>
Customer: <<[Ticket ID].[Customer]>>
Job Details: <<[Ticket ID].[Job]>>
Work Location: <<[Ticket ID].[Work Location]>>
Type of Ticket: <<[Ticket ID].[Type of Ticket]>>

I’d like it to say something like:

<<LOOKUP(“Main Phone”, “CSI Employees”, “Main Phone”, “First Name”)>>,

You’ve been assigned as crew to Ticket #<<[Ticket Number]>>.
Date: <<[Ticket ID].[Ticket Date]>>
Customer: <<[Ticket ID].[Customer]>>
Job Details: <<[Ticket ID].[Job]>>
Work Location: <<[Ticket ID].[Work Location]>>
Type of Ticket: <<[Ticket ID].[Type of Ticket]>>

2

You’d have to send separate text messages to each person. As in separate actions or bots or tasks.

3

If you have an automation task configured to send the same message simultaneously to multiple numbers, I believe that you cannot customize the message dynamically for each number. Maybe there’s some backdoor way to reference the current number in a template variable; you could poke around in the automation monitor or audit logs for anything that resembles a schema dereference (e.g., [Send an SMS task].[To]).

Presumably you could at least include all the names in every message. For example:

SELECT(CSI Employees[First Name], CONTAINS([_THISROW].[New Crew], [Employee ID]))

// or, maybe use [IN](https://support.google.com/appsheet/table/10104782?hl=en#query=in(t) instead of CONTAINS

SELECT(CSI Employees[First Name], IN([Employee ID], [_THISROW].[New Crew]))

// or, if the New Crew column is a list of key values from the CSI Employees table, maybe use a [list dereference](https://support.google.com/appsheet/answer/10107396?hl=en#dereference-a-list)

[New Crew][First Name]

If you include a list of names, you may want to use a different delimiter than the default.

4

Thanks, dbaum! I appreciate you taking the time to answer my question. Those are great ideas. I’ll see what I can find.

1 Like
5

I have a similar issue and your solution is the closest i have seen… I have a job dispatch app that stores email addresses in an list for jobs that have multiple instructors. I need to take the instructors column and then pull each insructor phone and send the SMS. Any thoughts?

6

mnprivate,

The solution by dbaum worked well for me. I wasn’t able to personalize each notification message as I wanted, but just saying “You’ve been assigned…” has worked well enough.

The body message ended up being:

You’ve been assigned as crew to Ticket #<<[Ticket Number]>>.
Date: <<[Ticket Date]>>
Customer: <<[Customer]>>
Job Details: <<[Job]>>
Work Location: <<[Work Location]>>
Type of Ticket: <<[Type of Ticket]>>

The TO: expression ended up just a normal SELECT():

SELECT(CSI Employees[Main Phone], IN([Employee ID], [_THISROW].[New Crew]))

It should work the same to select email addresses to send email notifications.