cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
buncis_cakep
New Member

Gallery filter lookup value from sharepoint list problem

Hi all,
 
I want to make knowledge base powerapps for user with certain constraint.
 
Here are my share point list:
KB:
 
buncis_cakep_3-1609729582730.png

 

FilterJobTitle
 
buncis_cakep_4-1609729668916.png

 

 


Both of two list above lookup from this list:
SubCategory
 
buncis_cakep_5-1609729711149.png

 

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...
1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

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.

71.png

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

 

 

70.png

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.

72.png

Hope it could help.

Best Regards,

Qi

 

View solution in original post

4 REPLIES 4
v-qiaqi-msft
Community Support
Community Support

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.

71.png

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

 

 

70.png

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.

72.png

Hope it could help.

Best Regards,

Qi

 

View solution in original post

buncis_cakep
New Member

Hi, your code works well.

 

Could you explain more about your code?

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

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'.Valueis 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
New Member

IC...

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

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

 

Many thanks!

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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (40,480)