cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HCLin
Helper II
Helper II

Filter a field which is Lookup datatype

Hi,

 

I am building up a management system, and have a "Contract" table, and then have a view from the data "All Contract".

I inserted a gallery and connect to the table view with filter, and would like to also filter the Owner name by the current user

 

And the field of the "Owner name" is a Lookup datatype

 

So the code I wrote:

Filter(Contracts, 'Contracts (Views)'.'All Contracts', User().FullName in "Owner name")

 

And I keep getting error from the part ->  User().FullName in "Owner name"

 

I think it cause from the datatype,

I have tried 

User().FullName in "Owner name".Value

User().FullName in Value("Owner name")

User().FullName in Text("Owner name")

User().FullName in First("Owner name").Value

...

 

They all didn't work : ((((

1 ACCEPTED SOLUTION

Accepted Solutions
PowerAddict
Super User II
Super User II

Hi @HCLin 

Store the user's full name in a variable first, using:

Set(varUserName, User().FullName)

Then assuming the Owner Name field is a lookup to the Users entity, you can change your filter expression to: 

Filter(Contracts, 'Contracts (Views)'.'All Contracts', 'Owner name'.'Full Name' = varUserName)

Let me know if this works. Also, you can simply add this filter condition to your view: 

PowerAddict_0-1608008275929.png

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

 

 

View solution in original post

13 REPLIES 13
WarrenBelz
Super User III
Super User III

Hi @HCLin ,

You cannot filter on Lookup columns - you need to do something like this (note you field names may need adjusting)

Filter(
   AddColumns(
      Contracts, 
      "Owner"
      'Owner Name'.Value
   ),
   Owner = User().FullName
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @WarrenBelz ,

 

Thanks for you reply first,

 

In my data, I created a "table" (called "Contracts") -> then created a "view" (called "All Contracts") (*view creating by selecting some specific fields from the table)

 

So if I want to show the "view" in my gallery, I have to write the formula with:

Filter(Contracts, 'Contracts (Views)'.'All Contracts' , (Other conditions))

*The first two items are for showing the view: "All contract"

 

And I have tried you suggestion, then add the two first items

Filter(Contracts, 'Contracts (Views)'.'All Contracts', 
   AddColumns(
      Contracts, 
      "Owner"
      'Owner Name'.Value
   ),
   Owner = User().FullName
)

But still got error showed "The function 'AddColumns' has some invalid arguments".

 

I am thinking the problem may also come from the "view" part, but haven't figured out yet.

@HCLin ,

What is your data source and why do you need the "view"?

Also as I mentioned the AddColumns needs valid field names - I do not know yours.

Hi @WarrenBelz ,

 

My data source is Common Data Service, and the reason I want to use "View", that's because it can custom fields.

From different role/position in a company, they don't need to see all fields, so we custom diff view for diff usage,

(ex: A view called "Quick Contracts" -> will only contain 5 main field;

a view called "Detail Contracts" may contain almost all field (may up to 30);

a view called "PI Contracts" will only contain the field PI concerned etc..)

 

and also we can set filter by field in the view directly, (ex: A View named "Active PI Contracts", so the filter in this view is -> Active = "True")

 

And the field name I tried is valid, so I thought the problem came from View.

 

I will also think about not using View.

 

Thank you for your reply!

PowerAddict
Super User II
Super User II

@WarrenBelz if I may chime in, @HCLin I have a few questions for you: 

1. Is Owner Name a custom field or are you using the Owner field? 

2. Can you not simply add this filter condition to the view itself? 

3. Also, using different views with different subsets of fields doesn't restrict visibility of the rest of the fields in a canvas app. You can control that by simply using labels to show the fields you want to show. 

Let us know the answers to these questions and we can help you further. 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

 

Thanks @PowerAddict ,

Good to hear from you.

I was a little confused with the view reference but was fairly sure it was irrelevant to the filter as this is directly on the data source - thanks for confirming this.
@HCLin , you can simply use ShowColumns to display the ones your want the user to see

ShowColumns(
   Filter(
      AddColumns(
         Contracts, 
         "Owner"
         'Owner Name'.Value,
      ),
      Owner = User().FullName
   ),
   "ColumnName1",
   "ColumnName2",
   "ColumnName3",
   "ColumnName4"
)

or simply restrict the view for/gallery to the ones you need.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

PowerAddict
Super User II
Super User II

@WarrenBelz glad to be back!

The reason I was stressing on the view is that filtering is handled way better with CDS views (no delegation) and this is a pretty standard filter (owner = current user). Let's see what the answers are as I like to avoid table shaping expressions since even though they don't result in delegation warnings, they are not delegable (what I call the invisible delegation). 

 

Hi @PowerAddict  and @WarrenBelz ,

 

Thanks for both your reply, I am a pretty new to PowerApps, so have lots need to explore.

 

For the questions,

1. The "Owner Name" is one of the field/column in my table

(* Image HCLin_1)

 

2. I am not really sure about this question, but let me show you how I deal with the filter in view,

(* Image HCLin_2)

 

Then inside the View I created,

(* Image HCLin_3)

 

The right bottom is the area for setting filter.

 

3. Ok, I got it, great point.

 

That's what the process I made it become that complex....

So the goal in my apps, is have a table shows the "view" I set, and restrict the "Owner Name" = User().FullName

 

Thanks for your help : ), and sorry that I seem to make a simple question very complicated😅

(It doesn't allow me to reply with the image inside the post 😞 )

 

 

@HCLin ,

@PowerAddict is the CDS expert here (I use SharePoint but pretty much all of it generally works on CDS). 

I am still surprised the filter does not work directly on your data source.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (94,792)