I’m trying to generate a list of mobile number to send an SMS via the Send Message Workflow, but I can’t seem to get the query working…
There are 3 tables involved
The computed key provides restrictions so that the system will not allow the same student to be registered for the same class twice.
I’ve tried several directions for getting the information by interrogating the tables in different ways… but nothing seems to work. i.e.
From Students
SELECT(Students[Mobile], IN(“someClassID”, [Related Registrations By Student]))
From StudentToClass
SELECT(Student[Mobile], [Class]=“someClassID”)
One primary issue is that the Workflow TO field is not attached to the Expression Builder, so it’s difficult to build elsewhere and move over…
Thoughts, suggestions?
Thanks in advance all…
#SOLVED
StudentToClass table must have proper Ref columns; one referring to Students table (e.g. [StudentRef]), and another referring to Class table. 2) In StudentToClass table create a Virtual Column e.g. [StudentMobile], with this formula: [StudentRef].[StudentMobile] – assuming there is such column as [StudentMobile] in Students table.
If you have above in place, then you can have a SELECT on StudentToClass table that makes a list of the Virtual Column for a particular class; something like this:
SELECT(StudentToClass[StudentMobile], [Class]=“ClassID”)
@RezaRaoofi Very nice… i had not thought about the VT…
So… Im not sure if this is a work around, or the right answer… but… it’s working…
SELECT(Students[Mobile], CONTAINS([Related Registrations By Student], LOOKUP([SendTo], “Classes”, “Title”, “key”)))
This is the query that I put in to the TO field of the workflow… it’s works perfectly…
User | Count |
---|---|
39 | |
28 | |
24 | |
24 | |
13 |