Unanswered

Trying to update a cell with it's column decided in a switch

(1) Share

Report

Posted on by danielboesing

I am having difficulty updating a table in excel using the patch() function because the column of the cell I am referencing is chosen in a switch. Read below for full context.

My department has an excel sheet that we use as a calendar for WFH scheduling. I am trying to make an app that updates the status for a selectable worker on a selectable day. Below is my progress so far.

This is 'Table1' which is the excel table I am referencing

Screen 1 has you select a worker and a date to see/update their status.

BELOW IS WHERE I AM HAVING MY ISSUE

I want the 'Submit' button to update the cell at row 'SelectedDate' and column 'SelectedTeamMember' with the value of the status chosen in the dropdown menu. Since 'Team Member' is not it's own column, I have to use the switch() function to tell powerapps what column to go to. I think I have a syntax error trying to make the 'update' entry in the patch() function vary for each possible TeamMember in switch(). Any ideas or clarifications I can provide?

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@danielboesing
If there's no red squiggle or any error, you don't have any syntax error. I also checked and it seems like your formula is correct. It's likely something with the LookUp and the Date.
Try this:

If( IsBlank(LookUp(Table1, Date = Text(SelectedDate))), Notify("Date not found!", NotificationType.Error), Patch( Table1, LookUp(Table1, Date = Text(SelectedDate)), Switch( SelectedTeamMember, "Matt Puffer", {'Matt Puffer': Dropdown2.Selected.Status}, "Justin Roehm", {'Justin Roehm': Dropdown2.Selected.Status} // Continue with the other 7 or 8 users ) ) )

if it says Date not found, check on your LookUp to see if there's anything incorrect about SelectedDate, the column, that you're picking a Date for which exactly one record exists in Table 1, etc. @danielboesing

Note: To avoid repeating the same LookUp twice, use With like below:

With( {wSelectedDateRecord: LookUp(Table1, Date = Text(SelectedDate))}, If( IsBlank(varSelectedDateRecord), Notify("Date not found!", NotificationType.Error), Patch( Table1, wSelectedDateRecord, Switch( SelectedTeamMember, "Matt Puffer", {'Matt Puffer': Dropdown2.Selected.Status}, "Justin Roehm", {'Justin Roehm': Dropdown2.Selected.Status} // Continue with the other 7 or 8 users ) ) ) )

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @danielboesing,

Could you remove the enclosing curly brackets around the Switch() statement, since you already provide the brackets within the Switch value itself.

Patch( DataSource, Defaults(Datasource), Switch( Condition, Case1, {'Column Name': "TextValue"} ) )

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@LaurensM Yes, that's a very important detail.
@danielboesing Yes, even with no syntax error, the detail brought up by @LaurensM is very important as it may not work correctly like you have it.
You are already in record scope, so you should either omit the outer curly braces or the inner ones. Since there's more inner braces, eliminating the inner would be better at reducing more repetition, and keeping only the outer curly braces, but if it really doesn't work you may resort to only eliminating the outer curly braces and keeping the inner ones instead.

Was this reply helpful? Yes No