cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Using ForAll() with UpdateIF() inside?

I have a SQL DB with two tables and a view. The view combines to the two tables for a Gallery but I must post changes back to the tables (as far as I know in PowerApps). I have a button that works fine on a single item in the gallery.

UpdateIf(
    '[dbo].[Pickup Tracking]',
    User_x0020_ID = ThisItem.'User ID',
    {
        Car_x0020_Order: LookUp(
            '[dbo].[Variable Trackers]',
            Variable_x0020_Name = "Pickup",
            Numeric_x0020_Value
        ),
        Weight: Weight + (100-LookUp(
            '[dbo].[Variable Trackers]',
            Variable_x0020_Name = "Pickup",
            Numeric_x0020_Value
        ))
    }
);

I was hoping to wrap that command in a ForAll() based on filtering the Gallery. I filter the Gallery Items with:

Sort(Filter('[dbo].[Pickup]', (StartsWith('Student Last Name', FilterTextBoxName.Text) || Value(FilterTextBoxName.Text) = 'Car #'), 'Car Order' = 0), Weight, SortOrder.Descending)

I tried to this with ForAll()

ForAll(Outside.AllItems, 
UpdateIf('[dbo].[Pickup Tracking]',
User_x0020_ID=ThisItem.'User ID',
{Car_x0020_Order:LookUp('[dbo].[Variable Trackers]', Variable_x0020_Name="Pickup",Numeric_x0020_Value),
Weight:Weight+(100-LookUp('[dbo].[Variable Trackers]',Variable_x0020_Name="Pickup",Numeric_x0020_Value))}))

It tells me that Outside.AllItems isn't valid and also ThisItem.'User ID' isn't valid. I feel like I am missing something obvious in my syntax but I can't figure it out.

 

Any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Post Patron
Post Patron

Re: Using ForAll() with UpdateIF() inside?

I think I have fixed this and wanted to share in case someone else has similar issue. I changed one of my 'User ID' fileds to be PickupUID to make sure I wasn't tripping myself up with 'User ID' = 'User ID'. Then I removed the ThisItem. since apparently that isn't needed in ForAll loops and breaks it. Finally I captured my data into a collection based on same filter as Gallery since using Gallery.AllItems did everything not the filtered list. I still don't understand how to get filtered list from a gallery. Anyway here is my revised code that seems to be working.

ClearCollect(
    CarNumberCollection,
    Filter(
        '[dbo].[Pickup]',
        (StartsWith(
            Student_x0020_Last_x0020_Name,
            FilterTextBoxName.Text
        ) || Value(FilterTextBoxName.Text) = Car_x0020__x0023_),
        Car_x0020_Order = 0
    )
);
ForAll(
    CarNumberCollection,
    UpdateIf(
        '[dbo].[Pickup Tracking]',
        PickupUID = User_x0020_ID,
        {
            'Car Order': LookUp(
                '[dbo].[Variable Trackers]',
                Variable_x0020_Name = "Pickup",
                Numeric_x0020_Value
            ),
            Weight: Weight + (100-LookUp(
                '[dbo].[Variable Trackers]',
                Variable_x0020_Name = "Pickup",
                Numeric_x0020_Value
            ))
        }
    )
)

Also as I bonus I made the button only visible when the list was filtered to prevent accidentally acting upon entire unflitered list.

View solution in original post

2 REPLIES 2
Highlighted
Post Patron
Post Patron

Re: Using ForAll() with UpdateIF() inside?

I think I have fixed this and wanted to share in case someone else has similar issue. I changed one of my 'User ID' fileds to be PickupUID to make sure I wasn't tripping myself up with 'User ID' = 'User ID'. Then I removed the ThisItem. since apparently that isn't needed in ForAll loops and breaks it. Finally I captured my data into a collection based on same filter as Gallery since using Gallery.AllItems did everything not the filtered list. I still don't understand how to get filtered list from a gallery. Anyway here is my revised code that seems to be working.

ClearCollect(
    CarNumberCollection,
    Filter(
        '[dbo].[Pickup]',
        (StartsWith(
            Student_x0020_Last_x0020_Name,
            FilterTextBoxName.Text
        ) || Value(FilterTextBoxName.Text) = Car_x0020__x0023_),
        Car_x0020_Order = 0
    )
);
ForAll(
    CarNumberCollection,
    UpdateIf(
        '[dbo].[Pickup Tracking]',
        PickupUID = User_x0020_ID,
        {
            'Car Order': LookUp(
                '[dbo].[Variable Trackers]',
                Variable_x0020_Name = "Pickup",
                Numeric_x0020_Value
            ),
            Weight: Weight + (100-LookUp(
                '[dbo].[Variable Trackers]',
                Variable_x0020_Name = "Pickup",
                Numeric_x0020_Value
            ))
        }
    )
)

Also as I bonus I made the button only visible when the list was filtered to prevent accidentally acting upon entire unflitered list.

View solution in original post

Highlighted
Advocate V
Advocate V

Re: Using ForAll() with UpdateIF() inside?

I've been struggling with this, and not having any luck. 

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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