Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Filter items in gallery

(2) ShareShare
ReportReport
Posted on by 289
Goal is to filter table 'Sites' where column 'ParentAccount' = the field 'ParentAccount' in the variable varSelectedCompany set using OnChange in the combo box 'cmbSearchCompanies' AND where 'Active' is true AND where 'SiteType' = Customer.
 
During testing, I find the simple mode works (just selecting the right sites based on the company selected in the combobox. That's this:
 
Filter(Sites, ParentAccount = varSelectedCompany.'Account Name')
So, I got it basically working, then decided to get what I really want.
 
Filter(Sites, ParentAccount = varSelectedCompany.'Account Name' && Active = true && SiteType = Customer)
 
I get the error: Incompatible types for comparison. These types can't be compared: Table, Control.
 
The error is here: SiteType = Customer.
 
SiteType is a choice field. Pretty sure this is the issue, although I've found some info suggesting different explanations and methods, none seem to work.
 
I've searched ms support pages and cannot find any help either.
 
So if someone has a thought or a direction to push I would appreciate it.
  • Verified answer
    STS1SS Profile Picture
    STS1SS 289 on at
    Filter items in gallery
    This works... I just realized I had not closed this so If anyone is looking this worked for me.
     
    //THIS WORKS
    Filter(colCompanySites, ParentAccount = varSelectedCompany.'Account Name' && Active && "Customer" in Concat(SiteType, Value))
     
    This bit was the issue and worked like this:  "Customer" in Concat(SiteType, Value)
  • STS1SS Profile Picture
    STS1SS 289 on at
    Filter items in gallery
    OK, I think the issues is because the choice column allows multiple selections.
     
    Another issue is the 'SiteType' column doesn't show up in intellisense... EG: when I try to reference it 
     
     
    Am I missing something? Why can't I reference the column?
  • STS1SS Profile Picture
    STS1SS 289 on at
    Filter items in gallery
    Appreciate the look. Trying to clarify the issue here...
     
  • STS1SS Profile Picture
    STS1SS 289 on at
    Filter items in gallery
    @FLMike,
     
    So, I guess I did not do a very good job explaining. And for some reason recently I am not able to properly format text as code snippets etc, images, any of that, not sure what or when it changed, so I apologize for he plain text look.
     
    I'll describe this as best I can:
     
    Concept is we have CUSTOMERS (Accounts) which have SITES (sites), and those SITES have ASSETS (Assets). This Canvas App will allow user to select a CUSTOMER, then filter SITES, and ultimately populate a gallery with ASSETS.
     
    Regarding 'Customer' and how we set the value: this is a choice column in the Dataverse table 'Sites'. A 'Site' is a record in the table 'Sites' and the column 'SiteType' is a choice column; one choice can be "Customer" (other choices: Vendor, Partner, Division, Contractor etc.) We manitain that list of choices as a dataverse choice 
     
     
     
    1. I have a combo box which offers 'Companies' which are records in the 'Accounts' table

     
    2.  When a user selects a company, I use OnChange to set a variable: 
    If(IsBlank(cmbSearchCompanies.Selected),"",Set(varSelectedCompany, cmbSearchCompanies.Selected))
     
    3. A gallery 'galSites' populates by filtering sites using the column 'Account name'
    4. In it's most basic iteration this works exactly as intended. 
    Filter(Sites, ParentAccount = varSelectedCompany.'Account Name')
     
     
    5. When I try to further filter sites by only showing 'Active' sites that are 'Customers' I have an issue.
     
    This works:
     
    Filter(Sites, ParentAccount = varSelectedCompany.'Account Name' && Active)
     
    But this doesn't:
     
    Filter(Sites, ParentAccount = varSelectedCompany.'Account Name' && Active = true && SiteType=26700001)
    Now, the number there represents the value of that choice. In other code I've found I can use the text "Customer",
    and sometimes I have to use the value, so I've tried all I can think of.
     
    Also, I have had several instances where the code is right, doesn't work, then later, maybe hours, maybe days, after
    setting that issue aside I return to find it works fine with nothing changed.
     
    Thanks for looking, I appreciate it.
     
     
     
     
     
  • STS1SS Profile Picture
    STS1SS 289 on at
    Filter items in gallery
    @WarrenBelz, I tried that and get the same error. Thanks for the reminder on the boolean.
  • Suggested answer
    FLMike Profile Picture
    FLMike 31,580 on at
    Filter items in gallery
    Hi,
     
    So, I am not sure how Site Type would equal the Customer , but that's not ;-) up to me.
     
    However, 
     
    Is this single or multi-choice Choice Field?
     
    And you have to compare a Value OF the Choice to either the Value of something representing a SINGLE instance of the Choice
    Or you need to compare the Text OF the Choice to the Text in one of your controls
     
    Understanding he above is for Single Choice Multi-Choice is worse
     
    Can you please explain what Customer is? and how it gets set?
     
    Here is an Example
    I created a SharePoint List with a single select Choice Column Called TestChoice
     
    Now in my App, which is a Canvas App.
    I have a DropDown where the Items are set to the Choice Field, so I can select them.
    The drop downs name is TestChoiceDropDown
     

    In my drop down I have this code in the Items property so it lists all the possible choices in TestChoice, which I have 3
    Choices('Sample - Multi-Person Unique Project List'.TestChoice)
     
     
    Now I haven't filtered my Table yet (you are using a gallery doesn't matter same thing to do)
     
    Now I want to use the Drop Down to Filter my rows in the Table
     
    In My Table Items property I change it to this
    Filter('Sample - Multi-Person Unique Project List',
                             TestChoice.Value = TestChoiceDropDown.Selected.Value)
     
    So now when I have Choice 1 selected the Table is filtered.
     
    So that is how you do it.
     
     
     
     
  • Suggested answer
    WarrenBelz Profile Picture
    WarrenBelz 145,580 on at
    Filter items in gallery
    Hi STS1SS
    I suspect SiteType is a Choice field and you need to compare it with a String, so
    Filter(
       Sites, 
       ParentAccount = varSelectedCompany.'Account Name' && 
       Active && 
       SiteType.Value = "Customer"
    )
    You also do not need = true when dealing with Boolean values.
     
    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)     Visit my blog Practical Power Apps    Buy me a coffee

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,580

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,909

Leaderboard