I'm trying to generate a list of mobile numbe...

expressions
(David Hopkins) #1

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

  • Classes - key, title, Related Registrations By Class * Students - name (is the key) * StudentToClass - _computedKey (appformula of CONCATENATE([Class],": ",[Student])), Class, Student, Related Registrations By Student

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…

(David Hopkins) #2

#SOLVED

(Reza Raoofi) #3

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”)

(David Hopkins) #4

@RezaRaoofi Very nice… i had not thought about the VT…

(David Hopkins) #5

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…