Sort and filter on a Lookup table

[KhrystinaM]

Hello, I am trying to perform this function: Sort(Filter('Associate Library','Department Name'.Value="Administration"),EmployeeName,SortOrder.Ascending) but I'm getting a text error. The department name is a lookup table from another list. Hoping someone can help. 

Thank you,

Khrystina

[developingteal]

what type of value is administration -> you might need to take departmentname.value and convert it to text using text() - or some other way of converting the value

I would suggest trying hovering over each part of it to determine what value it currently is, and then convert as needed using text() or value()

[Anonymous_Hippo]

Hello @KhrystinaM ,

Can you please try

Sort(Filter('Associate Library', 'Department Name'.Value.Value="Administration"), EmployeeName, Ascending)

Note the use of .Value twice - first to access the lookup field and second to access the actual value of that lookup field.

Cheers!

[KhrystinaM]

Administration is a text field that is populating from the Department Library list using a lookup table. How would I convert that to text in my formula?

Breakdown of what I am working with: 

Associate Library SharePoint list with an Employee Name text field and a Department Name lookup field (pulling from the Department Library SharePoint list). I am trying to pull in the Employee Name based off of the Department Name from the Associate Library.  

[KhrystinaM]

@Anonymous_Hippo , I tried that and this is the error I am getting.

Sort(Filter('Associate Library', 'Department Name'.Value.Value="Administration"), EmployeeName, SortOrder.Ascending)

[developingteal]

Try this -

Sort(Filter('Associate Library', Text('Department Name'.Value)="Administration"), EmployeeName, Ascending)

[Anonymous_Hippo]

Looks like i am confused regarding this - let me re-read the question

[KhrystinaM]

@Anonymous_Hippo I tried the following formula and got the below error.

Sort(Filter('Associate Library','Department Name'.Department Name = "Administration"),EmployeeName,SortOrder.Ascending

Error Message

Column Settings in the Associate Library list for Department Name and Employee Name

Department Name Type = Lookup

 

Employee Name Type = Single line of text

Column Settings in the Department Library list for Department Name

Department Name Type = Single line of text

[KhrystinaM]

@developingteal I tried that and got this error message

[developingteal]

I think the issue is the underlying lookup is throwing an error. Anytime you try to change the type of the value it keeps returning an error.

Double check your lookup and see if it is working. If the lookup is not returning any values it might not show up as an error in the app checker, but it is causing an error down the line.

Make a quick gallery to display the items of the lookup and if nothing is showing up, then you need to fix the lookup.