CountRows with a filter and the need to look up a value

[Hollie0316]

My data source is sharepoint.  My powerapp allows for requests to be submitted. My goal is to have the submit button visible only when specific data is filled in, in two different galleries. When the end user makes an edit and saves it, the data is patched. Once they fill in all data and save it, it should trigger the submit button visibility to be true. Currently the submit button is viewable even though they are missing dates. I believe my error is that my code is counting rows even if there is no value and I only wanted it to count rows with a date added.

• I need it to be delegable.
• I need it to filter through and make sure my column: RefID = Text(varRecordG3.ID).
• I need it to look through all filtered records and count only the rows that have a date inputted for Start Date and End Date

Here is my current code:

If(

    CountRows(

        Filter(

            SPlist,

            RefID = EG_IDLabel.Text

        )

    ) = 0 || IsBlank(EGChargetoRB_PP.Selected.Value) || CountRows(

        Filter(

            SPlist,

            RefID = EG_IDLabel.Text

        )

    ) <> CountRows(

        Filter(

            SPlist,

            RefID = EG_IDLabel.Text

        ).EndDate

    ) || CountRows(

        Filter(

            SPlist,

            RefID = EG_IDLabel.Text

        )

    ) <> CountRows(

        Filter(

            SPlist,

            RefID = EG_IDLabel.Text

        ).StartDate

    ),

    false,

    true

)

[james1994 · Accepted solution]

Hey @Hollie0316,

You're right that your formula is counting all rows regardless of date as your third and fifth CountRows are not filtering out dates that are blank. Try this: 

If(
    CountRows(Filter(SPlist, RefID = EG_IDLabel.Text)) = 0 || 
    IsBlank(EGChargetoRB_PP.Selected.Value) || 
    CountRows(Filter(SPlist, RefID = EG_IDLabel.Text)) <> CountRows(Filter(SPlist,RefID = EG_IDLabel.Text, EndDate <> Blank())) || 
    CountRows(Filter(SPlist,RefID = EG_IDLabel.Text)) <> CountRows(Filter(SPlist,efID = EG_IDLabel.Text, StartDate <> Blank())),
    false,
    true
)

  I updated 2 of your CountRows to filter on dates that are not blank.

// End Date not blank
CountRows(Filter(SPlist, RefID = EG_IDLabel.Text)) <> CountRows(Filter(SPlist,RefID = EG_IDLabel.Text, EndDate <> Blank()))

// Start Date not blank
CountRows(Filter(SPlist,RefID = EG_IDLabel.Text)) <> CountRows(Filter(SPlist,efID = EG_IDLabel.Text, StartDate <> Blank())),

CountRows is also a non-delegable function, so you will have potential issues with it if your filter ever exceeds your data row limit in the app (500 by default). If you know it won't, then you probably won't need to worry about it too much. 

[Amik]

@Hollie0316 -

I can understand that @james1994 is correcting your existing code. However, I would suggest focusing on what you're trying to do, rather than troubleshoot whatever your existing solution is.

You have stated that you need this:

• I need it to be delegable.
• I need it to filter through and make sure my column: RefID = Text(varRecordG3.ID).
• I need it to look through all filtered records and count only the rows that have a date inputted for Start Date and End Date
  
  

As I understand it, your Button control should only be Visible if the count of all filtered rows in your data matches the count of all filtered rows in your data where the start and end dates are not blank.

As you're aware, the CountRows function is not delegable with SharePoint. There are no workarounds for functions which are not delegable. You can hide the message with workarounds, but the delegation will persist. However, the Filter function is delegable with SharePoint. If you use the With function to create a temporary filtered table which returns less than 2,000 rows, you can then perform your non-delegable CountRows function on that smaller subset of data.

For example we could have population data that has 50,000 rows of people per district. However, if we know for certain that each neighbourhood within those districts will return less than 2,000 rows, we can safely use the With function to create a temporary table which is filtered on a specific neighbourhood. We can then use any non-delegable function on top of that smaller subset of data.

If we apply this logic to your scenario, if you can be certain that filtering on the RefID field will return less than 2000 rows, we can apply the below to the Visible property of your Button control:

With(
    {
        _prefiltered_data_all: Filter(
            SPlist,
            RefID = EG_IDLabel.Text
        ),
        _prefiltered_data_dates_completed: Filter(
            SPlist,
            RefID = EG_IDLabel.Text,
            StartDate <> Blank() && EndDate <> Blank()
        )
    },
    CountRows(_prefiltered_data_all) = CountRows(_prefiltered_data_dates_completed)
)

Notes:

Note the absence of the IF function. Given the Visible property only accepts a Boolean, there is no need to wrap an IF function around a formula which is already going to return a Boolean anyway.

Because I do not understand the purpose of checking if your filtered data source contains any rows, or the purpose of checking if "EGChargetoRB_PP" Dropdown/ComboBox is blank, I have excluded them from the suggested formula for the time being.

[james1994]

I would recommend @Amik's solution, if you want something much cleaner (though I would combine the With function). 

It's very interesting to see different approaches. I definitely would not have done it the way you did @Amik. I would have instead used the gallery to check if the dates had been filled in the controls.

[Amik]

@james1994 - seeing different solutions to tackle the same problem is a key reason I return to this forum. The solution I provided above is the way I would do it and the most simplest. The key focus should be on mitigating delegation issues with non-delegable functions like CountRows.

[WarrenBelz]

@Hollie0316 ,

Firstly @Amik has done the hard work here, so please accept that solution. One small thing on Delegation is the Not <> is not Delegable, so you can do this

With(
   {
      _Data:
      Filter(
         SPlist,
         RefID = EG_IDLabel.Text
      )
   },
   With(
      {
         _Dates:
         Filter(
            _Data,
            StartDate <> Blank() && EndDate <> Blank()
         )
      },
      CountRows(_Dates) = CountRows(_Data)
   )
)

[Amik]

oops - thanks for pointing out the mistake @WarrenBelz 

@Hollie0316 / @james1994 - for context:

Image ref: https://learn.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-op... 

[james1994]

Not sure if you updated your original solution but what was the non-delegable query you had used? I don't remember seeing you using IsBlank() or Not(). If it's because of the "<>" operator, isn't this delegable for Dates?

[WarrenBelz]

@james1994 ,

<> is not (equals) - Dates are not Delegable for this, although for some reason I only got the yellow triangle and not the blue lines when I tested it here.