Patch Gallery Items only with condition

[Anonymous]

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.

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

Thanks in advance for your help,

Regards

[metsshan]

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.

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]

Hello @Anonymous ,

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

[Anonymous]

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

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 

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

[metsshan]

Hi @Anonymous,

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
    )

[Anonymous]

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(.............)

[metsshan]

"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(.............)
)

[Anonymous]

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.

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

[metsshan · Accepted solution]

Hey @Anonymous, 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))}
		)
	)
)

[Anonymous]

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

My parent gallery is used to group child item.