Create new condition based on Year Of Service

[syhrh]

Hi, I'm doing the flow for when a new employee is added to the list, it will calculate the Year of Service from the joining date and what is the Job Grade to determine the employee entitled leave. I have this condition that read only all the values in the rows in the Employee Entitled Leave so if the Years of Service are not present then it will display all rows that have the same Job Grade.

this is my Employee Entitled Leave list:

The leave if the employee's years of service is longer than 5 years then it will follow the entitled leave of year of service: 6. In the Employee Entitled Leave list, the maximum number of Years of Service is 6 so if the calculated Year of Service is 7 and above then, it will display all the Leave Balance that reference the Job Grade. How to put the condition that can detect the Year Of Service is 6 then display the Leave Balance according to Job Grade?

For example, if the Year of service is 7 and the Job Grade is JG5, it will then follow Year of service: 6 and job grade JG5 and display it in Leave Balance list where all the columns are numbers.

this is my flow:

Thank you.

[Rhiassuring]

I might be over simplifying, but couldn't you just say "if year of service is greater than 6, then use 6" ? You could have a variable where you set the years of service - but within that variable, use an expression that says "if today minus anniversary date is more than 6 years, then set this variable as 6; otherwise, use the number of years"

Am I totally missing the need? I hope I read correctly haha

[syhrh]

@Rhiassuring yes, you read it correctly. I'm trying to be as detailed as possible in explaining it but probably too much😅

I don't get by what you mean "variable, use an expression that says "if today minus anniversary date is more than 6 years, then set this variable as 6; otherwise, use the number of years"

I have the variable YearOfService but what do you mean by within that variable?

[Rhiassuring]

Bahaha, no, you gave good information - always better to have too much than too little. 

So I'm recommending that you create a variable to hold what you will use as Year of Service, because then you will have more control over it. You can use an expression right in that variable to figure out what you need. Check this out:

I initialized a variable that I can use to hold things in, and then I clicked in Value and added the following expression...

if(greaterOrEquals(sub(int(utcNow('yyyy')),1996),6),'6', sub(int(utcNow('yyyy')),1996))

Where I have "1996" is where your employees anniversary date would come from - you could put that right within here as well. So this is just an example, but, when it runs - it evaluates; is 2022 - 1996 greater than 6? Yes? Then I will put "6" in my txtYearOfService. Is it less than 6? If it is, then I will put that number in my txtYearOfService. 

[syhrh]

@Rhiassuring why the variable type is in String, not integer?

the output of the variable YearOfService is like 6, 7 not years like 2015, 2016.

I tried using your formula.

I got this error.

[Rhiassuring · Accepted solution]

> the output of the variable YearOfService is like 6, 7 not years like 2015, 2016.

Our formula gives the output of a number like 6 or lower. (I know you said "or 7" but my understanding of your need was that 6 was the highest they could get - is that right?)  In my post I mentioned I added 1996 as my example year - you would get this from your person's join date by formatting the date formatDateTime(JoinDate,'yyyy') -- but since I am using dummy data, I'm just dumping an arbitrary year in there for example.

>why the variable type is in String, not integer?

Good point - you could use either, if your years of service is a strict number column then use Integer, for sure. I think I have formed a bad habit in that I almost always use string...   and if you're going to evaluate it further as a number (is 6 greater than...) then you'd want number. 

So, I re-did this with int so you can use the number:

if(greaterOrEquals(sub(int(utcNow('yyyy')),1996),6),6, sub(int(utcNow('yyyy')),1996))

(Again, where I have 1996, you'll have something like formatDateTime(JoinDate,'yyyy') )

Here's what the output looks like:

If I change the person's join date to, say, 2020 - here's the output for that:

 The formula makes it so even if the persons join date is 1786, they're going to get 6 years of service.

Let me know if this helps or if I've made it worse lol

[syhrh]

@Rhiassuring Thank you for your formula. I get by what you mean now. But it doesn't do anything to filter the YearOfService is 6 and still display everything according to the JobGrade.

This is the created items in Leave Balance

I input Job Grade, 5 and the YearOfService is 7 so the row that should be displayed is just one row that is with the red line in the first picture but it displayed all according to JG5.

I tried this condition but it doesn't work. How to fix it?

[Rhiassuring]

That is the next step, we haven't done that yet. But looking at your condition, I don't know why you have "YearofService" in there - you only need the variable from here on out, and can filter using that.

However, we need to clarify something - based on what I'd built, it would never be higher than 6. It would be 6 or lower. Is that not your use case?  Then, no condition is needed. The variable now holds what you need. 

Is your "Years of Service" column a string of text or a number? I can demo something quick to see how we can filter your list.

[syhrh]

@Rhiassuring 

"It would be 6 or lower. Is that not your use case? " 

Yes, I put the condition greater than 6 because there's another condition for 6 and lower. 

"Is your "Years of Service" column a string of text or a number?"

The column in the list is Number

Thank you.

[Rhiassuring · Accepted solution]

Ah, gotcha. Okay. So, regardless of the number that is brought back, wouldn't we just filter the YearOfService column on intYearOfService (as it will only be 1 - 6), and then filter the Job Grade by whatever their grade is? What is the different condition if it is 6? 

To do that, you'd just use a "Get Items" and in the Filter Query you can do something like YearOfService eq 'intYearOfService' and Job Grade eq 'JobGrade'. That would bring you back the relevant line with all of the annual leave and whatnot. 

For that though you'd need to ensure you use the internal field names for YearOfService and Job Grade, and then for intYearOfService it'd be the variable, and for JobGrade it'd be .. well, whereever you define their job grade from. Dynamic content.

[syhrh]

@Rhiassuring Okay, I got it. Thanks

[syhrh]

.

[syhrh]

.