cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EpicTriffid
Resolver I
Resolver I

Updateif working for one field but not another in same function

So, I have a screen where I can update someone's department and name. This will change it on my StaffTotals table, and then cascades it down to my RKE, Support, and Unit table, so that a person would have the same department in all 4 tables.

 

This is my code:

//Takes the Entry_ID of the person being updated, and saves it to a collection so that a department change can be replicated across all tables.
Concurrent(
ClearCollect(unitRecordToChange,Filter('[dbo].[Units]', Entry_ID = ThisItem.Entry_ID)),
ClearCollect(supportRecordToChange,Filter('[dbo].[Support]', Entry_ID = ThisItem.Entry_ID)),
ClearCollect(rkeRecordToChange,Filter('[dbo].[RKE]', Entry_ID = ThisItem.Entry_ID)));

//Cascades a department change across all tables, and updates Staff Name and FTE on the StaffTotals table.
Concurrent(
ForAll(unitRecordToChange,
UpdateIf('[dbo].[Units]',
ID = unitRecordToChange[@ID],
    {Department: First(chosenDept).Value,
    'Staff Name':First(chosenName).Value})),

ForAll(supportRecordToChange,
UpdateIf('[dbo].[Support]',
ID = supportRecordToChange[@ID],
    {Department: First(chosenDept).Value,
    'Staff Name':First(chosenName).Value})),

ForAll(rkeRecordToChange,
UpdateIf('[dbo].[RKE]',
ID = rkeRecordToChange[@ID],
    {Department: First(chosenDept).Value,
    'Staff Name':First(chosenName).Value})),

Patch(
    '[dbo].[StaffTotals]',
    {ID: Value('Admin AER - Gallery - Value - ID'.Text)},
    {Staff_x0020_Name: 'Admin AER - Gallery - Value - Staff Name'.Text},
    {Department: Text('Admin AER - Gallery - Dropdown - Dept'.SelectedText.Value)},
    {FTE: Value('Admin AER - Gallery - Value - FTE'.Text)}
));

 

chosenDept and chosenName are defined in collections using the OnChange property when you change the department or name field. It just saves the new value, and uses this to send it across to all the tables. My main table is StaffTotals, so that's why it has a more involved Patch that the others, and only modifies one row, where the other might have to change many rows in their respective tables. 

 

My problem: I just added the Staff Name bit of the ForAll/UpdateIf statements, and IT'S JUST NOT DOING ANYTHING! The department will update brilliantly, but the staff name is just not being patched. I can even see it in the monitor, running the PatchRows action four times for each of the four tables when I'm changing department, but only doing PatchRows once on my main table when changing staff name. 

 

Am I missing something really obvious here?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@EpicTriffid 

Yep, DelayedLoad is on by default and causes lots of issues like that.

What was happening is that your ClearCollect was forcing the data to all be gathered (this was the first "gathering", so the values were then loaded because of Delay Load).  Then your UpdateIf functions actually had records to work with.  For some reason, UpdateIf (and most other functions) will not trigger Delayed Load to actually load and then act on them...so, it was acting on nothing - and thus why you saw no changes.

 

As for the other formula, how about this:

//Cascades removal across all tables to avoid ghost records
Concurrent(
    RemoveIf('[dbo].[Units]', ID = recordtoDelete.Entry_ID),
    RemoveIf('[dbo].[Support]', ID = recordtoDelete.Entry_ID),
    RemoveIf('[dbo].[RKE]', ID = recordtoDelete.Entry_ID),
    Remove('[dbo].[StaffTotals]', recordtoDelete)
);

//Re-collects the editStaff collection. Then informs user that record has been deleted.
ClearCollect(
    editStaff,
    Filter('[dbo].[StaffTotals]',
        Department = 'Admin AER - Dropdown - Dept'.Selected.Department,
        'Academic Year' = 'Admin AER - Dropdown - AY'.Selected.'Academic Year'
    )
);
    
Notify("This member of staff has been deleted from the " & recordtoDelete.'Academic Year' & " academic year", NotificationType.Warning);
UpdateContext({deletePrompt: false});
Reset('Admin AER - Toggle - Delete')

Assuming you are getting the proper record in recordToDelete.

_____________________________________________________________________________________
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!

View solution in original post

6 REPLIES 6
RandyHayes
Super User
Super User

@EpicTriffid 

You've got a lot of baggage in that Formula...you might consider the following as a replacement:

//Cascades a department change across all tables, and updates Staff Name and FTE on the StaffTotals table.

Concurrent(
    UpdateIf('[dbo].[Units]', Entry_ID = ThisItem.Entry_ID,
       {Department: Text('Admin AER - Gallery - Dropdown - Dept'.Selected.Value),
       'Staff Name':'Admin AER - Gallery - Value - Staff Name'.Text}
    ),

    UpdateIf('[dbo].[Support]', Entry_ID = ThisItem.Entry_ID,
       {Department: Text('Admin AER - Gallery - Dropdown - Dept'.Selected.Value),
        'Staff Name':'Admin AER - Gallery - Value - Staff Name'.Text}
    ),

    UpdateIf('[dbo].[RKE]', Entry_ID = ThisItem.Entry_ID,
        {Department: Text('Admin AER - Gallery - Dropdown - Dept'.Selected.Value),
        'Staff Name':'Admin AER - Gallery - Value - Staff Name'.Text}
    ),

    Patch('[dbo].[StaffTotals]',
        LookUp('[dbo].[StaffTotals]', ID=Value('Admin AER - Gallery - Value - ID'.Text)),
        {
         Staff_x0020_Name: 'Admin AER - Gallery - Value - Staff Name'.Text,
         Department: Text('Admin AER - Gallery - Dropdown - Dept'.Selected.Value),
         FTE: Value('Admin AER - Gallery - Value - FTE'.Text)
        }
    )
)

As I read your formula, you we collecting a list of records based off of the Entry_ID and then Updating each one...this can all be done in the UpdateIf function.  There is no need for collecting them into a collection first.

 

You mention that you are creating a collection in the OnChange of some other control for the department and the staff name, yet in your patch you refer to them by control (this is better than collections).  So, in the suggested formula above your UpdateIf statements refer to the selected values as well.  You can get rid of the extra collections in the OnChange.

 

Finally, your Patch statement should (best practices) specify the exact record to replace as the second parameter.  Here in the formula I have added a LookUp to get that exact record.

 

Take a look over it - there may be some subtle changes needed based on other conditions in your app, but this should give you the results you want.

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
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!

Hi @RandyHayes 

 

Thank you for the suggestions. I had to shift around the Entry_ID conditions, but then it works. Now I have a very interesting conundrum. My code looks like this:

 

//Takes the Entry_ID of the person being updated, and saves it to a collection so that a department change can be replicated across all tables.
Concurrent(
ClearCollect(unitRecordToChange,Filter('[dbo].[Units]', Entry_ID = ThisItem.Entry_ID)),
ClearCollect(supportRecordToChange,Filter('[dbo].[Support]', Entry_ID = ThisItem.Entry_ID)),
ClearCollect(rkeRecordToChange,Filter('[dbo].[RKE]', Entry_ID = ThisItem.Entry_ID)));

//Cascades a department change across all tables, and updates Staff Name and FTE on the StaffTotals table.
Concurrent(
UpdateIf('[dbo].[Units]', 
ThisItem.Entry_ID = Entry_ID,
    {'Staff Name':'Admin AER - Gallery - Value - Staff Name'.Text,
    Department:Text('Admin AER - Gallery - Dropdown - Dept'.SelectedText.Value)}),

UpdateIf('[dbo].[Support]',
ThisItem.Entry_ID = Entry_ID,
    {'Staff Name':'Admin AER - Gallery - Value - Staff Name'.Text,
    Department:Text('Admin AER - Gallery - Dropdown - Dept'.SelectedText.Value)}),

UpdateIf('[dbo].[RKE]',
ThisItem.Entry_ID = Entry_ID,
    {'Staff Name':'Admin AER - Gallery - Value - Staff Name'.Text,
    Department:Text('Admin AER - Gallery - Dropdown - Dept'.SelectedText.Value)}),

Patch(
    '[dbo].[StaffTotals]',
    LookUp('[dbo].[StaffTotals]', ID = Value('Admin AER - Gallery - Value - ID'.Text)),
    {Staff_x0020_Name: 'Admin AER - Gallery - Value - Staff Name'.Text,
    Department: Text('Admin AER - Gallery - Dropdown - Dept'.SelectedText.Value),
    FTE: Value('Admin AER - Gallery - Value - FTE'.Text)}
));

That works great. But I still have those ClearCollects in there. And if I remove them, it stops working. It always updates the StaffTotals table as that is a direct patch, but it doesn't update the other tables. If I leave in the ClearCollects it works. If I remove them, it doesn't. Am I missing something here?

 

Unofrtunately I don't have the option of removing and re-adding my data sources as we are operating in the grace period from when the licenses changed, but refreshing them doesn't do anything either. 

 

@EpicTriffid 

There is definitely no need to remove the datasource - leave as is.

 

I'm not sure what you meant by changing the entry ID around.  The formula I provided should do the trick.  There is no dependency on the collections in the formula, so they can go.  

//Cascades a department change across all tables, and updates Staff Name and FTE on the StaffTotals table.

Concurrent(
    UpdateIf('[dbo].[Units]', Entry_ID = ThisItem.Entry_ID,
       {Department: Text('Admin AER - Gallery - Dropdown - Dept'.Selected.Value),
       'Staff Name':'Admin AER - Gallery - Value - Staff Name'.Text}
    ),

    UpdateIf('[dbo].[Support]', Entry_ID = ThisItem.Entry_ID,
       {Department: Text('Admin AER - Gallery - Dropdown - Dept'.Selected.Value),
        'Staff Name':'Admin AER - Gallery - Value - Staff Name'.Text}
    ),

    UpdateIf('[dbo].[RKE]', Entry_ID = ThisItem.Entry_ID,
        {Department: Text('Admin AER - Gallery - Dropdown - Dept'.Selected.Value),
        'Staff Name':'Admin AER - Gallery - Value - Staff Name'.Text}
    ),

    Patch('[dbo].[StaffTotals]',
        LookUp('[dbo].[StaffTotals]', ID=Value('Admin AER - Gallery - Value - ID'.Text)),
        {
         Staff_x0020_Name: 'Admin AER - Gallery - Value - Staff Name'.Text,
         Department: Text('Admin AER - Gallery - Dropdown - Dept'.Selected.Value),
         FTE: Value('Admin AER - Gallery - Value - FTE'.Text)
        }
    )
)

However...you indicate this does not Update the first three tables - so let's explore!

First, this formula, I assume, is on the OnSelect of some control IN your gallery?

Next, in your advanced settings, do you have Delayed Load turned on?  Try to turn that off and attempt again.

 

 

_____________________________________________________________________________________
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!

hi @RandyHayes 

 

DelayedLoad being turned off did the trick! Why was that causing a problem?

 

As you're here, would you mind having a look at this formula as well? It operates in very much the same way, but removes records associated with the selected person instead. I tried to alter it using ThisRecord in the condition and ended up wiping all the fields in my rke, support and units table. Had a heart attack, but thank god for backups!

 

//Takes the Entry_ID of the person being removed, and saves it to a collection so that a removal can be replicated across all tables.
Concurrent(
ClearCollect(unitRecordToDelete,Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID)),
ClearCollect(supportRecordToDelete,Filter('[dbo].[Support]', Entry_ID = recordtoDelete.Entry_ID)),
ClearCollect(rkeRecordToDelete,Filter('[dbo].[RKE]', Entry_ID = recordtoDelete.Entry_ID)));

//Cascades removal across all tables to avoid ghost records
Concurrent(
ForAll(unitRecordToDelete,
RemoveIf('[dbo].[Units]',
ID = unitRecordToDelete[@ID])),

ForAll(supportRecordToDelete,
RemoveIf('[dbo].[Support]',
ID = supportRecordToDelete[@ID])),

ForAll(rkeRecordToDelete,
RemoveIf('[dbo].[RKE]',
ID = rkeRecordToDelete[@ID])),

Remove(
    '[dbo].[StaffTotals]',
    recordtoDelete));

//Re-collects the editStaff collection. Then informs user that record has been deleted.
ClearCollect(
    editStaff,
    Filter('[dbo].[StaffTotals]',
    Department = 'Admin AER - Dropdown - Dept'.Selected.Department,
    'Academic Year' = 'Admin AER - Dropdown - AY'.Selected.'Academic Year'));
    
Notify("This member of staff has been deleted from the " & recordtoDelete.'Academic Year' & " academic year", NotificationType.Warning);
UpdateContext({deletePrompt: false});
Reset('Admin AER - Toggle - Delete')

 

@EpicTriffid 

Yep, DelayedLoad is on by default and causes lots of issues like that.

What was happening is that your ClearCollect was forcing the data to all be gathered (this was the first "gathering", so the values were then loaded because of Delay Load).  Then your UpdateIf functions actually had records to work with.  For some reason, UpdateIf (and most other functions) will not trigger Delayed Load to actually load and then act on them...so, it was acting on nothing - and thus why you saw no changes.

 

As for the other formula, how about this:

//Cascades removal across all tables to avoid ghost records
Concurrent(
    RemoveIf('[dbo].[Units]', ID = recordtoDelete.Entry_ID),
    RemoveIf('[dbo].[Support]', ID = recordtoDelete.Entry_ID),
    RemoveIf('[dbo].[RKE]', ID = recordtoDelete.Entry_ID),
    Remove('[dbo].[StaffTotals]', recordtoDelete)
);

//Re-collects the editStaff collection. Then informs user that record has been deleted.
ClearCollect(
    editStaff,
    Filter('[dbo].[StaffTotals]',
        Department = 'Admin AER - Dropdown - Dept'.Selected.Department,
        'Academic Year' = 'Admin AER - Dropdown - AY'.Selected.'Academic Year'
    )
);
    
Notify("This member of staff has been deleted from the " & recordtoDelete.'Academic Year' & " academic year", NotificationType.Warning);
UpdateContext({deletePrompt: false});
Reset('Admin AER - Toggle - Delete')

Assuming you are getting the proper record in recordToDelete.

_____________________________________________________________________________________
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!

View solution in original post

Thankyou for all your help @RandyHayes 

 

You're an absolute credit to this community.

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 (2,887)