Distinct filtered SORTED gallery with delegation

[NickiPT]

I have a "parent" list that collects the basic details about someone's foreign travel trip (name of traveler, reason for travel) and a "child" list that stores the individual legs of their trip (location and dates), where the ID of the parent is stored in a column called MasterID. Here is a screenshot of the child list (we have to use SharePoint as our data source):

In my Power App I want a filtered gallery that shows current and upcoming trips, and I want to be able to sort it by 'Date Outgoing Departure' in descending order...but I also want to group the results so each trip has only one row in the gallery. That row will concatenate the values of all of the countries they are visiting and find the date range of their trip. For example (and yes, I know that only MasterID #1 is current or upcoming travel as of today's date but let's pretend I posted this question in early November):

MasterID	Countries Visiting	Dates of Travel	Status464
1	France, United Kingdom of Great Britain and Northern Ireland (the), Antarctica, Australia	11/17/2023 - 1/4/2024	Submitted
2	Italy	11/27/2023 - 11/30/2023	Submitted
3	Angola	11/14/2023 - 11/29/2023	Submitted

What I cannot figure out is how to accomplish what I want without worrying about delegation. It seems like if I use "Distinct" or "Collect" or "GroupBy" everything is going to be subject to delegation. I don't expect for there to ever be more than 500 trips in our system that are occurring currently or in the future, but I'm not sure of how to correctly filter the data AND get the distinct values I'm looking for. Maybe I'm misunderstanding delegation.

Here's what I have right now...

Items property of my gallery:

With(
    {
        wItineraryFiltered: Filter(
            'usm464-travelitinerary',
            Status464 = "Submitted" And 'Date Return Arrival' > DateAdd(
                Today(),
                -1,
                TimeUnit.Days
            )
        )
    },
    Distinct(
        wItineraryFiltered,
        MasterID
    )
)

This just returns this (given today's date is 12/27/2023) - only MasterID #1 with my current test data:

Then to concatenate the values of countries being traveled to (separated by a comma), here's what I have for the COUNTRY TO label in the gallery:

Concat(
    SortByColumns(
        Filter(
            'usm464-travelitinerary',
            MasterID = ThisItem.Value
        ),
        "Date_x0020_Outgoing_x0020_Depart",
        SortOrder.Ascending
    ),
    Country_x0020_TO,
    ", "
)

And this is what I have for the TRAVEL DATES label in the gallery:

With(
        {
            wTripID: Filter(
                SortByColumns(
                    'usm464-travelitinerary',
                    "Date_x0020_Outgoing_x0020_Depart",
                    SortOrder.Ascending
                ),
                MasterID = ThisItem.Value
            )
        },
        First(wTripID).Date_x0020_Outgoing_x0020_Depart & " - " & Last(wTripID).Date_x0020_Return_x0020_Arrival
)

I was all excited because this DOES work to display the data...until I realized that there's no way for me to now sort the gallery with 'Date Outgoing Departure' in descending order because the Items property of the gallery is only returning a "Value" column that has the MasterID values.

And if I wanted to add a filter option to allow someone to filter the gallery to see upcoming trips to a specific country, that also wouldn't be possible with my current set up.

What is the correct way for me to handle these requirements?? Any guidance would be greatly appreciated! Thanks in advance!

[WarrenBelz]

Hi @NickiPT ,

Firstly, this will sort before the values are grouped and also add a filter for your country  (xxxx is Value or the field name depending on your Items)

With(
   {
      wItineraryFiltered: 
      Sort(
         Filter(
            'usm464-travelitinerary',
            Status464 = "Submitted" &&
            'Date Return Arrival' > 
            DateAdd(
               Today(),
               -1,
               TimeUnit.Days
            ) &&
            (
               Len(YourCountryDropdown.Selected.xxxx) = 0 ||
               Country = YourCountryDropdown.Selected.xxxx
            )
         ),
         'Date Outgoing Departure',
         SortOrder.Descending
      )
   },
   GroupBy(
      wItineraryFiltered,
      "MasterID",
	  "Grouped"
   )
)

 Next your Countries can be

Concat(
    SortByColumns(
        ThisItem.Grouped,
        "Date_x0020_Outgoing_x0020_Depart",
        SortOrder.Ascending
    ),
    Country_x0020_TO,
    ", "
)

and your Dates

First(ThisItem.Grouped).Date_x0020_Outgoing_x0020_Depart & " - " & Last(ThisItem.Grouped).Date_x0020_Return_x0020_Arrival

You will find the last two far more efficient as previously, you were making two calls to the data source for every record (now is is all local)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[NickiPT]

@WarrenBelz - thank you very much! I hadn't been sure how to reference the data in the table within a GroupBy function so your answer is enlightening!

However, the results that are returned in my gallery are now only the Antarctica and Australia trips which are still within the bounds of the "Date Return Arrival is today or later" filter criteria. (And if I check it tomorrow it would only show Australia because we will be past 12/28/2023.) Because I need to pull in all legs of the ongoing trip to include ones that have already passed, it seems that I need two queries: one to get the MasterID of trips with ongoing leg(s), and then another to get ALL rows associated with that trip.

I played around with creating a collection OnVisible of the screen to get a list of the MasterIDs that meet the criteria but then using filter criteria on the gallery with "MasterID in colItineraryData.MasterID" has a delegation warning of course... 

[WarrenBelz]

@NickiPT ,

I was using your filter and assumed you wanted present and future items. I suggest you put in a Text Input formatted to numeric with a Default of 1 and allow the user to choose how many days in the past to show. To get rid of the Delegation warning and do this

With(
   {
      _Date:
      DateAdd(
         Today(),
         Value(YourDaysTextBox.Text),
         TimeUnit.Days
      )
   },
   With(
      {
         wItineraryFiltered: 
         Sort(
            Filter(
               'usm464-travelitinerary',
               Status464 = "Submitted" &&
               'Date Return Arrival' > _Date &&
               (
                  Len(YourCountryDropdown.Selected.xxxx) = 0 ||
                  Country = YourCountryDropdown.Selected.xxxx
               )
            ),
            'Date Outgoing Departure',
            SortOrder.Descending
         )
      },
      GroupBy(
         wItineraryFiltered,
         "MasterID",
         "Grouped"
      )
   )
)

BTW - I assume from the flight schedule you work for/with Air NZ over the ditch.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[NickiPT]

@WarrenBelz Ha... I wish I worked for Air NZ - that sounds it would be like a fun job!

The "current and future" filter is accurate but only to the extent that it is needed to determine which trips (not legs) need to be seen. For example:

Trip #1:

• France 12/20 - 12/25
• Germany 12/25 - 12/28
• Spain 12/28 - 1/3

I need this trip because while France and Germany are now in the past, the Spain leg is still current/future. We are not treating the trip's legs as separate info that has to be pulled - we need to see someone's entire international travel trip overview from start to finish. So I want to return MasterID #1 in this case and have one row for it like this:

MasterID	Countries	Travel Dates
1	France, Germany, Spain	12/20 - 1/3

A trip with ALL legs in the past would not need to be shown in my gallery in order to limit the amount of trips the management pulls into the app. (I plan to have another screen where they can search for a specific past trip using filters if needed.)

[WarrenBelz]

@NickiPT ,

Now this is getting complex - try this

With(
   {
      _Date:
      DateAdd(
         Today(),
         Value(YourDaysTextBox.Text),
         TimeUnit.Days
      )
   },
   With(
      {
         wItineraryFiltered: 
         Sort(
            Filter(
               'usm464-travelitinerary',
               Status464 = "Submitted" &&
               'Date Return Arrival' > _Date &&
               (
                  Len(YourCountryDropdown.Selected.xxxx) = 0 ||
                  Country = YourCountryDropdown.Selected.xxxx
               )
            ),
            'Date Outgoing Departure',
            SortOrder.Descending
         )
      },
      AddColumns(
         GroupBy(
            wItineraryFiltered,
            "MasterID",
            "Grouped"
         ),
         "Countries",
         Concat(
            Grouped,
            Country,
            ", "
         ),
         "Dates"
		 With(
		 {
            _Date1: 
            Sort(
               Grouped,
               'Date Outgoing Departure',
               SortOrder.Ascending
            ),
            _Date2: 
            Sort(
               Grouped,
               'Date Return Arrival',
               SortOrder.Descending
            )
         },
         Text(
            First(_Date1).'Date Outgoing Departure',
            "dd/mm/yyyy"
         ) & 
         " - " & 
         Text(
            First(_Date2).'Date Return Arrival',
            "dd/mm/yyyy"
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[NickiPT]

@WarrenBelz - It's yelling at me after I get past the With statements at the "AddColumns" section of the statement. I am signing off work right now and will tackle this again after the holiday (will post any solution if I am able to come up with it). Thanks for your continued assistance with this nightmare of a gallery and happy new year!

[WarrenBelz]

@NickiPT ,

I free-typed that without testing - strange but it did not like the Sort function inside the AddColumns, so I moved it outside. I updated the original rather than re-post all of that.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[NickiPT]

@WarrenBelz - Happy new year!

I was still having issues at the AddColumns part of the formula so I messed around with it all day and finally came up with something that seems to work. Please let me know if it seems that delegation will be an issue with this solution!

OnVisible of my screen (and on a "reload" icon on the screen as well):

//Get a unique list of the MasterIDs for ongoing and future trips where at least 1 trip leg is today or later
ClearCollect(
    colItineraryIDs,
    ShowColumns(
        Filter(
            'usm464-travelitinerary',
            Status464 = "Submitted" And 'Date Return Arrival' > DateAdd(
                Today(),
                -1,
                TimeUnit.Days
            )
        ),
        "MasterID"
    )
);

This currently returns 2 trips (I added one this morning):

Then the Items property of my gallery is:

Sort(
    With(
        {
            wItineraryFiltered: Sort(
                Filter(
                    'usm464-travelitinerary',
                    MasterID in colItineraryIDs[@MasterID] && (Len(cbFilter_Country.Selected.Name) = 0 || 'Country TO' = cbFilter_Country.Selected.Name)
                ),
                'Date Outgoing Departure',
                SortOrder.Ascending
            )
        },
        AddColumns(
            GroupBy(
                wItineraryFiltered,
                "MasterID",
                "Grouped"
            ),
            "Countries",
            Concat(
                Grouped,
                'Country TO',
                ", "
            ),
            "DateDeparture",
            First(Grouped.Date_x0020_Outgoing_x0020_Depart).'Date Outgoing Departure',
            "DateReturnArrival",
            Last(Grouped.Date_x0020_Return_x0020_Arrival).Date_x0020_Return_x0020_Arrival
        )
    ),
    DateDeparture,
    SortOrder.Descending
)

Which results in:

And then for the labels in my gallery I can now reference these values:

ThisItem.Countries

ThisItem.DateDeparture & " - " & ThisItem.DateReturnArrival

I am getting a delegation warning in the Items property where I am using "MasterID in colItineraryIDs[@MasterID]" -- but if my "colItineraryIDs" collection, which is filtered, is fewer than 500 results then I should be fine, right? (Please say yes, please say yes 😂)

[WarrenBelz]

Hi @NickiPT ,

Yes if you have fewer than 500 records in 'usm464-travelitinerary', then it should work. I tend to do the below for smaller lists as I do not like errors or warnings anywhere in my apps (so if one comes up, it is a real issue). This only masks the warning and does not make it Delegable.

With(
   {_Ininerary: 'usm464-travelitinerary'},
   Sort(
      With(
      {
         wItineraryFiltered: 
         Sort(
            Filter(
               _Ininerary,
               MasterID in colItineraryIDs[@MasterID]
            ),
            'Date Outgoing Departure',
            SortOrder.Ascending
         )
      },
      AddColumns(
         GroupBy(
            wItineraryFiltered,
            "MasterID",
            "Grouped"
         ),
         "Countries",
         Concat(
            Grouped,
            'Country TO',
            ", "
         ),
         "DateDeparture",
         First(Grouped.Date_x0020_Outgoing_x0020_Depart).'Date Outgoing Departure',
         "DateReturnArrival",
         Last(Grouped.Date_x0020_Return_x0020_Arrival).Date_x0020_Return_x0020_Arrival
      )
   ),
   DateDeparture,
   SortOrder.Descending
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps