Filter not functioning correctly when blank

[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

[SebS]

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

[Schody1982]

@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

[SebS]

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

[Schody1982]

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

[SebS]

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

[Schody1982]

@SebS 

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

[SebS]

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

[Schody1982]

@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