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โ
Solved! Go to Solution.
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
Iโm not following here:
Do you have a seperate Tasks table you are assigning Employess?
Can you elaborate? May be a screenshot?
I will show you an example of the data base because I tried to explain it simplier
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
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.
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
You can try with this:
IF(
COUNT(
FILTER(
"Employee",
AND(
[Employee] = [_THISROW].[Employee],
[Start Date] <= TODAY(),
[Finish Date] >= TODAY()
)
)
)>0,
"Busy",
"Available"
)
In the cases that is not working is where the [start date] and [finish date] is empty
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
Do apologize, but Iโm not following you. Whatโs the source of the problem?
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
Youโre welcome.
User | Count |
---|---|
43 | |
28 | |
24 | |
24 | |
13 |