cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver II
Resolver II

Accessing an inner ForAll

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

2 REPLIES 2
Super User III
Super User III

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

@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

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (20,534)