Gallery filter lookup value from sharepoint list problem

[buncis_cakep]

Hi all,

I want to make knowledge base powerapps for user with certain constraint.

Here are my share point list:

KB:

FilterJobTitle

Both of two list above lookup from this list:

SubCategory

So what i want at powerapps gallery, when user login using their 365 account, they can view *only* assigned KB from FilterJobTitle list.

I already tried when the KB list not lookup type(Text only), it works perfectly.

But not with lookup type list.

Here is the items at gallery:    

    Filter(KB;'SubCategory' in filterCat.SubCategory)

filterCat is a collection with specific subcategory already filtered with job title.

It Shows error "your formula didnt have 'SubCategory' column with record type.

Thanks...

[v-qiaqi-msft · Accepted solution]

Hi@buncis_cakep,

Could you please tell me that:

1. What is the column type of the 'Sub Category' and 'Category' from the 'SubCategory' list, are both of them the Text type?
2. From your third screenshot about the 'SubCategory' list, it seems that the 'Category' is already a Lookup column. If so, which list do you look up for the 'Category'?
3. Is there any primary key in your second SP list 'FilterJobTitle'?

And I have a test on my side, please take a try as below.

Here is my three SP lists.

Combine that with your description, your formula works only for the Text types but not for the Lookup type columns. I want to explain to you that the Lookup column will be generated as a Table containing Id and Value,

Here is a formula how I create my collection:

Collect(filterCat,Filter(FilterJobTitle,JobTitle="Operations").'Sub Category')

Please do make sure that you have connected all the three SP lists to your app.

Set the Items property of the Gallery as below:

Filter(
    KB,
    'Sub Category'.Value in Concat(
        filterCat,
        'Sub Category'.Value,
        ","
    )
)

Note: I want to tell you that you should make the data types on both sides of the operator "in" are matched. For that the 'Sub Category' is a Lookup column which is actually generated as a Table, you should convert it into the Text type because that the 'Sub Category'.Value is a Text type.

Hope it could help.

Best Regards,

Qi

[buncis_cakep]

Hi, your code works well.

Could you explain more about your code?

Filter(
    KB,
    'Sub Category'.Value in Concat(
        filterCat,
        'Sub Category'.Value,
        ","
    )
)

[v-qiaqi-msft]

Hi@buncis_cakep,

I have reproduce the error happened to you and I want to explain to you that you have something wrong with the invalid data type.

'Sub Category' is a Lookup column which is actually generated as a Table by default. We used to lookup for a specific value by using the ".Value" which is actually a Text type value.  And filterCat is a nested table you can see from my screenshot. 

Now we should keep the data type right to the operator "in" a Text type because "'Sub Category'.Value" is a Text type. Then we should convert "filterCat.SubCategory" into a Text type, so you should use the Concat() function. That's why I write the formula.

Hope it helps.

Regards,

Qi

[buncis_cakep]

IC...

Concat(filterCat,'Sub Category'.Value,",")

is the keypoint...convert table to text delimited by ","...

Many thanks!