Format Rule - format group based on all entries?

[SKETCHwade]

Hello!

I have an app that is used to record and display users timesheets.
Here is some background information that will be helpful:

The data displayed above is the timesheet data grouped by employee,
then grouped by project.
The data is put through a slice to only display the last 8 days of entries.
I would like to change the text colour of the employee name to red if they have incorrect date entries for the week.
Examples of red:
Today is 28/10/2020 (Aus)
Bob:
27/10
26/10
23/10
22/10
(No entry for last Wednesday 21/10)
(too few entries)

Bob:
27/10
27/10
26/10
23/10
22/10
21/10
(double up of entries)
(Too many entries / duplication)

I would then like to make every other name green if they were successfully completed:

Bob:
27/10
26/10
23/10
22/10
21/10

Now finally. The issue I seem to have is the you can only format each entry based off of its own data.
I cannot find a way to inspect all entries and colour them dependent on the above criteria.
The closest thing I can seem to come to is colour the entry if the date is within the last 7 days but this should already be the case due to the slice.

Any ideas please let me know
Thank you

[Suvrutt_Gurjar · Accepted solution]

Hi @SKETCHwade

Based on your post, it sounds that there is

1. a slice with moving dates for past 7 days. Today()-7
2. Saturdays , Sundays are weekly holidays.

If so, I believe you may create a VC called say [Entry Status] with an expression something like

IF( COUNT(SELECT(TimeSheet Slice[Key Column], [Employees]=[_THISROW].[Employees]))<>5, “Duplicate entries or Few Entries”, “Correct Entries”)

Then you could use this column in setting the necessary format rules when column value is “Duplicate entries or Few Entries”

Basically expression is centered around 5 timesheets in last 7 days. So following issues could arise that I believe can be addressed but expressions will become complex.

1. If there is a holiday other than weekly holidays, the weekly correct number will be lesser than 5
2. If an employee is on leave , for a day or more and fills in duplicate timesheets on other days equaling the weekly tally to 5, the expression may not detect it.

Also, in general using multirow function like SELECT() in a VC could impact sync times based on data being processed.

[SKETCHwade]

Thank you very much for your thoughtful response! @Suvrutt_Gurjar

You are correct in that public holidays would affect this. I am not too worried about this as its not super common and the employees should figure that out… hopefully haha.

When filling out the timesheet form there is a section for leave so that should still equate to 5 days.

As you say I am worried about the affect on sync time but I will try it out and see how it goes.
If it does have a significant impact would reducing row count in the spreadsheet make a noticeable difference? or is the issue more so that it has to calculate it at all?

[SKETCHwade]

Its interesting that appsheet does that calculation by itself:

I wonder if its possible to access this information within the format rule so I no longer need a VC to run?

[Suvrutt_Gurjar]

Hi @SKETCHwade
I believe that group by count or sum information is not accessible for further use in the app logic expressions.

[Suvrutt_Gurjar]

SKETCHwade:

As you say I am worried about the affect on sync time but I will try it out and see how it goes.
If it does have a significant impact would reducing row count in the spreadsheet make a noticeable difference? or is the issue more so that it has to calculate it at all?

On this topic, yes, I believe testing will reveal more. But to me it sounds that since you anyway already have the weekly slice applied to the timesheet table, the SELECT() statement constructed for the format rule may not further degrade the sync time much.

This is so because the SELECT() statement for the format rule is applied on the slice rather than the table. So the SELECT() will process weekly data rather than the entire table for all the employees.

[SKETCHwade]

Ooh I was unaware that the expression is only applied to the slice! That is excellent!

Thank you @Suvrutt_Gurjar !