cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TB05
Helper III
Helper III

Update Multiple Records in one table that matches a value from second list

Hi, 

I have an employee collection based on Employee SP list and Time list.

I have a gallery created with the employee collection, and have a dropdown column, data source Time, when I select an option from the drop list, I populate a text field (saved to Time list in BonusRateValue field) with a value from the Employee collection that matches the dropdown selection.  This works well when one employee is selected.  But I would like to update multiple employees at once.  When I update multiple employees, the same value is saved for the BonusRateValue for each employee.  I know it's due to the code I have on the textbox as I'm using GallerySelEmp_1.Selected.  I'm not sure how to get the unique values for each employee when multiple users are selected.

 

The default code on the text field is:

If(
Dropdown1.SelectedText.Value = "MSK",
GallerySelEmp_1.Selected.'JobCode:MSKBonus'.Value,
Dropdown1.SelectedText.Value = "Tier1",
GallerySelEmp_1.Selected.'JobCode:Tier1'.Value,
Dropdown1.SelectedText.Value = "Tier2",
GallerySelEmp_1.Selected.'JobCode:Tier2'.Value,
Dropdown1.SelectedText.Value = "Tier3",
GallerySelEmp_1.Selected.'JobCode:Tier3'.Value
)

 

What can I use in place of GallerySelEmp_1.Selected to get the value for each selected employee?  

Each employee has a unique value for the selected job code.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@TB05 

Still...no flow needed 😉  This can all be done here in your formula.  

 

So, the label where you display the rates is not really relevant because it is only going to show one and then if you reference it in your formula, it will only use one.  So this need to move into the formula to create the records.

 

Your formula should be this:

Collect(BonusInput,
    ForAll(Filter(yourGallery.AllItems, yourGalleryCheckBox.Value),
        {Date: DataCardValue12_1.SelectedDate,
         Comments: txtComments.Text, 
         Title: EmployeeName, 
         JobID: varJobID.Title, 
         SelJobCode: Dropdown1.Selected.Title, 
         BonusRateValue: 
             Switch(Dropdown1.Selected.Value,
                 "MSK", 'JobCode:MSKBonus'.Value,
                 "Tier1", 'JobCode:Tier1'.Value,
                 "Tier2", 'JobCode:Tier2'.Value,
                 "Tier3", 'JobCode:Tier3'.Value
             ),
         TimeID: ID,
         PayPeriodID: Value(varPayPeriodSelected.ID), 
         Stat: Checkbox2_1.Value, 
         PayPeriodStartDate: varPayPeriodSelected.StartDate, 
         PayPeriodEndDate: varPayPeriodSelected.EndDate, 
         Status: {Value: "Pending"}, 
         GIN: Value(Title)
        }
    )
)

 

_____________________________________________________________________________________
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

TB05
Helper III
Helper III

Hi @RandyHayes ,

 

About a month ago, you helped me with an issue that I was having on updating multiple records at once.  It works great.  

I'm wondering if you could help me with a part two of this issue?   When a bonus is submitted  two important fields are updated, they are the DayTotal and Stat fields (along with other fields).  I would like to be able to use same code to submit an additional bonus entry (record) for the same day...but I don't want the DayTotal or Stat fields updated when saved.   I know I can just remove those fields but how do I use an IF statement to perform the check...If(CheckIfSomethingisTrueorFalse, TrueDoThis, FalseDoThis).....

 

IF.....record exist for user with selected date and daytotal is > 0...do this update

Collect(BonusInput,
ForAll(Filter(GallerySelEmp_1.AllItems, Checkbox2_6.Value),
{Date: DataCardValue12_1.SelectedDate,
Comments: txtComments.Text,
Title: EmployeeName,
JobID: varJobID.Title,
SelJobCode: Dropdown1.Selected.Title,
BonusRateValue:
Switch(Dropdown1.Selected.Title,
"MSK", 'JobCode:MSKBonus'.Value,
"Tier1", 'JobCode:Tier1'.Value,
"Tier2", 'JobCode:Tier2'.Value,
"Tier3", 'JobCode:Tier3'.Value
),
TimeID: ID,
PayPeriodID: Value(varPayPeriodSelected.ID),
Stat:Checkbox2_1.Value,
StatValue: If(Checkbox2_1.Value = true, Value( 'JobCode:StatHolidayRate'.Value)),
DayTotal: Value('JobCode:DayRate'.Value),
PayPeriodStartDate: varPayPeriodSelected.StartDate,
PayPeriodEndDate: varPayPeriodSelected.EndDate,
Status: {Value: "Pending"},
GIN: Value(Title)
}
)
);

 

ELSE

 

Collect(BonusInput,
ForAll(Filter(GallerySelEmp_1.AllItems, Checkbox2_6.Value),
{Date: DataCardValue12_1.SelectedDate,
Comments: txtComments.Text,
Title: EmployeeName,
JobID: varJobID.Title,
SelJobCode: Dropdown1.Selected.Title,
BonusRateValue:
Switch(Dropdown1.Selected.Title,
"MSK", 'JobCode:MSKBonus'.Value,
"Tier1", 'JobCode:Tier1'.Value,
"Tier2", 'JobCode:Tier2'.Value,
"Tier3", 'JobCode:Tier3'.Value
),
TimeID: ID,
PayPeriodID: Value(varPayPeriodSelected.ID),
PayPeriodStartDate: varPayPeriodSelected.StartDate,
PayPeriodEndDate: varPayPeriodSelected.EndDate,
Status: {Value: "Pending"},
GIN: Value(Title)
}
)
);

 

I  hope you can understand what I'm  trying to accomplish.  

View solution in original post

17 REPLIES 17
RandyHayes
Super User
Super User

@TB05 

How is it that you are "Selecting" multiple records in your Gallery?

_____________________________________________________________________________________
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 Randy, thanks for responding...I am selecting each employee using a checkbox in the Gallery.  I then have a few fields on my screen that I update and save to the Time list using a Patch function with ForAll.

 

This is my patch statement and the value that does not update with each record is the BonusRateValue. I know it's because on the default text I have the IF statement and it's for One person but unsure how to get value for each.

 

ForAll(RenameColumns(Filter(collectEmployeeRecord,EmpIsChoosen),"ID","EID"),Patch(BonusInput,Defaults(BonusInput),{Date: DataCardValue12_1.SelectedDate,Comments: txtComments.Text, Title:EmployeeName, JobID:varJobID.Title, SelJobCode: Dropdown1.Selected.Title, BonusRateValue: Value(lblBonusRateValue.Text),TimeID: Value(EID),PayPeriodID:Value(varPayPeriodSelected.ID), Stat:Checkbox2_1.Value, PayPeriodStartDate: varPayPeriodSelected.StartDate, PayPeriodEndDate: varPayPeriodSelected.EndDate, Status:{ID:1, Value :"Pending"}, GIN: Value(Title) }));

This is the code oncheck of the checkbox:

Patch(collectEmployeeRecord, ThisItem, {EmpIsChoosen:true})

And this the employee collection code:

ClearCollect(collectEmployeeRecord, AddColumns(WLEmployee, "EmpIsChoosen", false));

 

For example, Each person time is unique, so MSK value for one person is 50 and for another could be 60. 

And if from dropdown Tier1 is selected, its value is different for each person.

 

Hope this helps.   

RandyHayes
Super User
Super User

@TB05 

You can get rid of the OnCheck formula on the checkbox as well as the EmpIsChoosen column in your collection - you don't need them.

You need to base your formula for submitting on the Gallery - not the collection.  Your Gallery already is a table/collection and you don't need to duplicate that in your app, just use it.

 

So your formula would become this:

Collect(BonusInput,
    ForAll(Filter(yourGallery.AllItems, yourGalleryCheckBox.Value),
        {Date: DataCardValue12_1.SelectedDate,
         Comments: txtComments.Text, 
         Title: EmployeeName, 
         JobID: varJobID.Title, 
         SelJobCode: Dropdown1.Selected.Title, 
         BonusRateValue: Value(lblBonusRateValue.Text),
         TimeID: ID,
         PayPeriodID: Value(varPayPeriodSelected.ID), 
         Stat: Checkbox2_1.Value, 
         PayPeriodStartDate: varPayPeriodSelected.StartDate, 
         PayPeriodEndDate: varPayPeriodSelected.EndDate, 
         Status: {Value: "Pending"}, 
         GIN: Value(Title)
        }
    )
)

This will create records in the BonusInput datasource based on the items in the gallery that are checked and the values according to that row in the gallery.

 

 

_____________________________________________________________________________________
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!
TB05
Helper III
Helper III

Thank you, the sharepoint list did update  selected records in the gallery but still a problem with the bonusratevalue.

BonusRateValue: Value(lblBonusRateValue.Text)

On the default property of the lblBonusRateValue, I have the following code:

If(
Dropdown1.SelectedText.Value = "MSK",
GallerySelEmp_1.Selected.'JobCode:MSKBonus'.Value,
Dropdown1.SelectedText.Value = "Tier1",
GallerySelEmp_1.Selected.'JobCode:Tier1'.Value,
Dropdown1.SelectedText.Value = "Tier2",
GallerySelEmp_1.Selected.'JobCode:Tier2'.Value,
Dropdown1.SelectedText.Value = "Tier3",
GallerySelEmp_1.Selected.'JobCode:Tier3'.Value
)

 

The JobCode:Tier3.Value for example, is coming from the employee table and each employee has a different value.  employee1 could have JobCode:Tier3 value of 50 and another have a value of 60.  So when I select the dropdown and choose Tier3, I would like to have the associated values for each employee.  But I'm only patching the last employee value that is selected.  What code should I use on that text default to get the correct value?

 

 

RandyHayes
Super User
Super User

@TB05 

I "lightly" understand what you are saying.  What I am not understanding is the connection of the Gallery with the values you want.

Is GallerySelEmp_1 that gallery of all the employees?  Is it that, in your app, the user would select a user in that Gallery and then perform the update/actions that you have for the bonuses?  

And, again you are saying "the last employee value that is selected"...are you implying multiple selections are in action on the employee gallery as well?  And, if so, what is the connection from the Bonus gallery items to the Employee items?

_____________________________________________________________________________________
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!
TB05
Helper III
Helper III

Hey Randy, thanks for your quick response...sorry for not being clear...I will do my best here.

I have an employee list:

TB05_0-1628180678499.png

In this list, each employee has a value a job code assigned that has different values depending on the job they are assigned too.   The gallery is based on this table.  I have another table for time, called BonusInput, so I have a dropdown in this list that has values of MSK, Tier1, etc.....so when this is selected, I have text box that collects the value from that selection based on the gallery....using the default code I provided earlier, so it uses galleryselected user which gets the associated value from the employee.  Note, in my employee list, I have ID and in the BonusInput list I have TimeID which is the relationship factor (ID=TimeID).  

 

Here is a screen shot of the text box and associated code that gets the value of the dropdown selected.

TB05_1-1628181387571.png

I know with this code, I'm only getting the value of the last user that is selected in the gallery....just not sure how to get all the values for each employee.  So when I patch my records, the same value for the selected job code is saved for all selected employees.  

Maybe once the bonusinput is updated with my dropdown selection, I can populate the bonus rate value another way???  Maybe using a flow of some kind?  I'm very new at this.   I need the value for each selected dropdown.

RandyHayes
Super User
Super User

@TB05 

No, you'll not need any other activities other than your formula to do this.  You just need to reference the information in the Gallery properly.  That was where I was a little foggy!

 

So, then in your picture, I see the Gallery with the checkboxes - what Datasource is that getting records from, the Employee list or the BonusInput list?

 

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

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,814)