cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TB05
Resolver I
Resolver I

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.

 

17 REPLIES 17
TB05
Resolver I
Resolver I

The Gallery is getting it's information from the Employee List.

TB05_2-1628183445720.png

I select several employees from my gallery, and select the dropdown option from the bonusinput.  

The dropdown has several options:  MSK, Tier1, Tier2...etc.  That is saved in the bonusinput list, when I save I would like to save the value of those options (value is in the employee list) to another field.

 

I could update this list in some kind of flow??  From previous response, you can see how I'm getting one user value associated to the dropdown selection.  when I save, the same bonusratevalue is saved for all employees, although they have their own value.  Just not sure, how to capture all the values for each employee selected and update record with the appropriate value.  

RandyHayes
Super User
Super User

@TB05 

Yeah still...you can do all this in your formula.  I misunderstood, I thought you had another gallery somewhere called GallerySelEmp_1.

 

So, based on what I am hearing and seeing, in your picture, the bonus code control is where you have the value you're looking up based on the dropdown.

However, this is pretty much non-relevant as you describe it.  If you select three employees (like you have in the picture) then what would that control show?  I ask because you state that each employee can have different rates.  So, if you select 3 employees with different rates, what would you expect that to show?

 

If the above assumption is correct, then the reality is, you just need to account for the correlated rate during the Formula that submits the records.

 

Let me know first if I'm picking up what you're putting down.

_____________________________________________________________________________________
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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
Resolver I
Resolver I

You are correct in saying, the bonus code control is where you have the value you're looking up based on the dropdown.  But with my code, I'm only getting the value of one person and it gets saved to all 3 people (the last person selected in the gallery value).

I'm not sure how to get 3 individual values for 3 employees selected at once, as they are different values? 

If MSK is selected, I need to save the value for MSK for each user and the value for each user is different.  If each person had the same value, there would be no issue.  But one of the requirements is to update many records at once.    Do you think, the update needs to happen in a flow or something?  Or is there a way to collect all values and update with correct values?

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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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
Resolver I
Resolver I

OMG!  Thank you, I have been struggling with this for so long!!!  So glad, you understood what I was trying to 'lay down'...lol!!

RandyHayes
Super User
Super User

@TB05 

Yeah, sorry for so many back-and-forth messages.  I was having a hard time getting the connection, mostly because I was focused on that dropdown and the label.  Now it all made sense.

Glad to help out!!

_____________________________________________________________________________________
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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
Resolver I
Resolver I

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.  

I solved my issue....I set a variable and looked up the date..

 


Set(varRecord, LookUp(BonusInput, Date = DataCardValue12_1.SelectedDate));

If(IsBlank(varRecord.ID),

 

//then added code to patch the record

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,702)