cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JR-BejeweledOne
Super User
Super User

Gallery with groupBy not allowing Results column

I have a gallery that shows filtered or filtered/grouped results based on the value of a Radio value.   It is displaying the information as it should.

 

I can't access the Results column though in the Gallery.    It looks like it's because the one selection is grouped and the other isn't.  I need to be able to access the Results column. 

 

Items Property on Gallery A

Switch(RadioSelectViewType.Selected.Value,

  "View by Region",

If(
   IsBlank(DropdownRegion2.Selected.Result),
      Sort(
    Filter(Holidays,
      Country = DropdownCountry2.Selected.Result && Year = Text(varYear)), 
       HolidayDate, Ascending
       ),

       !IsBlank(DropdownRegion2.Selected.Result), 
       Sort(
    Filter(Holidays,
      Country = DropdownCountry2.Selected.Result && Region = DropdownRegion2.Selected.Result && Year = Text(varYear)), 
       HolidayDate, Ascending
       )
),

"View All",

   Sort(
         GroupBy(
            Filter(Holidays,
             Country = DropdownCountry2.Selected.Result && Year = Text(varYear)),
             "HolidayName", "HolidayDate", "Details"),
            HolidayDate, SortOrder.Ascending
       )
   )

 

CalendarGallery.jpg

 

In the image, the yellow color is the default color for a date that has a holiday.    In this image, this date would be a different color as 3 different Holidays are on this date in different regions.  And hovering over the item in Gallery B would popup a ToolTip with the region(s) for that specific holiday.

 

I don't want to use collections unless necessary, too much overhead for this.

 

Goals - #'s 1 & 2 are accomplished.   #3 is partially accomplished.

  1. Show all the holidays in a selected country for the current year
    1. Tooltip and Fill for the Gallery B items based on matching dates in the Gallery A
  2. Show all the Holidays by a selected country and region pair for the current year
    1. Tooltip and Fill for the Gallery B based on matching dates in the Gallery A
  3. Show all the Holidays for a selected country that has multiple regions, grouped by holiday name (some regions may celebrate different holidays on the same date)
    1. In Gallery B, Fill - Color A (Holiday is in All regions), Color B (Holiday is in subset of regions) based on query to Gallery A
    2. Gallery A Tooltip - Show region or Regions (using Concat with Char(10) to make a list) for the specific Holiday.

   



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
1 ACCEPTED SOLUTION

Accepted Solutions

I have partially solved it like this.

 

2 Galleries, one for the Grouped Holidays and one for the Non-grouped holidays.  Visibility controlled by the Selection in the Checkbox.   I decided to use a checkbox instead of a radio control.

 

For the Tooltips and Fill properties, I query the data source instead of results.  Results wouldn't give me the correct information due to the possibility of duplicate dates with different names.

 

Tooltips: 

 

Switch(CheckboxSelectAllRegions.Value,

false,

LookUp(GalleryHolidays.AllItems, HolidayDate = DateValue(SubtitleDates.Text)).HolidayName,

true,

Concat(
    Filter(Holidays, HolidayDate = DateValue(SubtitleDates.Text)),
    Concatenate(Region, Region & Char(10))
   )

)

 

 

Edit:  This was solved in another thread (Thanks, @RandyHayes), see below. 

 

However, I get this duplication of regions in the tooltip.

 

Concatissue.jpg

Switch(CheckboxSelectAllRegions.Value,

false,

LookUp(GalleryHolidays.AllItems, HolidayDate = DateValue(SubtitleDates.Text)).HolidayName,

true,

Concat(
    Filter(Holidays, HolidayDate = DateValue(SubtitleDates.Text)),
    Region & Char(10)
   )

)

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

View solution in original post

3 REPLIES 3
RandyHayes
Super User
Super User

@JR-BejeweledOne 

Your Items property is creating two different schemas.  You cannot do that on a Gallery.  The schema needs to be identical.  Thus why it is not good practice to put If or Switch statements in your Items property that return multiple paths of datasources.

My suggestion would be to utilize Gallery A as your primary list of holidays.  I don't know the columns or values you are trying to have in the gallery, but in this case I am choosing the HolidayDate and Region as the grouping.  The Items property would be set to:

Filter(
    GroupBy(
        Sort(
            Filter(Holidays,
                Country = DropdownCountry2.Selected.Result && 
                Year = Text(varYear)
            ), 
            HolidayDate, Ascending
        ), 
        "HolidayDate", "Region", "_records"
    ),
    (IsBlank(DropdownRegion2.Selected.Result) ||
     RadioSelectViewType.Selected.Value = "View All" || 
     Region = DropdownRegion2.Selected.Result
    )
)

This takes into account the radio selection as well as the region selection.

From this you can derive your tool tips for all the place you need (from the _records column of the gallery).

 

However, I am not clear on your #3 of how you envisioned grouping the holiday and the regions all in the same gallery that you showed - that would not be easily done as they are two different record structures.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
JR-BejeweledOne
Super User
Super User

Gallery A is the primary list of Holidays.   The choices "View All" and "View by Region" determine how the holidays are displayed. 

 

I only need the grouped holidays if "View All" is selected.    There are only a few countries with varying holiday schedules in different regions, cities or facilities and that list can be quite long.   We have a couple countries with 16 or 18 different schedules.  "View by Region" is the default setting.   Gallery A shows the list of holidays for the selected country or selected country/region which contain no duplicate names or dates.

 

When "View All" is selected, Gallery A will show the holidays for the entire country (all regions).  Many of those are common holidays across the regions.   Some will be different holidays on the same date, so they are grouped by Name and not date.   This way the gallery will show a list of unique holiday names and the dates they fall on.  There will be some duplicate dates in this list.

 

Gallery B will have a different color fill for the holidays that are commonly shared between multiple regions and the default fill for those holidays that are only in a single region.

 

As I am writing this, I think that a simple solution would be 2 galleries with the visible property controlled by the "View All" and "View by Region" selections.   This would eliminate the schema issue.  



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

I have partially solved it like this.

 

2 Galleries, one for the Grouped Holidays and one for the Non-grouped holidays.  Visibility controlled by the Selection in the Checkbox.   I decided to use a checkbox instead of a radio control.

 

For the Tooltips and Fill properties, I query the data source instead of results.  Results wouldn't give me the correct information due to the possibility of duplicate dates with different names.

 

Tooltips: 

 

Switch(CheckboxSelectAllRegions.Value,

false,

LookUp(GalleryHolidays.AllItems, HolidayDate = DateValue(SubtitleDates.Text)).HolidayName,

true,

Concat(
    Filter(Holidays, HolidayDate = DateValue(SubtitleDates.Text)),
    Concatenate(Region, Region & Char(10))
   )

)

 

 

Edit:  This was solved in another thread (Thanks, @RandyHayes), see below. 

 

However, I get this duplication of regions in the tooltip.

 

Concatissue.jpg

Switch(CheckboxSelectAllRegions.Value,

false,

LookUp(GalleryHolidays.AllItems, HolidayDate = DateValue(SubtitleDates.Text)).HolidayName,

true,

Concat(
    Filter(Holidays, HolidayDate = DateValue(SubtitleDates.Text)),
    Region & Char(10)
   )

)

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,318)