Writing Non-Duplicative Collection Values to SharePoint List

[MCompton]

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!

[BCBuizer]

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.

[MCompton]

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]

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});
	)
)

[MCompton]

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 · Accepted solution]

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});
	)
)

[MCompton]

@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)?