Dynamically send an SMS message

[LadyT]

Based on this table structure,

I want to create a bot that does the following:  When a new message is added in the Message table, the system will send an SMS message to the phone numbers where the Message category equals the PositionsHeld category

I have tried several conditions to dynamically populate the "To" field of the SMS message. My expression yields no errors. When I monitor the bot, the message I get says the "To" field is blank.

One example of an expression I used it below.

Select(PositionsHeld[v_SMS], ([Category] = [_THISROW].[Category]))

I have been at this for weeks and can't figure out why it's not working for me. Any assistance is very much appreciated. Thanks!!!

[WillowMobileSys]

First, I assume your Bot is triggered when a new Message table row is ADDED. 

The "To" field for an SMS message must be a list of mobile phone numbers.  What value is in the v_SMS column - as there is no SMS column showing in the Person table? It should be pulling in the Mobile column and defined as Phone type.

If you are pulling in the Mobile column into the v_SMS column then it should be working.  Make sure you have full valid mobile phone numbers.  Additionally, since it seems you may have multiple rows per user for different Categories, you could potentially get the same mobile number multiple times.  I would suggest using the UNIQUE() function to reduce the list down.  So, that final expression would be:

UNIQUE(Select(PositionsHeld[v_SMS], ([Category] = [_THISROW].[Category])))

After reviewing the above, if you are still getting no recipients populated, then try adding a this expression to the Message table in order to verify the List being returned.

I hope this helps!!

[LadyT]

Thank you for the response!! I get no errors with the expression, however the SMS field is not populating with any data.  It looks like a data type mismatch, but I'm not sure. I'll keep digging!