Filter jobs by week and by user with with an ElseIf statement

[pketeldijk]

Hi, I have been struggling with this issue for the longest time. I have a gallery that shows our week planning by filtering all our jobs within the current week. 

Filter(WorkOrders,
//StartDate and EndDate fall on the Sunday/Saturday of current week or in between
StartDate>=Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or 
//StartDate is before the current Sunday and EndDate is after the current Saturday
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()+(7-Weekday(Today()))
Or
//StartDate is before current Sunday and EndDate is on or before current Saturday
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or
//StartDate is on or before current Saturday and before current Sunday and EndDay is after current Saturday
StartDate>=Today()-Weekday(Today())+1&&StartDate<=Today()+(7-Weekday(Today()))&&EndDate>Today()+(7-Weekday(Today()))
)

This works fine (just some delegations issues). I would like to further filter the week planning by logged in user. But if I am the user it must show the entire weekplanning. 

We can use the following statement to filter the week planning by user. How do I add this in the above code with an ElseIf statement that if I am the current user (show all records week planning)?

ProjectLeader.Value = varUser.DisplayName

[v-bofeng-msft]

Hi @pketeldijk :

I think I may not understand what you mean, could you point out my mistake:

• What is the judgment condition of "Elseif" you mentioned?Is it User().DisplayName="XXXX"?
• When the conditions are met, the filtering rules remain unchanged, and when the conditions are not met, the new filtering conditions "ProjectLeader.Value = varUser.DisplayName"will be added.

If so,you could try this solution:

My method is to nest the IF function outside the Filter function, execute the first Filter function when the condition is met, otherwise execute the second Filter function.

If(User().DisplayName="XXXX",
Filter(WorkOrders,
StartDate>=Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or 
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()+(7-Weekday(Today()))
Or
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or
StartDate>=Today()-Weekday(Today())+1&&StartDate<=Today()+(7-Weekday(Today()))&&EndDate>Today()+(7-Weekday(Today()))
),Filter(WorkOrders,ProjectLeader.Value = varUser.DisplayName &&
(StartDate>=Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or 
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()+(7-Weekday(Today()))
Or
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or
StartDate>=Today()-Weekday(Today())+1&&StartDate<=Today()+(7-Weekday(Today()))&&EndDate>Today()+(7-Weekday(Today())))
))

If the problem is not resolved,could you describe your judgment conditions and screening rules in detail?

Best Regards,

Bof

[pketeldijk]

Hi Bof,

My current filter shows the week planning for all project leaders. But I want to filter the week planning by current project leader so that each project leader only views his week planning: ProjectLeader.Value = varUser.DisplayName

But if I am the user varUser.DisplayName = "XXXX" then show all records from the week planning.

I hope this is more clear. If not let me know.

[v-bofeng-msft · Accepted solution]

Hi @pketeldijk :

I understand what you mean now.Have you tried this code? Does it work?

If(varUser.DisplayName = "XXXX",
Filter(WorkOrders,
StartDate>=Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or 
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()+(7-Weekday(Today()))
Or
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or
StartDate>=Today()-Weekday(Today())+1&&StartDate<=Today()+(7-Weekday(Today()))&&EndDate>Today()+(7-Weekday(Today()))
),Filter(WorkOrders,ProjectLeader.Value = varUser.DisplayName &&
(StartDate>=Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or 
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()+(7-Weekday(Today()))
Or
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or
StartDate>=Today()-Weekday(Today())+1&&StartDate<=Today()+(7-Weekday(Today()))&&EndDate>Today()+(7-Weekday(Today())))
))

 Best Regards,

Bof

[pketeldijk]

 I've tried it with a dropdown but is doesn't apply the filter.

Filter(WorkOrders, ProjectLeader.Value = DropdownProjLeader_1.Selected.Result And
//StartDate and EndDate fall on the Sunday/Saturday of current week or in between
StartDate>=Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or 
//StartDate is before the current Sunday and EndDate is after the current Saturday
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()+(7-Weekday(Today()))
Or
//StartDate is before current Sunday and EndDate is on or before current Saturday
StartDate<Today()-Weekday(Today())+1&&EndDate>Today()-Weekday(Today())+1&&EndDate<=Today()+(7-Weekday(Today())) 
Or
//StartDate is on or before current Saturday and but before current Sunday and EndDay is after current Saturday
StartDate>=Today()-Weekday(Today())+1&&StartDate<=Today()+(7-Weekday(Today()))&&EndDate>Today()+(7-Weekday(Today()))
)

[v-bofeng-msft]

Hi @pketeldijk :

Could you show me a screenshot of the code area? Usually the wrong place will be marked.

Best Regards,

Bof