cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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

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

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (10,196)