cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MCompton
New Member

Writing Non-Duplicative Collection Values to SharePoint List

Hello all, I've searched around and tried several methods to make this work, but so far no luck. I'm hoping someone with more experience can shed some light.

 

I am writing a simple app that will allow group leaders to define a couple of data points about their direct reports. The app's OnStart begins by writing the contents of Office365Users DirectReportsV2 to a collection (since I do not have any direct reports, I am testing with another user's account, but this code will ultimately reflect the user launching the app):

 

 

ClearCollect(
    colMyDirectReports,
    Office365Users.DirectReportsV2("user@company.com").value
);

 

 

They are then shown a gallery that filters a SharePoint list based on who their direct reports are. The SharePoint list itself will contain all possible direct reports, but the group leader will only see their own:

 

 

Filter(SharePoint_List, Account in colMyDirectReports.userPrincipalName)

 

 

I am trying to get the app to write any of the collection's user names to the SharePoint list on start, while avoiding any duplicates. The scenario would be that Manager A has two direct reports: User B and User C. Sometime later Manager A hires User D. On first launch of the app, User D is written to the SharePoint list so that they are visible in the app, but Users B and C are not written to the list a 2nd time. 

 

So far I have managed to either A) write the direct reports to the list on every launch, resulting in many duplicates, or B) write only the first direct report in the collection to the list while avoiding duplicates.

 

I'm happy to provide any additional info that could help. Many thanks in advance for any assistance!

1 ACCEPTED SOLUTION

Accepted Solutions
BCBuizer
Super User
Super User

Hi @MCompton ,

 

Take 2 then:

 

ClearCollect(
    colMyDirectReports,
    Office365Users.DirectReportsV2("user@company.com").value
);

ForAll(
    colMyDirectReports As DRs,
	If(
		IsBlank(LookUp(SharePoint_List,Account= DRs.userPrincipalName).Account),
		Patch(SharePoint_List,
			{Availability: "Available",
			User: DRs.displayName,
			Account: DRs.userPrincipalName});
	)
)


Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

View solution in original post

6 REPLIES 6
BCBuizer
Super User
Super User

Hi @MCompton ,

 

Can you share the code you have tried so far to save the DRs in the data source? Scenario A seems most promising, but perhaps a check to see if the user already exists needs to be included.



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

Hi @BCBuizer, thanks for the reply! Below is my OnStart code. This does not currently make any attempt at filtering based on current values in the SP list, but it does successfully patch the stuff I want it to.

 

ClearCollect(
    colMyDirectReports,
    Office365Users.DirectReportsV2("user@company.com").value
);

ForAll(
    colMyDirectReports,
Patch(SharePoint_List,
{Availability: "Available"},
{User:displayName},
{Account:userPrincipalName});
)
BCBuizer
Super User
Super User

Hi @MCompton ,

 

I have included an If function to check if the record already exists and only perform the Patch function only  if it does not:

 

ClearCollect(
    colMyDirectReports,
    Office365Users.DirectReportsV2("user@company.com").value
);

ForAll(
    colMyDirectReports,
	If(
		!IsBlank(LookUp(SharePoint_List,Account= ThisRecord.userPrincipalName)),
		Patch(SharePoint_List,
			{Availability: "Available",
			User:displayName,
			Account:userPrincipalName});
	)
)

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

Thank you @BCBuizer. Unfortunately that code is being flagged in a couple of spots in line:

 

Account= ThisRecord.userPrincipalName

 

The = sign has an error with hover text: "Incompatible types for comparison. These types cannot be compared: Text, Error."

 

And userPrincipalName has an error with hover text: "Name isn't valid. userPrincipalName isn't recognized."

 

I tried changing = to in, and ThisRecord.userPrincipalName to colMyDirectReports.userPrincipalName. This didn't result in any errors, but it does just re-patch the same data every time, so it's not doing a proper duplicate check.

BCBuizer
Super User
Super User

Hi @MCompton ,

 

Take 2 then:

 

ClearCollect(
    colMyDirectReports,
    Office365Users.DirectReportsV2("user@company.com").value
);

ForAll(
    colMyDirectReports As DRs,
	If(
		IsBlank(LookUp(SharePoint_List,Account= DRs.userPrincipalName).Account),
		Patch(SharePoint_List,
			{Availability: "Available",
			User: DRs.displayName,
			Account: DRs.userPrincipalName});
	)
)


Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

@BCBuizer, that did it! Thank you so much! So is the solution to just redefine the collection name as some other variable (in this case DRs)?

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,412)