Re: Getting Email addresses from Distribution list

Domearian · 05-31-2024 03:04 AM

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.

Suvrutt_Gurjar

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

Domearian

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.

Domearian

would this work?

SELECT(Email List[Email], AND(CONTAINS([TEST Area], Test Data[Area]), [Test Data?]="Yes"))

Domearian

That didn't work

Suvrutt_Gurjar

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?

Domearian

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.

Suvrutt_Gurjar

I am afraid, you may need to elaborate that with some example data.

Domearian

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.

Suvrutt_Gurjar

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?

Domearian

That is a Ref List showing Location which provides the Area.

Suvrutt_Gurjar

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"

Domearian

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.

Suvrutt_Gurjar

@Domearian wrote:

SELECT(Email List[Email], AND(CONTAINS([TEST Area], Test Data[Area]), [Test Data?]="Yes"))

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.

Domearian

@Suvrutt_Gurjar wrote:
SELECT(Email List[Email], AND(CONTAINS([TEST Area] , ANY(Test Data[Area])), [Test Data?]="Yes"))

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?

Suvrutt_Gurjar

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.

Domearian

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.

JMPeterson

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.

Domearian

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.

JMPeterson

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.

Suvrutt_Gurjar

I think the answer may also lie in how the bot is structured and what you are trying to achieve with the bot. So far we have been concentrating on the email expression but I think based on the fact that it does not work with so many alternatives means maybe we need to understand more about bot.

Domearian

It's a simply BOT that send an email for the submitted bit of information. Nothing complicated.

Suvrutt_Gurjar

Well, you may want to elaboarate what does it mean by " submitted bit of information". Does it mean only the records added? If so are the records added in bulk?

Many times as we know the answer could lie somewhere else.

The reaponses so far are considering 2 tables in entirity. But maybe with bot you are looking email to be sent for only newky added records etc.

Domearian

I don't know how much simpler I can put it. As explained, it is a single UX form submitting 4 fields:

So it's one form. Being submitted 1 at a time. The bot is simply run when a new item is Added. That is it. It's fine, I'll work it out. Thanks for your input.

Suvrutt_Gurjar

Sure, thank you.

@Domearian wrote:

So it's one form. Being submitted 1 at a time. The bot is simply run when a new item is Added.

I felt this was not coming out. But I could be wrong in my previous understanding and if so, I am sorry,

With this latest input, I also feel @JMPeterson following suggestion should work.

@JMPeterson wrote:

Can you show me the formula? It should look something like the following;
SELECT(Email List[Email],IN([_THISROW].[Area],[TEST AREA]))