Unanswered

Record count in datable based on search criteria

(0) Share

Report

Posted on by Community Power Pla...

I need to search a datable with the current results in the data table.

in a specific field. “Status”

if any of the rows contain the word “open” in the Status field. Return 1. In a text box.
but only in the data table results,

not the entire dataset.

so the the textbox will either display 0 or 1

dave

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@Anonymous

Unlike a gallery where you can use CountRows(Gallery.AllItems) there is no similar .AllItems for a datatable. You need to wrap CountRows around the items property of the datatable to get the number of rows in it.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Ok but I want to count the rows based on a search criteria. Status = “open”. Can I do that in a gallery maybe?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Delegation is my problem in a dataset source. More than 2000 records to search through.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Hi @Anonymous

I used the same filter criteria for the Items property of the gallery and the datatable in the example below. It is a SharePoint list so I used a delegatable Filter + StartsWith() to obtain the results as Search() is not delegatable in SharePoint. My filter selects for Company name, first name or last name in a list of fictitious contacts. As you can see, it has selected any of those columns beginning with "fa". Both the gallery and the datatable have the same number found, in this case 11 rows, but the formula for counting the gallery is simpler than the one to count the datatable.

To answer your question, you can use either one. You can always format a gallery to look identical to a datatable and it will be much more flexible and have more features.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@Anonymous

Regardless of how you obtain the table to supply the items in your datatable or gallery, if you wrap CountRows() around it, you will get a number for the Items in it. Since I have seen that you use SQL for your datasource, you can do the same by wrapping CountRows() around your Search formula. In SQL, Search() is delegatable, but in PowerApps it isn't so if your filtered count is >2000, a label with CountRows around your Search() shows 2000 it most likely means that you have more than 2000 items in your search. If it is less than 2000 then it will accurately reflect the number of rows in the search.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

this is what i'm currently using:
I changed my source to a view, the view contains less than 2000 records.
But still not working ?
its almost as if countrows is not re-calculating correctly ?

If(CountRows(
        Filter('[dbo].[v_nsc_trackcode_Dataentry_open]',
            Status = "Open",
            ID_Racfid = lbl_Racfid_ms.Text
        )
    )> 1,Set(TheVar,Now());Reset(txt_ReOpenedDate)
;Navigate(scrn_OpenIssue,Fade);
    Patch('[dbo].[t_nsc_trackcode_assigned_DataEntry]',
    LookUp('[dbo].[t_nsc_trackcode_assigned_DataEntry]',
    NSC_Id = Value(txt_NextId.Text)),{Status:"In Progress"})

)

Was this reply helpful? Yes No