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 

View solution in original post

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 

View solution in original post

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
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 402 members 6,034 guests
Please welcome our newest community members: