Filter a field which is Lookup datatype

[HCLin]

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 : ((((

[WarrenBelz]

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.

[HCLin]

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.

[WarrenBelz]

@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.

[HCLin]

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]

@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

[WarrenBelz]

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]

@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). 

[HCLin]

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 😞 )

[WarrenBelz]

@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.