Accessing an inner ForAll

[hantsjoel]

Hi all,

I'm creating a collection which combines user data from a SP list with data from the Office365Users connector. The below code delivers what I need, but I'd be interested to learn if there is a more efficient way of doing this.

ClearCollect(
    AllUsersList,
    ForAll(
        ForAll(
            Filter(NewStarterJourney, Started),
            Office365Users.UserProfileV2(UserID)
        ),
        {
            JobTitle:jobTitle,
            UserPrincipalName:userPrincipalName,
            Id:id,
            GivenName:givenName,
            Surname: surname,
            Mail:mail,
            Department:department,
            Manager: With({manager: Office365Users.ManagerV2(id)}, manager.givenName & " " & manager.surname),
            StartDate: LookUp(NewStarterJourney, UserID = userPrincipalName, StartDate),
            Team: LookUp(NewStarterJourney, UserID = userPrincipalName, Team),
            SatisfactionW1: LookUp(NewStarterJourney, UserID = userPrincipalName, SatisfactionW1),
            SatisfactionW2: LookUp(NewStarterJourney, UserID = userPrincipalName, SatisfactionW2),
            Photo:If(
                Not(id in UserPhotoCache.UserId),
                    Collect(UserPhotoCache,
                    {
                        UserId:id, 
                        Photo:If(Office365Users.UserPhotoMetadata(id).HasPhoto,Office365Users.UserPhoto(id),'generic-user'),
                        Time:Now()
                    }
                    );
                LookUp(UserPhotoCache,UserId = id).Photo,
                LookUp(UserPhotoCache,UserId = id).Photo)
        }
    )
);

As you can see with the fields StartDate, Team, SatisfactionW1 and SatisfactionW2, I'm performing a LookUp on the same SP datasource in the inner ForAll. Is there a better way of doing this so I'm not making as many calls to the SP list?

Thanks

Joel

[RandyHayes · Accepted solution]

@hantsjoel 

You can consider this formula as a replacement:

ClearCollect(
    AllUsersList,
    AddColumns(
        AddColumns(
            Filter(NewStarterJourney, Started), 
            "userProfile", Office365Users.UserProfileV2(UserID)                
        ),
        "Manager", With({manager: Office365Users.ManagerV2(userProfile.id)}, manager.givenName & " " & manager.surname),
        "Photo":If(Not(userProfile.id in UserPhotoCache.UserId),
                      Collect(UserPhotoCache,
                          {UserId:userProfile.id, 
                           Photo:If(Office365Users.UserPhotoMetadata(userProfile.id).HasPhoto,Office365Users.UserPhotoV2(userProfile.id),'generic-user'),
                           Time:Now()
                          }
                      );
                   LookUp(UserPhotoCache,UserId = userProfile.id).Photo,
                   LookUp(UserPhotoCache,UserId = userProfile.id).Photo)
               )
    )
);

I'm assuming that the initial UserID is an email.

In this formula, you are already filtering by the NewStarterJourney list based on a Started column being true.  So, the only real task is to add any missing columns that you want - the Manager, and Photo (as they are outside of the data you have already collected in the Filter and the UserProfile.  

Your resulting collection will already have the StartDate, Team, SatisfactionW1 & 2 in the results - so there is no need to do any further lookups.

Even though you will have all of the UserProfile information in the collection column called "userProfile", if you still wish to have individual properties as columns you could bring those out as new columns as well with this modification:

ClearCollect(
    AllUsersList,    
    AddColumns(
        AddColumns(
            Filter(NewStarterJourney, Started), 
            "userProfile", Office365Users.UserProfileV2(UserID)                
        ),
        "Manager", With({manager: Office365Users.ManagerV2(userProfile.id)}, manager.givenName & " " & manager.surname),
        "Photo":If(Not(userProfile.id in UserPhotoCache.UserId),
                      Collect(UserPhotoCache,
                          {UserId:userProfile.id, 
                           Photo:If(Office365Users.UserPhotoMetadata(userProfile.id).HasPhoto,Office365Users.UserPhotoV2(userProfile.id),'generic-user'),
                           Time:Now()
                          }
                      );
                   LookUp(UserPhotoCache,UserId = userProfile.id).Photo,
                   LookUp(UserPhotoCache,UserId = userProfile.id).Photo)
               ),
        "JobTitle", userProfile.jobTitle,
	"UserPrincipalName", userProfile.userPrincipalName,
	"Id", userProfile.id,
	"GivenName", userProfile.givenName,
	"Surname", userProfile.surname,
	"Mail", userProfile.mail,
	"Department", userProfile.department,
    )
);

I hope this is clear and helpful for you.

[hantsjoel]

@RandyHayes, thanks as always for the great solutions and concise explanations. I went for your first suggestion and altered my use of the collection accordingly.

Cheers,

Joel