Answered

How to use multiple filters to update a record in sharepoint list using Patch function

(0) Share

Report

Posted on by kiritisaranam Microsoft Employee

The below mentioned rows are my rows in SharePoint list.

Project Id	Closing Month	Comments
1	January	Hi
1	February	Hello

I have a power app which patches the data to SharePoint. While patching the data, I need to check whether there is an existing entry for the same project id in that closing month and I need to update the record if it already exists and create a new record if that do not exist in that particular month. In the above example, if some one submits an entry for project id 1 and if still the closing month is February it needs to update the entry. This is the query I am using

If(IsEmpty(Filter('Source', Title = ClosingMonth && 'Project Id' = ProjectId)) ,Patch('Source',Defaults('Source'),{'Title':ClosingMonth,'Project Id':ProjectId,'Comments':Comments}, Patch('Source',First(Filter('Source', 'Project Id' = ProjectId && Title = ClosingMonth)),{'Comments':Comments}));

But this condition is not working. Appreciate any help.

Categories:

Building Power Apps

All responses (56)

Answers (2)

Sort by

Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@kiritisaranam

Consider the following change to your formula:

Patch('Source', Coalesce( LookUp('Source', 'Project Id' = ProjectId && Title = ClosingMonth), Defaults('Source') ), {'Title':ClosingMonth, 'Project Id':ProjectId, 'Comments':Comments } )

The Coalesce statement returns the first parameter that is not blank. If the lookup returns nothing (no record), then it will use Defaults instead (new record). If the Lookup returns a record, then it will use that as the source record to patch.

Just an FYI - avoid using First(Filter(... this is a performance hog that can be replaced completely with a LookUp as shown in the formula above.

I hope this is helpful for you.
kiritisaranam Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes I have replaced my function with your formula. But it is not updating the entry even it exists in the SP list. Instead it is adding a new item every time I submit.
kiritisaranam Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Mike8 You can find my query in this thread and you can suggest me a formula.
Mike8 1,330 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @kiritisaranam,

Can you please try if it works when you use fixed text instead of ClosingMonth ,ProjectId and Comments?
Something like this:
If(IsEmpty(Filter('Source', Title = "January"&& 'Project Id' = 1)) ,
Patch('Source',Defaults('Source'),{'Title':"January",'Project Id':1,'Comments':"Hello world"},
Patch('Source',First(Filter('Source', 'Project Id' = 1&& Title = "January")),{'Comments':"Updated Hello World"}));

The data that I added are from your initial post. Please change them a bit if you need to test more thoroughly.
Also the Project Id is an integer, right?
Please let me know if it works. I am trying to understand what is going wrong.

I created a simple application in Powerapps using collections instead of a Sharepoint list and it works as intended.
How many records does the list have?
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@kiritisaranam

If that is not working, then your LookUp is not returning a record based on your criteria.

Check the criteria you have there as this works perfectly.

What is ClosingMonth in the formula? I would suspect that is where the problem is.
kiritisaranam Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Mike8 , the formula is working fine if I use static values in Filter condition. Project Id is number type. The SharePoint List which I am using now is currently have 400rows. But as time goes the list size increases and might cross 2000 rows as well.

Could you please help me in making the formula work even if I use variables inside the Filter condition as well.

Background: The power app gets the project id from a PowerBI report where I am setting the Project Id using set function in Gallery's On select. The Project Id in the BI report is a whole number and the column Project Id in the SharePoint list is of type number.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Mike8

Seems like you are on a separate conversation? Are you working with @Mike8 directly on this from another conversation? If so, I will step out.
kiritisaranam Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @RandyHayes , the closing month is the Title column in my SP list. So my filter criteria is trying to check the Title Column. Is there any thing that needs to be additionally added to check the Title Column?
kiritisaranam Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @RandyHayes, this is my lookup formula where Title is the Title column in my SP list

LookUp('Source', 'Project Id' = ProjectId && Title = ClosingMonth)
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@kiritisaranam

So this is why my formula produces new records...because the criteria in the lookup is NOT getting the row from the list.

We need to review your criteria.

You have two variables in your criteria - ProjectId and ClosingMonth. If 'Project Id' and Title are columns in your list, then the ProjectId and ClosingMonth cannot be columns as well.

Where or what are you basing those two blue items on? Where are they defined?