cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JR-BejeweledOne
Super User
Super User

Updating Records with patch

Is there a better way than a lengthy If statement to bulk update records (patch) when some fields may not change?

 

Multi-select gallery (using Checkboxes)

4 Fields - 12 possible combinations of updates

 

UpdateIf is not delegable (https://youtu.be/zINTAUJqQhg), so I will use patch, but am trying to avoid having to write a 12 condition If statement since I only will be patching the fields that have changed.

 

 

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
10 REPLIES 10
RandyHayes
Super User
Super User

@JR-BejeweledOne 

Yes, you can make it very simple.  You can also delegate UpdateIf depending on your criteria, but I don't believe it is applicable in this case.

Can you provide a little more context on your scenario, or some screenshots or formulas?  That would be helpful in guidance.

 

_____________________________________________________________________________________
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

Really want to show your appreciation? Buy Me A Cup Of Coffee!
JR-BejeweledOne
Super User
Super User

Gallery Items:  colEditSchedule

 

Columns : The first 4 correspond with the fields that they can modify, the others are updated based on the choices in these 4 controls.  (Week, Weekend and Team are not updated)

  1. PrimaryContact
  2. BackupContact
  3. EscalationManager
  4. ScheduleNotes
  5. PrimaryContact 2
  6. PCName
  7. PC2Name
  8. BCName
  9. EMName

 

Any combination (but at least 1) of Primary Contact, BackupContact, Escalation Manager or Schedule Notes might be updated (12 possible combinations).  

 

Primary Contact is a multi-select ComboBox that feeds PrimaryContact, PrimaryContact2, PCName and PC2Name

 

 

primarycontact: First(ComboBoxPrimaryContact.SelectedItems).Email,
primarycontact2: Last(ComboBoxPrimaryContact.SelectedItems).Email,
pcname: First(ComboBoxPrimaryContact.SelectedItems).Name,
pcname2: Last(ComboBoxPrimaryContact.SelectedItems).Name 

 

 

 

schedule.png

 

 

the patch will be something like this:

 

 

 

ForAll(
   RenameColumns(
      Filter(
            colEditSchedule,
            IsSelected,
          ),
        "ID",
        "tID,
 ),

   Patch(
       OnCallSchedules,
         LookUp(
             OnCallSchedules,
             ID = tID
          ),
       {PrimaryContact: First(ComboBoxPrimaryContact.SelectedItems).Name

 

 

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
RandyHayes
Super User
Super User

@JR-BejeweledOne 

Your ForAll is backward - ForAll is a function that returns a table, it is not a For/Loop like in development.  Although it can be used that way, it is not its intended purpose and will cause performance issues in many cases like that.

Your formula should be the following:

Patch(
    OnCallSchedules,
    ForAll(
       Filter(yourGallery.AllItems, yourCheckBox.Value),
       {ID: ID,
        PrimaryContact: First(ComboBoxPrimaryContact.SelectedItems).Name 
        Primarycontact2: Last(ComboBoxPrimaryContact.SelectedItems).Email,
        pcname: First(ComboBoxPrimaryContact.SelectedItems).Name,
        pcname2: Last(ComboBoxPrimaryContact.SelectedItems).Name
       }
    )
)   

Now, the above assumes that you are collecting the ID as part of your collect function formula that is in the Items property of the Gallery.

Also, you can get rid of the IsSelected column in your collection...you don't need it and you can then get rid of all the OnCheck and OnUncheck formulas you've put on the checkboxes...they are not needed.

 

 

 

_____________________________________________________________________________________
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

Really want to show your appreciation? Buy Me A Cup Of Coffee!

So how do I avoid overwriting existing values without writing a 12 condition if statement to not patch the items that have not been updated?



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
RandyHayes
Super User
Super User

@JR-BejeweledOne 

See, that's where I get lost...what do you mean by this??

If the checkbox is checked...do you not want to update those records?

_____________________________________________________________________________________
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

Really want to show your appreciation? Buy Me A Cup Of Coffee!

It's a matter of what is being updated.

 

If any of these controls are not blank:

Primary Contact

BackupContact

EscalationManager

ScheduleNotes

 

Then update the necessary columns in the collection for all the selected records.  The catch is that they might only need to update the Primary contact and not the others, or they might want to update all 3 individuals but not the schedule manager.

 

So I would need an If statement something like this to account for all the scenarios as I don't want to overwrite existing data where it's not being updated.

 

If(
!Isblank( comboboxprimarycontact.selecteditems)&& IsBlank(dropdownbackupcontact)&& IsBlank(dropdownescalationmanager) && IsBlank(HTMLTextScheduleNotes), Patch .....,

IsBlank(comboboxprimarycontact.selecteditems) && !IsBlank(dropdownbackupcontact)&& !IsBlank(dropdownescalationmanager) && IsBlank(HTMLTextScheduleNotes), Patch.....,

!IsBlank(comboboxprimarycontact.selecteditems) && !IsBlank(dropdownbackupcontact) && IsBlank(dropdownescalationmanager) && IsBlank(HTMLTextScheduleNotes), Patch.....

)

 as 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
RandyHayes
Super User
Super User

@JR-BejeweledOne 

Interesting scenario...but let me see if I am understanding you.  My take is, you have a gallery of items that you want to do a bulk update to based on ONLY what is entered.  So, if you ONLY enter data into the richtext control, then you would only want the items checked to have the html notes updated.  And if you then also changed a contact, you would only want the checked items to have the html notes and the contact columns changed.

 

Am I on the right planet?

_____________________________________________________________________________________
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

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Exactly!    It's a schedule and there might be times when someone has left the department/team or company and the schedule needs to be updated with a new person.   This usually requires some manipulation of the existing schedule thus the need to be able to bulk update with any, all or some of the 4 controls.



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
RandyHayes
Super User
Super User

@JR-BejeweledOne 

So just use the base values coalesced with the new values:

Patch(
    OnCallSchedules,
    ForAll(
       Filter(yourGallery.AllItems, yourCheckBox.Value),
       {ID: ID,
        PrimaryContact: Coalesce(First(ComboBoxPrimaryContact.SelectedItems).Name, PrimaryContact),
        Primarycontact2: Coalesce(Last(ComboBoxPrimaryContact.SelectedItems).Email, PrimaryContact2),
        pcname: Coalesce(First(ComboBoxPrimaryContact.SelectedItems).Name, pcname),
        pcname2: Coalesce(Last(ComboBoxPrimaryContact.SelectedItems).Name, pcname2),
       }
    )
)   
_____________________________________________________________________________________
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

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,814)