cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Peace-In
Regular Visitor

Patch if all rows in a SharePoint list with matching ID's meet a condition

Hi there,

I'm new to Power Apps and I am trying to get a 'patch if' statement to work without success as I don't think I'm going about it right...

I have two SharePoint lists, a register and a master which use form submission ID's as a reference with a many to one relationship (register=many, master=one).  Once an item in the register is finalised, I want power apps to check to see if all of the rows with a matching ID are finalised, if so, patch to the relevant record in the master marking it as finalised too. 

I have two galleries in my app, MasterGallery and RegisterGallery, when an item in the MasterGallery is selected, it displays the relevant records in the RegisterGallery. Users can update the item, log checks and finalise via the RegisterGallery.

I'm using an icon (as opposed to a button) to finalise the RegisterGallery item; On Select:

Patch('Conditions Register', ThisItem, {Finalised1: true, FinalisedDate: Today()});  //this bit works fine
ForAll('Conditions Register', With({Item: LookUp ('Conditions Register', MasterIDR = ThisItem.MasterIDR)}, If(!IsBlank('Finalised1'), Patch('Conditions Master', MasterGallery.Selected, {Finalised: true}))));  //this bit patches to the master if it finds just one record in the register that has been finalised whereas I need to check that all records with matching MasterIDR meet the condition before patching to the master

I'd appreciate all the help I can get 🙂 

11 REPLIES 11
WarrenBelz
Super User
Super User

Hi @Peace-In ,

I am guessing a bit here visualising what you want, but have a look at this structure and see if this is what you need

Patch(
   'Conditions Master',
   ForAll(
      Filter(
         'Conditions Register', 
         MasterIDR = ThisItem.MasterIDR &&
         !IsBlank('Finalised1')
      )
   ) As aPatch,
   {
      ID:aPatch.ID
      Finalised: true
   }
)

 

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.

Visit my blog Practical Power Apps

Hi WarrenBelz,

Thanks for your quick reply; I've tried your suggestion this morning and it doesn't appear to like the 'As aPatch'.

Hi @Peace-In ,

The issue as not the As statement, that is valid syntax, but rather you do not have the ID column in 'Conditions Master'. You need a unique identifier to do this. Also can you please post your code it Text as well.

Thanks & sorry @WarrenBelz

I updated the reference to 'ID' to 'MasterID' which is the corresponding column in the 'Conditions Master' and added a comma but it now creates new items in the Master list as opposed to updating the one related record.  

 

 

Patch('Conditions Master', ForAll(Filter('Conditions Register', MasterIDR=ThisItem.MasterIDR && !IsBlank(Finalised1)) As aPatch, {MasterID:aPatch.ID, Finalised: true}));

 

 

 

Hi @Peace-In ,

That is because you need the ID (the unique identifier) in the syntax I gave you. Try the long way

ForAll(
   Filter(
      'Conditions Register', 
      MasterIDR = ThisItem.MasterIDR &&
      !IsBlank('Finalised1')
   ) As aPatch,
   Patch(
      'Conditions Master',
      LookUp( 
         'Conditions Master',
         MasterID = aPatch.MasterID
      ),
      {Finalised: true}
   }
)

 

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.

Visit my blog Practical Power Apps

Nearly @WarrenBelz!

The syntax finalised the Master item however, it did not check to see if all items in the Register were finalised first. It's like we need a 'where all' instead of a 'for all'.

@Peace-In ,

I am a bit lost at your question now - if you simply want to patch all the Finalised1 items to the matching MasterID in 'Conditions Master'

ForAll(
   Filter(
      'Conditions Register', 
      !IsBlank('Finalised1')
   ) As aPatch,
   Patch(
      'Conditions Master',
      LookUp( 
         'Conditions Master',
         MasterID = aPatch.MasterID
      ),
      {Finalised: true}
   }
)

 

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.

Visit my blog Practical Power Apps

@WarrenBelz 

Apologies on the miscommunication. I need it to - Where all Register items with the selected ID are finalised, finalise the corresponding ID in the Master.  Where some or no Register items with the selected ID are finalised, do not finalise the corresponding ID in the Master.

 

This syntax finalises the Master regardless of the whether the Register items with corresponding ID's are finalised.

ForAll(
   Filter(
      'Conditions Register', 
      MasterIDR = ThisItem.MasterIDR &&
      !IsBlank('Finalised1')
   ) As aPatch,
   Patch(
      'Conditions Master',
      LookUp( 
         'Conditions Master',
         MasterID = aPatch.MasterID
      ),
      {Finalised: true}
   }
)

 This syntax finalises everything in the Master regardless of ID's or corresponding register items finalisation status.

ForAll(
   Filter(
      'Conditions Register', 
      !IsBlank('Finalised1')
   ) As aPatch,
   Patch(
      'Conditions Master',
      LookUp( 
         'Conditions Master',
         MasterID = aPatch.MasterID
      ),
      {Finalised: true}
   }
)

 

Hi @Peace-In ,

 I suspect this is more complex than you realise - the structure below is probably close to what you need.

With(
   {
      wFinal:
      Filter(
         GroupBy(
            'Conditions Register',
            "MasterID",
            "Data"
         ),
         CountRows(Data) =
         CountRows(
            Filter(
               Data,
               !IsBlank(Finalised1)
            )
         )
      )
   },
   ForAll(
      wFinal As aPatch,
      UpdateIf(
         'Conditions Master',
         MasterIDR = aPatch.MasterIDR,
         {Finalised: true}
      )
   )
)

 

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.

Visit my blog Practical Power Apps

 

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (1,933)