Solved: Show All Usernames in a filtered View

ghost5 · 01-25-2023 11:00 AM

I have built a time card app with a table named 'Time Card Data'. In this, I have a dynamic dashboard for checking the time cards.

This is linked to 'Week Numbers' table, which lists all the dates and the relevant week number for our business.

It is also linked to 'Users' table which lists all employees and various other data about them, along with the filter options for each user in the dynamic dashboard.

This is the dynamic view.

The control box and Filtered Table View work well. I am struggling with the weekly summary and I don't know what to try next.

I would like the weekly summary to list 'all' people who are in the Users[Name] column and count how many hours they have input for that week number ( week numbers[week] ). This is to allow a quick visual overview to see who has input their hours for that week and to make sure that they have input at least 40.

I cannot get it to list all names from the user table, it only shows people who have already entered data for that week. As an example, I would like it to show the below, where Tim, James and Ben are listed in Users[Name] but have not input any data yet for week 41.

This is the filter row condition for the filtered Table view which works well for that

and(

IF(ISBLANK(ANY(Users[Filter 1])), TRUE, 
[Week] = ANY(Users[Filter 1])),

IF(ISBLANK(ANY(Users[Filter 2])), TRUE, 
[Username] = ANY(Users[Filter 2])),

IF(ISBLANK(ANY(Users[Filter 3])), TRUE, 
[Week Year] = ANY(Users[Filter 3]))

)

and this is the filter row condition that I currently have for the weekly summary view

IF(ISBLANK(ANY(Users[Filter 1])), TRUE, 
[Week] = ANY(Users[Filter 1]))

I have two trails of thought for this.

1. Find the correct way to add all names into the filter (without it complaining about not being able to compare a list).

2. Create an automation that every Saturday adds a time entry with 0 hours for everyone in Users[Name] . If they have time entered for that week they will then appear, but I don't know how to get it to do this, and number 1 seems like a cleaner option.

I'd be grateful for any help or pointers. Thanks,

Marc_Dillon

You can't display data that doesn't exist. 0 is not the same as null. So in your current set up, yah, you'd have to add a 0-hour entry for every user for every week. You don't know how to do it? Where are you stuck? Run a forEachRow scheduled Bot across the users table, at the beginning of every week that adds a 0-hour record.

Another option would be to add an additional view into your dashboard that shows an unfiltered list of users. Then you can add a new Virtual Column in the users table that sums up their hours based on the selected filter controls, and display that VC in your new view.

Finally, I can't figure out what you're referring to with your #1 item.

View solution in original post

Marc_Dillon

You can't display data that doesn't exist. 0 is not the same as null. So in your current set up, yah, you'd have to add a 0-hour entry for every user for every week. You don't know how to do it? Where are you stuck? Run a forEachRow scheduled Bot across the users table, at the beginning of every week that adds a 0-hour record.

Another option would be to add an additional view into your dashboard that shows an unfiltered list of users. Then you can add a new Virtual Column in the users table that sums up their hours based on the selected filter controls, and display that VC in your new view.

Finally, I can't figure out what you're referring to with your #1 item.

ghost5

Thank you. I missing the bot running for the users table, then the action being added new rows. I like the idea of using VC's as I could then show additional data in the future. I will look into in the future but at the moment it is doing what I want it to.