Look for data in the actual table

Questions
,
#1

Hi everyone.

At the moment I’m having an issue for something that “should not” be very difficult.

I have a table with the records of employees [Employees].
Here I upload the information like “Task” daily, and this “Task” have an “Start Date” and “Finish Date”.

I want that when I will more information about any employee if he is working in any “Task” this row will appear as “Bussy”.

What I have done so far is:

if(
count(
FILTER(“Employee”, and([Employee] = [_THISROW].[Employee],
[Start date]<today(),[Start Date]>today()))
)>0,
“Busy”,
“Available”
)

I suspect that my error is in “[Employee] = [_THISROW].[Employee]” but I’m doing this because I want to filter the actual Employee in this hole table.

Please let me know if you need more information.

PD: My error is that ALWAYS show it as “Available”

#2

I’m not following here:
Do you have a seperate Tasks table you are assigning Employess?

Can you elaborate? May be a screenshot?

1 Like
#3

I will show you an example of the data base because I tried to explain it simplier

image
image758×127 5.57 KB

In this case of the table, if I want to ad a new row for “Jhon” i want it to tell me in the [Status] Colum “Bussy” because he is in a task fro 05/01/2021 to 10/01/2021

#4

Basically you want to use a validation rule, right? In this case as the employee will be busy till 10/1/2021, you shouldn’t be able to assing any task to him OR even you assign, the [Start] date column shall automatically fill in 10/01/2021.

1 Like
#5

Yes, But I want to do it formulated, because after this I will create a report to trigger an action that brings to this table all the employess automatically every day at 00:00.
So… at that point that formula will give me the status automatically (Bussy or Available) with an if

I dont know if i got you lost of my point again hahaha…

But your second point was completely right, in this case will be in that way but automatically done

1 Like
#6

You can try with this:

IF(
	COUNT(
		FILTER(
			"Employee",
			AND(
				[Employee] = [_THISROW].[Employee],
				[Start Date] <= TODAY(),
				[Finish Date] >= TODAY()
			)
		)
	)>0,
	"Busy",
	"Available"
)
1 Like
#7

In the cases that is not working is where the [start date] and [finish date] is empty

#8

And now that I’m doing it with the report to TakeAction It is putting ALL the Employess as “Bussy” and just 1 should be Bussy

#9

Do apologize, but I’m not following you. What’s the source of the problem?

#10

It becomes a little more complecated with the action to do it automatically, but after some time I could solve it.

What I did with action was:

Take the information of the users from table “USERS” ([Name], [Email] and [Telephone]) I did it with the action “Data: add a new row to another table using values from this row”. And after that triggering an action for each row using the report structure.

So my idea was to create the status of every employee automatically.

So It was givin to all operator an status to “Busy” because I couldn´t filter properly the data from it’s own data.

So I create 2 diferent actions and applied the formula that you gave me in 2 different actions, actions that give me the bussy employess and and the available employess

Behavior for the available empoyess:

COUNT(
FILTER(
	"Employee",
	AND(
		[Employee] = [_THISROW].[Employee],
  [Date] = today()-1,
  OR( 
    [Status] = "Available",
    AND(
    [Asistió ?] = "Bussy",
    [End Date] < today() -1
    )
    )
	)
))>0,

Behavior for the bussy operators:

COUNT(
FILTER(
	"EMployee",
	AND(
		[Employee] = [_THISROW].[Employee],
  [Status] = "Bussy",
		[Start Date] <= TODAY()-1,
		[Finish Date] >= TODAY()-1      
	)
))>0,

And it worked, now that I solve if anyone has a question because I could not have explained in the best way i could give more detail.

Thanks @LeventK for your help

2 Likes
#11

You’re welcome.

Company

Platform

Pricing

Solutions

Support

Account

Built in Seattle, WA | Copyright 2019