LinkToFilteredView Criteria

[Griff]

I have a form that allows the user to choose a date for a variable called Select Date. This form is placed on the top of a dashboard.

I have another form that lists Names down 1st column and days across. The form is on the bottom of the same dashboard.

The result is that, when the user chooses a date in the top form, the bottom form displays all the assignments for that week.

What I need is for the user to be able to click on a name and display all the records for the selected user for the selected week. In a previous question I was given the LINKFILTEREDTOVIEW selection option that showed me how to filter by name but now I am having trouble extending the filter to include the date. This is what I’ve tried but the messages is "Arithmetic expression '(Assignments[Work Date] >= Selection[Select Date]) does not have valid input types. If I change the >= to = the Expression is accepted
LINKTOFILTEREDVIEW(“Assigned Jobs”,AND([Worker]=[_ThisRow].[Worker],Assignments[Work Date]>=Selection[Select Date],Assignments[Work Date]<=Selection[Select Date]+6))

How do I modify the Action to display the desired records?

[Suvrutt_Gurjar]

Hi @Griff,

Does the following expression help?

LINKTOFILTEREDVIEW(“Assigned Jobs”, AND([Worker]=[_ThisRow].[Worker],[Work Date]>=ANY(Selection[Select Date]),[Work Date]<=ANY(Selection[Select Date])+6))

Assumes that the Selection table has just one row for user input. If this expression does not work, I believe we may request more inputs from you.

[Griff]

Fantastic. It works perfectly with the current structure of my table. However since this is a filter table and I now realise that I will eventually need to make one row in the table for every user, I will need to eventually modify it further. Still, this will get me started and hopefully by the time I am ready to introduce multiple users I will have a better handle on code structure

[Suvrutt_Gurjar · Accepted solution]

Hi @Griff*.

Great to know it works per your current requirement. Also you mentioned that that you are thinking in terms of implementing “per user one row” concept of User table that we had touched upon as one possible option in another post earlier.

As and when you implement the per user one row concept, your expression may change to

LINKTOFILTEREDVIEW(“Assigned Jobs”, AND([Worker]=[_ThisRow].[Worker],[Work Date]>=ANY(SELECT(Selection[Select Date], [USEREMAIL]=USEREMAIL())),[Work Date]<= ANY(SELECT(Selection[Select Date], [USEREMAIL]=USEREMAIL()))+6))

Here [USEREMAIL] is column name that has users’ emails in the assigned row for respective users in the Selection table.

If I may add, in your earlier expression the part Selection[Select Date] will produce a list , as well as Assignments[Work Date] will produce a list. So we qualified Selection[Select Date] with ANY() make it a single date type and Assignments[Work Date] was unnecessary because we are dealing with the same table’s view in “Assigned Jobs” View , just the way we have [Worker] that does not need to be qualified by the table name. Once those changes were done to make fields non list types, the arithmatic comparison between two lists that was giving error , got sorted out.

Hope this helps. All the best.

[Griff]

Thanks for the solution and the extra explanation. Really appreciate your kind assistance.