Combine Filter and If Statement

[skewa1]

I have a PowerApps. In a Text Box, i would like is to filter all records for those with similar reference ID (e.g. REFID1). After this, i would like to do an IF statement that loops into all those filtered results, and in the Status column, if any one or more of the results are still in Open Status, then the box will be open, if not then closed.

[Amik]

@skewa1, as I understand it.

You have a data source (SharePoint? Dataverse?) which has a single line text column called "Reference ID". The data source also has a column called "Status" (Free text? Choice?)

You have a Gallery connected to this data source.

You have a TextInput box. If a user enters a reference ID which matches any existing reference ID in the data source, the Gallery should be filtered based on the entered reference ID.

What you want:

You want a button which will update the value of the Status column to "Closed" if the Status column value is "Open" for all records associated to the reference ID.

Is that right?

[skewa1]

Apologies for not being clearer.

SharePoint list 1 has a few columns, from which I have connected to a Gallery in PowerApps.(I will call it Gal1)

SharePoint List 2 also has few columns, This includes a RefID column (type is number) and Status Column (type is choice) with "Open" or "Close" choice. The Numbers are allocated to a record in RefID through a variable created in SharePoint list 1, from the ID Column. Thus relating the record from SharePoint list 1 to a record or records in SharePoint list 2 (SharePoint list 1 ID = SharePoint list 2 RefID). I have created a Gallery for this too. (I will call it Gal2)

When I select a record in Gal1 and navigate to Gal2, all new records created therein have a RefID = ID column from the record in Gal1 (the record selected and navigated from). I have created multiple records in Gal2 that have the same RefID as the ID of Record in Gal1. Say Gal 1 Record ID = 47, If select and navigate to Gal2 and add 5 records, all those records will have RefID as 47.

Question 1: In Gal 1. on the record / item, I want to show if (a) how may records in Gal2 relate to that particular record (in Gal1). [i.e. How many records in Gal2 have the same RefID as Gal1 Record ID]

Question 2: In Gal 1. on the record / item, i want to find out, of those records relating to the Gal1 Record (given by the answer to question 1, How may are of status "Open")

e.g: we should have something like Total Records: 5     Open Records: 2

Would Really appreciate some help

[Amik · Accepted solution]

@skewa1 - to display a count in your Gallery of how many RefIDs in List 2 are associated to the Record ID in List 1, you can use:

"Total Records: " & CountRows(
    Filter(
        ‘SharePoint List 2’,
        RefID = ThisItem.'Record ID'
    )
)

To display a count in your Gallery of the number of RefIDs associated to the Record ID in List 1 with a Status of "Open", you can use:

"Open Records: " & CountRows(
    Filter(
        ‘SharePoint List 2’,
        RefID = ThisItem.'Record ID',
        Status.Value = "Open"

    )
)

Note because SharePoint is your DataSource, the CountRows function is not delegable.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved.

If you like my response, please give it a Thumbs Up.