Multiple similar values from a SELECT expression

Here is a funny one… (and a challange) :slight_smile:

In many of my forms/reports I have a smart feature where one can choose whom to get the workflow report. I called it [Send to].
So I have an enumlist to checkmark the names to sent to.
"Select(Employes[Name], TRUE) I use this in Suggested values.
Quick and easy. So far so good.

Next I have a column to get the list of their email adresses (to use in the workflow).
Here I use a “Select(…in(…” formula to find those email adresses matching the names in the previous column. I put it in Initial value. I also got this to work, but here the fun starts…
The expresion I used here is:
Select(Employees[Email],In([Name],[_Thisrow].[Send to]))))

But the result is:
If I pick 1 name, I get his email adress 3 times.
If I pick 2 names, I get their email adresses 3+4 times.
If I pick 3 names, I get their email adresses 2+3+4 times

So… i tried:
Unique(list(Select(Employees[Email],In([Name],[_Thisrow].[Send to]))))))
But with the same result…???
It works, of course, but a bit anoying getting the same report 4 times…:slight_smile:

Anyone with an idea how to work around this one…
VERY greatful for any help here… and some explanation. It’s interresting to understand how this works.

I think I solved it:)
Just removed the “LIST”.

But is it better ways to do this?

I wonder if you might be hitting a test environment limitation. In the test environment, emails will only be sent to the app creator. So if your Workflow is designed to generate an email to a list of people, you personally will get all of those emails because they will not be sent out to the “real” email addresses.

The same goes for CC or BCC fields in the email Workflow.

I think, though, if this is the issue, that if you review those emails carefully, you will see some indication of the “real” email address the message was intended for.

1 Like

Hi.
Thanks for reply.
I haven’t really tried it with workflows yet. I just see the result in the column where I collect the email adresses.
It works now, but I’m concerned it is a slow way of doing things…

Since you challenged us…:slight_smile:

Now that I understand where you were having issues, I was curious and have been playing around with this in various different ways and cannot seem to replicate the issue you described.

I have a row key and have played with the expressions using the row key and without. storing the emails in an EnumList, a Virtual Column as List and a Virtual Column as LongText.

I wasn’t certain how you had your email capture column defined.

EnumList to select names - emails stored in EnumList column AND LongText Virtual Column

1 Like

Thanks for looking into this. Interresting to understand, even if I can make it work now…
I’ll try and explain better (my english is not great). To simplify, I took a shortcut explaining the issue above last time, but that might have done the difference… (but I think not…). Anyways, here is the full story:

I have a table with “COMPANIES” and a dauther table “PERSONS”. Names and emails++.
I have a table with “PROJECTS”, and a dauther-table with “PROJEKT_PARTICIPANTS”.
Both have a REF, and is a part of mother-table. (Through (1)Companyname and (2)Projectname.

So to add [PARTICIPANTS] into “PROJEKT_PARTICIPANTS” from “PERSONS”, I do this:
“PROJEKT_PARTICIPANTS” has a column [COMPANY_NAME] so I first can narrow down optinal names to choose from (also easier to find). Here I use REF to “COMPANIES”
Once the company is chosen, the next column in “PROJEKT_PARTICIPANTS” is [Name].
Here I use: "Select(PERSONS[Name], ([COMPANY_NAME]=[_Thisrow].[COMPANY_NAME]))
I use this in Valid if, and text. So only employees in that company, is shown in my dropdown.
In next column I also collect his email in formula: “Lookup([_Thisrow].[Name], PERSONS, Name, Email)”
So now, I have name and email in “PROJEKT_PARTICIPANTS”.

From here, it’s basically what I explaind i my post above.
I have several different reports, typically a deviation report, users can send, and in these they can choose whom to send to in an enumlist, “checking” of several names.
In [Send to] I use:
“Select(PROJECT_PARTICIPANTS[Name], ([Prosjectname]=[_Thisrow].[Prosjektname]))”
Used in Suggested values and as enumlist.
The [Projectname] is chosen in a previous column in the deviation report by using REF to “PROJECTS”.

Next I have a column to get the list of their email adresses (for later to use in a workflow).
Here I first used (in initial value):
Select(PROJECT_PARTICIPANTS[Email],In([Name],[_Thisrow].[Send to]))))
Here, the result was:
If I pick 1 name, I get his email adress 3 times.
If I pick 2 names, I get their email adresses 3+4 times.
If I pick 3 names, I get their email adresses 2+3+4 time

But when I wrapped it in “Unique”, it worked perfect.
Unique(Select(PROJECT_PARTICIPANTS[Email],In([Name],[_Thisrow].[Send to])))))

But still curious if this is the best way. Does it slow my APP down more than nessesary?
And the easter mystery remains: Why did it collect the emails multiple times…:slight_smile: (still does underneath, I asume)