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?
Solved! Go to 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!
Using a comma separated list won’t work.
Here is one way to model the group data that will work.
@Phil Thanks! I’ll give this a try and will let you know if I’m successful
@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!
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.
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
)
SELECT(StaffGroup[Staff], ..., TRUE)
gathers a list of distinct (per TRUE
) staff which match the given criteria (...
, see (2) below). “List group members.”
([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?”
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.”
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?”
Steve,
Can you help with writing the expression that does the following:
He has three tables (using my table names).
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.
Thanks so much Steve. Really appreciate your help!
@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!
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |