cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MayankGupta
Regular Visitor

Patch function to update existing record based common column in SP lists

Hi,

 

I want to patch my SharePoint lists based on the common column "RelationShip_ID".  its works fine for creating new records into the lists but not getting exact condition to update the existing items.

 

below is the code, can anyone please help.


If(CountRows(Gallery2.AllItems)=0, Patch(CheckIn_Priorities, Defaults(CheckIn_Priorities), {
Title:priorityName,
Relationship_ID:RelationShipID,
PriorityStatus:priorityStatus,
PriorityReason:priorityReason,
UserEmail:User().Email
});Patch(UserCheckInDetails, Defaults(UserCheckInDetails), {
Title:User().Email,
Relationship_ID:RelationShipID,
UserFullName:User().FullName,
Status:"Pending Manager Review",
ManagerEmail: MyManager1.UserPrincipalName,
ManagerName: MyManager1.GivenName & " " & MyManager1.Surname}),

Patch(UserCheckInDetails, First(Filter(UserCheckInDetails,Relationship_ID=LookUp(CheckIn_Priorities, Relationship_ID)){
UserFullName:User().FullName,
Relationship_ID:RelationShipID,
Status:"Pending Manager Review",
ManagerEmail: MyManager1.UserPrincipalName,
ManagerName: MyManager1.GivenName & " " & MyManager1.Surname});
Patch(CheckIn_Priorities, First(Filter(CheckIn_Priorities,Relationship_ID=LookUp(UserCheckInDetails, Relationship_ID)), {
Title:priorityName,
Relationship_ID:RelationShipID,
PriorityStatus:priorityStatus,
PriorityReason:priorityReason,
UserEmail:User().Email
});Patch(UserCheckInDetails, Defaults(UserCheckInDetails)
);

9 REPLIES 9
RandyHayes
Super User III
Super User III

@MayankGupta 

I am not sure why you have a blank Patch statement at the end of your formula to create a blank record.  I assume that was some oversight, so I have ignored that in the following.

 

Also not completely sure where and what Gallery2 plays in the overall formula.  But I incorporated it in anyway.

 

Please consider changing your Formula to the following:

Patch(CheckIn_Priorities, 
    If(CountRows(Gallery2.AllItems)=0, Defaults(CheckIn_Priorities), 
       Coalesce(LookUp(CheckIn_Priorities, RelationShip_ID=RelationShipID), Defaults(CheckIn_Priorities)
       )
    ),
    {
     Title: priorityName,
     Relationship_ID: RelationShipID,
     PriorityStatus: priorityStatus,
     PriorityReason: priorityReason,
     UserEmail: User().Email
    }
);
    
Patch(UserCheckInDetails, 
    If(CountRows(Gallery2.AllItems)=0, Defaults(UserCheckInDetails), 
       Coalesce(LookUp(UserCheckInDetails, RelationShip_ID=RelationShipID), Defaults(UserCheckInDetails)
       )
    ),
    {
      Title: User().Email,
      Relationship_ID: RelationShipID,
      UserFullName: User().FullName,
      Status: "Pending Manager Review",
      ManagerEmail: MyManager1.UserPrincipalName,
      ManagerName: MyManager1.GivenName & " " & MyManager1.Surname
    }
)

 

I hope this is helpful for you.

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

Thanks Randy,

 

The above code works but i might missing something with relationship_ID as it creates multiple entries as shown in below screenshot. but i want it should lookup the first relationsip_ID and create new records with the same ID.
Capture.PNG
Can you please help.

RandyHayes
Super User III
Super User III

@MayankGupta 

Can you describe more about what the relationship ID is supposed to be?  I was not clear on it from your original formula, but I wrote my formula based on what was there.

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

@RandyHayes 

I want to create this realtionship_ID only when the first item is created in the Gallery2, and whenever another item created in that gallery it should use the same relationship_ID for all other items.
So I assume it should lookup the relationship_ID of my existing items in Gallery2, and create another item with same relationship_ID.

RandyHayes
Super User III
Super User III

@MayankGupta 

Yes, this would be a classic parent child relationship if I am understanding you properly.
So, looking at the picture you have of your list, you have new RelationshipID's on all the new created records.

In tracking that to the formula, the RelationshipID is coming from RelationShipID.

So, what are priorityName, RelationShipID, priorityStatus and priorityReason in the formula?  Where are they coming from?

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

Hi @RandyHayes ,

Apologies for the delay here, let me make it more clear.

I have two tables and want to make parent child relationship among these.

Parent Table: UserCheckInDetails

Columns:
Title: User().Email,
Relationship_ID: RelationShipID,
UserFullName: User().FullName,
Status: "Pending Manager Review",
ManagerEmail: MyManager1.UserPrincipalName,
ManagerName: MyManager1.GivenName & " " & MyManager1.Surname

Child Table: CheckIn_Priorities

Columns:
Title: priorityName,
Relationship_ID: RelationShipID,
PriorityStatus: priorityStatus,
PriorityReason: priorityReason,
UserEmail: User().Email

So when i will submit it should create a record in both tables like all user details should be stored in "UserCheckInDetails" and Priority details should store in "CheckIn_Priorities" table. and user can insert multiple priorities for the same record i.e. (1 to Many relationship) as shown in below image.

In Addition, for next time if I'm retrieving the record, it should populate only associated priorities using relationship_ID for the particular UserCheckIn record.CheckIn_tables.PNG

Hi ,

Can anyone please help on above request.

RandyHayes
Super User III
Super User III

@MayankGupta 

Sorry for the delay on this.  I lost a whole bunch of open posts that I needed to follow up on the other day, so I am glad you posted again.

 

So in reviewing everything again, I believe your solution is much simpler than you have.

 

But I have a question:

Where is RelationShipID defined?  I had originally thought this was in your Gallery2, but given what you are describing, you want a master record made with a RelationshipID (in UserCheckInDetails) and then the other  records in CheckIn_Priorities to be made to reference the master.

 

Also, what column is your primary key for the list?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
RandyHayes
Super User III
Super User III

@MayankGupta 

Were you able to work through your issue or do you still need help?

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,857)