Relationship by means of result from Check Box sets

[CKonash]

Hello,  In my fire department I have a table PERSONNEL RECORDS, I put a field on there LIST SELECT TO SMS.  In that field I have a check box value list assigned like "Co. 1, Co. 2, All FD Officers, etc"

each record in the personnel record table will get a variety of these check boxes checked of to match which group they are currently in.  I have a separate layout and table (MESSAGESSMS) that a chief officer will go to and write a message to be sent out using the AWS SMS service, which I have working.  

In the personnel record table I have the members mobile phone numbers filtered and a separate field (ListOfMoblePhones) that lists all the numbers in the found set of the personnel records.   

I was trying to find a relationship between the Personnel records and MessagesSMS table that would connect the results of the check box results and then it would lookup the correct ListOfMobilePhone to be sent out.  

I'm wondering if I'm going in the right direction?  What would be the best way to identify a persons personnel record as part of one or many of the check box groups and then in the MessagesSMS table be able to get the list mobile numbers from the personnel record field that contains the result of that checkbox set.  

 

Thank you! 

Chris

[comment]

If the chief officer will select a group (or groups) to send the SMS to from the same value list you use to assign personnel to groups, then linking the two tables using these two fields should give you what you ask for.  Then you can use a calculation of List ( Personnel::Mobile) to get the list in the Messages table.

Strictly speaking, there should be a join table between these two tables. But if you don't need to record individual responses to a message, or produce a report listing the personnel in each group (and a few other more advanced tasks), you can manage without it.

 

 

Edited May 5, 2019 by comment