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
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,767)