SMS workflow - is it possible to initiate a group text from AppSheet

[Ethan_Hildebran]

I’m building a Staff/Contacts table. In addition to individuals/individual contacts, I am also creating groups as records within this table - i.e., “Management”, “Admin” - that would include an [EMAIL] and [CELL PHONE] column with entries as a list of comma separated email addresses and phone numbers (respectively). I would like to use this table as my official departmental contacts and allow all staff to initiate either emails, and/or SMS message from a detail view in my app. I’ve noticed that I can send an email from a details view that includes group [EMAIL] in the To: of an email. However, when I attempt to text from a group (comma separated phone numbers) only the first number is pushed to the message app on my phone (iOS).

Is it possible to initiate group SMS text message from AppSheet?

[Phil]

Using a comma separated list won’t work.

Here is one way to model the group data that will work.

1. Keep the Staff table as is with one phone number and one email address per staff member. However, remove all of the Group records from this table.
2. Create a new Group table that contains one record per Group. Its key is GroupName. Add your groups here.
3. Create a new GroupStaff table that contains one Ref to Staff and another Ref to Group.The Staff to Group relationship is a many-to-many relationship. The GroupStaff table allows you to model that. It will contain one record per Staff member per Group. For example, if “Paul” belongs to groups “A” and “B”, then GroupStaff would contain two records. One record would contain {“Paul”,“A”} and the other would contain {“Paul”,“B”}.
4. Now if your workflow email or SMS refers to a group, you can look up all of the group members in the “Staff” member and retrieve their email address or phone number.

[Ethan_Hildebran]

@Phil Thanks! I’ll give this a try and will let you know if I’m successful

[Ethan_Hildebran]

@Phil I setup my reference tables as you suggested - easy enough. Now I’m having trouble pulling in the email and phone numbers based upon a group reference. I think my trouble may be due to the fact that my [GROUP] selection is an ENUMLIST (?)

I’ve tried…

SELECT(OAR STAFF[EMAIL], IN([Related STAFF GROUPSs], [_THISROW].[TEAM]))

SELECT(OAR STAFF[EMAIL], contains([_THISROW].[TEAM], [Related STAFF GROUPSs]))

SELECT(OAR STAFF[EMAIL], contains([_THISROW].[TEAM], STAFF GROUPS[GROUP NAME]))

and various permutations of the above.

The best I’ve been able to manage is an expression that is valid, but doesn’t pull in any data. Help please!

[Steve]

Your SELECT() statements will produce lists (possibly empty lists, but lists nonetheless). If you’re trying to get only a single value, wrap SELECT() with ANY().

ANY(SELECT(OAR STAFF[EMAIL], IN([Related STAFF GROUPSs], [_THISROW].[TEAM])))

The above SELECT() format is the correct of the three you said you’d tried.

See also: ANY(), SELECT()

[Steve]

At @phil’s request…

The following expression would be suitable for use within the Group table, such as the app formula for a virtual column:

SELECT(
  Staff[Phone],
  IN(
    [Name],
    SELECT(
      StaffGroup[Staff],
      ([Group] = [_THISROW].[Name]),
      TRUE
    )
  ),
  TRUE
)

1. SELECT(StaffGroup[Staff], ..., TRUE) gathers a list of distinct (per TRUE) staff which match the given criteria (..., see (2) below). “List group members.”

2. ([Group] = [_THISROW].[Name]) selects only rows of the StaffGroup table (per (1), above) with a Group column value that matches the current group’s Name. As noted above, this entire expression is intended for use within the Group table, where [_THISROW] would refer to a group row. “Is this staffer a member of the group?”

3. SELECT(Staff[Phone], ..., TRUE) gathers a list of distinct (per TRUE) phone numbers from the Staff table from rows matching the given criteria (..., see (4) below). “List group member phone numbers.”

4. IN([Name], ...) selects only rows of the Staff table (per (3), above) with a Name that occurs in the list of group members gathered by (1), above. “Is this staffer in the group?”

[Phil]

Steve,

Can you help with writing the expression that does the following:

He has three tables (using my table names).

1. Table “Staff” with key [Name], and field [Phone] and [Email] (This is the list of people with a name and phone for each)
2. Table “Group” with key [Name] (This is the name of the group e.g. “Accounting”, “Sales”, “Management”)
3. Table “StaffGroup” with two ref fields. One to “Staff” the other to “Group”. (This is the many-to-many relationship table. It has one row for each group member. For example, if “Paul” is in “Sales” and “Management” it has two rows {Paul, Sales} and {Paul, Management}

Starting with the “Group” name, we want to retrieve the list of phone numbers of the members in that group.

You are much better at expressions than I am.

[Phil]

Thanks so much Steve. Really appreciate your help!

[Ethan_Hildebran · Accepted solution]

@Steve @Phil Thank you both very much! I got the expression to work as I wanted. The final format (with my table/column names)…

SELECT(
OAR STAFF[CELL PHONE],
IN(
[KEY],
SELECT(
STAFF GROUPS[STAFF KEY],
IN([GROUP NAME], [_THISROW].[TEAM]),
TRUE
)
),
FALSE
)

I added the second IN expression - IN([GROUP NAME], [_THISROW].[TEAM]) - because [TEAM] is an ENUMLIST selection and I was getting an expression error (can’t compare name to list).

I’ll probably cleanup my table and column names, replacing GROUP with TEAM, for consistency before I roll out this functionality into my production app.

Thanks again!