cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ali_Nawaz
Level: Powered On

Lookup field not working in filter function

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.

Screenshot_1.png

 

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)

Screenshot_4.png

But after using this function I see this in the collection

Screenshot_2.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Level 10

Re: Lookup field not working in filter function

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

Annotation 2019-09-18 145202.png

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

 

Best regards,

Sik

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Lookup field not working in filter function

@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

 

img4.png

 

---

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
Level 10

Re: Lookup field not working in filter function

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

Annotation 2019-09-18 145202.png

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

 

Best regards,

Sik

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,419)