Table Control - Filtering not working as expected

[BB91]

Hallo community,

I am working on a Power App that uses a SharePoint list "Test List" as a data source for a table. The app allows users to filter the table by different criteria, such as Personalnummer, List Status, Name, and Abteilung. I am using the Filter function with delegable operations to achieve this: SharePoint - Connectors | Microsoft Learn

However, I am facing a weird issue that I don't understand. When I edit the app, I can see all the entries of the SharePoint list and the filtering works perfectly. But when I open the app as a user that has full access to the Sharepoint list, the table is empty and I have to type something in the Name and Abteilung text inputs and then delete it to see the entries. And the filtering works just fine. When I reset the input fields with a button, the entries disappear again.

I have noticed that the problem only occurs when I use the StartsWith function for the Name and Abteilung columns. When I remove this part of the formula, the filtering works fine with the other criteria.

Here is the formula that I am using for the Items property of the table:

Filter(
'Test List',
(Value(TextInputCanvas1.Value) = Blank() || Value(TextInputCanvas1.Value) = Personalnummer) && (DropdownCanvas1.Selected.Value = Blank() || 'List Status'.Value = DropdownCanvas1.Selected.Value) && StartsWith(Name,
TextInputCanvas2.Value
) && StartsWith(
Abteilung,
TextInputCanvas3.Value
)
)

Does anyone know why this is happening and how I can fix it?

Is there a problem with the StartsWith function or the SharePoint list?

Thank you for your help.

Kind regards,

BB

[Amik · Accepted solution]

@BB91 - try the revised version below:

Filter(
    'Test List',
    Len(TextInputCanvas1.Value) = 0 || Personalnummer = TextInputCanvas1.Value,
    Len(DropdownCanvas1.Selected.Value) = 0 || 'List Status'.Value = DropdownCanvas1.Selected.Value,
    Len(TextInputCanvas2.Value) = 0 || StartsWith(
        Name,
        TextInputCanvas2.Value
    ),
    Len(TextInputCanvas3.Value) = 0 || StartsWith(
        Abteilung,
        TextInputCanvas3.Value
    )
)

[BB91]

@Amik that works. Thank you.

Could you explain why my Filter is not working as expected? Is there a bug?

Kind regards,

BB

[Amik]

@BB91 - I cannot say whether this is a bug but certainly something I have noticed (when using either SharePoint or Dataverse as a db at least).

For whatever reason, the StartsWith function reads the TextInput control as Blank rather than Empty/Null. We cannot use an IsEmpty function on a Text Input control, but we can leverage the Len function as a workaround.

@CarlosFigueira might have better insight on this behaviour.

[CarlosFigueira · Accepted solution]

Thanks @Amik for the report. This seems like an issue with the modern text input control - if the default value (Value property) is set to "", its value will return <blank> instead of "" when the app is opened for playback. I'll forward this issue to the team that owns that control.

Meanwhile, another option would be to use the IsBlank function, which returns true for both blank values and empty text values:

Filter(
    'Test List',
    IsBlank(TextInputCanvas1.Value) || Personalnummer = TextInputCanvas1.Value,
    IsBlank(DropdownCanvas1.Selected.Value) || 'List Status'.Value = DropdownCanvas1.Selected.Value,
    IsBlank(TextInputCanvas2.Value) || StartsWith(
        Name,
        TextInputCanvas2.Value
    ),
    IsBlank(TextInputCanvas3.Value) || StartsWith(
        Abteilung,
        TextInputCanvas3.Value
    )
)

[BB91]

@Amik thank you for your quick reply. I will stay with your revised version for now.

[BB91]

@CarlosFigueira thank you for your answer. But as I mentioned in the beginning of this post IsBlank is not a delegable function to use for a Sharepoint list: SharePoint - Connectors | Microsoft Learn

I will stay with @Amik's revised version for now.

Kind regrads,

BB

[Amik]

@BB91 - you have misunderstood here, IsBlank is being used on the selected value of control.

Delegation is associated to whether an operation can be "delegated" back to a data source for processing rather than the device which triggers that operation.

IsBlank(DropdownCanvas1.Selected.Value) is perfectly fine here.

[BB91]

@Amik thank you for the explanation.

@CarlosFigueira it works perfectly. Thank you.