cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 8

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

2 REPLIES 2
mdevaney
Level 8

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 8

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

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 195 members 4,122 guests
Recent signins:
Please welcome our newest community members: