Answered

Table Control - Filtering not working as expected

(0) Share

Report

Posted on by 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,

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Verified answer

Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@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 ) )

Was this reply helpful? Yes No
BB91 19 on at

Like (0)

Report
Copy link

Link copied!

@Amik that works. Thank you.
Could you explain why my Filter is not working as expected? Is there a bug?

Kind regards,
BB

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
CarlosFigueira Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
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 ) )

Was this reply helpful? Yes No
BB91 19 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
BB91 19 on at

Like (0)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
BB91 19 on at

Like (0)

Report
Copy link

Link copied!

@Amik thank you for the explanation.
@CarlosFigueira it works perfectly. Thank you.

Was this reply helpful? Yes No