Lookup field not working in filter function

[Ali_Nawaz]

Hi PowerUsers,

I am trying to create a collection in which i am using Filter function. 
The entity (Leave Enrollment) I am trying to filter has a lookup filed called "WorkerId / cdm_WorkerId". 
When I am comparing it with the desired variable (which holds the ID on the basis of which I want to filter this entity ) i am receiving the following error.

I have the understanding that the variable (WorkerId) is of Text type and "cdm_WorkerId (Lookup Field)" is of record type. 
But I need to filter the entity on this variable by all means. 

I tried using this function as well (Sreenshot pasted below).
Where I tried to reference "cdm_workerid" putting a dot "." after cdm_Workerid   
( cdm_Worker_id with capital W belongs to "Leave enrollment entity" and cdm_workerId with small case w belongs to "Worker Entity" which has relation with Leave enrollment entity)



But after using this function I see this in the collection

This doesn't even open when clicked on. 


Looking for a solid workaround or proper method to filter entity which has lookup fileds as I have to do this later-on many time. 


Expecting a swift and helpful response.

Best Regards, 
Ali Nawaz

[mdevaney]

@Ali_Nawaz 

I think I know why you have the error "Invalid argument type. Cannot use Text Value this context".  You've actually set WorkerId as a text variable.  The First function selects a single record and then by appending .cdm_workerid you selected an individual field containing a text value.

Set(WorkerId, First(WorkerCollection).cdm_workerid);

I would need some more information to advise you on exactly what to do, but for example, here's an example of WorkerId being created as a single column table variable. 

Set(WorkerId, ShowColumns(WorkerCollection, cdm_workerid));

Then you could use it in a statement like:

ClearCollect(LeaveEnrollmentCollection, Filter('Leave Enrollments', cdm_WorkerId in WorkerId));

Here's your original screenshot which I found to be the most helpful

---

Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly.  If your thought the post was helpful please give it a "Thumbs Up."

[v-siky-msft · Accepted solution]

Hi @Ali_Nawaz ,

According to my test, I find that your second workaround( reference "cdm_workerid" putting a dot "." after cdm_Workerid  )  is great, the look up field can be filtered as excepted. Though you cannot click it on in the collection page, actually you can call it in powerapps.

Please refer to my following test:

The left table in below screenshot show a entity(reservations) with look up field (Product reservation), the button create a collection(AA3) with filter function, and the right table show that collection which is filtered by the look up field. Also I put a label to show the look up field value of Collection.

Here is the formulas:

Button Onselect property: 

ClearCollect(AA3,Filter(reservations,'Product Reservation'.Name="Laptop"))

Label Text property:

First(AA3).'Product Reservation'.Name

The Text property of two lookup field in Table should be modify from ThisItem.'Product Reservation' to ThisItem.'Product Reservation'.Name

So as you can seen ,the filter function is effective for look up field.

Best regards，

Sik