cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrianHFASPS
Level 8

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
BrianHFASPS
Level 8

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
BrianHFASPS
Level 8

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

vsslasd
Level 8

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

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,915)