Filter not functioning correctly when blank

(0) Share

Report

Posted on by Schody1982

Hi guys,

In my powerapp, the gallery is filtered using the following code:

SortByColumns(Filter('[ext].[pwr_OwnerBookingsAll]', ParkName = Left(ParkDropdown.Selected.Value, Len(ParkDropdown.Selected.Value) - 5), Or(PitchFilterTextbox.Text = "", PitchFilterTextbox.Text in LookUp(Filter('[dbo].[VanBasicDetails]', PARK_CODE = Right(ParkDropdown.Selected.Value, 2)), VAN_ID = LodgeNumber).PITCH_DESC) && (Status = "Pending" Or ShowAdded.Value)),"StartDate", SortOrder.Ascending,"LodgeNumber", SortOrder.Ascending)

The problem is that the results only show when the 'show added' checkbox is ticked, if unchecked, nothing is displayed.

However, if I remove the pitch filter, so I'm left with:

SortByColumns(Filter('[ext].[pwr_OwnerBookingsAll]', ParkName = Left(ParkDropdown.Selected.Value, Len(ParkDropdown.Selected.Value) - 5) && (Status = "Pending" Or ShowAdded.Value)),"StartDate", SortOrder.Ascending,"LodgeNumber", SortOrder.Ascending)

This works but I can't filter the results.

I'm clearly missing something and was hoping anyone could shed a light on what I'm doing wrong.

Many thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

SebS 4,616 Moderator on at

Like (0)

Report
@Schody1982

The code do not handle the blanks means when one of the controls is blank it don't know what to do.

You should handle the blanks in the code I hope I did not make any mistake :

SortByColumns( Filter('[ext].[pwr_OwnerBookingsAll]', IsBlank(ParkDropdown.Selected.Value) || ParkName = Left(ParkDropdown.Selected.Value, Len(ParkDropdown.Selected.Value) - 5), IsBlank(PitchFilterTextbox.Text) || PitchFilterTextbox.Text in LookUp(Filter('[dbo].[VanBasicDetails]', IsBalnk(ParkDropdown.Selected.Value) || PARK_CODE = Right(ParkDropdown.Selected.Value, 2)), VAN_ID = LodgeNumber).PITCH_DESC) && (Status = "Pending" || Isbalnk(ShowAdded.Value) || ShowAdded.Value)) ,"StartDate", SortOrder.Ascending,"LodgeNumber", SortOrder.Ascending)

Was this reply helpful? Yes No
Schody1982 8 on at

Like (0)

Report

@SebS , Thank you so much for taking the time to reply. Unfortunately it didn't work but I really appreciate you taking the time to have a look.

The blanks should be skipped to allow for it to view all records.

Or(PitchFilterTextbox.Text = "", PitchFilterTextbox.Text in LookUp(Filter(VanBasicDetailsCollection, PARK_CODE = Right(ParkDropdown.Selected.Value, 2)), VAN_ID = LodgeNumber).PITCH_DESC))

I think it's because of this:

SortByColumns(Filter('[ext].[pwr_OwnerBookingsAll]', ParkName = Left(ParkDropdown.Selected.Value, Len(ParkDropdown.Selected.Value) - 5), Or(PitchFilterTextbox.Text = "", PitchFilterTextbox.Text in LookUp(Filter('[dbo].[VanBasicDetails]', PARK_CODE = Right(ParkDropdown.Selected.Value, 2)), VAN_ID = LodgeNumber).PITCH_DESC) && (Status = "Pending" Or ShowAdded.Value)),"StartDate", SortOrder.Ascending,"LodgeNumber", SortOrder.Ascending)

Status is part of the Ownerbookingsall and not Vanbasicdetails, would this make a difference given where it's placed?

Many thanks again

Was this reply helpful? Yes No
SebS 4,616 Moderator on at

Like (0)

Report
@Schody1982

it's always hard to debug filters without Data Source 🙂

Try this approach introducing If condition :

SortByColumns( Filter( '[ext].[pwr_OwnerBookingsAll]', And( ParkName = Left(ParkDropdown.Selected.Value, Len(ParkDropdown.Selected.Value) - 5), (Status = "Pending" Or ShowAdded.Value), If( !IsBlank(PitchFilterTextbox.Text), PitchFilterTextbox.Text in LookUp( Filter('[dbo].[VanBasicDetails]', PARK_CODE = Right(ParkDropdown.Selected.Value, 2)), VAN_ID = LodgeNumber ).PITCH_DESC, true ) ) ), "StartDate", SortOrder.Ascending, "LodgeNumber", SortOrder.Ascending )

Was this reply helpful? Yes No
Schody1982 8 on at

Like (0)

Report

@SebS
Hi mate,
Thanks again for your reply, no error's this time but it's showing when 'show added' is selected which shows all completed jobs but not the one's that are still pending.

Above shows the pitch filter blank and the 'show added' as checked which shows the completed job.
Below shows the pitch filter blank and the 'show added' as unchecked which should show all pending jobs but is blank.

Hope that helps and I'm really grateful again for your replies.

Was this reply helpful? Yes No
SebS 4,616 Moderator on at

Like (0)

Report
@Schody1982

Check if this modification change result :

SortByColumns( Filter( '[ext].[pwr_OwnerBookingsAll]', And( ParkName = Left(ParkDropdown.Selected.Value, Len(ParkDropdown.Selected.Value) - 5), If( ShowAdded.Value, true, Status = "Pending" ), If( !IsBlank(PitchFilterTextbox.Text), PitchFilterTextbox.Text in LookUp( Filter('[dbo].[VanBasicDetails]', PARK_CODE = Right(ParkDropdown.Selected.Value, 2)), VAN_ID = LodgeNumber ).PITCH_DESC, true ) ) ), "StartDate", SortOrder.Ascending, "LodgeNumber", SortOrder.Ascending )

Was this reply helpful? Yes No
Schody1982 8 on at

Like (0)

Report

@SebS
Same result I'm afraid mate, doesn't show any pending.

Was this reply helpful? Yes No
SebS 4,616 Moderator on at

Like (0)

Report
@Schody1982

So annoying maybe when we get Or that will sort it out or it's due to something ales than the validation 😞

SortByColumns( Filter( '[ext].[pwr_OwnerBookingsAll]', And( ParkName = Left(ParkDropdown.Selected.Value, Len(ParkDropdown.Selected.Value) - 5), Or( And(Status = "Pending", !ShowAdded.Value), ShowAdded.Value ), If( !IsBlank(PitchFilterTextbox.Text), PitchFilterTextbox.Text in LookUp( Filter('[dbo].[VanBasicDetails]', PARK_CODE = Right(ParkDropdown.Selected.Value, 2)), VAN_ID = LodgeNumber ).PITCH_DESC, true ) ) ), "StartDate", SortOrder.Ascending, "LodgeNumber", SortOrder.Ascending )

Was this reply helpful? Yes No
Schody1982 8 on at

Like (0)

Report

@SebS
If it helps, this is the original code prior to introducing the text filter which worked with the 'show added' text box.

SortByColumns(Filter('[ext].[pwr_OwnerBookingsAll]', ParkName = Left(ParkDropdown.Selected.Value, Len(ParkDropdown.Selected.Value) - 5) && (Status = "Pending" Or ShowAdded.Value)),"StartDate", SortOrder.Ascending,"LodgeNumber", SortOrder.Ascending)

This is the code I used for the text filter on a previous app but redirected to the right data sources and fields:

Or(PitchFilterTextbox.Text = "", PitchFilterTextbox.Text in LookUp(Filter(VanBasicDetailsCollection, PARK_CODE = Right(ParkDropdown.Selected.Value, 2)), VAN_ID = LodgeNumber).PITCH_DESC))

So essentially I need to combine them both together.
On the app where it works, it looks like this:

Filter(SortByColumns(Filter('[ext].[pwr_GuestPassesAll]',PassType <> "Car" ,STATUS <> "Pending deletion"),"LodgeNumber",SortOrder.Ascending,"PassType",SortOrder.Ascending, "FirstName", SortOrder.Ascending, "LastName", SortOrder.Ascending), Park=Left(ParkDropdown.Selected.Value,Len(ParkDropdown.Selected.Value)-5), Or(PitchFilterTextbox.Text = "", PitchFilterTextbox.Text in LookUp(Filter(VanBasicDetailsCollection, PARK_CODE = Right(ParkDropdown.Selected.Value, 2)), VAN_ID = LodgeNumber).PITCH_DESC))

I hope that makes a bit more sense.
Many thanks

Was this reply helpful? Yes No