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

[kiritisaranam]

 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.

[RandyHayes · Accepted solution]

@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]

@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]

@Mike8 You can find my query in this thread and you can suggest me a formula.

[Mike8]

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]

@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]

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]

@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]

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]

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

LookUp('Source', 'Project Id' = ProjectId && Title = ClosingMonth)