Patching O365 Users in a Collection to then patch back to SharePoint List

[dw909]

Hi all. I've build an app with a repeating table in a gallery that allows for inline editing of a number of actions (new rows, deleting rows and editing rows) and an overall "Save Changes" to patch back to SharePoint list once done , roughly based on Shane Young / PowerApps 911 solution: https://www.youtube.com/watch?v=xgznk4XlPCo

However, I've gone beyond what Shane demonstrates in that my solution works with a People picker combo box and a Person / Group column in the SharePoint list.

The fields shown in the Power App match those of my SharePoint List. (Action Description is in fact a renamed "Title" column in SharePoint List).

For the inline changes - i.e. editing an item, and temporarily saving the change in the app, or for adding a new line item and temporarily saving in the app, I'm working with a Collection. The Collection itself is pulling data from the SharePoint list, so consequently my People information form O365 is presented within the Collection as a table of data (call it "ActionOwner"):

Claims     Department    DisplayName    Email    JobTitle     Picture

The "Claims" is in the format of i:0#f|membership|firstname.lastname@company.com

For the items already saved in my gallery this is of course fully populated ok. Now, if I want to add a new item in my gallery in a new blank row, or I want to edit an existing entry, either editing the person to be someone else, or perhaps just editing another field, I need to update the collection. I have been unable to work out how to actually patch the ActionOwner table within my Collection.

I did do a workaround of creating some additional columns in my Collection to capture Display Name and Email address and then use those to patch the SharePoint list. I found that creating a new entry or modifying the person field when editing an existing entry I could write both the Display Name and the Email address to these new columns, and then use these to patch the SharePoint list. Oddly whilst it worked, the system displays an annoying error: The requested operation is invalid. Server Response <SharePoint List Name> failed. The specified user i:o#f|membership| could not be found. Monitoring with the app showed a Network "createRow" Bad Request 400 error. I think it was finding that for some of my patching there was no email address which is required for the claims - and because the gallery contains always a blank row I assume it might have been because of that. I also noted that if I edit a row in the app but don't change the Action owner (person) then only the Display Name of the person would be added to the additional column for the Display Name in the collection, and the email isn't captured. It only gets captured if you remove the person in the combo box, then search for them again.

I've tried lots of ways to sort out the issues, but every single method I follow from these forums seems to result in one or more errors - most recently with my current syntax I get Network error when using patch function, the requested operation is invalid and in fact it's not even saving to SharePoint now.

I think rather than patching Display Name and Email address to these additional columns in the Collection, what I really need to do is have a way to update the actual ActionOwner table within my collection:

Then I need the syntax to patch from this Collection back to the SharePoint list. I suspect this is the only way to avoid all these patching areas related to people.

Any ideas anyone? 

Currently my inline save icon has an OnSelect of:

Patch(ActionCollection, ThisItem, {Title: TextInput3_1.Text, ActionOwner: ComboBox3_1.Selected.DisplayName, ActionOwnerEmail: ComboBox3_1.Selected.Mail, 'Action Status': Dropdown2_1.Selected, 'Target Date' : DatePicker1_1.SelectedDate, 'Associated ID' : BrowseGallery2.Selected.ID}); 

My overall Save Changes button has this syntax:

//Edit Existing Record
ForAll(ActionCollection As EditAction, Patch('SharePointList', LookUp(''SharePointList',ID = EditActionID), {Title: EditAction.Title, 'Action Status': EditAction.ActionStatus, 'Target Date': EditAction.TargetDate, 'Action Owner':{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Department:"",
Claims:"i:0#.f|membership|"& Lower(EditActionRTPOwner.Email),
Email:"",
DisplayName:EditAction.ActionOwner,
JobTitle:"",
Picture:""
}
}));

//Add New Record
ForAll(ActionCollection As EditAction If(IsBlank(EditAction.ID), Patch(''SharePointList, Defaults('SharePointList'), {Title: EditAction.Title, 'Action Status': EditAction.ActionStatus, 'Target Date': EditAction.TargetDate, 'Action Owner':{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Department:"",
Claims:"i:0#.f|membership|"& Lower(EditAction.ActionOwner.Email),
Email:"",
DisplayName:EditAction.ActionOwner,
JobTitle:"",
Picture:""
}
,'Associated ID': BrowseGallery2.Selected.ID})));

Really feel like I'm going round in circles here so any help would be greatly appreciated!! Many thanks!

[sperry1625]

You don't need to do a Patch to update your collection. You can use an UpdateIf() to do that.

UpdateIf(
    ActionCollection,
    ID = ThisItem.Id,   // Pull ID from Current Gallery Item
    {
        Title: EditAction.Title,
        ActionStatus: ThisItem.ActionStatus.Selected.Value,
        TargetDate: ThisItem.TargetDate.SelectedDate,
        ActionOwner:
        {
            '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            Department: "",
            Claims: "i:0#.f|membership|"& Lower(ThisItem.RTPOwner.Email),
            Email: Lower( ThisItem.RTPOwner.Email ),
            DisplayName: ThisItem.ActionOwner,
            JobTitle: "",
            Picture: ""
        }
    }
);

You would call this from the row Save to update an item in your collection. For creating an item, is there a new button? Upon clicking New you can use Collect(...) to add a new entry to your collection.  Set ID to -1, add another property to your collection called "InternalId" that you can use for lookups. Set it to Max( InternalId ) + 1.  When loading your list into the collection set Internal Id equal to Id.

You can then trigger a "New" record based on ID being "-1". Otherwise, they are just updates.

A SharePoint People column needs the following structure to save the data to the column.  Also, the user must be a valid O365 user with access to SharePoint. The structure is as follows, which you appear too already be aware of.

{
    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
    Department:"IT",
    Claims: "i:0#.f|membership|[User UPN]",
    Email: "Email address",
    DisplayName: "John Doe",
    JobTitle: "Developer",
    Picture: Blank()
}

Some Questions

1. What is RTPOwner?
2.  

For your Reference

I will mention one thing for future reference.  The Claim ID is not based on email it is based on the users UPN (User Principal Name). Now, very often they are the same, but not always. If you are using the email address, it is possible that the users email, and UPN are not the same.  This is a stretch, but something to look at.

I hope this help.

Regards,

-S

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[dw909]

Thanks for the super fast response @sperry1625  - in answer to your question 1, the SharePoint list has effectively two names for the same column. I renamed the display name of the column but the original name still exists behind the scenes (in the URL for example). I'll try out your UpdateIf and see how I get on! Really appreciate your detailed reply!

[dw909]

Hi again @sperry1625  - not doing very well here at all with the syntax you provided for the OnSelect of the Save icon when editing a line. 

 ID = ThisItem.Id,   // Pull ID from Current Gallery Item

It doesn't like ".Id" - it doesn't complain if I type it as ID

  Title: EditAction.Title,

It doesn't like this syntax either. The "EditAction" comes from  the overall Save icon of all the edited lines which patches to SharePoint list (the final step) where I have a ForAll(ActionCollection As EditAction, Patch(  - as this is a step before the "EditAction" is not yet recognised, so I assume here I need to use Title: ActionCollection.Title instead?

 ActionStatus: ThisItem.ActionStatus.Selected.Value,

It's not happy about this either - throws an error Name isn't valid. 'Selected' isn't recognized.

If I modify this to ActionStatus: ThisItem.ActionStatus.Value I get a new error Incompatible type. The 'Action Status' column in the data source you're updating expects a 'Record' type and you're using a 'Text' type.

TargetDate: ThisItem.TargetDate.SelectedDate,

Problems here too - error is Invalid use of '.'  If I change to TargetDate: ThisItem.TargetDate, it at least seems happier

Other errors also coming up:

1. Invalid argument type. Expecting a Record value, but of a different schema.
2. Missing column. Your formula is missing a column 'DisplayName' with a type of 'Invalid'.
3. The function 'UpdateIf' has some invalid arguments.

Not sure if it helps you to know the following:

• The Items property of the Gallery is set as ActionCollection
• There is no concept of a button for a new row - the repeating table always has a blank row like this:

When you enter something in this blank row and hit the little save icon, a new blank row is automatically created. (If you follow through Shane's videos [he did 3 in this series] the concept basically tracks whether or not the edit button is pressed - got this extra syntax on the end of the OnSelect:

If(EditPressed, false, Collect(ActionCollection, {Title: "", ActionOwner: "", ActionOwnerEmail: "", ShowSaveButtons:true})); UpdateContext({EditPressed: false}); Set(NeedsSaved, false)

(note I also append a ,ShowSaveButtons:false} as part of the above syntax for editing)

Not sure if this is making any sense at all to you, but I'd appreciate your further thoughts! Thanks!