Patch SharePoint list

[AshishJaiswal]

Hello

I'm looking for a way to patch existing item in sharepoint.

But I'm not sure how to relate to the existing item or perform some kind of lookup to find existing item and update it.

[Pstork1]

The formula will look something like this

patch(sharePointList, LookUp(sharePointList, ID = someId),{Title: "value", field: "value"})

Do the lookup using some value that is unique for the item you want.  It doesn't have to be ID. and then build a record with the values you want to update and their column names.

[AshishJaiswal]

@Pstork1 

Thank you for replying to my post sir.

Actually My patch function is patching/creating new itemd in SP - when the user clicks the submit button these new items are created (details like - his team, his role, his location)

Now these entries in SP doesn't have anything unique in it that would indicates  - that this entries belong to a user.

Could you guide me - what shall I add in sharepoint or in my function that would link these entries unique to a specific user

Next time - user would update the same entry

[Pstork1]

To use patch to create a new entry in a list the format is slightly different.  Instead of the Lookup() you use an function called default()

patch(sharePointList, defaults(sharePointList),{Title: "value", field: "value"})

As I said, to update an existing record you need to have something that uniquely identifies which record to update.  This is often done using a Gallery that shows all the records.  Then the user selects one to edit.  In that case the lookup would be 

Lookup(SharePointList, ID = Gallery.selected.ID)

If you aren't using a gallery and there is only one record per user then I would add a column to SharePoint that records their email address.  Then the Lookup() would be

Lookup(SharePointList, emailcolumn = currentUserEmail)

You would also need to save the email of the current user to currentUserEmail before the patch like this.

set(currentUserEmail, User().Email)

[AshishJaiswal]

@Pstork1 

Yes I'm using default itself to create a new item

How do a link the newly created entry to the user - So next time when he clicks on submit instead of creating a new item it should update the existing entry which was just created.


Here is my patch function 

Spoiler

Patch(
'Response Collected from Certification Tracker',
Defaults('Response Collected from Certification Tracker'),
{
Title:Concat(InputCustomer.SelectedItems,Customer & ","),
Role:InputRole.Selected,
Team:InputTeam.Text,
Location:InputLocation.Selected,
'Primary Skill':InputPrimaryskill.Text,
'Secondary Skill':InputSecondarySkill.Text,
Cert1ValidTill:Cert1Valid.SelectedDate,
Cert2ValidTill:Cert2Valid.SelectedDate,
Cert3ValidTill:Cert3Valid.SelectedDate,
Cert4ValidTill:Cert4Valid.SelectedDate,
Cert5ValidTill:Cert5Valid.SelectedDate,
Cert6ValidTill:Cert6Valid.SelectedDate,
Cert7ValidTill:Cert7Valid.SelectedDate,
Cert8ValidTill:Cert8Valid.SelectedDate,
Cert9ValidTill:Cert9Valid.SelectedDate,
Cert10ValidTill:Cert10Valid.SelectedDate,
StatusCert1:{Value:LookUp('Master List of Certificates',Title= InputCert1.Selected.Title,Status.Value)},
StatusCert2:{Value:LookUp('Master List of Certificates',Title= InputCert2.Selected.Title,Status.Value)},
StatusCert3:{Value:LookUp('Master List of Certificates',Title= InputCert3.Selected.Title,Status.Value)},
StatusCert4:{Value:LookUp('Master List of Certificates',Title= InputCert4.Selected.Title,Status.Value)},
StatusCert5:{Value:LookUp('Master List of Certificates',Title= InputCert5.Selected.Title,Status.Value)},
StatusCert6:{Value:LookUp('Master List of Certificates',Title= InputCert6.Selected.Title,Status.Value)},
StatusCert7:{Value:LookUp('Master List of Certificates',Title= InputCert7.Selected.Title,Status.Value)},
StatusCert8:{Value:LookUp('Master List of Certificates',Title= InputCert8.Selected.Title,Status.Value)},
StatusCert9:{Value:LookUp('Master List of Certificates',Title= InputCert9.Selected.Title,Status.Value)},
StatusCert10:{Value:LookUp('Master List of Certificates',Title= InputCert10.Selected.Title,Status.Value)},
'Certificate No1':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert1.Text),
Value:InputCert1.Selected.Title},
'Certificate No2':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert2.Text),
Value:InputCert2.Selected.Title},
'Certificate No3':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert3.Text),
Value:InputCert3.Selected.Title},
'Certificate No4':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert4.Text),
Value:InputCert4.Selected.Title},
'Certificate No5':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert5.Text),
Value:InputCert5.Selected.Title},
'Certificate No6':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert6.Text),
Value:InputCert6.Selected.Title},
'Certificate No7':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert7.Text),
Value:InputCert7.Selected.Title},
'Certificate No8':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert8.Text),
Value:InputCert8.Selected.Title},
'Certificate No9':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert9.Text),
Value:InputCert9.Selected.Title},
'Certificate No10':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert10.Text),
Value:InputCert10.Selected.Title}
}
)

;Navigate(ThankyouScreen);
Reset(InputCert1);
Reset(InputCert2);
Reset(InputCert3);
Reset(InputCert4);
Reset(InputCert5);
Reset(InputCert6);
Reset(InputCert7);
Reset(InputCert8);
Reset(InputCert9);
Reset(InputCert10);
Reset(InputCustomer);
Reset(InputTeam);
Reset(InputRole);
Reset(InputLocation);
Reset(InputPrimaryskill);
Reset(InputSecondarySkill);
Reset(Cert1Valid);
Reset(Cert2Valid);
Reset(Cert3Valid);
Reset(Cert4Valid);
Reset(Cert5Valid);
Reset(Cert6Valid);
Reset(Cert7Valid);
Reset(Cert8Valid);
Reset(Cert9Valid);
Reset(Cert10Valid)

[Pstork1 · Accepted solution]

As mentioned above

1) Add a column and save the user's email to that column

2) You can't use the same patch for new and update.  You have to do a Lookup and use the new version if its null and the update version if its not.

3) If you add the email this would be the update patch()

Set(currentEmail, User().Email);
Patch(
'Response Collected from Certification Tracker',
Lookup('Response Collected from Certification Tracker', 'User Email' = currentEmail),
{
Title:Concat(InputCustomer.SelectedItems,Customer & ","),
Role:InputRole.Selected,
Team:InputTeam.Text,
Location:InputLocation.Selected,
'Primary Skill':InputPrimaryskill.Text,
'Secondary Skill':InputSecondarySkill.Text,
Cert1ValidTill:Cert1Valid.SelectedDate,
Cert2ValidTill:Cert2Valid.SelectedDate,
Cert3ValidTill:Cert3Valid.SelectedDate,
Cert4ValidTill:Cert4Valid.SelectedDate,
Cert5ValidTill:Cert5Valid.SelectedDate,
Cert6ValidTill:Cert6Valid.SelectedDate,
Cert7ValidTill:Cert7Valid.SelectedDate,
Cert8ValidTill:Cert8Valid.SelectedDate,
Cert9ValidTill:Cert9Valid.SelectedDate,
Cert10ValidTill:Cert10Valid.SelectedDate,
StatusCert1:{Value:LookUp('Master List of Certificates',Title= InputCert1.Selected.Title,Status.Value)},
StatusCert2:{Value:LookUp('Master List of Certificates',Title= InputCert2.Selected.Title,Status.Value)},
StatusCert3:{Value:LookUp('Master List of Certificates',Title= InputCert3.Selected.Title,Status.Value)},
StatusCert4:{Value:LookUp('Master List of Certificates',Title= InputCert4.Selected.Title,Status.Value)},
StatusCert5:{Value:LookUp('Master List of Certificates',Title= InputCert5.Selected.Title,Status.Value)},
StatusCert6:{Value:LookUp('Master List of Certificates',Title= InputCert6.Selected.Title,Status.Value)},
StatusCert7:{Value:LookUp('Master List of Certificates',Title= InputCert7.Selected.Title,Status.Value)},
StatusCert8:{Value:LookUp('Master List of Certificates',Title= InputCert8.Selected.Title,Status.Value)},
StatusCert9:{Value:LookUp('Master List of Certificates',Title= InputCert9.Selected.Title,Status.Value)},
StatusCert10:{Value:LookUp('Master List of Certificates',Title= InputCert10.Selected.Title,Status.Value)},
'Certificate No1':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert1.Text),
Value:InputCert1.Selected.Title},
'Certificate No2':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert2.Text),
Value:InputCert2.Selected.Title},
'Certificate No3':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert3.Text),
Value:InputCert3.Selected.Title},
'Certificate No4':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert4.Text),
Value:InputCert4.Selected.Title},
'Certificate No5':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert5.Text),
Value:InputCert5.Selected.Title},
'Certificate No6':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert6.Text),
Value:InputCert6.Selected.Title},
'Certificate No7':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert7.Text),
Value:InputCert7.Selected.Title},
'Certificate No8':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert8.Text),
Value:InputCert8.Selected.Title},
'Certificate No9':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert9.Text),
Value:InputCert9.Selected.Title},
'Certificate No10':{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id:Value(IDCert10.Text),
Value:InputCert10.Selected.Title}
}
)

[AshishJaiswal]

@Pstork1 

Thank you sir 🙂 

But unfortunately - I cannot capture user's email address - as per GDPR

Hence I need to invent/create something that would link to each user

Create sort of ID/Number which would associate itself to the user profile - then patch function would work something like > if user is adding a new entry for the first time associate an ID to his profile and add new items in sharepoint list, if found existing then update existing item

Hope - I'm making some sense here 😋

[Pstork1]

Yes you are making sense.  But I don't think there is any way to get around the issue.  I don't think you can extend the user's profile to record an ID that you create.  However, if user's are the only ones who ever update the record they create then you can just use the ModifiedBy field that is built into the system.  That will automatically save a user object to the record.  The lookup would then be this.

Lookup('Response Collected from Certification Tracker', 'ModifiedBy'.Email = currentEmail),

[AshishJaiswal]

@Pstork1 

Wow!!! I think I can use that 

So how would my patch function work?
Because If modifiedby is empty or blank 
then my patch function should add new entries in SP list.

Patch function should check, if modifiedby is present - yes then update existing item, if not then add new entry 

[Pstork1 · Accepted solution]

Both patches go inside an IF()

If(isEmpty(Lookup('Response Collected from Certification Tracker', 'ModifiedBy'.Email = currentEmail)),Patch(###new version###),Patch(###updateversion###))