cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PPSA
Resolver II
Resolver II

GroupBy error

Morning, I've posted this before but had no response and the thread has disappeared down many pages I imagine.

I have a screen with 3 separate galleries

2020-08-17 09_41_08-Goods In - Saved (Unpublished) - Power Apps.png
 Items property of each is - 

1 - GroupBy(SortByColumns(Filter('[dbo].[Lichfield_Transactions]',Sts="A"&&!IsBlank(BOOKING_REF)&&PICKUP_DATE>=DateAdd(Today(),-3,Days)),"PICKUP_DATE",Ascending),"BOOKING_REF","DATA")

2 - GroupBy(Gallery12.Selected.DATA,"Sales_Del_No","DATA2")

3 - Gallery16.Selected.DATA2

All works as expected whilst editing, but if I save the app and close, when I reopen gallery "2" (gallery16) will only show one entry rather than the multiple that i know there are.

To fix I change its items property to Gallery12.Selected.data then back to GroupBy(Gallery12.Selected.DATA,"Sales_Del_No","DATA2")

This is not useable though, why is this behaving like this, is it an error I have somewhere?

Desperate for some help on this please!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @WarrenBelz .

Thank's for your help here, managed to get it all working reliably again.

I used a series of Lookup's instead of First(xx. )in the end to get what I needed.




View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

Hi @PPSA ,

Firstly I can see a Delegation warning there caused by the !IsBlank and probably also the Date (I don't think it is Delegable in SQL). There is a workaround on this if the filter Sts-"A" produces less than 2,000 records (and you have your limit set to this in Advanced Settings). Try the code below if this is the case.

With(
   {
      vData:
      Filter(
         '[dbo].[Lichfield_Transactions]',
         Sts="A"
      )
   },
   GroupBy(
      SortByColumns(
         Filter(
            vData,
            !IsBlank(BOOKING_REF) &&
            DateDiff(
               PICKUP_DATE,
               Today()
               Days
            )=3
         ),
         "PICKUP_DATE",
         Ascending
      ),
      "BOOKING_REF",
      "DATA"
   )
)

 

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.

Hi @WarrenBelz , thanks for the advice,

My delegation warning has now gone, so thank you, I'll look into the With function to understand its use.

My second gallery is still behaving in the same way though, works as expected until I close and re open.

Thanks @PPSA ,

I firstly wanted to make sure that Delegation was not the issue. For more on the logic of With() in this context, I did a blog recently that may be of assistance to you.

Now to your issue. I assume in this code

GroupBy(
   Gallery12.Selected.DATA,
   "Sales_Del_No",
   "DATA2"
)

that Gallery12 is the other (main) gallery. The problem you face is that you cannot "pre-select" a gallery item by any other way other than physically clicking on it. I actually have the same issue with a "dashboard" app where when first opened, a form and a sub-gallery relating tot the form-item are blank. I "solve" it (more for user aesthetics) by "setting" the form Item to the ID of the first item in the collection forming the gallery.

I will have to let you think this through a bit, as you know the structure, but is there any way you can pick up the first record in your GroupBy function - you have to effectively set Gallery12.Selected.DATA without selecting the item in the gallery.

Please give me your thoughts.

 

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.

Thanks @WarrenBelz , I'll read your blog post in full later today.

Your assumption that gallery 12 was the main gallery is correct, I'll have a think about trying to achieve what you have suggested now.

Thank you

Hi @WarrenBelz ,

I've been looking at this today after a few days on other projects.

I've fixed the issue I had, the second gallery only showing one item instead of the list of grouped items.

I set a variable on navigating to the page - Set(fix,true);Navigate(xxxx

Then on this page I have Set(fix,false) on the onvisible property.

My second items gallery is now set to

If(fix=true,
Gallery12.Selected.DATA
,GroupBy(
Gallery12.Selected.DATA,
"Sales_Del_No",
"DATA2"
))


This seems to have worked, i consistently get all grouped items displaying as expected in this gallery.

Now it doesn't like my labels in here though.

PPSA_0-1597924953431.png

All labels are along the lines of - First(ThisItem.DATA2).Account , First(ThisItem.DATA2).Del_Name etc

If i change the items back to just the groupby formula all labels work, using it in the IF statement, even though the result is just the GroupBy function I'm getting this.

Any ideas?

Thank you!





Hi @PPSA ,

DATA is already a table and you are grouping by it again, so the First() item will still be a record. You need to "drill down" another level to find the value and you will probably have two First() statements in it. Do a couple of collections instead of the gallery filters and have a look what is inside them.

 

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.

Hi @WarrenBelz .

Thank's for your help here, managed to get it all working reliably again.

I used a series of Lookup's instead of First(xx. )in the end to get what I needed.




View solution in original post

Thanks @PPSA ,

Please consider Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,418)