Using Checkboxes in a Gallery to be able to select multiple records to update a SQL dB(as my datasource) column call AssignedTo

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

All - I'm struggling(lost) - pretty new to using SQL DB as a data source (also using the ForAll/Patch)

I have a ClearCollect (called ccXYZCompany) pulling data from a SQL DB called dbo.ABC_XYZCompany.

Gallery called: gal_XYZCompany with label names such as g_Date, g_AssignedTo, g_PayAmount

Basic SQL column names: {"Date", "AssignedTo", "PayAmount"} etc.

When the gallery row "IsSelected" it will pop up a Form.

Change:

Need to be able to select multiple records to update "AssignedTo" column using an outside DropDown1

Added a CheckBox inside the ccXYZCompany gallery. Right now called CheckBox1

CheckBox1:

OnCheck: Collect(colAssignedTo, {AssignedTo: Dropdown2.Selected.displayName})
OnUnCheck: Remove(colAssignedTo, {AssignedTo: ThisItem.AssignedTo})

Default: !IsBlank(LookUp(colAssignedTo, AssignedTo = ThisItem.AssignedTo))

I hope above is correct..

The ForAll & Patch is where I don't understand the correct syntax. I have tried different combination but nothing seems to be working.

Patch Button: ForAll(colAssignedTo, Patch('dbo.ABC_XYZCompany', Defaults('dbo.ABC_XYZCompany'),

This is about as far as I got, just unsure how to get the selected Checkboxes using DropDown2 selected name to update AssignedTo column to SQL? Any help would be very appreciated

Categories:

Building Power Apps

All responses (10)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

To start, your formula has the ForAll backward. You are trying to use it like a ForLoop in some development language - which PowerApps is not. ForAll is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance.

Next - Is Dropdown2 inside your gallery our outside?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

DropDown2 is outside my gallery. When the user selects multiple checkboxes, then goes up to select DropDown2 (displayname), need it to update the SQL DB AssignedTo to that displayname from DropDown2.
Sorry, I'm just lost. I know my Checkbox Default is wrong too. Not sure what to use there.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

Your Checkbox default should be nothing.

You don't need a collection and can get rid of the OnCheck and OnUnCheck actions on the Checkbox.

Your formula for patching should be the following:

Patch('dbo.ABC_XYZCompany', ForAll( Filter(yourGallery.AllItems, yourCheckbox.Value), {Date: Now(), AssignedTo: Dropdown2.Selected.Value, //see below PayAmount: 123, ...etc... } ) )

In the above, .Value is used as the presumed column name of the dropdown Selected record. However this is entirely based on the Items property formula of your dropdown, so replace the .Value with whatever column from your Items property holds the value of the AssignedTo information.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes Okay, I removed the Collection, and got rid of the OnCheck/OnUnCheck/Default values.
I don't want the other items to change within that Gallery only the AssignedTo from the dropdown selection. Is there a way to only update the AssignedTo in that Gallery from the formula you mention above?
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Well we need to roll back a bit!! Your original posted formulas used Defaults in them...this is normally the indication that you are creating new records.

I am assuming now, based on your last response, that you do not want to create records, but update the records represented in the gallery...is that correct??

If so, then what is the Items property of your gallery?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

oh shoot, I'm new to PowerApps. Sorry about that. Yes, it's an update to those current records. Just need to update the AssignedTo using the Checkbox (to select that current row) in the Gallery and the DropDown2 (outside the gallery).

As of right now, I'm using another dropdown to filter the gallery by dates. So the Gallery Items property looks like this: Filter ( ccXYZCompany, Date=DropDown1.Selected.Result)
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

No problem...

What is your datasource? SharePoint? SQL? Dataverse?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

SQL DB
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

Very good. You will need to know the name of the Primary Column of your table. For simplicity, I will refer to it as ID, but replace with the actual name of your column.

Formula would be:

Patch('dbo.ABC_XYZCompany', ForAll( Filter(yourGallery.AllItems, yourCheckbox.Value), {ID: ID, AssignedTo: Dropdown2.Selected.Value } ) )
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

@RandyHayes This worked!! Gosh, I owe you a beer (coffee, Tequila etc. - what ever you drink) for your help with this. Thank you so much!!! I appreciate your time! Take care!