Apply format rule for employees with 2 or more of same record, but only for a specific category

[Surdusvacca]

Hi all,

I have 2 tables

• Medarbejdere (Employees)
• Forbrug (Usage)

In my table Medarbejdere I have added a virtual column with this app formula:

COUNT(
  FILTER(
    Forbrug,
    AND(
      [Medarbejder nr.] = [_THISROW].[Medarbejder nr.],
      [Kategori] = "Handsker",
      [Dato] >= (TODAY() - 30)
    )
  )
)

In the Format Rules I have added this

It actually works.. Kind of.. 🙂

Look at this example from registrations I made as a test:

The name turned red as it should after I added the second pair of gloves.
But when I add something else to the same employee from the 'Kategori', I dont want the formatting rule to apply.
E.g when Målebånd is added, that row should not be colored.
Is that possible?

[lynchk21 · Accepted solution]

If you only want this to only format entries that have the value "Handsker" in the [Kategori] column, you can update your format rule to the following:

• AND([GlovesRegistrations30Days]>=2, [Kategori]="Handsker")

[lynchk21 · Accepted solution]

If you only want this to only format entries that have the value "Handsker" in the [Kategori] column, you can update your format rule to the following:

• AND([GlovesRegistrations30Days]>=2, [Kategori]="Handsker")

[Surdusvacca]

Hi lynchk21,
Thanks for your reply.

Since my column 'Kategori' is part of table named Forbrug, I have added this virtual column KategoriFromForbrug in table 'Medarbejdere' where the virtual column 'GloveRegistrations30Days' are as well:

Virtual column named: KategoriFromForbrug

LOOKUP([Medarbejder nr.], Forbrug, Medarbejder nr., Kategori)

And then I have updated the formatting rule expression to this:

AND([GloveRegistrations30Days]>=2, [KategoriFromForbrug]="Handsker")

But it is still formatting the other values from 'Kategori'.
Am I doing something wrong in my expressions?

Or could it be that the format rule expression cannot use virtual column from table Medarbejdere to find the column 'Kategori' in table Forbrug?

[lynchk21 · Accepted solution]

In this picture, it looks like you are trying to format data that is on the Forbrug table:

---

But the format rule is on the Medarbejdere table

---

Unless I misunderstood those images, if you use the format rule on the Forbrug table, it should work as stated above. 

[Surdusvacca]

Oh yeah sorry you are so right. I completely overlooked that one.
I have been playing so much around with this I got my self confused. Earlier I got errors referencing from 'Forbrug' to 'Medarbejdere', so I ended up doing the virtual column in the 'Medarbejdere' instead.

But anyways, I think I have made the setup correct now.
I have created a virtual column in table 'Forbrug'

And my format rule for this data is set to 'Forbrug' with your expression.

Now it does not color anything? 🙂 

[lynchk21]

It looks like you have selected "Navn" as the column to format. Is that what you want? I assumed you would want "Medarbejder" 

Sorry I don't know what "Navn" means! 

[Surdusvacca]

Yes its because 'Navn' mean Name. And I keep forgetting that Medabejder nr (Which means Employee ID) is ref from Forbrug to Medarbejdere, and that Navn is the label. So in my world I would have to choose formatting of 'Navn' because it is the name of the employee I would like to format, but yeah, it has to be the Employee ID column instead. 

Hopefully one day I learn this 😄 

Thanks again man, really appreaciated 

[Surdusvacca]

Never mind. I am an idiot. It works now.
Ofcourse I had to choose 'Medarbejder nr.' here as it is the ref column 😄 

My bad. Thanks a million! You are a genius.

[lynchk21]

AppSheet makes an idiot of us all sometimes 🤣 Glad you got it working!