cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KickingApps
Level 8

Filter table within collection

Scenario:

  • Two collections,
  • 3 controls 
    • Dropdown (colVehSelectedLocation, vehNum)
    • DatePicker - StartDate/Time
    • DatePicker - EndDate/Time

1.  colVehSelectedLocation                                2. colTodaysReservations

Location || VehNum                                            Location     || VehNumber         || StartTime      || EndTime  || AllDay   

A            || 123                                                             A                || 123                 || 0800              || 1200        || 0 (false)

A            || 234                                                             A                || 234                 || 0000              || 2359        || 1 (true)

A            || 567                                                             A                || 123                 || 1600              || 1700        || 0 (false)

 

For the dropdown, I want to Filter the Items to exclude for vehicles where AllDay in colTodaysReservations is true OR If

colTodaysReservations StartTime >= datePicker Start Time AND

colTodaysReservations EndTime<= datePicker EndTime.

The trick here is that the filter will have to lookup the veh number in the colTodaysReservations.  

So far, this works for only the first record that matches. For example, if user selects 0800 in date/time picker, veh 123 will not display in dropdown.  However, if user selects 1600, veh 123 is displaying when it shouldn't be.  

Filter(
     colVehSelectedLocation, vehNum <>
          Lookup(colTodaysReservations,

                 StartTime >= dtStartTime.SelectedDate +

                        Time(
                                Value(HourValue1.Selected.Value),

                                Value(MinuteValue1.Selected.Value),

                                0) &&              

              EndTime <= dtEndTime.SelectedDate +

                        Time(
                                Value(HourValue1.Selected.Value),

                                Value(MinuteValue1.Selected.Value),

                                0)
                ).VehNumber.Value
).VehNum

 

**Note - I have yet integrated formula to exclude vehicles if AllDay is true.  That needs to also be included.

Thank you.

@wyotim @RandyHayes  -- any thoughts?  Thank you, in advance, for taking a look.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter table within collection

@KickingApps So I couldn't figure this out in a straight-forward manner but I did manage a work around that seems to do the trick. 

 

I made a gallery (galHelperGallery) which uses colTodaysReservations as the data source. In the gallery, I have a text label titled lblConditionTest with the following:

 

// Check that the reservation start date time is before the chosen start date time
DateDiff(
    DateTimeValue(lblVehStartDateTime.Text),
    DateTimeValue(lblDatePickerStartDateTime.Text),
    Minutes
) >= 0 &&
// Check that the reservation end date time is after the chosen end date time
DateDiff(
    DateTimeValue(lblVehEndDateTime.Text),
    DateTimeValue(lblDatePickerEndDateTime.Text),
    Minutes
) <= 0 &&
// Check that the chosen start time is before and not equal to the chosen end time
DateDiff(
    DateTimeValue(lblDatePickerStartDateTime.Text),
    DateTimeValue(lblDatePickerEndDateTime.Text),
    Minutes
) > 0 &&
// Check that it is not an all day reservation
ThisItem.AllDay <> true

where

  • lblDatePickerStartDateTime and lblDatePickerEndDateTime are text labels with the selected date times from the respective date pickers

and 

  • lblVehStartDateTime and lblVehEndDateTime are text labels with the reservation start and end times respectively. (These labels were in the gallery.)

Then in the dropdown I have the following:

// Filter the Helper Gallery by items that have a true value in the Condition Test label
Filter( galHelperGallery.AllItems, lblConditionTest.Text = "true" )

Set the Value of the dropdown to VehNumber and it should work.

 

I tried a variety of approaches, including a similar one to the above in the dropdown itself, but kept running into the same issue you described. I finally made a gallery to help check against the conditions and decided to try the above approach and it worked out. So weird! I added an export of the test app I made so you didn't have to rely totally on my attempt at describing what I did.  Smiley Very Happy 

4 REPLIES 4
Super User
Super User

Re: Filter table within collection

@KickingApps So I couldn't figure this out in a straight-forward manner but I did manage a work around that seems to do the trick. 

 

I made a gallery (galHelperGallery) which uses colTodaysReservations as the data source. In the gallery, I have a text label titled lblConditionTest with the following:

 

// Check that the reservation start date time is before the chosen start date time
DateDiff(
    DateTimeValue(lblVehStartDateTime.Text),
    DateTimeValue(lblDatePickerStartDateTime.Text),
    Minutes
) >= 0 &&
// Check that the reservation end date time is after the chosen end date time
DateDiff(
    DateTimeValue(lblVehEndDateTime.Text),
    DateTimeValue(lblDatePickerEndDateTime.Text),
    Minutes
) <= 0 &&
// Check that the chosen start time is before and not equal to the chosen end time
DateDiff(
    DateTimeValue(lblDatePickerStartDateTime.Text),
    DateTimeValue(lblDatePickerEndDateTime.Text),
    Minutes
) > 0 &&
// Check that it is not an all day reservation
ThisItem.AllDay <> true

where

  • lblDatePickerStartDateTime and lblDatePickerEndDateTime are text labels with the selected date times from the respective date pickers

and 

  • lblVehStartDateTime and lblVehEndDateTime are text labels with the reservation start and end times respectively. (These labels were in the gallery.)

Then in the dropdown I have the following:

// Filter the Helper Gallery by items that have a true value in the Condition Test label
Filter( galHelperGallery.AllItems, lblConditionTest.Text = "true" )

Set the Value of the dropdown to VehNumber and it should work.

 

I tried a variety of approaches, including a similar one to the above in the dropdown itself, but kept running into the same issue you described. I finally made a gallery to help check against the conditions and decided to try the above approach and it worked out. So weird! I added an export of the test app I made so you didn't have to rely totally on my attempt at describing what I did.  Smiley Very Happy 

Super User
Super User

Re: Filter table within collection

@KickingApps 

Did @wyotim get you further on your issue?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
KickingApps
Level 8

Re: Filter table within collection

@wyotim - thanks for taking time to work on this. I’ll be applying this gallery (duct tape) solution. Very much appreciate your help!
KickingApps
Level 8

Re: Filter table within collection

@RandyHayes - thanks for checking. And yes; @wyotim helped me along!

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 375 members 5,383 guests
Please welcome our newest community members: