Filter Collection Where Alias Contains

[hnguy71]

Hi All. 
I have a variable set to obtain the current logged in user's alias by splitting the email address and returning only the left value by delimiter. The formula alias formula works and is as followed:

Set(_UserEmail, User().Email);
Set(_UserAlias, Lower(First(Split(_UserEmail, "@")).Result));

I then have a collection called UserList with a column called Reviewer which has a large range of users. Inside this large list I want to create a new collection where my alias is equal to any record inside Userlist. My current formula is this which gives me no error nor does it return any results:

ClearCollect(_MyAccess, Filter(UserList, _UserAlias in Lower(Reviewer.Email)));

Sample Data for UserList:

ID	Reviewer
1	ME@Company.com
	Person1@Company.com
	Person2@Company.com
	Person3@Company.com
	Person4@Company.com
2	ME@Company.com
3	Person1@Company.com
	Person2@Company.com
	Person3@Company.com

Sample Result for _MyAccess:

ID	Reviewer
1	ME@Company.com
	Person1@Company.com
	Person2@Company.com
	Person3@Company.com
	Person4@Company.com
2	ME@Company.com

Any help would be greatly appreciated!

[Jeff_Thorpe]

I see a couple of concerns with the example you provided. The "in" operator is trying to match the UserAlias value with the full email address in the Review column. The second concern is the "Reviewer.Email" when I think you really want to see if the UserAlias is in the list of email address in the "Reviewer" column for each row.

Here is an example that I think accomplishes what you are trying to do. The two big changes here are I am using the user's email address to compare against the Reviewer field since it contains complete email addresses and then I am comparing against the Reviwer column and not Reviewer.Email.

The "Set()" and "ClearCollect()" and just setting up my sample data to match your use case. You should be able to cut and paste this example into a button and test it out. If it works for you then you should be able to make some minor tweaks to it to get it to work in your app.

Set(gvUserEmail, "Me@Company.com");
ClearCollect(
    colUserList,
    {
        ID: 1,
        Review: Table(
            {Email: "ME@Company.com"},
            {Email: "Person1@Company.com"},
            {Email: "Person2@Company.com"},
            {Email: "Person3@Company.com"},
            {Email: "Person4@Company.com"}
        )
    },
    {
        ID: 2,
        Review: Table({Email: "ME@Company.com"})
    },
    {
        ID: 3,
        Review: Table(
            {Email: "Person1@Company.com"},
            {Email: "Person2@Company.com"},
            {Email: "Person3@Company.com"}
        )
    }
);
ClearCollect(
    colMyAccess,
    Filter(
        colUserList,
        gvUserEmail in Lower(Review)
    )
)

[hnguy71]

Hi @Jeff_Thorpe ,
Thanks for the reply!
Originally I had a similar code to match by the full email address. The issue is that each person can have alternate email addresses which sometimes doesn't pick up. However, their alias (email nick) always remains the same regardless of which email account they're using. To extend your solution that mimics my table I'm expecting the new collection to return two records:

Set(gvUserEmail, "Me@CompanyA.com");
ClearCollect(
    colUserList,
    {
        ID: 1,
        Review: Table(
            {Name: "Myself", Email: "ME@CompanyA.com"},
            {Name: "Person1", Email: "Person1@CompanyB.com"},
            {Name: "Person2", Email: "Person2@CompanyA.com"},
            {Name: "Person3", Email: "Person3@CompanyA.com"},
            {Name: "Person4", Email: "Person4@CompanyB.com"}
        )
    },
    {
        ID: 2,
        Review: Table({Name: "Myself", Email: "ME@CompanyB.com"})
    },
    {
        ID: 3,
        Review: Table(
            {Name: "Person1", Email: "Person1@CompanyA.com"},
            {Name: "Person2", Email: "Person2@CompanyB.com"},
            {Name: "Person3", Email: "Person3@CompanyB.com"}
        )
    }
);
ClearCollect(
    colMyAccess,
    Filter(
        colUserList,
        gvUserEmail in Lower(Review.Email)
    )
)

[RandyHayes · Accepted solution]

@hnguy71 

Can you explain why you would expect two records from that formula?

My assumption is that you would expect ID 1 and 2 to be in your collection - however, you will not get that because I believe you have a typo in your formula for ID 2.  There you have ME@CompanyB.com - again, my assumption is that you wanted that to be ME@CompanyA.com

IF the above is correct that it was wrong, then your formula needs to adjust to this in order to get the results that you want:

Set(gvUserEmail, "Me@CompanyA.com");
ClearCollect(
    colUserList,
    {
        ID: 1,
        Review: Table(
            {Name: "Myself", Email: "ME@CompanyA.com"},
            {Name: "Person1", Email: "Person1@CompanyB.com"},
            {Name: "Person2", Email: "Person2@CompanyA.com"},
            {Name: "Person3", Email: "Person3@CompanyA.com"},
            {Name: "Person4", Email: "Person4@CompanyB.com"}
        )
    },
    {
        ID: 2,
        Review: Table({Name: "Myself", Email: "ME@CompanyA.com"})
    },
    {
        ID: 3,
        Review: Table(
            {Name: "Person1", Email: "Person1@CompanyA.com"},
            {Name: "Person2", Email: "Person2@CompanyB.com"},
            {Name: "Person3", Email: "Person3@CompanyB.com"}
        )
    }
);
ClearCollect(
    colMyAccess, 
    Filter(
           Ungroup(colUserList, "Review"), 
           gvUserEmail in Lower(Email)
          )
)

You need to ungroup the table to be able to filter on it like that.

I hope that is helpful for you.

[hnguy71]

Hi @RandyHayes 

Sorry for the misunderstanding. The email in ID 2 is correct. I'd like to evaluate if the alias / mailnick is the same regardless of email domain name. So Me@CompanyA.com should also return true if Me@CompanyB.com.  Ungrouping them did the trick! 

Could you explain why ungrouping them would work versus just leaving them inside a table?

[RandyHayes]

@hnguy71 

Glad to hear that worked.

The reason the ungrouping worked for you is that your filter is applying to a set of records.  Since the table is an element within the records, you just need to get it to a state where there are records that you can filter against. 

When you ungroup, you are creating a table of records, one for each record in the sub table.  This can then be easily filtered.  

If you put the ungrouped collection into another collection, you can see the results...and those are a table of all the records with their corresponding "main" record elements.  That satisfies the filter function much better.

I hope that is clear...I re-read it and I'm wondering if I am stating the point properly.  Best thing to do is to throw the ungrouped collection into something you can see in the Collection Viewer or in a Gallery.  That will make more sense.

[hnguy71]

Yep that explanation definitely helped. Thaks a lot for the help as always!