Answered

Conditional Formating based on day of week and scheduled work location

(0) Share

Report

Posted on by jonfuego

Hello,

I am trying to incorporate "Work Location" into a seating chart app that I built previously. My thought was to dynamically change the user label color based on the user's work location for the current day (ie: HQ = Green, WFH = Purple). So for instance, if today were Wednesday, the label text color for Jon would be green and Mike would be purple. I have gathered the schedule information and placed it into a new Dataverse table (shown below).

The label to be changed is part of a gallery that displays the User and the Desk Location.

This data is pulled from a collection, created from the Office365 connector:

ClearCollect(

col_Users,

Filter(

Office365Users.SearchUser({top: 999}),

OfficeLocation <> Blank()

)

I am then looking up the label info as follows:

LookUp(

col_Users,

OfficeLocation = ThisItem.locName,

DisplayName

)

My thinking is to use a Lookup against the Dataverse table to find the user based on DisplayName (lbl_User_FL1) and then change the text color based on the information in the Dataverse column for the current day.

I have run myself in circles trying to use Switch and Lookup, but I just cannot seem to get it right....

Any help is appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @jonfuego,

You need to check the weekday of today and retrieve location name based on it from Dataverse table.

Please check as below:

1). Populate the Gallery with this collection

2). Set the Label Text property as below:

Switch(Weekday(Today()), 1, LookUp( 'User Work Schedules', 'User ID'= ThisItem.DisplayName, ).Monday, 2, LookUp( 'User Work Schedules', 'User ID'= ThisItem.DisplayName, ).Tuesday, ... )

3). Set the Fill property of Label as:

If(Self.Text="HQ" ,Color.Green, Self.Text="WFH",Color.Purple)

Was this reply helpful? Yes No
jonfuego on at

Like
a
(0)

Report
Copy link

Link copied!

@v-qiaqi-msft Thank you very much for your reply. I am not sure I am following you. To clarify, are you saying, create a new gallery based on the User Work Schedules table?

To add a bit more detail. Here is what I thought would work, but I am getting an error that the Switch function has invalid arguments.

Switch(
Text(Weekday(Today())),
"1", // Monday
Switch(LookUp('User Work Schedules', 'User ID' = lbl_User_FL1.Text).Monday,
"HQ", Color.Green,
"DT", Color.DarkOrange,
"BIA", Color.LightBlue,
"WFH", Color.Purple,
varColor
),
"2", // Tuesday
Switch(LookUp('User Work Schedules', 'User ID' = lbl_User_FL1.Text).Tuesday,
"HQ", Color.Green,
"DT", Color.DarkOrange,
"BIA", Color.LightBlue,
"WFH", Color.Purple,
varColor
),
"3", // Wednesday
Switch(LookUp('User Work Schedules', 'User ID' = lbl_User_FL1.Text).Wednesday,
"HQ", Color.Green,
"DT", Color.DarkOrange,
"BIA", Color.LightBlue,
"WFH", Color.Purple,
varColor
),
"4", // Thursday
Switch(LookUp('User Work Schedules', 'User ID' = lbl_User_FL1.Text).Thursday,
"HQ", Color.Green,
"DT", Color.DarkOrange,
"BIA", Color.LightBlue,
"WFH", Color.Purple,
varColor
),
"5", // Friday
Switch(LookUp('User Work Schedules', 'User ID' = lbl_User_FL1.Text).Friday,
"HQ", Color.Green,
"DT", Color.DarkOrange,
"BIA", Color.LightBlue,
"WFH", Color.Purple,
varColor
),
RGBA(0, 0, 0, 1) // Default color if no match
)

Was this reply helpful? Yes No
v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @jonfuego，

No, your provided formula is wrong in logic....

You have collected all the users in your Gallery, so each record represents a single user, if I understand correctly, you want to display the label color based on the weekday of today, well, then you just need to check the weekday of the current day, if it is monday, go to your Dataverse table, retrieve the location name based on the weekday column and user id.

There is no need to use a nested Switch(), which is the error you have in your formula.

Just try my formula.

Was this reply helpful? Yes No
jonfuego on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you again for you assistance. I don't think I explained my situation well enough though. I am not looking to change the color based on what day of the week it is. I want to change the color based on the location that they are at "Today", based on the Dataverse table.

Was this reply helpful? Yes No
jonfuego on at

Like
a
(0)

Report
Copy link

Link copied!

@v-qiaqi-msftFloating this back up because I realized that I did not describe my issue accurately. Where I described the gallery data as coming from "col_Users", I was incorrect.

The gallery data is coming from a different collection entirely (col_Locations). Therefore the User name is not actually in the gallery, and is instead populated by a lookup. I am sure this is where my confusion is coming from. This means, in your example, "ThisItem.DisplayName" is not a valid item.

Was this reply helpful? Yes No
Verified answer

jonfuego on at

Like
a
(0)

Report
Copy link

Link copied!

I figured out my mistake. I needed to nest a lookup inside my existing lookup to get the correct User ID

Switch(
Text(Weekday(Today())),
"1",// Monday
Switch(
Text(
LookUp(
'Employee Schedule',
'User ID' = LookUp(
col_Users,
OfficeLocation = ThisItem.locName,
DisplayName
)
).Monday
),
"HQ",
Color.Green,
"DT",
Color.DarkOrange,
"BIA",
Color.LightBlue,
"WFH",
Color.Purple,
varColor
),

Thank you again @v-qiaqi-msft for your offer of assistance.

Was this reply helpful? Yes No