I’ve setup an email distribution set on the area an item of data is submitted. The only problem now is that there are more areas being added all the time so the slices to select the distribution list for the areas is getting larger.
Lets say, submitted Data is called “Test data” and [Area] = “TES”
Email Distribution data with fields [Email Address]=(PII Removed by Staff), [Area] contains “TES”, [Test Data?]=“Yes”
So when a submission is made, and it is for area TES, I want the “To” to perform a lookup to see who it should be sending the email to without the need to create Slices for each Area.
AND(LOOKUP(“Yes”, “Email List”, “Test Data?”, “Email Address”), but not sure what to put for it to see which AREA contains “TES” within the Email Distribution Data.
If understanding of your requirement is correct, you could try below.
Actually, yes, slice would have been a better option in terms of not processing a SELECT() on a large data set.
But since, you do not wish to use a slice, instead of LOOKUP() which anyway is a wrapper for a SELECT() , you could try an expression of ’
SELECT(Email List[Email Address], AND(CONTAINS([Area], “TES”), [Test Data?]=“Yes”))
That would solve the initial problem of not using slices yes thank you, but what if the submission was for the area WOR. So I would need the email list[Area] to contain the Test Data[Area] from the Test Data. Hope that makes sense. So if I had 25 possible areas, I want it to decipher who can receive emails for that area. Otherwise I would have to create 25 slices to cater for each area and do an IFS statement.
would this work?
SELECT(Email List[Email], AND(CONTAINS([TEST Area], Test Data[Area]), [Test Data?]=“Yes”))
Could you elaborate what is Test Data[Area]
Is “Test Data” a different table?
and if so will it have multiple areas?
And if it can have multiple areas , how the user will select one out of those areas in the app for sending the email?
The Table is called Test Data, and the submission made will have a field called [Area]. So the email recipient must have that same area within the Email List [Test Area] for the email to send to them.
I am afraid, you may need to elaborate that with some example data.
Maybe this?
SELECT(Email List[Email], AND(CONTAINS([TEST Area] , ANY(Test Data[Area])), [Test Data?]=“Yes”))
This assumes Test Data will have only one [Area] at a time.
Here is a snippet of Test Data:
Snippet of Email List Data:
So if the test data is submitted for WES, it would only send it to Stephen Laurence email. If it was COR, it would send it to both people you can see.
Thank you very much. My question is how the app knows the test data is submitted for “WES” and not “COR”. Where and how the user selects the Area in the Test Data?
That is a Ref List showing Location which provides the Area.
Could you update where the location gets selected in the app ? I am asking because , I believe that selection will need to come into expression somehow. Or else how will the area be shortlisted to say 'WES"
It’s the AREA field within the test data. That [Area] field References the “Location Data”, so the user selects his area. Quite simple. It’s a simple test to see if it is possible to do the email distribution without the need to create multiple slices.
It seems to work but only for the the first person in the list, what if there are 2? So if a COR submission was made? In this test data it would need to send it to 2 people. Is it possible that it is not putting in the “,” comma, so it is in fact blending the email addresses?
You may want to try
SELECT(Email List[Email], AND(ISNOTBLANK(INTERSECT([Test Area], Test Data[Area])), [S/R Report]=“Yes”))
Assumes [Test Area] is an enumlist column.
That seems to want to send it to all the users despite one user not having the area within the [Test Area] field. Just can’t figure it out.
Perhaps instead of INTERSECT(), using the IN() function to check to see if the submitted item [Area] is in the [TEST AREA] column for the email list. That should be inclusive.
That also selects both users. I’ve just created a virtual column on the form now so when I select location, it assigns the location and I can instantly see if it works or not.
Can you show me the formula? It should look something like the following;
SELECT(Email List[Email],IN([_THISROW].[Area],[TEST AREA]))
I have done similar formulas before with success, so I might be missing a detail.
