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

Search with Filtering the whole Excel Table with particular word

Hi,

 

Its for building a safety audit document as per the site survey carried out. I am a beginner in Powerapps too.

 

I have a data source where in will be searching the search box with a project name/Job Number.

 

But I want to search the whole Table - that has almost 45 columns and 1500 rows, in such as way that only projects with "Open" comments should appear while searching a project name/ Job number. This is so that the team doesnt have to work on the data that already has other comments written such as "Satisfactory or Not Applicable or any other comments".  Below is screenshot of the excel data sheet connected from onedrive.

 

SANJURK_0-1593841849761.png

 

 

Below is the screenshot of the searchbox and the how the information on the screen appears.

 

SANJURK_1-1593842430710.png

 

 

I have attached the files too -  to give a clear picture

17 REPLIES 17

Hi @WarrenBelz ,

 

Thanks for your effort. I did try with few more added in the code and it only returned just one. I have almost 28 columns and I think the code would work only for two columns and not more than that. Below is what I did and I got only one Project, when it had to return all the projects.

 

What I trying to do is like as team members go to the edit form and close each project, the row in which a project appears satisfactory will not appear in the list. I have just shown an example - the yellow marked will not come in the list it has no "Open" comments that the team has to work on. This in turn will not appear too.

SANJURK_0-1593943537005.png

 

The code I entered, sorry that I am too basic in this.

 

SortByColumns(
    Filter(
        [@OnlyOpen],
        (
            "Open" in '1.Conformance' ||
            "Open" in '2.Conformance'
        ) &&
        (
            "Open" in '3.Conformance' ||
            "Open" in '4.Conformance'
        ) &&
        (
            "Open" in '5.Conformance' ||
            "Open" in '6.Conformance'
        ) &&
        If(
            IsBlank(TextSearchBox1.Text),
            true,
            TextSearchBox1.Text in 'Unit Number' ||
            TextSearchBox1.Text in Building
        )
    ),
    "Unit_x0020_Number",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

The code only returned just one project. 

Hi @SANJURK ,

This is where you need to understand the code you are writing. I provided the basic syntax, but the key here are the bracketing of the and && or || combinations.

SortByColumns(
    Filter(
        [@OnlyOpen],
        (
            "Open" in '1.Conformance' ||
            "Open" in '2.Conformance' ||
            "Open" in '3.Conformance' ||
            "Open" in '4.Conformance' ||
            "Open" in '5.Conformance' ||
            "Open" in '6.Conformance'
        ) &&
        If(
            IsBlank(TextSearchBox1.Text),
            true,
            TextSearchBox1.Text in 'Unit Number' ||
            TextSearchBox1.Text in Building
        )
    ),
    "Unit_x0020_Number",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

Do you understand now what has to be done for the rest?

 

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.

View solution in original post

Thanks @WarrenBelz ,

 

That really worked.Really appreciate your effort.

 

Sorry about starting a new post, it was not to confuse different methods. I just tried another method. I will have a column at the end of the excel to count the Number of Conformance at the end - Count Conformance Column which counts the Number of "OPEN" in one row.

 

And then filter the Gallery display with those projects greater than 0, rather than filtering all the 28 columns. I was trying on how to filter that particular column that just has a number only - greater than Zero.

 

I am also doubtful, once the team close the OPEN, will Power apps keep pulling up the information from excel sheet every time to refresh its data. Will it be instant

That's good @SANJURK ,

I cannot answer too many questions on the performance of Excel as I do not use it.

Would it be possible if you could let me know how to filter just a value greater than zero on particular column in the gallery.

Hi @SANJURK ,

Filter(
   YourListName,
   YourColumnName>0'
)

Hi @WarrenBelz 

 

I tried the below but says error. I had tried earlier, but couldn't get through.

 

SANJURK_1-1594205133786.png

 

 

 

Filter([@Table1],'Conformance Count'<0)

 

Hi @SANJURK ,

Firstly you have less than zero in your formula, but that will simply produce no results. If 'Conformance Count' is a numeric field, that formula is valid. Please confirm this is the field type and also where you are putting that formula (it needs to be on the Items of a gallery).

 

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (3,033)