cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mts1
Helper III
Helper III

Patch Gallery Items only with condition

Hi all,

 

Hope you are doing well.

 

I'm blocked since hours and maybe can someone help me here!

 

I have a gallery where user will need to fill 4 or 5 input : Calls Handled, Emails Handled, SL1, SL2, SL3, SL4 (all are numbers input)

 

I want to patch only gallery items with below condition : 

- IF both Calls Handled and Emails Handled are blank then not patch the item.

- IF Calls Handled OR Emails Handled are not blank AND SL1+SL2+SL3+SL4 doesn't equal to Calls Handled + Emails Handled then no patch the item.

 

As per the below photo, only the first item will be patched.

Mts1_0-1659615649731.png

 

 

I don't even know if this requirements is possible or not.

 

Thanks in advance for your help,

 

Regards

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hey @Mts1, you are getting close, but think about what we've already done - used ForAll() to iterate over multiple gallery items. So, if you have multiple nested galleries, then perhaps you need another ForAll()? 😉

 

ForAll(
    galParent.AllItems As ParentGallery,
	ForAll(
		Filter(
			AddColumns(
				ChildGallery.AllItems,
				//===Add a column for sum of Calls and Emails
				"CallsAndEmailsSum", Sum(Value(Prod_inptCallsHandled.Text), Value(Prod_inptEmailsHandled.Text)),
				//===Add a column for sum of all SL inputs
				"SLsSum", Sum(Value(Prod_inptSL1.Text), Value(Prod_inptSL2.Text), Value(Prod_inptSL3.Text), Value(Prod_inptSL4.Text))
			),
			//===Perform check: Calls + Emails is greater than zero
			//===Perform check: Calls + Emails = Sum of SL inputs
			CallsAndEmailsSum > 0 && CallsAndEmailsSum = SLsSum
		) As ChildGalleryRecord,
		Patch(PAT_Tracker_AppResults, Defaults(PAT_Tracker_AppResults), 
			{'Task Date': Text(Prod_DatePicker.SelectedDate, "dd/mm/yyyy")},
			{'Agent Name': User().FullName},
			{'Agent EmailAddress': User().Email},
			{'Task Type': Text("Handled")},
			{Country: Prod_DropDown_Country.Selected.Result},
			{Exception: Prod_lblException.Text},
			{Carrier: Prod_lblCarrier.Text},
			{Offered: Value(Prod_lblOffered.Text)},
			{'Calls Handled': If(IsBlank(Prod_inptCallsHandled.Text),0,(Prod_inptCallsHandled.Text))},
			{'Emails Handled': If(IsBlank(Prod_inptEmailsHandled.Text),0,(Prod_inptEmailsHandled.Text))},
			{'Handled SL1': If(IsBlank(Prod_inptSL1.Text),0,(Prod_inptSL1.Text))},
			{'Handled SL2': If(IsBlank(Prod_inptSL2.Text),0,(Prod_inptSL2.Text))},
			{'Handled SL3': If(IsBlank(Prod_inptSL3.Text),0,(Prod_inptSL3.Text))},
			{'Handled SL4': If(IsBlank(Prod_inptSL4.Text),0,(Prod_inptSL4.Text))}
		)
	)
)

View solution in original post

11 REPLIES 11
metsshan
Solution Sage
Solution Sage

I'm not sure if you're having trouble with the Filtering of the gallery, or with the Patching, but here is an example:

 

Filtering:

To avoid performing the same Sum() multiple times, I use AddColumns() to name, perform and temporarily store my calculations.

 

Patching:

I am using a ForAll() to set my 'Amount' column, even though it wasn't set in the Gallery. Patching a collection of changes is better described in long form here: https://www.matthewdevaney.com/patch-multiple-records-in-power-apps-10x-faster/ 

 

I am only updating a summary of the Calls + Emails to my 'Amount' field, please see if you can adjust this code to suit your specific needs.

 

metsshan_0-1659621028565.png

metsshan_1-1659621085063.png

 

Patch(
    People,
    ForAll(
        Filter(
            AddColumns(
                galHandled.AllItems,
                "CallsAndEmails", Sum(Value(txtCalls.Text), Value(txtEmails.Text)),
                "TypeSum", Sum(Value(txtSL1.Text), Value(txtSL2.Text), Value(txtSL3.Text), Value(txtSL4.Text))
            ),
            CallsAndEmails > 0 && CallsAndEmails = TypeSum
        ) As GalRecord,
        {
            ID: GalRecord.ID,
            Amount: GalRecord.CallsAndEmails
        }
    )
)

 

 

Edit: if you are CREATING new records with the Patch(), it might look like this:

ForAll(
    Filter(
        AddColumns(
            galHandled.AllItems,
            "CallsAndEmails", Sum(Value(txtCalls.Text), Value(txtEmails.Text)),
            "TypeSum", Sum(Value(txtSL1.Text), Value(txtSL2.Text), Value(txtSL3.Text), Value(txtSL4.Text))
        ),
        CallsAndEmails > 0 && CallsAndEmails = TypeSum
    ) As GalRecord,
    Patch(
        People,
        Defaults(People),
        {
            Title: GalRecord.txtName.Text,
            Amount: GalRecord.CallsAndEmails
        }
    )
)
RiccoHipp1903
Resolver II
Resolver II

Hello @Mts1 ,

 

A concept would be 

 

If(
  IsBlank(CallsHandled) && IsBlank(EmailsHandled),
  false,
  If(
   (!IsBlank(CallsHandled) || !IsBlank(EmailsHandled)&&
   (SL1.Text <> CallsHandled || SL1.Text <> EmailsHandled)&&
   (SL2.Text <> CallsHandled || SL2.Text <> EmailsHandled)&&
   (SL3.Text <> CallsHandled || SL3.Text <> EmailsHandled)&& 
   (SL4.Text <> CallsHandled || SL4.Text <> EmailsHandled),
   Patch(
    DATASOURCE,
    Defaults(DATASOURCE),
    {}
   ),
   false
  )
)

 

Not sure if it really works this way. I think you have to adjust the field they check, but the basic code would be this

Mts1
Helper III
Helper III

Hi,

 

A big thank you for your time and your answer.

 

I don't have issue with the patching but with the filtering,

 

I think i can simply my requirement :

 

Here my Sharepoint list

Mts1_0-1659621974496.png

 

I want to patch these inputs - to these Sharepoint Columns : 

inptCallsHandled to Calls Handled

inptEmailsHandled to Emails Handled

inptSL1 to Handled SL1

inptSL2 to Handled SL2

inptSL3 to Handled SL3

inptSL4 to Handled SL4

 

But i want only patch item where 

- ALL Sl's Columns are equal to Calls+Emails HAN

 

 

Based on the below picture, only the fisrt item will be patched 

Mts1_1-1659622571696.png

 

The second will not be patched because SLs are not equal to Handled

The third will not be patched because Calls & Emails are blank. (I think we can based on SL's are blank, if i'm not wrong it's the same)

 

Apologies for my explanations, i know is not really clear

 

Again Thank you

Hi @Mts1,

 

My filter was doing that, just with different control names. I will rename the fields, annotate it, and you let me know if it works for you?

 

    Filter(
        AddColumns(
            galHandled.AllItems,
            //===Add a columnd for sum of Calls and Emails
            "CallsAndEmails", Sum(Value(inptCallsHandled.Text), Value(inptEmailsHandled.Text)),
            //===Add a columnd for sum of all SL inputs
            "TypeSum", Sum(Value(inputSL1.Text), Value(inputSL2.Text), Value(inputSL3.Text), Value(inputSL4.Text))
        ),
        //===Perform check: Calls + Emails is greater than zero
        //===Perform check: Calls + Emails = Sum of SL inputs
        CallsAndEmails > 0 && CallsAndEmails = TypeSum
    )

 

I adapted your formula with my exact fields names but i have the error "As is not permitted in this context"

 

Below the formula used

 

Filter(
AddColumns(
ChildGallery.AllItems,
//===Add a column for sum of Calls and Emails
"CallsAndEmailsSum", Sum(Value(inptCallsHandled.Text), Value(inptEmailsHandled.Text)),
//===Add a column for sum of all SL inputs
"SLsSum", Sum(Value(inptSL1.Text), Value(inptSL2.Text), Value(inptSL3.Text), Value(inptSL4.Text))
),
//===Perform check: Calls + Emails is greater than zero
//===Perform check: Calls + Emails = Sum of SL inputs
CallsAndEmailsSum > 0 && CallsAndEmailsSum = SLsSum
) As ChildGalleryRecord,

Patch(.............)

"As is not permitted" because the Filter() is not an expression of a parent function. (Read more here)

 

I would recommend either using With() or ForAll(), eg:

ForAll(
    Filter(
        AddColumns(
            ChildGallery.AllItems,
            //===Add a column for sum of Calls and Emails
            "CallsAndEmailsSum", Sum(Value(inptCallsHandled.Text), Value(inptEmailsHandled.Text)),
            //===Add a column for sum of all SL inputs
            "SLsSum", Sum(Value(inptSL1.Text), Value(inptSL2.Text), Value(inptSL3.Text), Value(inptSL4.Text))
        ),
        //===Perform check: Calls + Emails is greater than zero
        //===Perform check: Calls + Emails = Sum of SL inputs
        CallsAndEmailsSum > 0 && CallsAndEmailsSum = SLsSum
    ) As ChildGalleryRecord,

    Patch(.............)
)

 

Hi @metsshan 

 

A big thank you for your reply. 

 

Actually i don't have any error with your formula, but it patch only the child selected item if i'm not wrong. AS you can see on the below photo, 2 items need to be patched. (the number in bracket is the number of items where SLsSum equal to HANsum.

Mts1_0-1659695840052.png

 

So i think the patch work only Child Item where the Parent Gallery is selected. I'm not sure if i'm clear with my explanations, here the code i used.

 

ForAll(
    Filter(
        AddColumns(
            ChildGallery.AllItems,
            //===Add a column for sum of Calls and Emails
            "CallsAndEmailsSum", Sum(Value(Prod_inptCallsHandled.Text), Value(Prod_inptEmailsHandled.Text)),
            //===Add a column for sum of all SL inputs
            "SLsSum", Sum(Value(Prod_inptSL1.Text), Value(Prod_inptSL2.Text), Value(Prod_inptSL3.Text), Value(Prod_inptSL4.Text))
        ),
        //===Perform check: Calls + Emails is greater than zero
        //===Perform check: Calls + Emails = Sum of SL inputs
        CallsAndEmailsSum > 0 && CallsAndEmailsSum = SLsSum
    ) As ChildGalleryRecord,
    Patch(PAT_Tracker_AppResults, Defaults(PAT_Tracker_AppResults), 
        {'Task Date': Text(Prod_DatePicker.SelectedDate, "dd/mm/yyyy")},
        {'Agent Name': User().FullName},
        {'Agent EmailAddress': User().Email},
        {'Task Type': Text("Handled")},
        {Country: Prod_DropDown_Country.Selected.Result},
        {Exception: Prod_lblException.Text},
        {Carrier: Prod_lblCarrier.Text},
        {Offered: Value(Prod_lblOffered.Text)},
        {'Calls Handled': If(IsBlank(Prod_inptCallsHandled.Text),0,(Prod_inptCallsHandled.Text))},
        {'Emails Handled': If(IsBlank(Prod_inptEmailsHandled.Text),0,(Prod_inptEmailsHandled.Text))},
        {'Handled SL1': If(IsBlank(Prod_inptSL1.Text),0,(Prod_inptSL1.Text))},
        {'Handled SL2': If(IsBlank(Prod_inptSL2.Text),0,(Prod_inptSL2.Text))},
        {'Handled SL3': If(IsBlank(Prod_inptSL3.Text),0,(Prod_inptSL3.Text))},
        {'Handled SL4': If(IsBlank(Prod_inptSL4.Text),0,(Prod_inptSL4.Text))}))

 

Again, a big thank your for your help,

Regards

Hey @Mts1, you are getting close, but think about what we've already done - used ForAll() to iterate over multiple gallery items. So, if you have multiple nested galleries, then perhaps you need another ForAll()? 😉

 

ForAll(
    galParent.AllItems As ParentGallery,
	ForAll(
		Filter(
			AddColumns(
				ChildGallery.AllItems,
				//===Add a column for sum of Calls and Emails
				"CallsAndEmailsSum", Sum(Value(Prod_inptCallsHandled.Text), Value(Prod_inptEmailsHandled.Text)),
				//===Add a column for sum of all SL inputs
				"SLsSum", Sum(Value(Prod_inptSL1.Text), Value(Prod_inptSL2.Text), Value(Prod_inptSL3.Text), Value(Prod_inptSL4.Text))
			),
			//===Perform check: Calls + Emails is greater than zero
			//===Perform check: Calls + Emails = Sum of SL inputs
			CallsAndEmailsSum > 0 && CallsAndEmailsSum = SLsSum
		) As ChildGalleryRecord,
		Patch(PAT_Tracker_AppResults, Defaults(PAT_Tracker_AppResults), 
			{'Task Date': Text(Prod_DatePicker.SelectedDate, "dd/mm/yyyy")},
			{'Agent Name': User().FullName},
			{'Agent EmailAddress': User().Email},
			{'Task Type': Text("Handled")},
			{Country: Prod_DropDown_Country.Selected.Result},
			{Exception: Prod_lblException.Text},
			{Carrier: Prod_lblCarrier.Text},
			{Offered: Value(Prod_lblOffered.Text)},
			{'Calls Handled': If(IsBlank(Prod_inptCallsHandled.Text),0,(Prod_inptCallsHandled.Text))},
			{'Emails Handled': If(IsBlank(Prod_inptEmailsHandled.Text),0,(Prod_inptEmailsHandled.Text))},
			{'Handled SL1': If(IsBlank(Prod_inptSL1.Text),0,(Prod_inptSL1.Text))},
			{'Handled SL2': If(IsBlank(Prod_inptSL2.Text),0,(Prod_inptSL2.Text))},
			{'Handled SL3': If(IsBlank(Prod_inptSL3.Text),0,(Prod_inptSL3.Text))},
			{'Handled SL4': If(IsBlank(Prod_inptSL4.Text),0,(Prod_inptSL4.Text))}
		)
	)
)

Hi @metsshan ,

 

Thanks a lot but this one is not working. It's patching child gallery items even if the conditions in not meet.

 

Just for you knowledge,

 

here my items properties of the Parent Gallery : 

 

Distinct(Filter(PAT_Tracker_AppSettings, Country = Prod_DropDown_Country.Selected.Result),Exception)

 

 

here my items properties of the Child Gallery : 

 

Filter(PAT_Tracker_AppSettings,ThisItem.Result = Title && Country = Prod_DropDown_Country.Selected.Result)

 

 

here my OnSelect properties of the button will patch.

 

//===Filter and Patch Child Gallery items.
ForAll(
    Filter(
        AddColumns(
            ChildGallery.AllItems,
            //===Add a column for sum of Calls and Emails
            "CallsAndEmailsSum", Sum(Value(Prod_inptCallsHandled.Text), Value(Prod_inptEmailsHandled.Text)),
            //===Add a column for sum of all SL inputs
            "SLsSum", Sum(Value(Prod_inptSL1.Text), Value(Prod_inptSL2.Text), Value(Prod_inptSL3.Text), Value(Prod_inptSL4.Text))
        ),
        //===Perform check: Calls + Emails is greater than zero
        //===Perform check: Calls + Emails = Sum of SL inputs
        CallsAndEmailsSum > 0 && CallsAndEmailsSum = SLsSum
    ) As ChildGalleryRecord,
		Patch(PAT_Tracker_AppResults, Defaults(PAT_Tracker_AppResults), 
			{'Task Date': Text(Prod_DatePicker.SelectedDate, "dd/mm/yyyy")},
			{'Agent Name': ChildGalleryRecord.lblAgentName.Text},
			{'Agent EmailAddress': Text(ComboBox2.Selected.Mail)},
			{'Task Type': Text("Handled")},
			{Country: ChildGalleryRecord.Prod_lblCountry.Text},
			{Exception: ChildGalleryRecord.Prod_lblException.Text},
			{Carrier: ChildGalleryRecord.Prod_lblCarrier.Text},
			{Offered: Value(Prod_lblOffered.Text)},
			{'Calls Handled': If(IsBlank(ChildGalleryRecord.Prod_inptCallsHandled.Text),0,(ChildGalleryRecord.Prod_inptCallsHandled.Text))},
			{'Emails Handled': If(IsBlank(ChildGalleryRecord.Prod_inptEmailsHandled.Text),0,(ChildGalleryRecord.Prod_inptEmailsHandled.Text))},
			{'Handled SL1': If(IsBlank(ChildGalleryRecord.Prod_inptSL1.Text),0,(ChildGalleryRecord.Prod_inptSL1.Text))},
			{'Handled SL2': If(IsBlank(ChildGalleryRecord.Prod_inptSL2.Text),0,(ChildGalleryRecord.Prod_inptSL2.Text))},
			{'Handled SL3': If(IsBlank(ChildGalleryRecord.Prod_inptSL3.Text),0,(ChildGalleryRecord.Prod_inptSL3.Text))},
			{'Handled SL4': If(IsBlank(ChildGalleryRecord.Prod_inptSL4.Text),0,(ChildGalleryRecord.Prod_inptSL4.Text))}))

 

 

But this code is patching only the parent item selected. 

 

On the below photo, 2 items need to be patched. (numbers between () in the parent gallery return child gallery items where meet the condition).

Mts1_0-1659793833508.png

My parent gallery is used to group child item.

Helpful resources

Announcements
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.

Users online (1,856)